MySQL Character set

MySQL supports storing data using different character sets, supporting four levels of character set settings: Server, database, table, and column. MyISAM, MEMORY and InnoDB three storage engines support a different set of character sets.

This article references: MySQL official website http://www.mysql.com/

I. Character sets and alignment rules

Character set (character Set): is a set of symbols and encodings

Alignment rule (collation): is a set of words transmitting the rules of the pair

Example:

Suppose A=0, b=1, a=2, b=3. Then a is a symbol, and 0 is a code. The sum of 4 letters and their encodings is called a character set.

1. If we want to compare the size of the characters A and B, then we use their codes 0 and 1 to compare, get a<b.

This alignment rule contains only one rule (a coded comparison), which we call a binary alignment rule.

2. If we are not case-sensitive, then A is =a.

This alignment rule consists of two rules, 1) uppercase and lowercase letters equal. 2) compare codes. What we call a case-sensitive alignment rule.

However, in real life, the character set contains a large number of letters and symbols. The rules of comparison also contain numerous rules.

What can MySQL do?

1) Use a different character set to store characters.

2) Use different comparison rules to compare characters.

3) Mix different character sets in the same server, database, and even data tables.

Ii. supported character sets and alignment rules in MySQL

After installing MySQL server, there are two ways to view its supported character sets and collation.

1) View system table: Infomation_schema character_sets

2) Use command: SHOW character_set (can follow like where conditional expression)

A set of character sets may use a number of comparison rules, and the following methods can be viewed:

1) View system table: Infomation_schema collations

2) Use command: SHOW COLLATION (can follow like where conditional expression)

Iii. specifying character set and alignment rules in MySQL

Server character set and Collation-server (server-level character set)
1. Specify when the service is started
Mysqld--character-set-server=latin1--collation-server=latin1_swedish_ci
2. Configuration file designation
[Mysqld]
Character-set-server=latin1
Collation-server=latin1_swedish_ci

Database character set and collation (DB level)
1. When created
Create Database Db_name
[[DEFAULT] CHARACTER SET Charset_name]
[[DEFAULT] COLLATE collation_name]

2. When modified
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET Charset_name]
[[DEFAULT] COLLATE collation_name]
Note: All the database options are stored in the db.opt text file of the database file

View character set information for the current database settings

Use db_name;
SELECT @ @character_set_database, @ @collation_database;

SELECT Default_character_set_name, Default_collation_name
From INFORMATION_SCHEMA. schemata WHERE schema_name = ' db_name ';

Table character set and collation (Tables level)

Same database level

CREATE TABLE tbl_name ( column_list )

[[DEFAULT] CHARACTER SET charset_name ]

[COLLATE collation_name ]]

ALTER TABLEtbl_name

[[DEFAULT] CHARACTER SET charset_name ]

[COLLATE collation_name ]

Column character set and collation (columns level)

col_name{CHAR | VARCHAR | TEXT} ( col_length )

[CHARACTER SET charset_name ]

[COLLATE collation_name ]

Examples:

CREATE TABLE T1

(

Col1 VARCHAR (5)

CHARACTER SET Latin1

COLLATE Latin1_german1_ci

);

ALTER TABLE T1 MODIFY

Col1 VARCHAR (5)

CHARACTER SET Latin1

COLLATE Latin1_swedish_ci;

Character and collation of strings (normal string level)

Select ' string ' is using the character_set_connection specified character set

Character set-specific syntax

[_ charset_name ] ' string [COLLATE collation_name ]

Examples:

SELECT ' abc ';

SELECT _latin1 ' abc ';

SELECT _binary ' abc ';

SELECT _utf8 ' abc ' COLLATE UTF8_DANISH_CI;

Iv. character set and alignment rules involved in Client-server program interaction

The final storage encoding of the data

1. System variables Character_set_server and Collation_server set the character set and alignment order of the server program

1. System variables Character_set_database and collation_database identify the default character set for the database

Encoding used by Client and server for information interaction

1. System variable character_set_client identifies the character set used by the client program to send to the server

2.Server Translate SQL commands from the client using the character set of the character_set_connection and collation_connection identities

3.character_set_results:server returns the encoding used to execute the result

The character encoding used by the Client program

Note: The default encoding on the OS is used by default. If MySQL does not support OS default encoding, use MySQL default encoding latin1

Code Modification Method:

1. When the client starts, add the option--default-character-set=char-name

2. Specified in configuration file My.ini

[MySQL]

Default-character-set=char-name

Note: At this point the C-s interaction encoding is automatically set to the above encoding

MySQL Character set