MySQL插入1000万条数据技巧

以下是关于如何在MySQL中创建1KW数据量的数据库、表和存储过程的详细指南。我们将使用SQL语句来实现这一目标，并保持原文的语言和结构，同时对内容进行伪原创处理。

首先，我们将创建一个名为bigData的数据库，并在其中创建两个表：dept（部门表）和emp（员工表）。然后，我们将定义两个存储函数和两个存储过程来插入大量数据。

创建数据库和表

-- 创建1KW数据量的数据库
CREATE DATABASE bigData;
-- 部门表
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '部门id',
deptno MEDIUMINT(9) NOT NULL DEFAULT '0' COMMENT '部门编号',
dname VARCHAR(20) NOT NULL DEFAULT '' COMMENT '部门名称',
loc VARCHAR(14) NOT NULL DEFAULT '' COMMENT '楼层',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=600001 DEFAULT CHARSET=utf8;
-- 员工表
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '员工主键id',
empno MEDIUMINT(11) NOT NULL DEFAULT '0' COMMENT '员工编号',
ename VARCHAR(20) NOT NULL COMMENT '员工姓名',
job VARCHAR(9) NOT NULL DEFAULT '' COMMENT '工作',
mgr MEDIUMINT(9) NOT NULL DEFAULT '0' COMMENT '上级编号',
hiredate DATE NOT NULL COMMENT '入职时间',
sal DECIMAL(7,2) DEFAULT NULL COMMENT '薪水',
comm MEDIUMINT(9) NOT NULL DEFAULT '0' COMMENT '红利',
deptno MEDIUMINT(9) NOT NULL DEFAULT '0' COMMENT '部门编号',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=12387608 DEFAULT CHARSET=utf8;

创建存储函数

接下来，我们定义一个存储函数rand_string，用于生成指定长度的随机字符串。

-- 创建一个生成指定长度随机字符串的函数
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND()  52), 1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
DELIMITER ;

创建存储过程

我们将定义两个存储过程来向emp和dept表中插入大量数据。

-- 定义存储过程，用于向emp表中插入大量数据
-- start 开始数，max_length 最大长度
DELIMITER $$
CREATE PROCEDURE insert_emp(IN start INT(10), IN max_length INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES ((start+i), rand_string(6), 'SALESMAN', 0001, CURDATE(), FLOOR(RAND()  10000), 400, FLOOR(RAND() * 100));
UNTIL i = max_length END REPEAT;
COMMIT;
END $$
DELIMITER ;
-- 向emp表中插入1000万条数据
CALL insert_emp(1000, 10000000);
-- 定义存储过程，用于向dept表中插入大量数据
-- start 开始数，max_length 最大长度
DELIMITER $$
CREATE PROCEDURE insert_dept(IN start INT(10), IN max_length INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno, dname, loc)
VALUES (FLOOR(RAND() * 1000), rand_string(10), rand_string(8));
UNTIL i = max_length END REPEAT;
COMMIT;
END $$
DELIMITER ;
-- 向dept表中插入10万条数据
CALL insert_dept(1000, 100000);

通过以上步骤，我们成功创建了数据库、表，并定义了存储函数和存储过程来插入大量数据。这种方法可以有效地生成大规模的数据集，用于测试和开发目的。