Mysql批量新增測試資料

1、去掉主鍵自動遞增，去掉主鍵

2、批量生成資料

insert into sys_user (select * from sys_user);
insert into '表名' (select * from '表名');
           

3、生成資料後修改主鍵

set @idl = 0;
UPDATE sys_user set duleId = (@idl := @idl + 1)
###duleId 對應修改的主鍵字段
           

4、設定主鍵，設定自動遞增

delimiter //

DROP PROCEDURE IF EXISTS proc_batch_insert;

CREATE PROCEDURE proc_batch_insert()

BEGIN

DECLARE pre_name BIGINT;

DECLARE ageVal INT;

DECLARE i INT;

SET pre_name=187635267;

SET ageVal=100;

SET i=1;

WHILE i <= 1000000 DO

        INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)%30,NOW(),NOW());

SET pre_name=pre_name+100;

SET i=i+1;

END WHILE;

END //

delimiter ;

call proc_batch_insert();