mysql插入千万条记录笔记 2019年6月5日
Linux
USE test;
DROP TABLE IF EXISTS student;
CREATE TABLE `student` (
`stu_id` INT(11) NOT NULL AUTO_INCREMENT,
`stu_name` CHAR(30) NOT NULL,
`stu_sex` CHAR(2) NOT NULL,
`stu_telephone` CHAR(11) DEFAULT NULL,
PRIMARY KEY (`stu_id`)
);
— 创建姓名函数
DELIMITER $$
DROP FUNCTION IF EXISTS rang_string $$
CREATE
FUNCTION `test`.`rang_string`()
RETURNS VARCHAR(6)
BEGIN
DECLARE chars_str NVARCHAR(50) DEFAULT ‘赵钱孙李周吴郑王高乔孟姜浮沉董工好啊的是和额发的我’;
DECLARE return_str VARCHAR(6) DEFAULT ”;
DECLARE i INT DEFAULT 0;
DECLARE n INT;
SET n=ROUND(2+RAND(),0);
WHILE i < n
DO
SET return_str=CONCAT(return_str,SUBSTRING(chars_str,ROUND(RAND()*25,0),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END$$
DELIMITER ;
— 姓名
SELECT test.rang_string();
— 性别
SELECT IF(RAND()>0.5,’男’,’女’)
— 手机
SELECT IF(RAND()<0.5,13000000000,15000000000)+ROUND(RAND()*100000000,0)
— 插入数据存储
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_student $$
CREATE PROCEDURE `test`.`sp_student` (n INT)
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < n DO
INSERT INTO test.student SELECT NULL,`test`.`rang_string`(),IF(RAND()>0.5,’男’,’女’),IF(RAND()<0.5,13000000000,15000000000)+ROUND(RAND()*100000000,0);
SET i = i +1;
END WHILE;
END$$
DELIMITER;
–调用存储过程
call sp_student(1000000);