同时修改某一个数据库中所有表的所有字段的编码格式（mysql）

最近在进行源码的二次开发，突然之间有了一个小的需求，就这么悄悄的突然出现了。

需求原因就不说了，只说这个怎么实现的。我是用的是mysql数据库，其他的略有不同，具体到哪一点就只能具体变化了。

看网上有的说直接把数据库或者表的编码做改变就行了，可是这样对字段来说都没有影响。想改字段的话，直接对字段进行alter修改。可是把每一个字段写一遍挺烦人的，于是乎找到了一个语句：

alter table 表名 convert to character set utf8;

可以同时修改这个表的所有字段为u8，但是想改所有的表难道我要把所有的表名输入一遍吗？我那么懒的人怎么可能这么做。就算是花点时间也要找一个一劳永逸的方法。又于是乎，找到了一个查询所有表名的语句：

select table_name from information_schema.`TABLES` where TABLE_SCHEMA = '数据库名';

mysql的表信息都保存在了 information_schema 数据库的 TABLES 表里面，其中的字段 TABLE_SCHEMA 保存了数据库名，打开看看就是这样的：

针对这样的两句 sql 进行操作就行了。问题也来了，我得设计一个思路啊。

最先想到的是 把alter语句中表名作为参数的，而查询出来的表名作为变量传进去，再执行alter语句不就行了吗？

又于是乎，想到循环这种东西。用到循环的话，那写个存储过程吧，在其中声明变量，弄个循环，查询的表名赋值给变量，再往alter语句中一传，美美的。

结果，你妹的，各种报错！！！！

至于为什么，很简单：我不是太擅长数据库的操作……，其中的规则也只能一边做一边查询怎么做。别说，各种百度之后我居然搞了两种方式实现最初的需求：

A方案： 循环 + 变量赋值 + 动态sql语句拼接执行     和     B方案 ：游标 + 循环 + 变量赋值 + 动态sql语句拼接

没错，其中的区别就是有没有用游标。可是涉及到的问题就不一样了。

循环，得有结束条件吧。看看网上的资料，各种条件都是以数字的大小为条件。当然我的A方案也是这样的，但是循环体不同。我这查询的数据中可不涉及数字啊，而且一下子查出来多行，就声明一个变量（声明多个也没用，都是一列对应一个变量）

要么说知道的多一点总是没错的：我每循环一次，不查出来多行，就查出一行，赋给变量去拼接sql执行不就行了。

每次查指定行数，循环时还继续往下查询，这不还是专门为一个关键字设计的嘛：limit 。就是它，一般用于分页查询所以上面的查询表名的语句就多了点东西：

select table_name from information_schema.`TABLES` where TABLE_SCHEMA = '数据库名'limit 起始位置, 每次查询的数量;

至此我就可以让声明的变量被依次赋予所有表的名称了，到这先把这部分整合下，存储过程的内容如下：

begin

	DECLARE cnt VARCHAR(100); -- 声明变量用来记录查询出的表名
	DECLARE i int;  -- 循环条件，同时可以用来标记表第几张表
	set i = 0;

-- 循环开始
	while i < 32 do -- 这里是32是因为我的数据库中表的数量是32，想不写死可以通过再定义一个变量，动态赋值
		select table_name into @cnt from information_schema.`TABLES` where TABLE_SCHEMA = 'xxx' limit i,1;
		select @cnt; -- mysql的打印语句
		-- 这里添加 alter 语句
		set i = i + 1;
	end while;  
-- 循环结束，注意分号

end

稍微说明下，其中的变量 i 循环条件的同时，也可以充当 limit 的起始位置；into 关键字是把查询结果赋值给 cnt 变量；32用变量代替就是：声明一个变量，比如： num，循环开始前：

select count(table_name) into @num from information_schema.`TABLES` where TABLE_SCHEMA = '数据库名'

这个语句的查询结果赋值给num，循环条件变成： while i < num do ……即可。

到这可以看到打印出来的该数据库中的所有表名。那这样，把之前 alter 语句中的表名替换为 @cnt 不就完成了吗？就这样，我有进坑了。

原因是：标准的 alter 语句中是没有@这个东西的，而把@去掉的话，它又会认为我想修改的表名就是 cnt ，而不是变量 cnt 。

所以需要动态拼接sql来执行，完整版

A方案：

begin

	DECLARE cnt VARCHAR(100); -- 声明变量用来记录查询出的表名
	DECLARE i int;  -- 循环条件，同时可以用来标记表第几张表
	set i = 0;

-- 循环开始
	while i < 32 do -- 这里是32是因为我的数据库中表的数量是32，想不写死可以通过再定义一个变量，动态赋值
		select table_name into @cnt from information_schema.`TABLES` where TABLE_SCHEMA = '数据库名' limit i,1;
		-- select @cnt; -- mysql的打印语句
		-- alter table @cnt convert to character set utf8; -- 这一句报错，必须动态拼接才行
		
		set @sql = concat("alter table ", @cnt, " convert to character set utf8");  -- 拼接，注意语句中的空格
		prepare stmt from @sql;  -- 预处理
			execute stmt;  -- 执行
		deallocate prepare stmt;  -- 释放

		set i = i + 1;
	end while;  
-- 循环结束，注意分号

end

反正就是这个意思，而带有游标的

B方案：

BEGIN


  DECLARE a VARCHAR(100);	-- 定义接收游标数据的变量 
  DECLARE SQL_FOR_SELECT varchar(500); -- 定义接收游标数据的变量 
 
  DECLARE done INT DEFAULT FALSE; -- 遍历数据结束标志
  DECLARE cur CURSOR FOR (select table_name from information_schema.`TABLES` where TABLE_SCHEMA = 'demo_survey');  -- 游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 将结束标志绑定到游标
 
 -- 打开游标
  OPEN cur; 
		-- 开始循环（loop循环）
		read_loop: LOOP
			-- 提取游标里的数据，这里只有一个，多个的话也一样；
			FETCH cur INTO a;
			
			-- 声明结束的时候
			IF done THEN
				LEAVE read_loop;
			END IF;

			-- 要循环的事件，使用了动态sql拼接alter语句，直接写的话报错		
			set SQL_FOR_SELECT = concat("alter table ", a, " convert to character set utf8"); -- 拼接
			set @sql = SQL_FOR_SELECT;  
			prepare stmt from @sql; 	-- 预处理
				execute stmt;  		-- 执行
			deallocate prepare stmt;	-- 释放prepare

		END LOOP;

  -- 关闭游标
  CLOSE cur;


END

B方案中用了 loop 循环，这样我可以学习下不同循环的使用方法嘛。对比两种方案可以看出来循环条件的不同，而且由于游标的特性，B方案中的查询结果不需要limit限制

两种方案的思路是一样的，手段不同而已。

当然了，我这个最初的需求很少遇到。

不过里面用到的东西很多我之前是没怎么用过的，所以记录下。

总结：

1.一次性修改表中所有字段的字符集语句：alter table `表名` convert to character set utf8;

2.查询某个数据库中所有表的信息语句：select * from information_schema.`TABLES` where TABLE_SCHEMA = '该数据库名';

3.查询结果赋值给变量可以用 into 关键字（也有其他的）。游标中没有@

4.变量赋值可以不用先声明，如A方案中的@sql ，直接使用 set @sql = xxx。

5.存储过程中可以直接使用 alter语句 ，也就是静态sql语句。但是需要传递参数的话，要使用动态sql拼接来执行（小慢，就循环了30多次，就能感觉出来比静态的慢，可能是因为我循环的是ddl语句吧）。

6.循环，游标，动态sql用完之后，都有个结束的语句和分号

LG