MYSQL使用存储过程为数据库下所有表添加相同字段

目录

一、实现

二、扩展

三、参考与鸣谢

---

 

---

一、实现

需求：为数据库中所有表添加相同的字段。

步骤：获取数据库表名列表，定义结束条件，打开列表，执行循环，关闭列表。

CREATE DEFINER=`myuser`@`%` PROCEDURE `alertDBtables`()
BEGIN
	
DECLARE s_tablename VARCHAR(100);# 定义循环的表名变量

DECLARE tablenames CURSOR FOR SELECT table_name FROM information_schema.`TABLES` WHERE table_schema='my_db_name' Order by table_name;# 获取所有表名，并赋值给定义的变量

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename  = NULL;# 02000：当游标移动到最后一行时，设置 s_tablename=NULL

OPEN tablenames;

FETCH tablenames INTO s_tablename;

WHILE  (s_tablename IS NOT NULL) DO

	SET @_sql = CONCAT(
	'ALTER TABLE ',
	s_tablename,
	" ADD (
	`CreationTime` DateTime NULL COMMENT '创建时间',
	`CreatorId` BIGINT(20) NULL COMMENT '创建人编号',
	`LastModificationTime` DateTime NULL COMMENT '最后修改时间',
	`LastModifierId` BIGINT(20) NULL COMMENT '最后修改人编号',
	`IsDeleted` TINYINT(1) NOT NUll DEFAULT 0 COMMENT '是否删除'
	)"
	);# 拼接SQL语句
	
	PREPARE ex_sql FROM @_sql;# 预定义SQL
	
	EXECUTE ex_sql;# 执行SQL
	
	DEALLOCATE PREPARE ex_sql;# 释放连接
	
	FETCH tablenames INTO s_tablename;
END WHILE;

CLOSE tablenames;

END

二、扩展

1.数据库中某些表：

SELECT table_name FROM information_schema.`TABLES` WHERE table_schema='my_db_name' Order by table_name

按需修改获取表名的SQL语句即可。

2.循环语句体还可以使用repeat、loop

3.数据名使用参数传入，修改部分如下

CREATE DEFINER=`myuser`@`%` PROCEDURE `alertDBtables`(IN `db_name` varchar(20))

...

SELECT table_name FROM information_schema.`TABLES` WHERE table_schema=`db_name` Order by table_name

...

#调用
CALL alertDBtables('数据库名')

三、参考与鸣谢

https://blog.csdn.net/showchi/article/details/100051625

https://www.cnblogs.com/Moming0/p/11821965.html