Batch modification of MySQL table, table, database character proofing rules

Recording a problem encountered at work is not a problem, in order to find a convenient way to bulk modify the data table field collation, in MySQL called collation, often with the code character appear together. There are three levels of collation, which are the database level, the data table level, and the field level.

1.The database Level2. The table Level3. The column level

Https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database This article is more detailed.

That was the problem that day.
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=‘, mainly because the collation rules were not changed in time when the database was migrated.

The solution found on the web, all mention the modification of the data table level collation collation. But the scenario I encountered was that the data table level is already utf8_unicode_ci , and the field level is utf8_general_ci , (here we care about the field type is varchar).

Because there are too many fields to modify, manual modification must be time-consuming and laborious. Naturally also thought of a script to batch modification, but found that by looking at MySQL information table, filtering, splicing generated batch modification of the statement is very useful, but also to the varchar type.

SELECT CONCAT (' ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', Data_type, ' (', Character_maximum_length, ') C Haracter SET UTF8 COLLATE utf8_unicode_ci ', (case is is_nullable = ' NO ' Then ' not NULL ' ELSE ' END), '; ') From INFORMATION_SCHEMA. Columnswhere table_schema = ' database ' and data_type = ' varchar ' and (character_set_name! = ' UTF8 ' OR Collation_nam E! = ' utf8_unicode_ci ');

databaseThe actual database name needs to be changed. Note that if you want to modify the word Gencun foreign key relationship, it should be handled with care, delete the foreign key, modify the collation and then add the external key relationship back.

Excerpted from http://segmentfault.com/a/1190000002570642

========================== above network reference, the following is my modified SQL, thank Zheng Classmate help =======================================

--  modifies the database table proofing rules SQL and modifies the collation rules for the columns in the table when executed. delimiter//drop procedure if exists  ' alter_table_character '  //--  Delete create if it already exists  procedure  ' Alter_table_character ' ()  begin    declare f_name  varchar (;     declare b int default 0;   )  /* whether to reach the end of the record control variable */--  Note Modify the following database name  wsm_aliyun    declare table_name  cursor for select table_name from information_schema. tables where table_schema =  ' Wsm_aliyun '  and TABLE_NAME like  ' wsm_% '  AND TABLE_COLLATION =  ' Utf8_unicode_ci ';         declare continue handler for not found set b = 1;        open table_name;    repeat    fetch  table_name into f_name; /* get the first record */set  @STMT  :=concat ("alter table ", F_name, "  convert to character set utf8 collate utf8_general_ci; ");    PREPARE STMT FROM  @STMT;        execute  stmt;  -- insert into testtable (name)  VALUES  (f_name);        -- ALTER TABLE f_name CONVERT TO CHARACTER  Set utf8 collate utf8_general_ci;     until b = 1end  repeat;    close table_name;        end ///* switch back to system default command end flag */delimiter ;--  Execute stored procedure call alter_table_character ();--  Modify the collation rule set for the database  names  ' UTF8 '  collate  ' utf8_general_ci ';

--  Query The result of the modification, in fact, you can also use the following statement to generate the corresponding SQL, execute the SQL to complete the modification, of course, there is no high efficiency of the above stored procedures. --  View the collation rules for the database, all results are: UTF8_GENERAL_CI, indicating modified show variables like  ' collation_% ';--  Review the database's proofing rules, and there is no data to indicate that all has been modified. Selectconcat (' alter table  ', table_name,  '  convert to character set  utf8 COLLATE utf8_general_ci; ')  as new_sqlfrominformation_schema. tableswheretable_schema =  ' Wsm_aliyun ' and table_name like  ' wsm_% '  --  Database name and table_collation =  ' utf8_unicode_ci ';--  query column results, no data indicates that all have been modified. Selectconcat (' alter table  ', table_name, '  MODIFY  ', column_name, '   ', data_type, ' (', Character_maximum_length, ')  CHARACTER SET UTF8 COLLATE utf8_general_ci; ')  as new_sqlfrominformation_schema. columnswheretable_schema =  ' Wsm_aliyun '  --  database name and table_name like  ' wsm_% ' and data_type =  ' varchar ' and character_set_name =  ' UTF8 ' and collation_name =  ' utf8_unicode_ci '; 

Batch modification of MySQL table, table, database character proofing rules