Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql change column collation and character set of information schema

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

like image 358
MySQL DBA Avatar asked Jun 11 '09 06:06

MySQL DBA


People also ask

What is difference between character set and collation?

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

How do I change MySQL encoding to UTF-8?

To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace dbname with the database name: Copy ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci; To exit the mysql program, type \q at the mysql> prompt.


2 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];
like image 77
Haim Evgi Avatar answered Oct 26 '22 07:10

Haim Evgi


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.

like image 30
Chris Vest Avatar answered Oct 26 '22 05:10

Chris Vest