mysql数据库生成百万计测试数据脚本

# 1.创建数据库
CREATE DATABASE test charset=utf8mb4;
USE test;
# 2.创建表
CREATE TABLE User (
    userId INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    registrationDate DATETIME NOT NULL,
    lastLogin DATETIME,
    createTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP -- 更新时间
);
# 3.插入测试数据
INSERT INTO test.`User` (username, email, registrationDate, lastLogin) VALUES
('JohnDoe01', 'john.doe01@example.com', '2023-02-01 08:00:00', '2023-02-02 09:00:00'),
('JaneDoe02', 'jane.doe02@example.com', '2023-02-02 10:00:00', '2023-02-03 11:00:00'),
('MikeSmith03', 'mike.smith03@example.com', '2023-02-03 12:00:00', '2023-02-04 13:00:00'),
('LucyBrown04', 'lucy.brown04@example.com', '2023-02-04 14:00:00', '2023-02-05 15:00:00'),
('DavidWilson05', 'david.wilson05@example.com', '2023-02-05 16:00:00', '2023-02-06 17:00:00'),
('LindaTaylor06', 'linda.taylor06@example.com', '2023-02-06 18:00:00', '2023-02-07 19:00:00'),
('RobertJones07', 'robert.jones07@example.com', '2023-02-07 20:00:00', '2023-02-08 21:00:00'),
('PatriciaWhite08', 'patricia.white08@example.com', '2023-02-08 22:00:00', '2023-02-09 23:00:00'),
('MichaelHarris09', 'michael.harris09@example.com', '2023-02-09 08:30:00', '2023-02-10 09:30:00'),
('SarahMartin10', 'sarah.martin10@example.com', '2023-02-10 10:30:00', '2023-02-11 11:30:00');

# 4.批量插入100w数据
# 4.1 创建存储过程
DELIMITER $$
CREATE PROCEDURE InsertUsers()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000000 DO
    INSERT INTO User (username, email, registrationDate, lastLogin) VALUES 
    (CONCAT('User', LPAD(i, 7, '0')), CONCAT('user', LPAD(i, 7, '0'), '@example.com'), NOW(), NOW());
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
# 4.2 调用存储过程，生成100w用户数据
CALL InsertUsers();