描述
这个函数可以修改数据库中所有表的结构和数据，是同时修改的啊，而且每次执行只能修改一个字段。具体使用看下面的dome。
需要的基础
- 存储过程的定义
- 游标
- 变量的定义
- 逻辑运算
- while , if
- delimiter
代码
-- 创建存储过程
drop procedure if exists proc01;
delimiter $$;
create procedure proc01(
-- 字段值
in lg varchar(255),
-- 库名
in `database` varchar(255),
-- 字段名
in `field` varchar(255),
-- 修改后的字段类型
in `type` varchar(255)
)
begin
declare table_name_copy varchar(255) default '';
declare table_index int(11) default 0;
-- 表 游标
declare cursor_table cursor for select table_name from information_schema.tables where table_schema = `database`;
-- 记录，不知道专有名词
declare continue handler for not found set table_index = 1;
open cursor_table;
-- 执行记录
set @response = '';
fetch cursor_table INTO table_name_copy;
while table_index <> 1 do
-- 判断字段是否存在
if exists(select * from information_schema.columns where table_schema = `database` and table_name = `table_name_copy` and column_name = `field`) then
begin
set @tb = table_name_copy;
-- 修改表结构
set @ddl = concat("alter table `", @tb, "` modify column ", `field` ," ", `type`);
prepare sddl from @ddl;
execute sddl;
deallocate prepare sddl;
set @response = concat(@response, ",", @ddl);
-- 修改数据
set @sqlStr = concat("update `", @tb , "` set ", `field`,"='", lg, "'");
prepare stmt from @sqlStr;
execute stmt;
deallocate prepare stmt;
end;
end if;
-- 取游标
fetch cursor_table INTO table_name_copy;
end while;
close cursor_table;
select @response;
end $$;
-- 调用存储过程
-- 字段值 库名 字段 类型
call proc01('ZH_CN', 'hczn', 'sys_language', 'varchar(255)');
