修改MYSQL的字符集和排序规则

 

/*

--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;

*/