[简记]Mysql存储过程实战:批量修改类型与批量新增字段
背景
有些时候我们会遇到一些需要批量新增字段的需求。比如原有系统部分表去烧基础字段：更新人ID、更新人名称、操作url等等。但是有些表已经包含了这些字段，所以为了能统一基础字段，就需要批量操作，去新增基础字段（需要注意的是更改表结构是会锁表的，所以这种批量修改表结构的方式是适合项目前期或者项目的流量不大的情况）。或者有时候有些字段如datetime想用bigint(对应java long类型)表示，也需要批量修改字段类型。下面会演示下批量操作的例子语法介绍，以及批量新增字段和批量修改字段类型的例子。
批量操作的基本描述[1]
批量操作的会涉及到下面几点，以下是简单的例子：mysql用存储过程循环遍历操作结果集
##创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS init_template;
##将结束符换为//，为了避免语法错误
DELIMITER // ##创建存储过程
CREATE PROCEDURE init_template()
begin
##定义变量
DECLARE s int DEFAULT 0;
DECLARE template_id int(11);
DECLARE template_url varchar(255);
##定义游标，并将sql结果集赋值到游标中
DECLARE template CURSOR FOR select id,url from a_table;
##声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
##打开游标
open template; ##将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致 fetch template into template_id,template_url; ##当s不等于1，也就是未遍历完时，会一直循环 while s<>1 do ##执行业务逻辑 insert into b_table(id,url) values(template_id,template_url); ##将游标中的值再赋值给变量，供下次循环使用 fetch template into template_id,template_url; ##当s等于1时表明遍历以完成，退出循环 end while;
##关闭游标
close template;
end;
//
##将结束符换回;
DELIMITER ;
##执行存储过程
call init_template()
批量新增不存在的字段
以下的例子是为不存在create_time和update_time的表增加这两个字段
DROP PROCEDURE IF EXISTS addColumn;
DELIMITER $$
CREATE PROCEDURE addColumn()
BEGIN
-- 定义表名变量
DECLARE s_tablename VARCHAR(100);
#显示所有
DECLARE cur_table_structure CURSOR
FOR
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
-- table_schema = 数据库名称
WHERE table_schema = 'mdm'
AND table_name NOT IN (
SELECT t.table_name
FROM (
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name IN (
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
-- table_schema = 数据库名称
WHERE table_schema = 'mdm')
) t
WHERE t.column_name = 'object_name'
);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename = NULL;
OPEN cur_table_structure;
FETCH cur_table_structure INTO s_tablename;
SET @dbname = DATABASE();
WHILE (s_tablename IS NOT NULL)
DO
SET @tablename = s_tablename;
SET @columnname = "create_time";
SET @MyQuery = (SELECT IF(
(
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) < 1,
"SELECT 1",
CONCAT("alter table `", s_tablename,
"` modify COLUMN `create_time` bigint null COMMENT '创建时间' after `create_by` ")
));
PREPARE msql FROM @MyQuery;
EXECUTE msql;#USING @c;
SET @columnname = "update_time";
SET @MyQuery = (SELECT IF(
(
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) < 1,
"SELECT 1",
CONCAT("alter table `", s_tablename,
"` modify COLUMN `update_time` bigint null COMMENT '更新时间' after `update_by` ")
));
PREPARE msql FROM @MyQuery;
EXECUTE msql;#USING @c;
FETCH cur_table_structure INTO s_tablename;
END WHILE;
CLOSE cur_table_structure;
END;
$$
#执行存储过程
CALL addColumn();
#删除存储过程
DROP PROCEDURE IF EXISTS addColumn;
批量修改字段类型
以下以批量将所有表的字段类型为date、datetime、timestamp的字段类型修改为bigint类型，保留注释与是否必填。可以通过修改查询条件来排除表或者字段名。按照需求进行变更:
DELIMITER $$
CREATE PROCEDURE modifyColumnType()
BEGIN
-- 定义表名变量
DECLARE dbname VARCHAR(100);
DECLARE tableName VARCHAR(100);
DECLARE columnName VARCHAR(100);
DECLARE columnDefault VARCHAR(100);
DECLARE isNullable VARCHAR(100);
DECLARE columnComment VARCHAR(100);
DECLARE state_loop int DEFAULT 0;
-- 开启游标并查询符合条件的字段和相关信息
DECLARE cur_table_date_time_column CURSOR
FOR
SELECT table_name, column_name, column_default, is_nullable, column_comment
FROM INFORMATION_SCHEMA.COLUMNS
-- table_schema = 数据库名称
WHERE table_schema = 'mdm'
AND table_name NOT IN (
SELECT t.table_name
FROM (
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name IN (
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
-- table_schema = 数据库名称
WHERE table_schema = 'mdm')
) t
WHERE t.column_name = 'object_name'
)
-- 以下条件查询符合要求的字段，可以加入相应的字段名称或类型限制需要修改的字段
AND (
column_type = "datetime"
OR column_type = "datetime(1)"
OR column_type = "datetime(2)"
OR column_type = "datetime(3)"
OR column_type = "datetime(4)"
OR column_type = "datetime(5)"
OR column_type = "datetime(6)"
OR column_type = "date"
OR column_type = "timestamp"
OR column_type = "timestamp(1)"
OR column_type = "timestamp(2)"
OR column_type = "timestamp(3)"
OR column_type = "timestamp(4)"
OR column_type = "timestamp(5)"
OR column_type = "timestamp(6)");
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET state_loop = 1;
OPEN cur_table_date_time_column;
FETCH cur_table_date_time_column INTO tableName, columnName, columnDefault, isNullable, columnComment;
SET @dbname = DATABASE();
WHILE (state_loop <> 1)
DO
-- 以下是组装修改字段的语句
SET @MyQuery = CONCAT("alter table ", tableName, " modify COLUMN `", columnName ,"` bigint ", if(isNullable="YES"," default null ", " not null "), if(columnComment is null or columnComment = ''," ",CONCAT(" COMMENT '", columnComment,"'")));
PREPARE msql FROM @MyQuery;
EXECUTE msql;
FETCH cur_table_date_time_column INTO tableName, columnName, columnDefault, isNullable, columnComment;
END WHILE;
CLOSE cur_table_date_time_column;
END;
$$
#执行存储过程
CALL modifyColumnType();
# 移除存储过程
DROP PROCEDURE IF EXISTS modifyColumnType;
参考
- mysql用存储过程循环遍历操作结果集
https://www.huaweicloud.com/articles/315219a239f3707c3f240e5a4b159f4d.html