MySQL快速创建1000条数据

#创建表t_user，这是用于存放数据的表
CREATE TABLE IF NOT EXISTS `t_user`
(
    `id`            int(11)     NOT NULL AUTO_INCREMENT,
    `c_user_id`     varchar(36) NOT NULL DEFAULT '',
    `c_name`        varchar(22) NOT NULL DEFAULT '',
    `c_province_id` int(11)     NOT NULL,
    `c_city_id`     int(11)     NOT NULL,
    `create_time`   datetime    NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

#创建内存表t_user_memory，这是用于快速插入数据的表
CREATE TABLE IF NOT EXISTS `t_user_memory`
(
    `id`            int(11)     NOT NULL AUTO_INCREMENT,
    `c_user_id`     varchar(36) NOT NULL DEFAULT '',
    `c_name`        varchar(22) NOT NULL DEFAULT '',
    `c_province_id` int(11)     NOT NULL,
    `c_city_id`     int(11)     NOT NULL,
    `create_time`   datetime    NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = MEMORY
  DEFAULT CHARSET = utf8mb4;

# 随机字符串函数
DROP FUNCTION IF EXISTS RAND_STR;
DELIMITER $$
CREATE
    FUNCTION `RAND_STR`(n INT)
    RETURNS varchar(255) NO SQL
    COMMENT 'rand string function'
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n
        DO
            SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END $$

# 随机时间函数
DROP FUNCTION IF EXISTS RAND_TIME;
DELIMITER $$
CREATE
    FUNCTION `RAND_TIME`(sd DATETIME, ed DATETIME)
    RETURNS DATETIME NO SQL
    COMMENT 'rand time function'
BEGIN
    DECLARE
        sub INT DEFAULT 0;
    DECLARE
        ret DATETIME;
    SET sub = ABS(UNIX_TIMESTAMP(ed) - UNIX_TIMESTAMP(sd));
    SET ret = DATE_ADD(sd,INTERVAL FLOOR(1 + RAND() * (sub - 1)) SECOND);
    RETURN ret;
END $$

# 存储过程函数
DROP PROCEDURE IF EXISTS ADD_T_USER_MEMORY;
DELIMITER $$
CREATE
    PROCEDURE ADD_T_USER_MEMORY(IN n INT)
BEGIN
    DECLARE
        i INT DEFAULT 1;
    WHILE i < n
        DO
            INSERT INTO t_user_memory (c_user_id,
                                       c_name,
                                       c_province_id,
                                       c_city_id,
                                       create_time)
            VALUES (uuid(),
                    RAND_STR(20),
                    FLOOR(RAND() * 1000),
                    RAND() * 100,
                    NOW());
            SET i = i + 1;
        END WHILE;
END $$

# 调用函数
CALL ADD_T_USER_MEMORY(1000);

# 从内存表中插入到实际表中
INSERT INTO t_user SELECT * FROM t_user_memory;

# 查看实际表的数据
SELECT COUNT(id) FROM t_user;

# 删除内存表
DROP TABLE IF EXISTS t_user_memory;