最近在进行源码的二次开发,突然之间有了一个小的需求,就这么悄悄的突然出现了。
需求原因就不说了,只说这个怎么实现的。我是用的是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