mysql字符集查询与设置方法

说到底mysql 乱码的根源是的 mysql 字符集设置不当的问题。

有关查看 mysql 字符集的命令。

包括查看 mysql 数据库服务器字符集、查看 mysql 数据库字符集，以及数据表和字段的字符集、当前安装的 mysql 所支持的字符集等。

一、查看 mysql 数据库服务器和数据库字符集。

mysql> show variables like '%char%';
+--------------------------+-------------------------------------+------
| variable_name            | value                               |......
+--------------------------+-------------------------------------+------
| character_set_client     | utf8                                |......   -- 客户端字符集
| character_set_connection | utf8                                |......
| character_set_database   | utf8                                |......   -- 数据库字符集
| character_set_filesystem | binary                              |......
| character_set_results    | utf8                                |...... www.jbxue.com
| character_set_server     | utf8                                |......   -- 服务器字符集
| character_set_system     | utf8                                |......
| character_sets_dir       | d:\mysql server 5.0\share\charsets\ |......
+--------------------------+-------------------------------------+------

二、查看 mysql 数据表（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 |......
+-----------+--------+---------+------------+------+-----------------+------

三、查看 mysql 数据列（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)     | utf8_general_ci | .......
| address_format_id    | int(11)     | null            | .......
+----------------------+-------------+-----------------+--------

• mysql字符集查看与设置详解
• Mysql字符集设置详解
• 有关mysql字符集设定的知识总结
• (图文)mysql字符集设置详解
  四、查看当前安装的 mysql 所支持的字符集。
  

  mysql> show charset;
  mysql> show char 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 |
  | 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 |
  | 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 |
  | 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 |
  | cp1251   | windows cyrillic            | cp1251_general_ci   |      1 |
  | cp1256   | windows arabic              | cp1256_general_ci   |      1 |
  | cp1257   | windows baltic              | cp1257_general_ci   |      1 |
  | 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 |
  +----------+-----------------------------+---------------------+--------+

  以上查看 mysql 字符集命令，适用于 windows & linux。