Shulou(Shulou.com)06/01 Report--

This article mainly introduces the setting method of MySQL character set, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

I. Overview of the content

In the process of using MySQL, it is very important to understand the concept of character set, character order, and the impact of different settings on data storage and comparison. The problem of "garbled code" encountered by many students in their daily work is probably due to inadequate understanding of character set and character order and missetting.

From shallow to deep, this paper introduces the following contents:

The basic concept and relation of character set and character order

MySQL supported character set, character order setting level, and the relationship between each setting level

Viewing and setting of server, database, table, column level character set and character order

When should the character set and character order be set

II. The concept and relation of character set and character order

MySQL provides different character set support for data storage. In the data comparison operation, it provides different character order support.

MySQL provides different levels of settings, including server level, database level, table level, column level, which can provide very precise settings.

What is the character set and character order? To put it simply:

Character set (character set): defines characters and their encoding.

Character order (collation): defines the comparison rules for characters.

For example:

There are four characters: a, B, a, b, the codes of these four characters are A = 0, B = 1, a = 2, b = 3, respectively. The character + encoding here forms the character set (character set).

What if we want to compare the size of two characters? For example, A, B, or a, b, the most intuitive way to compare is to use their coding, for example, because 0

< 1，所以 A < B。 另外，对于A、a，虽然它们编码不同，但我们觉得大小写字符应该是相等的，也就是说 A == a。 这上面定义了两条比较规则，这些比较规则的集合就是collation。 同样是大写字符、小写字符，则比较他们的编码大小； 如果两个字符为大小写关系，则它们相等。 三、MySQL支持的字符集、字符序 MySQL支持多种字符集 与 字符序。 一个字符集对应至少一种字符序（一般是1对多）。 两个不同的字符集不能有相同的字符序。 每个字符集都有默认的字符序。 上面说的比较抽象，我们看下后面几个小节就知道怎么回事了。 1、查看支持的字符集 可以通过以下方式查看MYSQL支持的字符集。 方式一： mysql>

SHOW CHARACTER SET +-+-+ | Charset | Description | Default collation | Maxlen | +-- -- + | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 |. Omit

Method 2:

Mysql > use information_schema;mysql > select * from CHARACTER_SETS +-+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +- -+-+ | big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 | | dec8 | dec8_swedish_ci | DEC West European | 1 |. Omit

When viewing using SHOW CHARACTER SET, you can also add WHERE or LIKE qualifications.

Example 1: use WHERE qualification.

Mysql > SHOW CHARACTER SET WHERE Charset= "utf8" +-+ | Charset | Description | Default collation | Maxlen | +-+ | Utf8 | UTF-8 Unicode | utf8_general_ci | 3 | +-+ 1 row in set (0.00 sec)

Example 2: use LIKE qualification.

Mysql > SHOW CHARACTER SET LIKE "utf8%" +-+ | Charset | Description | Default collation | Maxlen | +- -+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | +-+ 2 rows in set (0.00 sec)

2. View the supported character order

Similarly, you can view the character order supported by MYSQL as follows.

Method 1: view it through SHOW COLLATION.

As you can see, the utf8 character set has more than 10 character orders. Determine whether the default character order is based on whether the value of Default is Yes.

Mysql > SHOW COLLATION WHERE Charset = 'utf8' +-- +-+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-+- -- + | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 |. Slightly

Method 2: query information_schema.COLLATIONS.

Mysql > USE information_schema;mysql > SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME= "utf8" +-+-+ | COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | +- -+-+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | Yes | 1 | utf8_unicode_ci | utf8 | 192 | Yes | 8 |

3.Nomenclature of character order

The naming of the character order, prefixed with its corresponding character set, as shown below. For example, the character order utf8_general_ci indicates that it is the character order of the character set utf8.

You can refer to the official documentation for more rules.

MariaDB [information_schema] > SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM COLLATIONS WHERE CHARACTER_SET_NAME= "utf8" limit 2 +-+-+ | CHARACTER_SET_NAME | COLLATION_NAME | +-+-+ | utf8 | utf8_general_ci | | utf8 | utf8_bin | +- -+-+ 2 rows in set (0.00 sec) 4. Character set and character order of server

