# 1.插入数据前先把表中的索引去掉，数据插入完成之后，再创建索引 # 2.关闭事务的自动提交 DROP PROCEDURE if EXISTS BatchInsert; #分隔符 delimiter $$ CREATE PROCEDURE BatchInsert(IN initId INT, IN loop_counts INT) BEGIN DECLARE num INT; DECLARE id INT; SET num = 0; SET id = initId; set autocommit = 0; -- 关闭自动提交事务，提高插入效率 WHILE num < loop_counts DO INSERT INTO `xk-management`.`tb_user` (`second_name`,`name`, `account`, `password`, `email`) VALUES (CONCAT('20230228', 10000 + id), CONCAT('小明第',id,'号'),CONCAT('admin', 52 + id,'@test.com'), '62C8AD0A15D9D1CA38D5DEE762A16E0', CONCAT('admin', 52 + ID,'@test.com')); SET id = id + 1; SET num = num + 1; END WHILE; COMMIT; END$$; delimiter; -- 界定符复原为默认的分号 CALL BatchInsert(1, 10); -- 调用存储过程 select count(0) from tb_user; select * from tb_user where is_deleted = 0 AND account = 'admin57@test.com'; #查看创建的储存过程 SHOW PROCEDURE STATUS LIKE 'Batch%'; SELECT * FROM information_schema.Routines Where ROUTINE_NAME = 'BatchInsert'; show create procedure BatchInsert; --删除存储过程 DROP PROCEDURE BatchInsert;
添加字段：
CREATE PROCEDURE addColumn() BEGIN DECLARE ise INT DEFAULT 0; select count(1) into ise from information_schema.columns where TABLE_SCHEMA = 'xk-management' and TABLE_NAME = 'tb_user' and COLUMN_NAME = 'login_level'; if ise=0 then alter table tb_user add column login_level int DEFAULT '0' COMMENT '登录方式级别'; end if; END CALL addColumn(); SHOW PROCEDURE STATUS LIKE 'addColumn%';
drop procedure if exists addColumn;
#查看日志
SET GLOBAL log_output = ‘TABLE’;SET GLOBAL general_log = ‘ON’; //日志开启
SELECT * from mysql.general_log ORDER BY event_time DESC;
SET GLOBAL log_output = ‘TABLE’; SET GLOBAL general_log = ‘OFF’; //日志关闭
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/260283.html
