MySql创建存储过程

# 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 = '[email protected]';

#查看创建的储存过程
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'; //日志关闭