mysql字符集查看与设置详解

发布时间:2020-06-19编辑:脚本学堂
本文详细介绍了mysql字符集的查看与设置方法,查看 mysql 数据库服务器字符集、查看 mysql 数据库字符集,以及数据表和字段的字符集、当前安装的 mysql 所支持的字符集等。

注意,mysql 乱码的主要原因在于mysql 字符集设置不当的问题。

收集了一些mysql 字符集的命令。
包括查看 mysql 数据库服务器字符集、查看 mysql 数据库字符集,以及数据表和字段的字符集、当前安装的 mysql 所支持的字符集等。

一、查看 mysql 数据库服务器和数据库字符集。
<a href=http://www.jb200.com/zt/mysqlcharset/ target=_blank class=infotextkey>mysql字符集</a>查看与设置

二、查看 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字符集查看与设置

四、查看当前安装的 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

1,查找mysql的cnf文件的位置
 

复制代码 代码示例:

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/my-medium.cnf
/usr/share/doc/mysql-server-community-5.1.22/my-huge.cnf

2,复制small.cnf、my-medium.cnf、my-huge.cnf、my-innodb-heavy-4g.cnf其中的一个到/etc下,命名为my.cnf
 

复制代码 代码示例:
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

3,修改my.cnf
vi /etc/my.cnf
 

在[client]下添加
default-character-set=utf8
在[mysqld]下添加
default-character-set=utf8

4,重新启动mysql
 

复制代码 代码示例:
[root@jbxue ~]# /etc/rc.d/init.d/mysql restart
shutting down mysql   [ 确定 ]
starting mysql.   [ 确定 ]
[root@jbxue ~]# mysql -u root -p
enter 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,查看mysql字符集设置
 

复制代码 代码示例:
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| variable_name         | value            |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database    | utf8_general_ci |
| collation_server      | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.02 sec)
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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.02 sec)
 

1),修改数据库的字符集
 

复制代码 代码示例:
mysql>use mydb
mysql>alter database mydb character set utf-8(utf8);


创建数据库指定数据库的字符集
 

复制代码 代码示例:
mysql>create database mydb character set utf-8(utf8);

2),通过配置文件修改mysql字符集
 

修改/var/lib/mysql/mydb/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci

default-character-set=utf8
default-collation=utf8_general_ci

3)、重启mysql数据库
 

复制代码 代码示例:
[root@jbxue ~]# /etc/rc.d/init.d/mysql restart

4)mysql命令行修改字符集
 

复制代码 代码示例:
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)

5),查看mysql字符集
 

复制代码 代码示例:
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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| variable_name         | value            |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database    | utf8_general_ci |
| collation_server      | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.04 sec)