目录
一、实现
需求:为数据库中所有表添加相同的字段。
步骤:获取数据库表名列表,定义结束条件,打开列表,执行循环,关闭列表。
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('数据库名')