Mysql Character Set query and setting

The root cause of mysql Character Set query and Setup of MySQL garbled characters is that the MySQL character set is improperly set. This article summarizes the commands for viewing the MySQL character set. Including viewing the character set of the MySQL database server, viewing the character set of the MySQL database, the character set of the data table and field, and the character set supported by the currently installed MySQL. 1. view the MySQL database server and database character set. Mysql> show variables like '% char %'; + ---------------------------- + ------------------------------------- + ------ | Variable_name | Value | ...... + -------------------------- + ------------------------------------- + ------ | character_set_client | utf8 | ...... -- client character set | character_set_connection | utf8 | ...... | character_set_database | utf8 | ...... -- database character set | character_set_filesystem | binary | ...... | charact Er_set_results | utf8 | ...... | character_set_server | utf8 | ...... -- server character set | character_set_system | utf8 | ...... | character_sets_dir | D: \ MySQL Server 5.0 \ share \ charsets \ | ...... + -------------------------- + ------------------------------------- + ------ 2. view the character set of the MySQL DATA table. Mysql> show table status from sqlstudy_db like '% countries % '; + ----------- + -------- + --------- + ------------ + ------ + ----------------- + ------ | Name | Engine | Version | Row_format | Rows | Collation | ...... + ----------- + -------- + --------- + ------------ + ------ + ----------------- + ------ | countries | InnoDB | 10 | Compact | 11 | utf8_general_ci | ...... + ----------- + -------- + --------- + ------------ + ------ + ---- ------------- + ------ 3. view the character set of the MySQL DATA column (column. Mysql> show full columns from countries; + ---------------------- + ------------- + ----------------- + -------- | Field | Type | Collation | ....... + ---------------------- + ------------- + ----------------- + -------- | countries_id | int (11) | NULL | ....... | countries_name | varchar (64) | utf8_general_ci | ....... | countries_iso_code_2 | char (2) | utf8_general_ci | ....... | countries_iso_code_3 | char (3) | ut F8_general_ci | ....... | address_format_id | int (11) | NULL | ....... + ---------------------- + ------------- + ----------------- + -------- 4. view the character sets supported by the currently installed MySQL. Mysql> show charset; mysql> show char set; + ---------- + character + ------------------- + -------- + | Charset | Description | Default collation | Maxlen | + ---------- + character + -------- + | 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 | latin1 | latin1 | cp1252 West European | latin1_swedish_ci | 1 | 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 Japan | healthcare | 3 | sjis | shift-JIS Japan-ese | sjis_japan Nese_ci | 2 | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | tis620 | TIS620 Thai | California | 1 | euckr | EUC-KR Korean | California | 2 | koi8u | KOI8-U Ukrainian | percent | 1 | gb2312 | GB2312 Simplified Chinese | percent | 2 | greek | ISO 8859-7 Greek | greek_general_ci | 1 | cp1250 | Windows Central European | cp1250_general_ci | 1 | gbk | GBK Simp Lified Chinese | gbk_chinese_ci | 2 | latin5 | ISO 8859-9 Turkish | California | 1 | armscii8 | ARMSCII-8 Armenian | California | 1 | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | bytes | 2 | cp866 | DOS Russian | cp866_general_ci | 1 | keybcs2 | DOS Kamenicky Czech-Slovak | bytes | 1 | macce | Mac Central European | macce_general _ Ci | 1 | macroman | Mac West European | macroman_general_ci | 1 | cp852 | DOS Central European | average | 1 | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | cp1256 | Windows Arabic | cp1256_general_ci | 1 | cp1257 | Windows Baltic | average | 1 | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 G Eorgian | geostd8_general_ci | 1 | cp932 | SJIS for Windows Japanese | 2 | eucjpms | UJIS for Windows Japanese | 3 | + ---------- + Japanese + -------- + above view MySQL character set commands, applicable to Windows and Linux. 1. find/-iname '*. cnf '-print/usr/share/mysql/my-innodb-heavy-4G.cnf/usr/share/mysql/my-large.cnf/usr/share/mysql/my-small.cnf/usr/share/mysql/my-medium.cnf/usr/share /mysql/my-huge.cnf/usr/share/texmf/web2c/texmf. cnf/usr/share/texmf/web2c/mktex. cnf/usr/share/texmf/web2c/fmtutil. cnf/usr/share/texmf/tex/xmltex/xmltexfmtutil. cnf/usr/share/texmf/tex/jadetex/jadefmtutil. cnf/usr/share /Doc/MySQL-server-community-5.1.22/my-innodb-heavy-4G.cnf/usr/share/doc/MySQL-server-community-5.1.22/my-large.cnf/usr/share/doc/MySQL-server-community-5.1.22/my-small.cnf/usr/share/doc/MySQL-server-community-5.1.22/usr/share /doc/MySQL-server-community-5.1.22/my-huge.cnf 2. copy small. cnf, my-medium.cnf, my-huge.cnf, one of the my-innodb-heavy-4G.cnf to/etc, named my. cnf cp/usr/share/mysql/my-mediu M. cnf/etc/my. cnf 3. modify my. cnfvi/etc/my. cnf add default-character-set = utf8 under [client] and add default-character-set = utf8 4 under [mysqld. restart MySQL [root @ bogon ~] #/Etc/rc. d/init. d/mysql restartShutting down MySQL [OK] Starting MySQL. [OK] [root @ bogon ~] # Mysql-u root-pEnter password: Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 1 Server version: 5.1.22-rc-community-log MySQL Community Edition (GPL) Type 'help; 'or' \ H' for help. type '\ C' to clear the buffer. 5. view Character Set settings mysql> show variables like 'collation _ % '; + ------------------------ + --------------- + | Variable_name | Value | + -------------------- + ----------- ------ + | Collation_connection | utf8_general_ci | collation_database | utf8_general_ci | collation_server | utf8_general_ci | + rows + --------------- + 3 rows in set (0.02 sec) mysql> show variables like 'character _ set _ % '; + ------------------------ + bytes + | Variable_name | Value | + -------------------------- + bytes + | character_set_client | Utf8 | character_set_connection | utf8 | character_set_database | utf8 | bytes | binary | bytes | utf8 | UTF-8 | character_set_system | utf8 | bytes |/usr/share /mysql/charsets/| + -------------------------- + ---------------------------- + 8 rows in set (0.02 sec) mysql> other settings: Modify the character set of the database mysql> use mydbmysql> alter databas E mydb character set UTF-8 (utf8); create a mysql> create database mydb character set UTF-8 (utf8) character set for the specified database; modify it through the configuration file: modify/var/lib/mysql/mydb/db. optdefault-character-set = latin1default-collation = latin1_swedish_ci is default-character-set = utf8default-collation = utf8_general_ci restart MySQL: [root @ bogon ~] #/Etc/rc. d/init. d/mysql restart: MySQL> set character_set_client = utf8; Query OK, 0 rows affected (0.00 sec) mysql> set character_set_connection = utf8; Query OK, 0 rows affected (0.00 sec) mysql> set character_set_database = utf8; Query OK, 0 rows affected (0.00 sec) mysql> set character_set_results = utf8; Query OK, 0 rows affected (0.00 sec) mysql> set character_set_server = utf8; Query OK, 0 rows affected (0.00 sec) mysql> set character_set_system = utf8; Query OK, 0 rows affected (0.01 sec) mysql> set collation_connection = utf8; Query OK, 0 rows affected (0.01 sec) mysql> set collation_database = utf8; Query OK, 0 rows affected (0.01 sec) mysql> set collation_server = utf8; Query OK, 0 rows affected (0.01 sec) view: mysql> show variables like 'character _ set _ % '; + bytes + | Variable_name | Value | + bytes + | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | bytes | binary | utf8 | | character_set_server | utf8 | character_set_system | utf8 | character_sets_dir |/usr/share/mysql/charsets/| + ---------------------- + rows + 8 rows in set (0.03 sec) mysql> show variables like 'collation _ % '; + rows + --------------- + | Variable_name | Value | + rows + --------------- + | collation_connection | rows | collation_database | rows | collation_server | rows | + rows + 3 rows in set (0.04 sec)