WSL操作指南

MySQL创建一千万条测试数据，用于瞎捣鼓

1.创建User表：

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(222) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '地址',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX idx_age (age) COMMENT '年龄索引',
  INDEX idx_name (name) COMMENT '姓名索引'
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2. 创建存储过程

DROP PROCEDURE IF EXISTS pro_users ;
CREATE PROCEDURE pro_users()
BEGIN
    DECLARE count int  DEFAULT 0 ; -- 计数
	DECLARE max int ; -- 插入数据条数
	DECLARE name_length INT;
    DECLARE first_name VARCHAR(2);
    DECLARE last_name VARCHAR(4);
    DECLARE age INT;
    DECLARE city VARCHAR(16);
    DECLARE addr VARCHAR(128);
	SET max = 10000000 ;

	-- 开始执行循环
	WHILE count < max  DO
        SET name_length = ROUND(RAND() * 1) + 2;
        SET first_name = SUBSTRING('赵钱孙李周吴郑王冯陈唐卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜董谢邹丁柏水薛章云苏潘葛石范彭郎鲁韦昌马苗凤花方俞任袁柳乔岑程邱秋顾梅盛林刁钟徐高夏蔡田姚谭纪程', FLOOR(RAND() * 49) + 1, 1);
        SET last_name = SUBSTRING('一二三四五六七八九十', FLOOR(RAND() * 10) + 1, name_length - 1);
        SET age = FLOOR(RAND() * 100) + 1;
        SET city = CASE FLOOR(RAND() * 10)
        WHEN 0 THEN '北京'
        WHEN 1 THEN '上海'
        WHEN 2 THEN '广州'
        WHEN 3 THEN '深圳'
        WHEN 4 THEN '重庆'
        WHEN 5 THEN '天津'
        WHEN 6 THEN '苏州'
        WHEN 7 THEN '南京'
        WHEN 8 THEN '杭州'
        WHEN 9 THEN '成都'
        END;

        SET addr = CONCAT(city, '市', 
        CASE FLOOR(RAND() * 4)
        WHEN 0 THEN '东'
        WHEN 1 THEN '南'
        WHEN 2 THEN '西'
        WHEN 3 THEN '北'
        END, '区', 
        CASE FLOOR(RAND() * 4)
        WHEN 0 THEN '东'
        WHEN 1 THEN '南'
        WHEN 2 THEN '西'
        WHEN 3 THEN '北'
        END, '县',
        CASE FLOOR(RAND() * 4)
        WHEN 0 THEN '东'
        WHEN 1 THEN '南'
        WHEN 2 THEN '西'
        WHEN 3 THEN '北'
        END, '镇',
        CASE FLOOR(RAND() * 10)
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        WHEN 2 THEN 'C'
        WHEN 3 THEN 'D'
        WHEN 4 THEN 'E'
        WHEN 5 THEN 'F'
        WHEN 6 THEN 'G'
        WHEN 7 THEN 'H'
        WHEN 8 THEN 'I'
        WHEN 9 THEN 'J'
        END, '小区', 
        FLOOR(RAND() * 20) + 1, '号楼', 
        FLOOR(RAND() * 20) + 1, '单元', 
        FLOOR(RAND() * 5) + 1, '0', FLOOR(RAND() * 3) + 1, '室');

		INSERT INTO `users` ( `name`, `age`, `addr`, `create_date` )
		VALUES	(CONCAT(first_name, last_name), age, addr, NOW() );			
    	SET count = count + 1; 
	END WHILE;
	
END ;

call pro_users();

Refer

抄袭自
MySQL深分页场景下的性能优化

---

转载无需注明来源，放弃所有权利