今天分享一下mysql存储过程的写法及相应的高级写法。
顺便说一下写这篇文章前已经跑过千万级别测试,一个函数新增1000万数据大概十个小时。
想在短时间内添加大量数据,那就多执行几个函数呗。
将mysql库数据创建到十亿级别有什么方法吗?
我想到的方法有
1.写一个程序让它不断地跑,好处是稳定且可以人为控制,坏处也很明显会浪费开发时间效率较低。
2.利用mysql的函数特性手写一个存储过程,好处是开发时间短,大幅提升开发人员开发效率,坏处是移植性较差,基本不能人为干预函数执行。
下面是一个存储过程的简单案例:
delimiter $$
create procedure insert_record(in n int,in x int)
BEGIN
-- 声明变量并赋值
DECLARE i int DEFAULT x;
-- 声明变量
DECLARE req VARCHAR(50);
-- 循环
while i <= n DO
-- req变量初始赋值
set req = CONCAT(LPAD(FLOOR(FLOOR(RAND() * POWER(10,15))),15,0),LPAD(i,10,0));
insert into record set
id = i,
s_id = req,
req_id = req,
vaccination_card_number_master = CONCAT(LPAD(FLOOR(RAND() * POWER(10,4))%68,2,1),FLOOR(RAND() * POWER(10,8)),FLOOR(RAND() * POWER(10,8))),
vaccination_card_number_child = CONCAT(LPAD(FLOOR(RAND() * POWER(10,4))%68,2,1),FLOOR(RAND() * POWER(10,8)),FLOOR(RAND() * POWER(10,8))),
province = LPAD(FLOOR(RAND() * POWER(10,4))%68,2,1),
province_child = LPAD(FLOOR(RAND() * POWER(10,4))%68,2,1),
province_master = LPAD(FLOOR(RAND() * POWER(10,4))%68,2,1),
organ_code = CONCAT(LPAD(FLOOR(RAND() * POWER(10,4))%68,2,1),FLOOR(RAND() * POWER(10,10))),
organ_code_child = CONCAT(LPAD(FLOOR(RAND() * POWER(10,4))%68,2,1),FLOOR(RAND() * POWER(10,10))),
organ_code_master = CONCAT(LPAD(FLOOR(RAND() * POWER(10,4))%68,2,1),FLOOR(RAND() * POWER(10,10))),
ctstamp = STR_TO_DATE(
CONCAT(
FLOOR(
2019 + RAND() * ( 2023-2019 )),
'-',
LPAD( FLOOR( 1 + RAND() * 12 ), 2, '0' ),
'-',
LPAD( FLOOR( 1 + RAND() * 28 ), 2, '0' ),
' ',
LPAD( FLOOR(RAND() * 24), 2, '0' ),
':',
LPAD( FLOOR( 1 + RAND() * 60 ), 2, '0' ),
':',
LPAD( FLOOR( 1 + RAND() * 60 ), 2, '0' )),
'%Y-%m-%d %H:%i:%s'
);
set i = i + 1;
end while;
END $$
delimiter;
上面这个存储过程怎么执行呢?
嘻嘻这样执行~~
call insert_record(1000,1);
按照从1~1000生成主键循环添加数据;
下面是相关函数用法
-- 查看历史sql执行时间
show profiles
-- 查询表的大小
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`
FROM information_schema.tables
WHERE table_schema = 'test'
ORDER BY (data_length + index_length) DESC;
-- 查询库的大小
SELECT table_schema AS `Database`,
SUM(data_length + index_length) / 1024 / 1024 AS `Size (MB)`
FROM information_schema.tables
WHERE table_schema = 'test'
GROUP BY table_schema;
-- 根据字符长度删除
DELETE FROM record_log where LENGTH(id) <= 3
-- 判断字符长度
SELECT LENGTH('your_string') AS string_length;
-- 生成随机时间
SELECT
STR_TO_DATE(
CONCAT(
FLOOR(
2019 + RAND() * ( 2023-2019 )),
'-',
LPAD( FLOOR( 1 + RAND() * 12 ), 2, '0' ),
'-',
LPAD( FLOOR( 1 + RAND() * 28 ), 2, '0' ),
' ',
LPAD( FLOOR( 1 + RAND() * 24 ), 2, '0' ),
':',
LPAD( FLOOR( 1 + RAND() * 60 ), 2, '0' ),
':',
LPAD( FLOOR( 1 + RAND() * 60 ), 2, '0' )),
'%Y-%m-%d %H:%i:%s'
)
-- 生成随机自定义时间
SELECT CONCAT(FLOOR(2018 + RAND() * ( 2023-2018 )),
LPAD(FLOOR( 1 + RAND() * 12 ), 2, '0' ),
LPAD(FLOOR( 1 + RAND() * 28 ), 2, '0' ),
'T',
LPAD(FLOOR( 1 + RAND() * 24 ), 2, 0),
LPAD(FLOOR( 1 + RAND() * 60 ), 2, 0),
LPAD(FLOOR( 1 + RAND() * 60 ), 2, 0 )),
-- 指定随机字符串
SELECT CASE FLOOR(RAND() * 7)
WHEN 0 THEN '001'
WHEN 1 THEN '005'
WHEN 2 THEN '401'
WHEN 3 THEN '402'
WHEN 4 THEN '403'
WHEN 5 THEN '612'
WHEN 6 THEN '009'
ELSE ''
END
-- 生成随机字符时间转date
select CONCAT(DATE_FORMAT(STR_TO_DATE(CONCAT(FLOOR(2018 + RAND() * (2023-2018)), LPAD(FLOOR(1 + RAND() * 12), 2, '0'),LPAD(FLOOR(1 + RAND() * 28), 2, '0')),'%Y%m%d'),'%Y%m%d'),LPAD(FLOOR(RAND() * POWER(10,2)),2,0))
-- str 转 date
select STR_TO_DATE(CONCAT(FLOOR(2010 + RAND() * (2023-2010)),'-', LPAD(FLOOR(1 + RAND() * 12), 2, '0'),'-',LPAD(FLOOR(1 + RAND() * 28), 2, '0')),'%Y-%m-%d');
-- 生成uuid字符串
SELECT UUID() AS random_string;
-- 将字符转换成datetime
select STR_TO_DATE(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 10 day), '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d %H:%i:%s')
-- 随机生成日期
SELECT CONCAT(
FLOOR(1920 + RAND() * (2023-1940)),
LPAD(FLOOR(1 + RAND() * 12), 2, '0'),
LPAD(FLOOR(1 + RAND() * 28), 2, '0')
)
-- 获取当前年份
SELECT YEAR(CURRENT_DATE()) AS current_year;
-- 获取指定格式时间
select DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')
-- 获取前20年日期
SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 20 YEAR), '%Y-%m-%d %H:%i:%s') AS previous_year_time;
-- 获取前10天日期
SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 10 day), '%Y-%m-%d %H:%i:%s') AS previous_year_time;
-- 生成指定随机数字
select FLOOR(11 + RAND() * (64-11))
SELECT concat((FLOOR(RAND() * 60) + 10),(FLOOR(RAND() * 60) + 10)) AS random_number;
-- 生成指定长度数字
SELECT FLOOR(RAND() * POWER(10, 15));
-- 截取从第2位开始6个长度字符
SELECT SUBSTRING(789456, 2, 6);
-- 存储过程例子:
delimiter $$
create procedure insert_(in n int,in x int)
BEGIN
DECLARE i int DEFAULT x;
while i <= n DO
-- sql执行位置
set i = i +1;
END WHILE;
END $$
DELIMITER;
最后你有什么好的方法向数据库添加10亿数据呢?希望引起您的思考。
欢迎在评论区留言~~