??

说明

本文描述问题及解决方法同样适用于 腾讯云 云数据库 MySQL（TencentDB for MySQLx8seo，CDB）。

背景

在进行查询等操作的验证时x8seo，我们经常需要在线下环境构建大量的基础数据供我们测试x8seo，模拟线上的真实环境。

构建数据

这里我们快速构建一份测试数据，用来模拟实际生产中量级在100万的一张数据表。

创建测试库及基础表MySQL [(none)] CREATE DATABASE dts_demo;Query OK, 1 row affected (0.00 sec)MySQL [(none)] USE dts_demo;Database changedMySQL [dts_demo] CREATE TABLE `user_info` ( - `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`), - KEY `idx_user_id` (`c_user_id`)+WX:machinegunjoe666免费领取资料 - ) ENGINE=InnoDB;Query OK, 0 rows affected (0.01 sec)创建内存表

利用 MySQL 内存表插入速度快的特点，我们先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中。

MySQL [dts_demo] CREATE TABLE `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`), - KEY `idx_user_id` (`c_user_id`) - ) ENGINE=MEMORY;Query OK, 0 rows affected (0.00 sec)创建函数

创建随机字符串和随机时间的函数

MySQL [dts_demo] delimiter $$MySQL [dts_demo] MySQL [dts_demo] CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4 - DETERMINISTIC - BEGIN - DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' - DECLARE return_str varchar(255) DEFAULT '' ; - DECLARE i INT DEFAULT 0; - WHILE i SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1)); - SET i = i + 1; - END WHILE; - RETURN return_str; - END$$Query OK, 0 rows affected (0.00 sec)+wxx8seo：machinegunjoe666免费领取资料MySQL [dts_demo] CREATE DEFINER=`root`@`%` FUNCTION `randDataTime`(sd DATETIME,ed DATETIME) RETURNS datetime - DETERMINISTIC - 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 $$Query OK, 0 rows affected (0.00 sec)创建存储过

创建插入数据的存储过程

MySQL [dts_demo] CREATE DEFINER=`root`@`%` PROCEDURE `add_user_memory`(IN n int) - BEGIN - DECLARE i INT DEFAULT 1; - WHILE (i INSERT INTO user_memory (c_user_id, c_name, c_province_id,c_city_id, create_time) VALUES (uuid(), randStr(20), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW()); - SET i = i + 1; - END WHILE; - END - $$Query OK, 0 rows affected (0.00 sec)MySQL [dts_demo] delimiter ;调用存储过程

调用存储过程将测试数据写入内存表

MySQL [dts_demo] CALL add_user_memory(1000000);Query OK, 1 row affected (1 min 50.74 sec)

生产100万测试数据用时50秒，还是比较效率的。

写入正式表

从内存表插入普通表

MySQL [dts_demo] INSERT INTO user_info SELECT * FROM user_memory;Query OK, 1000000 rows affected (7.02 sec)Records: 1000000 Duplicates: 0 Warnings: 0MySQL [dts_demo] DROP TABLE user_memory;Query OK, 0 rows affected (0.00 sec)打乱创建时间

更新创建时间字段让插入的数据的创建时间更加随机

MySQL [dts_demo] UPDATE user_info SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);Query OK, 1000000 rows affected (2.94 sec)Rows matched: 1000000 Changed: 1000000 Warnings: 0MySQL [dts_demo] select * from user_info limit 20;+----+--------------------------------------+----------------------+---------------+-----------+---------------------+| id | c_user_id | c_name | c_province_id | c_city_id | create_time |+----+--------------------------------------+----------------------+---------------+-----------+---------------------+| 1 | 1afd2630-88bc-11eb-9c30-0c42a125994e | oxlXASuDAQhIAEmDVAZ4 | 8 | 33 | 2022-03-19 22:05:05 || 2 | 1afd300e-88bc-11eb-9c30-0c42a125994e | Nj27hTrqAwIQUPiO0qXo | 727 | 95 | 2028-03-19 22:05:05 || 3 | 1afd4041-88bc-11eb-9c30-0c42a125994e | J9rzo41MCC2dM5Whp4Zy | 482 | 22 | 2026-03-19 22:05:05 || 4 | 1afd4562-88bc-11eb-9c30-0c42a125994e | RX3eSuFHkqXmNJ8hSoas | 517 | 67 | 2023-03-19 22:05:05 || 5 | 1afd4a49-88bc-11eb-9c30-0c42a125994e | YcVRm6gPdssI6cxUMZs9 | 54 | 31 | 2023-03-19 22:05:05 || 6 | 1afd4ebd-88bc-11eb-9c30-0c42a125994e | ydfrgRm1VlPX8FLFSeo5 | 968 | 3 | 2027-03-19 22:05:05 || 7 | 1afd530c-88bc-11eb-9c30-0c42a125994e | rsMpwgyPk0TiBXO2AFr3 | 585 | 25 | 2027-03-19 22:05:05 || 8 | 1afd574a-88bc-11eb-9c30-0c42a125994e | H5aqu0qT4xgB06i1341J | 293 | 73 | 2027-03-19 22:05:05 || 9 | 1afd5cf9-88bc-11eb-9c30-0c42a125994e | Y10PZgc4AzTDjxyY5ke0 | 31 | 60 | 2025-03-19 22:05:05 || 10 | 1afd61a8-88bc-11eb-9c30-0c42a125994e | 761DXGqU7GUjHpKns2E0 | 732 | 12 | 2022-03-19 22:05:05 || 11 | 1afd662c-88bc-11eb-9c30-0c42a125994e | AVIBJG21NLi00PX8HS7O | 384 | 97 | 2022-03-19 22:05:05 || 12 | 1afd6ace-88bc-11eb-9c30-0c42a125994e | RK0E38ooDO0r1CSn6dz6 | 474 | 53 | 2022-03-19 22:05:05 || 13 | 1afd6f01-88bc-11eb-9c30-0c42a125994e | pNCyKUaVYVyQqowgB3kl | 370 | 31 | 2028-03-19 22:05:05 || 14 | 1afd7332-88bc-11eb-9c30-0c42a125994e | CvwX2bCq4VhshQeuy9Yf | 960 | 55 | 2024-03-19 22:05:05 || 15 | 1afd775f-88bc-11eb-9c30-0c42a125994e | 3YzKT2oEXGmAIDRdo9on | 383 | 26 | 2024-03-19 22:05:05 || 16 | 1afd7bcf-88bc-11eb-9c30-0c42a125994e | j8zjGigivtHUhwDq2OK9 | 172 | 90 | 2025-03-19 22:05:05 || 17 | 1afd800c-88bc-11eb-9c30-0c42a125994e | 9pqJfSuEE8AlMKdHHeTD | 130 | 24 | 2025-03-19 22:05:05 || 18 | 1afd842c-88bc-11eb-9c30-0c42a125994e | 0DZUqdFwtEGifda3AA4p | 480 | 67 | 2028-03-19 22:05:05 || 19 | 1afd886b-88bc-11eb-9c30-0c42a125994e | 6SRyZ7v0mCP981zBaSIL | 374 | 5 | 2022-03-19 22:05:05 || 20 | 1afd8c9f-88bc-11eb-9c30-0c42a125994e | jKFUparzjJAyRrv4DMST | 530 | 43 | 2024-03-19 22:05:05 |+----+--------------------------------------+----------------------+---------------+-----------+---------------------+20 rows in set (0.00 sec)

至此，Mysql测试表已模拟成功。