数据库所有表包含该字段并为该字段赋值
DROP PROCEDURE IF EXISTS init ; delimiter $ CREATE PROCEDURE init( in my_database VARCHAR(200), in column_name VARCHAR(200), in my_new_value VARCHAR(200), in my_old_value VARCHAR(200)) BEGIN -- 定义变量 declare table_name_str varchar(255); declare done int; -- 创建游标,并存储数据 declare class_group CURSOR for SELECT ic.TABLE_NAME FROM information_schema.COLUMNS ic WHERE TABLE_SCHEMA = my_database AND ic.COLUMN_NAME = column_name; -- 游标中的内容执行完后将done设置为1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 打开游标 open class_group; -- 执行循环 posLoop:LOOP -- 判断是否结束循环 IF done = 1 THEN LEAVE posLoop; END IF; -- 取游标中的值 FETCH class_group into table_name_str; SET @sql_text =CONCAT("UPDATE ",my_database, ".",table_name_str, " SET ", column_name, " = ", my_new_value, " WHERE ", column_name , " ", my_old_value, ";"); -- 执行更新操作 PREPARE my_table FROM @sql_text; EXECUTE my_table; DROP PREPARE my_table; -- SELECT table_name_str; END LOOP posLoop; CLOSE class_group; END $ -- 执行存储过程 CALL init('dev_b2csys','partner_id', "0", 'IS NULL'); -- 删除 DROP PROCEDURE init;