mysql--构造数据、导入导出(load)

上一篇 / 下一篇  2018-08-30 18:28:45 / 个人分类:mysql

一、mysql造数据脚本


DELIMITER $$
DROP PROCEDURE IF EXISTS `test3`$$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    
        PROCEDURE `test`.`test3`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
 DECLARE i INT DEFAULT 1;
    WHILE i<=10000 DO
        SET @firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐史贺何罗',FLOOR(1+25*RAND()),1);
SET @name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励国达雅蕾',ROUND(1+47*RAND()),1);
SET @namefull = CONCAT(@firstname,@name1);
SET @laowu=RAND();
INSERT INTO test3 (
  test3.zj,
  test3.NAME,
  test3.createdate,
  test3.UPDATE,
  laowu
) VALUES(1+i,@namefull,'2018-8-26 16:46:07', '2018-8-26 16:46:13',@laowu);
        SET i = i+1;
    END WHILE;
    END$$

DELIMITER ;

call test3();


二、导出到文件脚本

linux:
//导出10000执行耗时   : 0.023 sec
SELECT * 
INTO OUTFILE "/var/lib/mysql/test/test1.txt"
LINES TERMINATED BY "\r\n"
FROM test1 

windows:
SELECT * 
INTO OUTFILE "D:/zqctest/test/test1.txt"
LINES TERMINATED BY "\r\n"
FROM ex_zqc2018091003_v1;



三、导入到数据库脚本
//导入10000执行耗时   : 0.871 sec
LOAD DATA LOCAL INFILE 'D:/RTCK/临时/08/0830/test1.txt'
INTO TABLE test2 (
  test2.zj,
  test2.NAME,
  test2.createdate,
  test2.UPDATE,
  laowu);


四、导出问题解决:
错误代码: 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this 

my.ini 文件加入:
secure_file_priv=''

注:windows中,隐藏文件名后面有txt


TAG:

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar