MySQL批量生成32uuid mysql uuid 批量

前言

在MySQL很多测试场景，需要人工生成一些测试数据来测试。本文提供一个构造MySQL大表存储过程，可以生成包含用户名，手机号码，出生日期等字段。也可以通过滤重来使得手机号码不重复，模拟现实场景。

一、生成脚本

生成说明：

以下使用存储过程批量生成包含用户名，手机号，出生日期等字段大表。

该存储过程使用基于uid作为主键，因此会生成少量重复手机号码，后面有滤重SQL脚本。

如果想一次性生成不重复手机号码，可以考虑修改以下脚本，去掉uid，基于mobile列作为主键

DROP TABLE IF EXISTS big_table;
DROP PROCEDURE IF EXISTS prc_gen_user;
CREATE TABLE `big_table` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`mobile` char(11) DEFAULT NULL,
`passwd` varchar(50) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` tinyint DEFAULT NULL,
`birthday` datetime DEFAULT NULL,
`updated_time` datetime DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE PROCEDURE prc_gen_user(l_cnt int)
BEGIN
DECLARE x INT DEFAULT 0;
DECLARE p char(11);
WHILE x < l_cnt
DO
SET x = x + 1;
SET p =
concat('1',
substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1),
right(left(trim(cast(rand() AS char(50))), 11), 9));
INSERT INTO big_table(mobile,
passwd,
name,
sex,
birthday,
updated_time)
VALUES (
p,
md5(ceiling(rand() * 1000000)),
concat(
substring(

floor(1 + 190 * rand()),

1),

substring(

floor(1 + 400 * rand()),

1),

substring(

floor(1 + 400 * rand()),
1)),
ceiling(rand() * 10) % 2,
date(
now()
- INTERVAL (20 + ceiling(rand() * 100) % 40) YEAR),
concat('2018-',
1 + ceiling(rand() * 100) % 12,
'-',
1 + ceiling(rand() * 100) % 28))
ON DUPLICATE KEY UPDATE updated_time = now();
END WHILE;
END

二、数据填充

call prc_gen_user(1000);
Query OK, 1 row affected (1.38 sec)
select count(*) from big_table;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
select 'Leshami' author,'' Blog;
+---------+------------------------------+
| author | Blog |
+---------+------------------------------+
| Leshami |  |
+---------+------------------------------+

三、滤重SQL语句

DELETE FROM big_table
WHERE mobile IN (SELECT mobile
FROM (SELECT u1.mobile
FROM big_table u1
GROUP BY u1.mobile
HAVING count(*) > 1) a)
AND uid NOT IN (SELECT uid
FROM (SELECT min(u2.uid) AS uid
FROM big_table u2
GROUP BY u2.mobile
HAVING count(*) > 1) b);

四、其它

本文参考了以下代码，下面代码实现是MySQL批量造用户数据，姓名/手机号/生日/密码

DROP PROCEDURE IF EXISTS batchGenerateUsers;
DELIMITER $$
CREATE PROCEDURE batchGenerateUsers()
BEGIN
DECLARE x INT Default 0;
DECLARE p char(11);
WHILE x < 10000 DO
SET x=x+1;
SET p=concat('1', cast(3+(rand()*10)%7 as char(1)), right(left(trim(cast(rand() as char (20))), 11),9));
insert into my_users(mobile, passwd, name, sex, birthday, updated_time)
values(p,
md5(ceiling(rand()*1000000)),

concat(substringfloor(1+400*rand()),1)),

ceiling(rand()*10)%2,
date(now()-interval (20+ceiling(rand()*100)%40) year),
concat('2015-', 1+ceiling(rand()*100)%12,'-',1+ceiling(rand()*100)%28))
ON DUPLICATE KEY UPDATE
updated_time=now();
END WHILE;
END $$
#call batchGenerateUsers();

并对其进行适当修改。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流。