Tuesday, May 3, 2011

find unwanted charsets and collations in your mysql schemas

Use this command to find the unwanted charachter set and collations :

# note that the unwanted collation in this sample is 'utf8_persian_ci', and the
# correct collation is to be 'utf8_general_ci'


SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE collation_name = 'utf8_persian_ci'
and character_set_name = 'utf8'
and table_schema = 'smartbase'
ORDER BY table_schema, table_name,ordinal_position;


then use the follownig script to correct them :


ALTER TABLE THE_TABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE 'utf8_general_ci';