/*
--Desc : alter table and all columns collation to gbk_chinese_ci
--Author: Rainny Zhong
--Date : 2008-02-02
--db version : at least Mysql 5.0
*/
DROP PROCEDURE IF EXISTS p_alter_table_collation_1;
CREATE PROCEDURE p_alter_table_collation_1()
begin
declare done int default 0;
declare v_table_name varchar(4000);
declare v_constraint_name varchar(4000);
declare v_column_name varchar(4000);
declare v_referenced_table_name varchar(4000);
declare v_referenced_column_name varchar(4000);
declare v_sqlstr varchar(4000);
declare get_data cursor for
select table_name,
constraint_name,
column_name,
referenced_table_name,
referenced_column_name
from information_schema.key_column_usage
where constraint_schema='rainny'
and referenced_column_name is not null
order by table_name;
declare continue handler for sqlstate '02000' set done=1;
set @sqltext:="create table if not exists t_sql(c varchar(4000))";
prepare stmt from @sqltext;
execute stmt;
deallocate prepare stmt;
delete from t_sql;
#insert create foreign key sql to table t_sql
open get_data;
repeat
fetch get_data into v_table_name,v_constraint_name,v_column_name,v_referenced_table_name,v_referenced_column_name;
if not done then
insert into t_sql values(concat('alter table ',v_table_name,' add constraint ',v_constraint_name,' foreign KEY(',v_column_name,')',' references ',v_referenced_table_name,'(',v_referenced_column_name,')'));
end if;
until done
end repeat;
close get_data;
end ;
DROP PROCEDURE IF EXISTS p_alter_table_collation_2;
CREATE PROCEDURE p_alter_table_collation_2()
begin
declare done2 int default 0;
declare v_table_name varchar(4000);
declare v_constraint_name varchar(4000);
declare v_column_name varchar(4000);
declare v_referenced_table_name varchar(4000);
declare v_referenced_column_name varchar(4000);
declare v_sqlstr varchar(4000);
declare get_data cursor for
select table_name,
constraint_name,
column_name,
referenced_table_name,
referenced_column_name
from information_schema.key_column_usage
where constraint_schema='rainny'
and referenced_column_name is not null
order by table_name;
declare continue handler for sqlstate '02000' set done2=1;
#drop foreign key
open get_data;
repeat
fetch get_data into v_table_name,v_constraint_name,v_column_name,v_referenced_table_name,v_referenced_column_name;
if not done2 then
set @sqltext:=concat('alter table ',v_table_name,' drop foreign key ',v_constraint_name);
prepare stmt from @sqltext;
execute stmt;
deallocate prepare stmt;
end if;
until done2
end repeat;
close get_data;
end ;
DROP PROCEDURE IF EXISTS p_alter_table_collation_3;
CREATE PROCEDURE p_alter_table_collation_3()
begin
#convert table collation
declare done3 int default 0;
declare v_tablename3 varchar(4000);
declare get_tabname cursor for
select table_name from information_schema.tables
where table_schema='rainny'
and table_collation<>'gbk_chinese'
order by table_name;
declare continue handler for sqlstate '02000' set done3=1;
open get_tabname;
repeat
fetch get_tabname into v_tablename3;
if not done3 then
set @sqltext:=concat('alter table ',v_tablename3,' convert to character set gbk collate gbk_chinese_ci');
prepare stmt from @sqltext;
execute stmt;
deallocate prepare stmt;
end if;
until done3
end repeat;
close get_tabname;
end ;
DROP PROCEDURE IF EXISTS p_alter_table_collation_4;
CREATE PROCEDURE p_alter_table_collation_4()
begin
#create the original foreign key
declare done4 int default 0;
declare v_sql varchar(4000);
declare get_sql cursor for
select c from t_sql ;
declare continue handler for sqlstate '02000' set done4=1;
open get_sql;
repeat
fetch get_sql into v_sql;
if not done4 then
set @sqltext:=v_sql;
prepare stmt from @sqltext;
execute stmt;
deallocate prepare stmt;
end if;
until done4
end repeat;
close get_sql;
end ;
call p_alter_table_collation_1();
call p_alter_table_collation_2();
call p_alter_table_collation_3();
call p_alter_table_collation_4();
#validation sql
/*
#query collation of the columns
select *
from columns t
where table_schema='rainny'
and data_type in ('char','varchar','text','longtext')
and collation_name<>'gbk_chinese_ci'
order by table_name,column_name;
#query the foreign keys
select table_name,constraint_name,column_name,
referenced_table_name,referenced_column_name from key_column_usage
where constraint_schema='rainny'
and referenced_column_name is not null
order by table_name;
*/