mysql排序规则编码更改_修改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 doneint default 0;

declarev_table_name varchar(4000);

declarev_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'

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

CREATEPROCEDURE p_alter_table_collation_2()

begin

declare done2int default 0;

declarev_table_name varchar(4000);

declarev_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'

andreferenced_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 intov_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

declaredone3 int default 0;

declare v_tablename3 varchar(4000);

declare get_tabname cursor for

select table_name from information_schema.tables

where table_schema='rainny'

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

CREATEPROCEDURE p_alter_table_collation_4()

begin

#create the original foreign key

declaredone4 int default 0;

declarev_sql varchar(4000);

declare get_sql cursor for

select c from t_sql ;

declare continue handler for sqlstate '02000' set done4=1;

openget_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

/*

#querycollation 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'

andreferenced_column_name is not null

order by table_name;

*/