MySQL server character set query and Setup method

1. View MySQL database server and database character set

Show variables like '%char% ';

2. View the character set supported by the currently installed MySQL.

Show CharSet;

3, set the code:

Modify MY.CNF

Vi/etc/my.cnf

Add under [client]

Default-character-set=utf8

Add under [mysqld]

Default-character-set=utf8

Save, restart MySQL can;

Modifying the character set of a database

Mysql>use MyDB

Mysql>alter database MyDB Character set utf-8 (UTF8);

Create a database Specify the character set of the database

Mysql>create database MyDB Character set utf-8 (UTF8);

or modify the code with the MySQL command

Modify via MySQL command line:

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 modify table, field character set

Change the table default character set and all character columns (Char,varchar,text) to the new character set:

ALTER TABLE tbl_name CONVERT to CHARACTER SET character_name [COLLATE ...]

such as: ALTER TABLE logtest CONVERT to CHARACTER SET UTF8 COLLATE utf8_general_ci;

Just modify the default character set for the table:

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE ...];

such as: ALTER TABLE logtest DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

To modify the character set of a field:

ALTER TABLE tbl_name Change c_name c_name CHARACTER SET character_name [COLLATE ...];

such as: ALTER TABLE logtest Change title title VARCHAR (m) CHARACTER SET UTF8 COLLATE utf8_general_ci;

To view the database encoding:

Show CREATE DATABASE db_name;

View Table Encoding:

Show CREATE TABLE tbl_name;

To view the field encoding:

Show full COLUMNS from Tbl_name;

The character set conversion process in MySQL

1. When the MySQL server receives the request, converts the request data from character_set_client to character_set_connection;

2. The request data is converted from character_set_connection to the internal operation character set prior to internal operation, and the method is determined as follows:

• Use the character set set value for each data field;

• If the above values do not exist, then use the corresponding data table default CHARACTER Set set value (MySQL extension, non-SQL standard);

• If the above values do not exist, then use the corresponding database default CHARACTER set set value;

• If the above values do not exist, use the Character_set_server set value.

3. Converts the result of the operation from the internal operation character set to Character_set_results.

Other considerations

The Default_character_set setting in my.cnf only affects the connection character set when the MySQL command connects to the server, and does not have any effect on applications that use the Libmysqlclient library!

• SQL function operations on fields are usually performed in the internal operation character set, and are not affected by the connection character set setting.