Mysql Server Character Set query and setting

1. View MySQL database servers and database character sets

Show variables like '% char % ';

2. view the character sets supported by currently installed MySQL.

Show charset;

3. Set the encoding:

Modify my. cnf

Vi/etc/my. cnf

Add under [client]

Default-character-set = utf8

Add under [mysqld]

Default-character-set = utf8

Save and restart mysql;

Modify the character set of a Database

Mysql> use mydb

Mysql> alter database mydb character set UTF-8 (utf8 );

Creates a database and specifies the character set of the database.

Mysql> create database mydb character set UTF-8 (utf8 );

Or use the mysql command to modify the encoding.

Use the MySQL command line to modify:

Set character_set_client = utf8;

Set character_set_connection = utf8;

Set character_set_database = utf8;

Set character_set_results = utf8;

Set character_set_server = utf8;

Set character_set_system = utf8;

Set collation_connection = utf8;

Set collation_database = utf8;

Set collation_server = utf8;

Mysql modifies the table and field Character Set

Change the default character set and all character columns (CHAR, VARCHAR, TEXT) of the table to the new character set:

Alter table tbl_name convert to character set character_name [COLLATE...]

For example, alter table logtest convert to character set utf8 COLLATE utf8_general_ci;

Only modify the default Character Set of the table:

Alter table tbl_name default character set character_name [COLLATE...];

For example, alter table logtest default character set utf8 COLLATE utf8_general_ci;

Modify the character set of a field:

Alter table tbl_name CHANGE c_name character set character_name [COLLATE...];

For example, alter table logtest CHANGE title VARCHAR (100) character set utf8 COLLATE utf8_general_ci;

View the database code:

Show create database db_name;

View the table encoding:

Show create table tbl_name;

View the field encoding:

Show full columns from tbl_name;

Character Set conversion process in MySQL

1. MySQL Server converts the request data from character_set_client to character_set_connection when receiving the request;

2. Before performing internal operations, convert the request data from character_set_connection to the internal operation character set. The method is as follows:

• SET the character set value for each data field;

• If the preceding value does not exist, use the default character set Value of the corresponding data table (MySQL extension, non-SQL standard );

• If the preceding value does not exist, use the default character set Value of the corresponding database;

• If the preceding value does not exist, use character_set_server to set the value.

3. Convert the operation result from the internal character set to character_set_results.

Other considerations

• Default_character_set in my. cnf only affects the connection character set when the mysql command is used to connect to the server, and does not affect applications that use the libmysqlclient library!

• SQL function operations on fields are generally performed using the internal operation character set, which is not affected by the character set setting of the connection.