添加海量数据通用脚本

DELIMITER $$

CREATE PROCEDURE GenerateRecords(IN total_records INT, IN batch_size INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE random_name VARCHAR(50);
    DECLARE random_email VARCHAR(50);
    DECLARE random_age INT;
    DECLARE current_time DATETIME;

    -- 创建插入数据的循环
    WHILE i < total_records DO
        SET @insert_query = CONCAT('INSERT INTO your_table (id, name, email, age, created_at) VALUES ');

        -- 构建批量插入的值
        SET @values = '';
        WHILE (i < total_records AND CHAR_LENGTH(@values) + 100 < 100000) DO
            SET random_name = CONCAT('User', i);  -- 生成用户名
            SET random_email = CONCAT('user', i, '@example.com');  -- 生成邮箱
            SET random_age = FLOOR(18 + RAND() * 50);  -- 随机生成年龄（18到67岁）
            SET current_time = NOW();  -- 当前时间

            SET @values = CONCAT(@values, '(', i, ', ''', random_name, ''', ''', random_email, ''', ', random_age, ', ''', current_time, '''),');
            SET i = i + 1;
        END WHILE;

        -- 去掉最后的逗号
        SET @values = LEFT(@values, CHAR_LENGTH(@values) - 1);
        
        -- 完整的插入查询
        SET @insert_query = CONCAT(@insert_query, @values);
        PREPARE stmt FROM @insert_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 输出插入的记录数
        SELECT CONCAT('Inserted ', i, ' records so far.');
    END WHILE;
END $$

DELIMITER ;

添加海量数据user案例

创建数据库

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL COMMENT '用户名',
  `password` varchar(128) NOT NULL DEFAULT '' COMMENT '密码',
  `nickname` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
  `mobile` varchar(16) NOT NULL DEFAULT '' COMMENT '手机号',
  `wechat_code` varchar(64) NOT NULL DEFAULT '' COMMENT '微信号',
  `profession` varchar(64) NOT NULL DEFAULT '' COMMENT '职业',
  `gender` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别0未知1男2女',
  `head_img` varchar(256) NOT NULL DEFAULT '' COMMENT '头像',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `username` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户表';

为了保 random_username 在插入过程中是唯一的，我们可以采取以下两种策略：

1. 使用随机数或时间戳: 在用户名中增加随机数或时间戳，以确保它的唯一性。
2. 检查数据库中已有的用户名: 在生成新用户名时，检查当前数据库中是否已存在相同的用户名。

SQL 存储过程示例（确保唯一性）

DELIMITER $$

CREATE PROCEDURE GenerateUserRecords(IN total_records INT, IN batch_size INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE random_username VARCHAR(32);
    DECLARE random_password VARCHAR(128);
    DECLARE random_nickname VARCHAR(32);
    DECLARE random_mobile VARCHAR(16);
    DECLARE random_wechat_code VARCHAR(64);
    DECLARE random_profession VARCHAR(64);
    DECLARE random_gender TINYINT;

    -- 开始事务
    START TRANSACTION;

    -- 创建插入数据的循环
    WHILE i < total_records DO
        SET @insert_query = CONCAT('INSERT INTO user (username, password, nickname, mobile, wechat_code, profession, gender, created_at, updated_at) VALUES ');

        -- 构建批量插入的值
        SET @values = '';
        WHILE (i < total_records AND CHAR_LENGTH(@values) + 200 < 100000) DO
            -- 生成唯一用户名
            SET random_username = CONCAT('user', i, FLOOR(RAND() * 10000));  -- 在用户名后加上随机数

            SET random_password = CONCAT('password', i);  -- 生成密码
            SET random_nickname = CONCAT('Nickname', i);  -- 生成昵称
            SET random_mobile = CONCAT('138', LPAD(FLOOR(RAND() * 100000000), 8, '0'));  -- 生成手机号码
            SET random_wechat_code = CONCAT('wechat', i);  -- 生成微信号
            SET random_profession = CONCAT('Profession', FLOOR(RAND() * 10));  -- 生成职业
            SET random_gender = FLOOR(RAND() * 3);  -- 随机生成性别（0到2）

            -- 直接使用 NOW() 函数
            SET @values = CONCAT(@values, '(', '''', random_username, '''', ', ''', random_password, ''', ''', random_nickname, ''', ''', random_mobile, ''', ''', random_wechat_code, ''', ''', random_profession, ''', ', random_gender, ', NOW(), NOW()),');
            SET i = i + 1;
        END WHILE;

        -- 去掉最后的逗号
        SET @values = LEFT(@values, CHAR_LENGTH(@values) - 1);
        
        -- 完整的插入查询
        SET @insert_query = CONCAT(@insert_query, @values);
        PREPARE stmt FROM @insert_query;
        
        -- 执行插入
        BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            BEGIN
                -- 捕获并忽略重复错误
                SET @error_message = 'Duplicate entry for username, skipping batch.';
            END;

            EXECUTE stmt;
        END;

        DEALLOCATE PREPARE stmt;

        -- 输出插入的记录数
        SELECT CONCAT('Inserted ', i, ' records so far.');
    END WHILE;

    -- 提交事务
    COMMIT;
END $$

DELIMITER ;

改动说明

1. 唯一性生成:

   • 在生成 random_username 时，使用 CONCAT('user', i, FLOOR(RAND() * 10000)) 来确保每个用户名都是唯一的。i 是循环计数器，而 FLOOR(RAND() * 10000) 会生成一个0到9999之间的随机数。

2. 异常处理:

   • 保留了之前的异常处理逻辑以捕获可能的SQL异常，但在生成用户名时，通过添加随机数的方式，减少了重复的可能性。

使用示例

• 创建存储过程后，可以通过以下命令调用它生成记录：

CALL GenerateUserRecords(5000000, 10000);

删除表所有数据脚本

-- 禁用外键检查（如果没有外键依赖）
SET FOREIGN_KEY_CHECKS = 0;

-- 批量删除数据
DELIMITER $$

CREATE PROCEDURE batch_delete_users(IN batch_size INT)
BEGIN
    DECLARE rows_affected INT DEFAULT 1;

    WHILE rows_affected > 0 DO
        DELETE FROM `user`
        WHERE id IN (
            SELECT id FROM (
                SELECT id FROM `user` LIMIT batch_size
            ) AS temp_ids
        );
        SET rows_affected = ROW_COUNT();
    END WHILE;
END$$

DELIMITER ;

CALL batch_delete_users(10000);

-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 优化表
OPTIMIZE TABLE `user`;