Purpose: when you create a database, and do not specify the character set, character order, server character set, server character order will be the default character set, sorting rules for the database.

How to specify: when the MySQL service starts, it can be specified through command line parameters. It can also be specified through the variables of the configuration file.

Server default character set, character order: when MySQL compiles, it is specified by compilation parameters.

Character_set_server and collation_server correspond to server character set and server character order respectively.

1. View server character set and character order

Correspond to two system variables character_set_server and collation_server respectively.

Mysql > SHOW VARIABLES LIKE "character_set_server"; mysql > SHOW VARIABLES LIKE "collation_server"

2. Specify when starting the service

You can specify the server character set and character order when the MySQL service starts. If not specified, the default character order is latin1 and latin1_swedish_ci

Mysqld-- character-set-server=latin1\-- collation-server=latin1_swedish_ci

Specify the server character set separately, in which case the server character order is the default character order latin1_swedish_ci of latin1.

Mysqld-character-set-server=latin1

3. Configuration file assignment

In addition to being specified in the command line parameters, it can also be specified in the configuration file, as shown below.

[client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] collation-server = utf8_unicode_ciinit-connect='SET NAMES utf8'character-set-server = utf8

4. Runtime modification

Example: runtime modification (invalid after reboot, if you want to remain unchanged after reboot, you need to write it into the configuration file)

Mysql > SET character_set_server = utf8

5. Specify the default character set and character order at compile time

The default values of character_set_server and collation_server can be specified through the compilation option when MySQL compiles:

Cmake. -DDEFAULT_CHARSET=latin1\-DDEFAULT_COLLATION=latin1_german1_ ci V, character set and character order of database

Purpose: specify the character set and character order at the database level. Different character sets / character orders can be specified for databases under the same MySQL service.

1. Set the character set / character order of the data

You can specify the character set and sorting rules of the database through CHARACTER SET and COLLATE when creating and modifying the database.

Create the database:

CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]

Modify the database:

ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]

Example: create the database test_schema, the character set is set to utf8, and the default collation is utf8_general_ci.

CREATE DATABASE `test_ schema` DEFAULT CHARACTER SET utf8

2. View the character set / character order of the database

There are three ways to view the character set / character order of the database.

Example 1: check the character set and collation of test_schema. (you need to switch the default database)

Mysql > use test_schema;Database changedmysql > SELECT @ @ character_set_database, @ @ collation_database +-- +-- + | @ @ character_set_database | @ @ collation_database | +-- +-- + | utf8 | utf8_ General_ci | +-- +-- + 1 row in set (0.00 sec)

Example 2: you can also view the character set and database of test_schema with the following command (no need to switch the default database)

Mysql > SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name= "test_schema" +-+ | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +-+- -+-+ | test_schema | utf8 | utf8_general_ci | +-+ 1 row in set (0.00 sec)

Example 3: you can also view the character set by looking at the statement that creates the database.

Mysql > SHOW CREATE DATABASE test_schema +-+-- + | Database | Create Database | +-+- -- + | test_schema | CREATE DATABASE `test_ schema` / *! 40100 DEFAULT CHARACTER SET utf8 * / | +-- -+ 1 row in set (0.00 sec)

3. How to determine the database character set and character order

If CHARACTER SET or COLLATE is specified when the database is created, the corresponding character set and collation shall prevail.

When creating a database, if no character set and collation are specified, character_set_server and collation_server shall prevail.

VI. Character set and character order of table

The syntax for creating and modifying tables is as follows. Character set and character order can be set through CHARACTER SET and COLLATE.

CREATE TABLE tbl_name (column_list) [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]] ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]

1. Create table and specify character set / character order

As an example, the specified character set is utf8 and the character order is the default.

