A little understanding of MySQL character set _ MySQL

Understanding MySQL character set bitsCN.com

Understanding MySQL character set

Although this article is intended to understand, but it can be understood a little. after all, Zhuge Kong Ming is

Only character-based values have the so-called character set concept.

Some character sets may require more CPU, more memory and disk space, or even affect index usage.

This does not include gibberish,

Obviously, it is necessary for us to take some time to understand the MySQL character set.

First, you can intuitively understand the character set used for display under each step:

[SQL] # contains three levels: DB, Table, Column mysql> create database d charset utf8; Query OK, 1 row affected (0.04 sec) mysql> create table d. t-> (str varchar (10) charset latin1)-> default charset = utf8; Query OK, 0 rows affected (0.05 sec)

If no display is specified? How is MySQL set? There are two channels:

① Default settings when creating an object

This is the default settings for layer-by-layer inheritance:

Server → DB → Table → Column

The top layer sets the default value for the bottom layer, the bottom layer can be discarded,

No character set specified.

Display the specified character set, which can be discarded

② Settings for communication between the server and the client

When the client submits an SQL statement to MySQL, MySQL Server always assumes that the character set of the client is character_set_client.

Then, the Server converts character_set_client to character_set_connection for SQL processing,

When the returned result set is sent to the client, the Server converts character_set_connection to character_set_result, and then returns

The three character sets involved above can be done once through set names

Conversion between character sets requires additional system overhead,

How do I know?

Explain extended + show warnings

So how can we avoid this implicit conversion?

Here we introduce a method called "simple principle", as follows:

Select the appropriate character set for the server (or database), and then select the appropriate character set for certain columns based on the business.

Some surprises are implied in the MySQL character set, mainly including three:

① Interesting character_set_database

When character_set_database and character_set_server are different, the default character set of the database is determined by the latter.

You cannot directly modify csd or css to change csd, because the default character set of csd is the same as that of the library,

Changing the default character set of the library, csd changes, and css determines the default character set of the library.

Therefore, when the mysql instance is connected and no library is specified, the default character set is the same as that of css.

② Load data infile

The best practices are as follows:

Use Library;

Set names character set;

Start to load data;

In this case, the unified character set is used to avoid mixing "character set style"

③ Select into outfile

This action has not been transcoded!

Some people say that, whether 37 or 21, all utf8 is used, and the whole world is clean.

However, this not only consumes more disk space, but also sacrifices performance.

Why? Because utf8 is a multi-byte character set, for example, a Chinese character is three bytes.

This will bring about two problems:

① A waste of space, such as char (10) may open up 30 bytes of space, even if not needed

② Index length limit, mysql always assumes one character in three bytes, resulting in the maximum index length becoming 1/3

At this point, the general idea is clear, the future thoughts, and the new frontier

BitsCN.com