MySQL使用存储过程批量更新数据库所有表某个字段值

最近响应群里朋友完整开源之前那个博客系统，准备重构一番项目的代码，对数据库中的表决定都添加 create_by、update_by、create_time、update_time、del_flag 等字段。 当时添加表的时候没有设置默认值，现在要对二三十张表某个字段，如对 del_flag 设置默认值为0，怎么做呢？一张表一张表地设置比较蠢，如何实现批量操作呢？比如查出所有的表名，然后来一个循环操作。是的，没错，可以使用存储过程。 下面是对 sens_blog 这个库的所有的表中的 del_flag 设置默认值的示例

1. — 如果存储过程存在就删除
2. DROP PROCEDURE IF EXISTS updateColumn;
3. CREATE PROCEDURE updateColumn()
4. BEGIN
5. — 定义循环条件
6. DECLARE flag INT DEFAULT 0;
7. — 保存表名
8. DECLARE tname VARCHAR(50);
9. — 查询数据库sens_blog中含有del_flag列的表,如果区分大小写使用binary COLUMN_NAME = ‘del_flag‘
10. DECLARE result CURSOR FOR SELECT TABLE_NAME FROM  INFORMATION_SCHEMA.Columns WHERE TABLE_SCHEMA = ‘sens_blog‘ AND  COLUMN_NAME = ‘del_flag‘;
11. — 退出循环
12. DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET flag = 1;
13. — 打开游标
14. OPEN result;
15.     WHILE flag <> 1 DO
16.     — 游标指向下一个位置,可以有多个数据,比如FETCH result INTO tname,ttype,…;
17.     FETCH result INTO tname;
18.         — 拼接字符串表名sql,根据需要使用CONCAT函数连接
19.         — 批量设置所有表的为del_flag字段0
20.         — SET @execSql = CONCAT(‘UPDATE ‘, tname, ‘ SET del_flag = 0‘); 
21.                 — 批量设置所有表的为del_flag字段默认值为0
22.                 SET @execSql = CONCAT(‘ALTER TABLE  ‘, tname, ‘ ALTER COLUMN del_flag SET DEFAULT  0‘);
23.         PREPARE stmt FROM @execSql;
24.         EXECUTE stmt;
25.     END WHILE;
26. END;
27. — 调用存储过程更新数据
28. CALL updateColumn();

如果你想做其他的操作，只需要修改22行，改成你的SQL语句就行，当然数据库名和字段名也要改。