Understanding MySQL Character Set

A little understanding of MySQL Character Set Although this article aims to understand, but a little understanding can also, after all, Zhuge Kong Ming is only based on the character value can have the so-called character set concept, some character sets may need more CPU, consume more memory and disk space, or even affect index usage. This does not include messy and Visible data. It is still necessary for us to take some time to understand the MySQL character set. First, we can intuitively understand the character set used 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 ways: ① the default setting when creating an object is a layer-by-layer inheritance default setting: server → DB → Table → Column the top layer sets the default value for the underlying layer, the underlying can be discarded, there is no specified character set, it can be displayed as the specified character set, it can be discarded ② when the Server communicates with 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, and the Server converts character_set_client to character_set_connection for SQL processing. When the result set is returned to the client, the Server again converting character_set_connection to character_set_result, and then returning the three character sets involved above, we can set names once to achieve mutual conversion between character sets requires additional system overhead, how do we know? Explain extended + show warnings. How can we avoid this implicit conversion? Here we introduce a method called "simple principle", as follows) selecting the appropriate character set, and then selecting the appropriate character set for certain columns based on the business, implies some surprises in the MySQL Character Set, mainly including three: ① interesting character_set_database when character_set_database and character_set_server are different, and the default Character Set of the database is determined by the latter. You cannot directly modify csd or change css to change csd, changing the default Character Set of the library, csd changes accordingly, and css determines the default Character Set of the library. Therefore, when the database is connected to the mysql instance and no database is specified, the default character set is the same as css ② load data infile. the best practices are as follows: use Library; set names character set; start to load data; this uses the unified character set to avoid mixing "Character set style" ③ select into outfile this line No transcoding operation is performed! Some people say that, no matter 37-21, utf8 is all used, and the whole world is clean, but this not only consumes more disk space, but also brings about performance sacrifices. Why? Because utf8 is a multi-byte character set, for example, a Chinese character is three bytes, this will bring two problems: ① A waste of space, such as char (10) it may open up 30 bytes of space, even if the index length limit is not required, mysql always assumes a single character of three bytes, resulting in the maximum index length to 1/3 text so far, the effect is clear, subsequent thoughts, and continue leading Edge