mysql数据库生成百万计测试数据脚本 Published on Apr 5, 2024 in 随笔 with 0 comment ```sql # 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(); ```  本文由 admin 创作,采用 知识共享署名4.0 国际许可协议进行许可。本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名。