海滨擎蟹

MySQL 批量生成并插入数据

之前有过批量导入有规则的数据记录的情况,即设备信息批量导入。厂家会给出一个编码规则和范围,然后通过创建的函数过程(PROCEDURE)执行导入。

这几天在研究 MySQL左联多个表做分组查询 时,需要批量生成一批数据,来检验查询的结果和效率。

之前的一个批量导入过程结构大概像这样:

CREATE DEFINER=`schema_name`@`%` PROCEDURE `procedure_name`(IN total int)
BEGIN
declare i int default 0;
while i < total do
insert into boxes (id, created_at) 
values (concat('XYZ1000', LPAD(i, 4, 0)),NOW());
set i = i + 1;
end while ;
END

schema_name 为数据库名,procedure_name 为过程名,IN total int 表示输入参数,int 类型,字段名 total。下面 BEGINEND 包含的部分基本都能看懂,定义变量 i 循环 total 次,每次执行插入表记录操作,i 自增。

其中的一个设备编号字段比较有意思,加入了 i 变量,使用 concat 拼接成完整的设备编号。NOW() 表示插入当前时间戳,一般来填充 timestamp 类型字段。

然后了解了一下 MySQL 生成随机数、随机字符串 之后,就得到了以下的函数过程:

CREATE DEFINER=`root`@`%` PROCEDURE `insert_base`(IN total int)
BEGIN
declare i int default 0;
while i < total do
insert into user_operation2 (user_id, cate_id, num, money, created_at, updated_at) 
values (1000+RAND()*1000, SUBSTRING(MD5(ROUND(RAND() * 10)), 1, 10), RAND() * 100, RAND() * 1000, NOW(), current_timestamp);
set i = i + 1;
end while;
END

其中遇到一个比较有意思的点,因为 RAND() 函数返回一个介于 0(包括)和 1(不包括)之间的随机数,所以 (1000+RAND()*1000 应该是在 [1000, 2000) 之间的带小数。在随机的带小数插入到数据库中 int 类型值时,生成结果集的值范围为 [1000, 2000],也就是多了一个 2000。

猜测是生成了 1999.[5|6|7|8|9].* 这样的带小数,然后存储到数据库时,数据库类型转换,使用了四舍五入,也就是 ROUND() 处理后再插入的,所以也就多了一个 2000 的取值。

当前页面是本站的「Google AMP」版。查看和发表评论请点击:完整版 »