I want to change column collation and character set of system database information_schema…
Can anyone give any input on how to do this? Is there any special priviledges i need for this
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
To change the character set and collation for all columns in an existing table, use:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
Method 2
As far as I know, you cannot run ALTER TABLE commands on the tables in information_schema. Instead you will probably want to take a look at the character_set_* variabes. You can see which variables are set to which values in your MySQL server with a show variables command:
show variables like "character_set_%";
The variable that has to do with meta data in MySQL, such as the information_schema tables, is the character_set_system variable. I think the my.cnf is the right place to set it.
There’s more information on this page: UTF-8 for Metadata.
For ordinary tables, you change the character set of a table with an ALTER TABLE command:
alter table some_table convert to character set utf8;
To do this, you will need the “alter” privilege.
You can see which privileges your MySQL server supports with a show privileges command, and you can see which privileges are granted to your current user with a show grants command.
Method 3
alter table some_table convert to character set utf8;
awesome that worked great as far as i can tell for me, now i can use chinese in that tables!! and i can remove all the utf8_encode() utf8_decode() throughout my site!
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0