存储过程写入数据、存储过程分割字符串、存储过程事务、MySQL存储过程的查询、MySQL测试用户数据批量生成id保证多次执行存储过程不会重复
存储过程写入数据
delimiter $$;
drop procedure if EXISTS batch_insert;
create procedure batch_insert(in group_count int,in once_count int)-- 1.组数2.每组提交数
begin
DECLARE i int DEFAULT 0;
DECLARE excute_count int DEFAULT 0;
while group_count > 0 do
while i< once_count do
insert into my_user (uname,pwd,time1,time2) values(concat('t小测_',excute_count),concat('pwd_',excute_count),now(),now());
set i = i+1;
set excute_count = excute_count+1;
end while;
set group_count = group_count-1;
set i = 0;
end while;
end
$$
delimiter ;
call batch_insert(2,10); -- 两组,每组10条数据
存储过程分割字符串
DELIMITER $$
DROP PROCEDURE IF EXISTS rand_str;
CREATE PROCEDURE rand_str()
BEGIN
SET @array_content='营销部、客户部、公关部、人事部、研发部、财务部、采购部、生产部、配送部';-- 字符串数组
SET @tag='、';-- 分隔符
SET @i=1;
SET @count=CHAR_LENGTH(@array_content)-CHAR_LENGTH(REPLACE(@array_content,@tag,'')) + 1;
-- 得出数组成员总数
DROP TABLE IF EXISTS tbl_temp;
CREATE TABLE tbl_temp(id INT,field VARCHAR(100));-- 创建临时表存储
WHILE @i <= @count
DO
INSERT INTO tbl_temp VALUES
(@i,SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,@tag,@i),@tag,-1));
-- 依次插入每个成员
SET @i=@i+1;
END WHILE;
-- SELECT field FROM tbl_temp ORDER BY RAND() LIMIT 1;-- 随机取一条,解析到表后建议新建一个function去做读取数据,不然每次触发会造成不必要的IO
END$$
DELIMITER ;
CALL rand_str();
存储过程事务
1、mysql存储过程是否是一个事务
我的mysql数据库autocommit是on,数据迁移时运行一个没有设置事务的存储过程,执行一部分后出错停止了,执行的那部分提交到了数据库,说明mysql存储过程不是一个事务。如果需要设置为一个事务,在存储过程主体开始部分指定开始一个事务。declare之后start transaction,后面一定要加commit,两者之间是一个事务。
2、mysql存储过程是否需要commit;
如果是支持事务的引擎,如innodb,支持自动提交;
可以使用**mysql> show variables like ‘%autocommit%’;**查看,是on时就是会自动提交,是off时,不会自动提交,需要手动commit。
MySQL存储过程的查询
selectname from mysql.proc where db='数据库名';
-- 或者
selectroutine_name from information_schema.routines where routine_schema='数据库名';
-- 或者
showprocedure status where db='数据库名'
-- 存储过程的详细
SHOWCREATE PROCEDURE 数据库.存储过程名;
MySQL测试数据批量生成
MySQL存储过程生成用户数据,表video_user_info字段有id,name,salt,password,email,create_time,update_time,id类型为bigint固定17位有序,id保证多次执行存储过程不会重复,salt固定使用 16 个字符的随机字符串,password长度为8-12位的随机字符串,提供可执行脚本,可选每次commit的数据量。
DELIMITER $$
CREATE PROCEDURE generate_users_data(IN num_of_users INT, IN commit_size INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE id BIGINT;
DECLARE user_salt CHAR(16);
DECLARE user_password CHAR(12);
DECLARE user_name VARCHAR(255);
DECLARE user_email VARCHAR(255);
DECLARE create_time TIMESTAMP;
DECLARE update_time TIMESTAMP;
WHILE i <= num_of_users DO
SET id = CONCAT(FLOOR(UNIX_TIMESTAMP() * (RAND()+1)), LPAD(i, 5, '0'));
SET user_name = CONCAT('user', LPAD(i, 5, '0'));
SET user_salt = SUBSTRING(MD5(RAND()), 1, 16);
SET user_password = SUBSTRING(MD5(CONCAT(user_salt, RAND())), 1, FLOOR(RAND() * 5) + 8);
SET user_email = CONCAT(user_name, '@example.com');
SET create_time = NOW();
SET update_time = NOW();
INSERT INTO video_user_info
(id, name, salt, password, email, create_time, update_time)
VALUES
(id, user_name, user_salt, user_password, user_email, create_time, update_time);
IF i MOD commit_size = 0 THEN
COMMIT;
END IF;
SET i = i + 1;
END WHILE;
IF i-1 MOD commit_size <> 0 THEN
COMMIT;
END IF;
END$$
DELIMITER ;
上述存储过程通过循环生成 num_of_users 个用户数据,并将每个用户的 id、name、salt、password、email、create_time和update_time 插入到 video_user_info 表中。当插入数据量达到 commit_size 的倍数时,使用 COMMIT 命令显式提交事务,以提高性能并防止在插入大量数据时出现锁定问题。
执行
CALL generate_users_data(100000, 10000);
该命令将生成 10 万个用户数据,并在每插入 1 万条数据时显式提交一次事务。其中,id字段通过组合当前的 UNIX 时间戳和序号自动生成,确保id有序且不重复;name字段为固定值加上序号;salt字段采用随机字符串固定长度16个字符;password字段长度为8-12位随机字符串;create_time和update_time字段均采用当前时间作为默认值。