创建表
create table user_info_large (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`account` VARCHAR(20) NOT NULL COMMENT '用户账号',
`name` VARCHAR(20) NOT NULL COMMENT '用户名',
`password` VARCHAR(20) not null COMMENT '用户密码',
`area` VARCHAR(20) NOT NULL COMMENT '用户地址',
`signature` VARCHAR(50) not null COMMENT '个性签名',
PRIMARY KEY (`id`) COMMENT '主键',
UNIQUE (`account`) COMMENT '唯一索引',
KEY `index_area_signture` (`area`, `signature`) COMMENT '组合索引'
);
存储过程
SET @num_rows = 2000000;
SET @batch_size = 1000;
DELIMITER
CREATE PROCEDURE GenerateAndInsertTestData()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE username VARCHAR(255);
DECLARE email VARCHAR(255);
WHILE i < @num_rows DO
START TRANSACTION;
INSERT_LOOP: LOOP
SET username = CONCAT('user', i);
SET email = CONCAT(username, '@example.com');
INSERT INTO users (username, email) VALUES (username, email);
SET i = i + 1;
IF i % @batch_size = 0 THEN
COMMIT;
LEAVE INSERT_LOOP;
END IF;
END LOOP;
COMMIT;
END WHILE;
END;
DELIMITER ;
CALL GenerateAndInsertTestData();