MySQL查看和修改字符集

(1).查看字符集

1）查看Mysql客户端、Mysql服务器和Mysql数据库的默认字符集

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> show VARIABLES like 'character%'; +--------------------------+----------------------------+ | Variable_name            | Value                      | +--------------------------+----------------------------+ | character_set_client     | utf8                       |　　　　//客户端使用的编码 | character_set_connection | utf8                       |　　　　//连接时使用的编码 | character_set_database   | latin1                     |　　　　//数据库使用的编码 | character_set_filesystem | binary                     | | character_set_results    | utf8                       |　　　　//结果集使用的编码 | character_set_server     | latin1                     |　　　　//服务器使用的编码 | character_set_system     | utf8                       | | character_sets_dir       | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)

　　还可以show variables like 'collation%';

1 2 3 4 5 6 7 8 9

mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name        | Value             | +----------------------+-------------------+ | collation_connection | utf8_general_ci   | | collation_database   | latin1_swedish_ci | | collation_server     | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)

2）查看Mysql支持的字符集

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

mysql> show charset; +----------+---------------------------------+---------------------+--------+ | 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 | | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 | | cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 | | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 | | utf16le  | UTF-16LE Unicode                | utf16le_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 | | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec)

3）查看已存在的数据库的字符集

　　show create database [数据库名];

1 2 3 4 5 6 7

mysql> show create database test_db; +----------+--------------------------------------------------------------------+ | Database | Create Database                                                    | +----------+--------------------------------------------------------------------+ | test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec)

4）查看已存在的数据表的字符集

　　show table status from [数据库名] like '[表名]';

1 2 3 4 5 6 7

mysql> show table status from test_db like 'test_table'; +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | test_table | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2019-06-04 09:55:25 | 2019-06-04 09:55:35 | NULL       | latin1_swedish_ci |     NULL |                |         | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec)

　　还可以使用show create table [数据库名].[表名];

1 2 3 4 5 6 7 8 9 10

mysql> show create table test_db.test_table; +------------+-----------------------------------------------------------------------------------------------------------------------------------+ | Table      | Create Table                                                                                                                      | +------------+-----------------------------------------------------------------------------------------------------------------------------------+ | test_table | CREATE TABLE `test_table` (   `id` int(11) DEFAULT NULL,   `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------+-----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

5）查看已存在的数据表中列的字符集

　　show full columns from [数据库名].[表名];

1 2 3 4 5 6 7 8

mysql> show full columns from test_db.test_table; +-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type        | Collation         | Null | Key | Default | Extra | Privileges                      | Comment | +-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | id    | int(11)     | NULL              | YES  |     | NULL    |       | select,insert,update,references |         | | name  | varchar(20) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         | +-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ 2 rows in set (0.00 sec)

(2).修改字符集

1）修改默认字符集

　　临时修改，set [参数名]=[编码];

1 2 3 4 5 6 7 8 9 10 11 12

//建立连接使用的编码 set character_set_connection=utf8; //数据库的编码 set character_set_database=utf8; //结果集的编码 set character_set_results=utf8; //Mysql服务器的编码 set character_set_server=utf8; set character_set_system=utf8; set collation_connection=utf8; set collation_database=utf8; set collation_server=utf8;

　　永久修改，修改配置文件/etc/my.cnf，在[mysqld]模块内添加一行[参数名]=[编码]，然后重启mysqld服务。另外如果在配置文件找那个设置character_set_server=utf8，那么数据库默认字符集也会变为utf8。

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> show VARIABLES like 'character%'; +--------------------------+----------------------------+ | 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.01 sec)

2）修改已存在的数据库的字符集

　　alter database [数据库名] default character set [字符集];

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> show create database test_db; +----------+--------------------------------------------------------------------+ | Database | Create Database                                                    | +----------+--------------------------------------------------------------------+ | test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter database test_db default character set utf8; Query OK, 1 row affected (0.00 sec) mysql> show create database test_db; +----------+------------------------------------------------------------------+ | Database | Create Database                                                  | +----------+------------------------------------------------------------------+ | test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec)

3）修改已存在的数据表字符集

　　alter table [数据库名].[表名] convert to character set [字符集];

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

mysql> show create table test_db.test_table; +------------+-----------------------------------------------------------------------------------------------------------------------------------+ | Table      | Create Table                                                                                                                      | +------------+-----------------------------------------------------------------------------------------------------------------------------------+ | test_table | CREATE TABLE `test_table` (   `id` int(11) DEFAULT NULL,   `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------+-----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table test_db.test_table convert to character set utf8; Query OK, 1 row affected (0.06 sec) Records: 1  Duplicates: 0  Warnings: 0 mysql> show create table test_db.test_table; +------------+---------------------------------------------------------------------------------------------------------------------------------+ | Table      | Create Table                                                                                                                    | +------------+---------------------------------------------------------------------------------------------------------------------------------+ | test_table | CREATE TABLE `test_table` (   `id` int(11) DEFAULT NULL,   `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +------------+---------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

4）修改列的字符集

　　alter table [数据库名].[表名] modify [字段名] [字段属性] character set [字符集];

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> show full columns from test_db.test_table; +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id    | int(11)     | NULL            | YES  |     | NULL    |       | select,insert,update,references |         | | name  | varchar(20) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 2 rows in set (0.01 sec) mysql> alter table test_db.test_table modify name varchar(20) character set gbk; Query OK, 1 row affected (0.02 sec) Records: 1  Duplicates: 0  Warnings: 0 mysql> show full columns from test_db.test_table; +-------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type        | Collation      | Null | Key | Default | Extra | Privileges                      | Comment | +-------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+ | id    | int(11)     | NULL           | YES  |     | NULL    |       | select,insert,update,references |         | | name  | varchar(20) | gbk_chinese_ci | YES  |     | NULL    |       | select,insert,update,references |         | +-------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+ 2 rows in set (0.01 sec)