Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the MySQL database character set. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

I. 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).

II. Character set and character order supported by MySQL

MySQL supports multiple character sets and character orders.

A character set corresponds to at least one character order (usually one to many).

Two different character sets cannot have the same character order.

Each character set has a default character order.

1. View supported character sets

You can view the character sets supported by MYSQL in the following ways.

SHOW CHARACTER SET; select * from information_schema.HARACTER_SETS

two。 View supported character order

You can view the character order supported by MYSQL as follows.

SHOW COLLATION WHERE Charset = 'utf8'; SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME= "utf8"

3. The character set of the system (character_set_system)

Character_set_system is the character set of metadata, that is, all metadata uses the same character set. Imagine that if the metadata uses different character sets, it is difficult to display the relevant information in INFORMATION_SCHEMA between different lines. At the same time, the character set should be able to support multiple languages, and it is convenient for people with different languages to use their own languages to name database, table and column. MySQL chooses UTF-8 as the metadata encoding, fixed with the source code.

View the system character set:

Select @ @ global.character_set_system

Because there are very few settings, there is no introduction to the assignment.

4. Server character set, character order (character_set_server/collation_server)

When create database does not specify charset/collation, character_set_server/collation_server is used. These two variables can be set dynamically, with a session/global level.

In the source code, character_set_server/collation_server actually corresponds to a variable, because a collation corresponds to a charset, so only the collation_server of the CHARSET_INFO structure is recorded in the source code. When the character_set_server is modified, the default collation of the corresponding charset is selected. For other variables that have both charset and collation, the source record is also the record collation.

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

1. View server character set, character order

Correspond to two system variables character_set_server and collation_server respectively.

SET GLOBAL SHOW_COMPATIBILITY_56=ON; SHOW VARIABLES LIKE "character_set_server"; SHOW VARIABLES LIKE "collation_server"

two。 Specify when you start 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

3. Profile 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_ci init-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

6. Example

Make the default charset and collation for creating database different by setting different character_set_server in session.

Set character_set_server='utf8'; create database D1; select * from information_schema.schemata where SCHEMA_NAME='d1'; set character_set_server='latin1'; create database d2; select * from SCHEMATA where SCHEMA_NAME='d2'

5. Database character set, character order (character_set_database/collation_database)

Specifies 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. The session level of this variable value represents the charset/collation of the current database. This variable may be modified to read-only in later source code versions, and it is not recommended to modify this value. Its global level variable is also removed later.

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 a 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

two。 View the character set / character order of the database

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

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

Mysql > use test_schema; mysql > SELECT @ @ character_set_database, @ @ collation_database

Check the character set and database of test_schema (no need to switch default database):

Mysql > SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name= "test_schema"

View the statement that creates the database to view the character set:

Mysql > SHOW CREATE DATABASE test_schema

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

Specifies that the 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

two。 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.

SHOW TABLE STATUS FROM test_schema\ G

Method 2: check the information of information_schema.TABLES.

USE test_schema; SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE

Method 3: confirm through SHOW CREATE TABLE.

SHOW CREATE TABLE test_table

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

two。 View the character set / character order of column

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.

These are the MySQL database character sets shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.