MYSQ View and set character sets, databases, tables, fields

First, set the code

LINUX modification VI/ETC/MY.CNF

WINDOWS My.ini

Add under [client]

Default-character-set=utf8

Add under [mysqld]

Default-character-set=utf8

Save, restart MySQL can;

setting up the character set when creating an object

1 Creating a database The character set of the specified database

Mysql>createdatabase IF not EXISTS mydb DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci

2 Specify the character set when creating a table

CREATETABLE table_name (ID int unsigned) DEFAULT CHARACTER SET UTF8 collateutf8_general_ci;

3 Specify the character set when creating a field

CreateTable table_name2 (id int,name char (a) CHARACTER SET UTF8 COLLATE utf8_general_ci);

Third, view the code

1. View MySQL database server and database character set

Show variables like '%char% ';

2, look at MySQL database server and database check the sorting method (proofing rules)

Showvariables like ' collation ';

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

Show CharSet;

4, view the current database encoding:

Show CREATE DATABASE db_name;

5, view the table code:

Show CREATE TABLE tbl_name;

6, View the field code:

Show full COLUMNS from Tbl_name;

Iv. modifying character sets

1. Modify the character set of the database

Mysql>usemydb

Mysql>alterdatabase mydb CHARACTER SET utf-8 (UTF8) COLLATE utf8_general_ci

2. Modify the character set of the table

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 CHARACTERSET 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 CHARACTERSET UTF8 COLLATE utf8_general_ci;

3. Modify the character set of the field:

ALTER TABLE tbl_namechange c_name c_name CHARACTER SET character_name [COLLATE ...];

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

v. Modify code with mysql command

Setcharacter_set_client=utf8; Client Character Set

Setcharacter_set_connection=utf8; Link Character Set

Setcharacter_set_database=utf8; Database Character Set

Setcharacter_set_results=utf8; Result Character Set

Setcharacter_set_server=utf8; Server Character Set

Set Character_set_system=utf8; System Character Set

Setcollation_connection=utf8; Link Proofing

Setcollation_database=utf8; Database Proofing

Setcollation_server=utf8; Server Proofing

Setnames character set, while modifying the character_set_client/character_set_connection/character_set_results character set.

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 default CHARACTER set set value of the corresponding datasheet (MySQL extension, non-SQL standard);

If the above values do not exist, then use the Defaultcharacter set set value of the corresponding database;

If the above values do not exist, the value is set using Character_set_server.

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

vii. mysql default character set

MySQL Specifies the character set that can be refined into a database, a table, a column, and what character set should be used. However, traditional programs do not use a complex configuration when creating databases and datasheets, and they use the default matching

, so where does the default configuration come from?

(1) When compiling MySQL, a default character set is specified, which is latin1;

(2) When installing MySQL, you can specify a default character set in the configuration file (My.ini), and if not specified, this value inherits from the compile-time designation;

(3) When starting mysqld, you can specify a default character set in the command-line arguments, and if not specified, this value inherits from the configuration in the configuration file, at which point the character_set_server is set as the default character set;

(4) When a new database is created, the character set of the database is set to Character_set_server by default unless explicitly specified;

(5) When a database is selected, Character_set_database is set to the default character set of the database;

(6) When creating a table in this database, the default character set of the table is set to Character_set_database, which is the default character set of the database;

(7) When a column is set in a table, the default character set of this column is the default character set of the table unless explicitly specified;

Viii. Other matters of concern

MySQL's character set support (Character Setsupport) has two aspects of character set (Character set) and sorting method (collation). The support for character sets is refined to four tiers: Servers (server), databases (database)

, datasheet (table), and connection (connection).

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 manipulation character set, and are not affected by the connection character set setting.

If there is no place to modify, then all the tables of all the database all the fields are stored in latin1, but if we install MySQL, we will generally choose multi-language support, that is, the installer will automatically set the Default_character_set in the configuration file to UTF-8, which ensures that all the fields of all the tables in all databases are stored UTF-8 by default.

If you want to bulk modify the character set of tables and fields, use the following command to generate an SQL file and then execute

SELECT
CONCAT (' ALTER TABLE ', TABLE_NAME, ' CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ')
From
Information_schema.tables
WHERE
Table_schema= ' database_name ' into outfile '/mysql/1.sql ';

Reproduced Original: http://blog.csdn.net/flcandclf/article/details/24769599