The character set defines the encoding of the characters, the more common is the ASCII, GBK, UTF8 encoding set,all encoding sets are compatible with the ASCII character set.
| Character |
Is the negation of the long |
Encoding method |
| ascii |
is |
single byte 7-bit encoding |
| GBK |
|
double-byte encoding |
| UTF8 |
No (variable length) |
1~4 byte encoding |
Unicode encoding is typically two bytes, which can encode 99% in characters, improving efficiency and memory space. For MySQL, the Unicode character set is UTF8.
When MySQL executes the explain command, the length of the Key_len is very much related to the character set.
(1). Additional Information for indexed fields: can be divided into variable length and fixed length data types. when an index field is fixed to a Long data type , such as Char,int,datetime, it needs to have an empty tag, which takes up to 1 bytes, and for the variable-length data type, such as: varchar, except for the empty tag, There is also a need for length information, which takes up to 2 bytes (so the variable length data type requires a total of three bytes of extra information);(Note: If the field is defined as non-empty, the empty tag will not consume bytes)
(2). also need to consider the table used by the character set, different character sets, GBK encoded as a character 2 bytes, UTF8 encoded by a character 3 bytes;
Support for multiple character sets in MySQL to view MySQL-supported character setsshow Charater Set (or use SELECT * from Information_schema.character_sets)
The character set of MySQL includes the character set (CHARACTER) and the proofing Rules (COLLATION), which defines how MySQL stores strings,proofing rules are used to define how strings are compared. Character sets and proofing rules are one-to-many relationships, and each character set has at least a proofing rule (the default proofing rule).
can use show collation like ' UTF8 ' View collation rules for a character set
Naming of proofing rules: Character Set name + language name + suffix. The suffix _ci is case insensitive , _cs is case sensitive, and _bin indicates that comparisons are based on character-encoded values and are language independent.
MySQL can support a variety of granular character sets, ranging from large to small: server > Database > Table > Fields. Settings for the MySQL character set:
when the encoding set is not set, the default is to use Latin1 as the server encoding set. when you add mysqld below in the/etc/my.cnf filecharacter_set_server = UTF8after restarting the MySQL service, execute the command again.
The character set and proofing rules for a database are specified when the database is created, you can also use ALTER DATABASE after you create the database to modify it. But note:if the data already exists in the database, because modifying the character set does not hold the existing data according to the new character set, you cannot modify the database's character set to directly modify the contents of the data. character set and proofing rules for the database:Show variables like ' character_set_Database'
The database does not specify a character set and proofing rules, the character set of the MySQL service and the default collation rules for that character set are used. You can also useCREATE Database Character_set2 charset GBK Collate gbk_chinese_ci to specify the character set and proofing rules.
table's character set and proofing rules:the table's character set and proofing rules can be executed when the table is created or modified using ALTER TABLE. Similarly, if there is already data in the table, modifying the character set will not affect the original record and will not be stored according to the new character set. If the table does not have a character set, it inherits its upper-level character set.
You can use Show create table to view it.
settings for the character set and proofing rules for client and server interactionfor client and server interoperability, MySQL provides three different parameters:character_set_client, Character_set_connection, Character_set_results , which represent the client, connect and return the result of the character set。 Typically, these three character sets should be the same point in order to ensure that the data is read and written correctly.
- Set names xxxx (need to be executed every time)
- Add default_character_set=xxxx (permanent change) to MySQL section in MY.CNF
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL Character set
