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
Add a comment
|
3 Answers
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];
3 Comments
MySQL DBA
what priviledges do i need to to alter system database any idea
M. Atif Riaz
@MySQLDBA you will need the "alter" privilege, see Christian answer
M. Atif Riaz
Make sure you consider this warning before executing an
ALTER command > If you use ALTER TABLE to convert a column from one character set to another, > MySQL attempts to map the data values, but if the character sets are incompatible, there may be data loss. MySQL Reference ManualAs 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.
Comments
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!
Comments
