mysql--构造数据、导入导出(load)
上一篇 /
下一篇 2018-08-30 18:28:45
/ 个人分类：mysql
DELIMITER $$
DROP PROCEDURE IF EXISTS `test3`$$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `test`.`test3`()
| [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:
