The character set in MySQL

Binary installation of the database may have garbled problems, the compiled installation of the database specified by default utf-8 does not produce garbled

1. Compile the installed character set condition

View the format of a character set mysql> show  create  table  sanlang  \g***************  1. row ***************************       table:  sanlangCreate Table: CREATE TABLE  ' Sanlang '   (   ' id '  int (11)  DEFAULT NULL,   ' name '  varchar ( default null)  ENGINE=InnoDB  default charset=utf81 row in set  (0.00 sec)  mysql> inserting data mysql>  Insert into  sanlang  values (22, "bangs laugh");     query ok, 1  row affected  (0.00 sec) test mysql> select * from sanlang;                    +------+ -----------+| id   | name      |+------+-----------+|      1 | xiaoming  | |     2 | xiaoli    | |     3 | tt        | |     4 | gg        | |    22 | testdata  | |    22 |  Bangs Laugh     |+------+-----------+6 rows in set   (0.00 SEC) mysql>

change character Set test (you can see that ID 22 is garbled after changing the character set):

mysql> set  names  gbk ; query ok, 0 rows affected  (0.00 sec)  mysql> select * from  sanlang;                       +------+----------+| id   | name      |+------+----------+|    1 | xiaoming | |     2 | xiaoli   | |     3 | tt       | |     4 | gg       | |    22 | testdata | |    22 | ц        |+------+----------+6  rows in set  (0.10 sec)  mysql> 

Introduction to common character sets for 2.mysql databases

650) this.width=650; "title=" (0RB) bu]21b_@fbywla~v}4.png "src=" http://s5.51cto.com/wyfs02/M01/83/6B/ Wkiom1dzlc3gymqeaaa8zycprwo022.png-wh_500x0-wm_3-wmp_4-s_2114651218.png "alt=" Wkiom1dzlc3gymqeaaa8zycprwo022.png-wh_50 "/>

3.MYSQL How to choose the right character set

1. If you deal with a wide variety of text, published to a different eye country area, MySQL UTF-8 (each kanji three themselves), if the application needs to handle English, Only a few Chinese characters utf8 better 2. If only need to support Chinese, and the amount of data is large, performance requirements are very high, optional GBK (booking, each Chinese characters occupy double bytes, English also accounted for double bytes), such as a large number of operations, comparative sorting, fixed long character set, faster, high performance 3. You may need to use Utf8mp4 Word Set of characters 4. Teacher advice: No special needs, choose UTF8 Character Set

4. View the system character set

mysql> show  character set ;mysql> show  variables   like  ' character_set% '; +--------------------------+-------------------------------------------+|  variable_name            | value                                       |+------ --------------------+-------------------------------------------+| character_set_client      | utf8                                        |   #客户端字符集 | character_set_connection | utf8                                        | #客户端连接字符集 |  character_set_database   | utf8                                        | #数据库字符集, configuration file designation or build table designation | character_set_ filesystem | binary                                      | #文件系统字符集 | character_set_results    | utf8                                        | #返回结果字符集 | character_set _server     | utf8                                        | #服务器字符集, configuration file designation or Database build table designation | character_set_system      | utf8                                        | #系统字符集 | character_sets_dir        | /application/mysql-5.5.49/share/charsets/ |+--------------------------+--------- ----------------------------------+8 rows in set  (0.00 sec) mysql>

5.set names control the character set of the client

You can see that set names just controls the character set of the client.

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       | /application/mysql-5.5.49/share/ charsets/ |+--------------------------+-------------------------------------------+8 rows in  set  (0.00 sec)  mysql> set  name  gbk;mysql> show   variables  like  ' character_set% '; +--------------------------+---------------------- ---------------------+| variable_name            |  Value                                       |+--------------------------+-------------------------------------------+| character_ set_client     | gbk                                         | |  character_set_connection | gbk                                         | |  character_set_database   | utf8                                        | |  character_set_filesystem | binary                                      | |  character_set_results    | gbk                                         | |  character_set_server     | utf8                                        | |  character_set_system     | utf8                                        | |  character_sets_dir       | /application/mysql-5.5.49/share/ charsets/ |+--------------------------+-------------------------------------------+8 rows in  set  (0.00 SEC)

6. Client Character Set a

1. Client character Set uniform method ①set names UTF8 ② the following three commands instead of set names (in fact, changed the character set of the client) set character_set_client = Gbkset Character_set_conne ction = Gbkset Character_set_results = GBK method ③ Specify the character set when logging in [[email protected] ~]# mysql-uroot-poldboy123-s/data/3306/mysql . sock--default-character-set=utf8; ④ modifies the character set of the MYSL client by modifying the MY.CNF implementation and is permanently in effect [client]default-character-set = UTF8 #不需要重启服务 , exit Login Effective Note: Multi-instance Modify client character set to modify/ETC/MY.CNF

7. Change the character set of the MySQL server

Modify the MY.CNF parameter as required [mysqld] Default-character-set = UTF8 #适合5.1 and Previous versions character-set-server = UTF8 #适合5.5

8. Specifying a character set when creating a library

Mysql> CREATE Database Oldboy CHARACTER SET GBK COLLATE gbk_chinese_ci; Description: Specifies the character set when you build the table, and if you do not specify a character set, the table and library characters are the same

9. The procedure should also be unified

Uniform character set for program and library tables

10. The character set of the system

[Email protected] ~]# cat/etc/sysconfig/i18nlang= "ZH_CN. UTF-8 "

11. How SQL statements are executed in the database

A.sql statement in Windows client changed to UTF8 no signature format b. Import data specifies the format of the character set mysql-uroot-poldboy123--default-character-set=utf8 <test.sql

12. Change the library character set (the library has data in its table)

Note: In the case of data, the new data becomes effective, The idea of changing the library character set for the old database does not take effect 1. Do not update the database, export all the data 2. Replace the exported data with the character set (replace table and library) 3. Modify the my.cnf, change the character set of the service side of the MySQL client, and restart the 4. Import data for changed character sets, including table structure statements, SH client, and the program changes to the corresponding character set

This article is from the "small Three Lang blog" blog, please be sure to keep this source http://wsxxsl.blog.51cto.com/9085838/1793875

The character set in MySQL