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

Basic concepts:

Character (Character) refers to the smallest semantic symbol in human language. For example,'A','B', etc.

Encoding means that given a series of characters, each character is assigned a numerical value, and the corresponding character is represented by a numerical value. For example, if we assign a value of 0 to the character'A' and a value of 1 to the character'B', then 0 is the encoding of the character'A'.

A character set (Character Set) is a collection of characters and encoding pairs.

Character order (Collation) refers to the comparison rules between characters in the same character set.

Character order names in MySQL follow naming conventions: they start with the character set name corresponding to the character order, and end with _ ci (for case insensitivity), _ cs (for case sensitivity), or _ bin (for comparison by coded value). For example, under the character order "utf8_general_ci", the characters "a" and "A" are equivalent.

Mysql > show variables like 'character%';-- View MySQL character set settings MySQL character set settings

+-+

| Variable_name | Value |? System variable:

+-+

| | character_set_client | utf8 |-character_set_client: the character set used by the client source data |

| | character_set_connection | utf8 |-character_set_connection: connection layer character set |

| | character_set_database | latin1 |-character_set_database: the default character set of the currently selected database |

| | character_set_filesystem | binary |-character_set_filesystem: file system character set |

| | character_set_results | utf8 |-character_set_results: query result character set |

| | character_set_server | latin1 |-character_set_server: internal operation character set (server character set) |

| | character_set_system | utf8 |-character_set_system: system metadata (field name, etc.) character set |

| | character_sets_dir | / usr/share/mysql/charsets/ |

+-+

8 rows in set (0.00 sec)

Detailed explanation:

1.

The character set and collation on the server (data) side can be specified layer by layer in four levels-server, database, table, column. When MySQL accesses data located in a column (column), if the character set and collation of column are not specified, table will be traced up; if table does not specify character set and collation, then database character set and collation will be used as default values; if database is still not specified, then server character set and collation will be used as default values.

two。

Where did the server character set and collation defaults come from? The answer is that both the configuration file (my.ini) and the mysqld (or mysqld-nt) can be specified in the command line arguments. If unfortunately you don't specify it at all in my.ini or on the command line, then MySQL will use the default character set specified when compiling MySQL-latin1.

3.

Data character set conversion process:

Character_set_client- > character_set_connection- > character_set_ (column,table,database,server)-> character_set_results

MySQL default character set

MySQL can specify the character set into a database, a table, and a column. Traditional programs do not use such complex configurations when creating databases and data tables, they use default configurations.

(1) when compiling MySQL, you specify a default character set, which is latin1

(2) when installing MySQL, you can specify a default character set in the configuration file (my.ini). If not, this value inherits from the

(3) when you start mysqld, you can specify a default character set in the command line parameters. If not, this value inherits from the configuration in the configuration file. In this case, character_set_server is set to the default character set.

(4) install MySQL select multilingual support, the installer will automatically set default_character_set to UTF-8 in the configuration file to ensure that all columns of all tables in all databases are stored in UTF-8 by default.

Mysql > create database demo;-- create a database without a specified character set, use the default character set to create a database

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | demo |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > show create database demo\ G;-- see what character set is used when creating the database.

* * 1. Row *

Database: demo

Create Database: CREATE DATABASE `demo` / *! 40100 DEFAULT CHARACTER SET latin1 * /

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql > create database demo default character set gbk collate gbk_chinese_ci;-- specify the character set to create the database

Query OK, 1 row affected (0.00 sec)

Mysql > show create database demo

+-+

| | Database | Create Database |

+-+

| | demo | CREATE DATABASE `demo` / *! 40100 DEFAULT CHARACTER SET gbk * / | |

+-+

1 row in set (0.00 sec)

Mysql > create database demo2 default character set utf8 collate utf8_general_ci;-- specify the character set to create the database

Query OK, 1 row affected (0.00 sec)

Modify the character set:

Method 1: (permanently effective)

1. Find out if there are my.cnf files in the / etc directory

# ls-l | grep my.cnf (look for the existence of a my.cnf file under / etc)

two。 If you don't have to copy one from / usr/share/mysql to / etc, there are five files with the suffix .cnf in the / usr/share/mysql directory, which are my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf; copy any one of them to the / etc directory and change it to a my.cnf file, and I choose my-medium.cnf:

# cp / usr/share/mysql/my-medium.cnf / etc/my.cnf

3. Modify the my.cnf file by adding default-character-set=utf8 ([client] [mysql]) in two places in the file,

Add character-set-server=utf8 to [mysqld]

Example (red is added):

[mysqld]

Character-set-server=utf8

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysql]

Default-character-set=utf8

[client]

Default-character-set=utf8

Validation: viewing database character set

Mysql > show variables like 'character%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | utf8 |

| | character_set_connection | utf8 |

| | character_set_database | utf8 |

| | character_set_filesystem | binary |

| | character_set_results | utf8 |

| | character_set_server | utf8 |

| | character_set_system | utf8 |

| | character_sets_dir | / usr/share/mysql/charsets/ |

+-+

Success!

Method 2: (temporarily effective)

Modify through the MySQL command line:

Mysql > set character_set_client=utf8

Query OK, 0 rows affected (0.00 sec)

Mysql > set character_set_connection=utf8

Query OK, 0 rows affected (0.00 sec)

Mysql > set character_set_database=utf8

Query OK, 0 rows affected (0.00 sec)

Mysql > set character_set_results=utf8

Query OK, 0 rows affected (0.00 sec)

Mysql > set character_set_server=utf8

Query OK, 0 rows affected (0.00 sec)

Mysql > set character_set_system=utf8

Query OK, 0 rows affected (0.01 sec)

Mysql > set collation_connection=utf8

Query OK, 0 rows affected (0.01 sec)

Mysql > set collation_database=utf8

Query OK, 0 rows affected (0.01 sec)

Mysql > set collation_server=utf8

Query OK, 0 rows affected (0.01 sec)

Summary:

1. If the database character set is not specified when you install mysql, the default database character set is latin1.

two。 When you select a database character set, you need to select the character set according to the developer.

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.