Shulou(Shulou.com)05/31 Report--

Editor to share with you about the MySQL character set garbled how to do, I believe that most people do not understand, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

Preface

Character set is a set of rules for symbols and coding. No matter in oracle database or in mysql database, there is a problem of character set selection. If the character set is not selected correctly in the database creation phase, then the character set may need to be replaced in the later stage, and the character set replacement is a costly operation, and there are certain risks, so we recommend that at the beginning of the application. Correctly select the appropriate character set according to the requirements to avoid unnecessary adjustments in the later stage.

Actual combat

1. Install MySQL database

2. Garbled demonstration

Mysql > show variables like 'character_set%' +-- +-- + | 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/local/mysql/share/charsets/ | +-+ | -- + 8 rows in set (0.00 sec) mysql > insert into test.table10 values (1 'crane in the cloud') Query OK, 1 row affected (0.00 sec) mysql > select * from test.table10;+-+-+ | id | names | +-+-+ | 1 | Yunzhonghe | +-+-+ 1 row in set (0.00 sec) mysql > set names latin1;Query OK, 0 rows affected (0.00 sec) mysql > select * from test.table10 +-+-+ | id | names | +-+-+ | 1 |? | +-+-+ 1 row in set (0.00 sec) mysql > show variables like 'character_set%' +-- +-- + | Variable_name | Value | + -+ | character_set_client | latin1 # character set used by client source data | character_set_connection | latin1 # connection layer character set | character_set_database | utf8 # default character set of the selected database | character_set_filesystem | binary | character_set_results | latin1 # query result character set | | character_set_server | utf8 # default internal operation character set | | character _ set_system | utf8 # system metadata (field name, etc.) character set | character_sets_dir | / usr/local/mysql/share/charsets/ | +-- +-- + 8 rows in set (0.00 sec)

Solution to garbled code

As you can see from the above, set names latin1; changed three parameters.. As long as the client side, the three MySQL character-set-client,table charset character sets are exactly the same, you can guarantee that there will not be garbled.

Method:

1. Execute set names xxx in mysql command line mode

Mysql > set names utf8; Query OK, 0 rows affected (0.01 sec)

2. Specify the default character set when logging in to mysql

[root@node1 ~] # mysql-S / tmp/mysql.sock4-- defaults-character-set=utf8 #-S specifies the socket file for multi-instance mysql, and uses-- defaults-character-set to specify the default character set.

3. Modify the / etc/sysconfig/i18n file if my.cnf does not specify the default character set.

Vim / etc/sysconfig/i18n LANG='zh_CN.UTF-8' # if my.cnf is not specified, the system character set is used by default

4. Modify the my.cnf file. The following two fields are available in any one of the fields.

[client] default-character-set=latin1 [mysql] default-character-set=latin1

The first two are temporary solutions, and the last two are permanent solutions.

View character set

1. Check the current character set settings of the system, and log in to mysql for execution after modification. Show variables lile 'character_set%'

Mysql > show variables like 'character_set%';# I modified to utf8, so the client's three parameters are utf8. You can set your own character set. +-- +-- + | 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/local/mysql/share/charsets/ |

2. Mysql common character set. Execute the command show character set; to view the character set supported by the system.

Mysql > show character set +-+ | Charset | Description | Default collation | Maxlen | +-+- -+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | latin1 | cp1252 West European | latin1_swedish _ ci | 1 | # Common | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | tis620 | TIS620 Thai | tis620_thai_ci | 1 | euckr | | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | greek | ISO 8859-7 Greek | greek_general_ci | 1 | cp1250 | Windows Central European | cp1250_general_ci | 1 | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | # commonly used | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | armscii8 | ARMSCII-8 Armenian | armscii8 | _ general_ci | 1 | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | # Common | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | cp866 | DOS Russian | cp866_general_ci | 1 | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | macce | Mac Central European | macce_general_ci | 1 | macroman | Mac West European | macroman_general_ci | 1 | cp852 | DOS Central European | cp852_general_ci | 1 | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | # commonly used | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | cp1257 | Windows Baltic | cp1257_general_ci | 1 | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | binary | Binary pseudo charset | binary | 1 | geostd8 | | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +-+-- + 39 rows in set (0.00 sec) |

Character set selection

1. If you deal with all kinds of text and publish it to different countries and regions, select Unicode. , for mysql, check the number utf-8.

2. Only Chinese, with a large amount of data and high performance requirements, choose gbk.

3. To deal with mobile animal networking business, choose utf8mb4.

It is recommended to use a small character set as far as possible on the premise that it can fully meet the application. Because a smaller character set means that it can save space, reduce the number of bytes transmitted over the network, and indirectly improve the performance of the system due to the smaller storage space.

Server character set settin

[mysqld]... character-set-server=utf8 # add this statement to set the server-side character set.

When the mysql server is restarted, these two parameters are changed to the set values.

| | character_set_server | utf8 | character_set_database | utf8 |

Toggle character set

Convert one encoded database into another encoded data.

Alter database dbname character set xxx; # is only valid for later data, not for previous data. Basically do not use

Common schemes for converting database character sets

Mysqldump-S / tmp/mysql.sock4-- default-character-set=utf8-d test > / data/test- `date +% F`.sql # 1, export table structure without exporting data vim / data/test- `date +% F`.sqlDROP TABLE IF EXISTS `table10`; / *! 40101 SET @ saved_cs_client = @ @ character_set_client * /; / *! 40101 SET character_set_client = utf8 * /; CREATE TABLE `table10` (`id`int (11) DEFAULT NULL, `names` char (20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 # 2. Change the CHARSET to the character set mysqldump-S / tmp/mysql.sock4-- extended-insert-- no-create-info test > / data/test-data- `date +% F`.sql # 3, export the data content vim / data/test-data- `date +% F`.sqlLOCK TABLES `table10` WRITE;set names utf8; # 4. This line is added. Specify the character set INSERT INTO `table10` VALUES you want to convert. Mysql > create database if not exists test # 5. Create a database and store the converted data Query OK, 1 row affected, 0 warning (0.00 sec) mysql-S / tmp/mysql.sock4 test < / data/test- `date +% F`.sql # 6, import table structure mysql-S / tmp/mysql.sock4 test < / data/test-data-2015-09-25.sql # 7, import table data above are all the contents of the article "what to do about garbled MySQL character set", thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.