mysql快速生成100万条测试数据脚本（InsCode AI 创作助手）

1、mysql快速生成100万条测试数据存储过程。

CREATE DEFINER=`root`@`%` PROCEDURE `generate_employee_data`(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE employee_name VARCHAR(50);
  DECLARE employee_number VARCHAR(10);
  DECLARE gender ENUM('男', '女');
  DECLARE phone_number VARCHAR(11);
  DECLARE highest_education ENUM('初中', '高中', '大专', '本科', '硕士', '博士');
  DECLARE department_id INT;
	
  DECLARE org_id INT;
	DECLARE dept_id VARCHAR(10);
	DECLARE SEX VARCHAR(10);
	DECLARE job VARCHAR(10);
	DECLARE status VARCHAR(10);
	DECLARE created_id  VARCHAR(10);
	DECLARE created_by VARCHAR(10);
	DECLARE updated_id VARCHAR(10);
	DECLARE updated_by VARCHAR(10);
	DECLARE ca_uuid VARCHAR(10);
	
  WHILE i < n DO
    SET employee_name = CONCAT('员工', i+1);
    SET employee_number = CONCAT('EMP', LPAD(i+1, 7, '0'));
    SET gender = IF(RAND() > 0.5, '男', '女');
    SET phone_number = CONCAT('1', LPAD(FLOOR(RAND() * 100000000), 8, '0'));
    SET highest_education = IF(RAND() > 0.5, NULL, 
                                CASE FLOOR(RAND() * 6) 
                                    WHEN 0 THEN '初中' 
                                    WHEN 1 THEN '高中' 
                                    WHEN 2 THEN '大专' 
                                    WHEN 3 THEN '本科' 
                                    WHEN 4 THEN '硕士' 
                                    ELSE '博士' 
                                END);
    SET department_id = FLOOR(RAND() * 5) + 1;
		SET org_id =1;
		SET dept_id=16277;
    SET SEX = 602;
		SET job = 809;
		SET status = 0;
		SET created_id = 1;
		SET created_by = '系统管理员';
		SET updated_id = 1;
		SET updated_by = '系统管理员';
		
    INSERT INTO employees (employee_name, employee_number, gender, phone_number, highest_education, department_id) VALUES
    (employee_name, employee_number, gender, phone_number, highest_education, department_id);
    
    SET i = i + 1;
  END WHILE;
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

2、执行存储过程

-- 生成10条数据，可根据实际情况修改
call generate_test_data(10);
1
2

3、附脚本对应测试表创建脚本（协助调试）

CREATE TABLE `employees` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_name` varchar(50) COLLATE utf8_bin NOT NULL,
  `employee_number` varchar(10) COLLATE utf8_bin NOT NULL,
  `gender` enum('男','女') COLLATE utf8_bin NOT NULL,
  `phone_number` varchar(11) COLLATE utf8_bin NOT NULL,
  `highest_education` enum('初中','高中','大专','本科','硕士','博士') COLLATE utf8_bin DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1100001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1
2
3
4
5
6
7
8
9
10