CREATE TABLE `test_ schema`.`test _ table` (`id`schema`', PRIMARY KEY (`id`) COMMENT'') DEFAULT CHARACTER SET = utf8

2. View the character set / character order of table

Similarly, there are three ways to view the character set / character order of table.

Method 1: check the table status through SHOW TABLE STATUS, and note that Collation is utf8_general_ci and the corresponding character set is utf8.

MariaDB [blog] > SHOW TABLE STATUS FROM test_schema\ G * * 1. Row * * Name: test_table Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 11534336 Auto_increment: NULL Create_time: 2018-01-09 16 : 10:42 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (sec)

Method 2: check the information of information_schema.TABLES.

Mysql > USE test_schema;mysql > SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table"; +-+ | TABLE_COLLATION | utf8_general_ci | +-+

Method 3: confirm through SHOW CREATE TABLE.

Mysql > SHOW CREATE TABLE test_table +- -+ | Table | Create Table | +-+- -+ | test_table | CREATE TABLE `test_ table` (`id` int (11) NOT NULL PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-+- -+ 1 row in set (0.00 sec)

3. How to determine the table character set and character order

Suppose the values of CHARACTER SET and COLLATE are charset_name and collation_name, respectively. If you create a table:

When charset_name and collation_name are defined, charset_name and collation_name are used.

If only charset_name is specified, but collation_name is not, then the character set is charset_name and the character order is the default character order corresponding to charset_name.

If only collation_name is specified, but charset_name is not, then the character order is collation_name and the character set is the character set associated with collation_name.

Charset_name, collation_name are not clear, then use the database character set, character order setting.

7. Character set and sorting of column

For columns with types of CHAR, VARCHAR, TEXT, you can specify character set / character order. The syntax is as follows:

Col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name] [COLLATE collation_name]

1. Add column and specify character set / collation

The example is as follows: (similar to creating table)

Mysql > ALTER TABLE test_table ADD COLUMN char_column VARCHAR (25) CHARACTER SET utf8

2. View the character set / character order of column

Examples are as follows:

Mysql > SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= "test_schema" AND TABLE_NAME= "test_table" AND COLUMN_NAME= "char_column" +-+-+ | CHARACTER_SET_NAME | COLLATION_NAME | +-+-+ | utf8 | utf8_general_ci | +-+- -+ 1 row in set (0.00 sec)

3. Column character set / collation determination

Suppose the values of CHARACTER SET and COLLATE are charset_name and collation_name, respectively:

If both charset_name and collation_name are clear, the character set and character order shall be subject to charset_name and collation_name.

If only that charset_name,collation_name is not specified, the character set is charset_name and the character order is the default character order of charset_name.

If only that collation_name,charset_name is not clear, the character order is collation_name and the character set is the character set associated with collation_name.

If charset_name and collation_name are not clear, the character set and character order of table shall prevail.

Choose when to set the character set and character order

In general, it can be configured in three places:

Configure when you create the database.

Configure mysql server when it starts.

When compiling mysql from source code, configure it with compilation parameters

1. Method 1: configure when creating a database

This approach is flexible and secure, and it does not rely on the default character set / character order. Specify the character set / character order when you create the database, and then inherit the character set / character order of the corresponding database when you create table and column, if not specified.

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci

2. Mode 2: configure mysql server when it starts.

You can add the following configuration so that character-set-server and collation-server will be configured when mysql server starts.

When you create a database/table/column through mysql client and no declared character set / character order is displayed, then character-set-server/collation-server is used as the default character set / character order.

In addition, the character set / character order when connecting client and server still needs to be set through SET NAMES.

[mysqld] character-set-server=utf8collation-server=utf8_general_ci

3. Method 3: when compiling mysql from source code, set it through compilation parameters

If-DDEFAULT_CHARSET and-DDEFAULT_COLLATION are specified at compile time, then:

When you create database and table, they are used as the default character set / character order.

When client connects to server, it is used as the default character set / character order. (no separate SET NAMES)

Shell > cmake. -DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci Thank you for reading this article carefully. I hope it will be helpful for everyone to share the content of the MySQL character set. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you have any problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.