各种修改Mysql字符集
以下方法最好在没有数据时操作，否则有可能导致乱码。如果已有数据，做好备份尝试Mysql迁移由于字符集导致乱码的数据
创建测试环境
mysql> create database test_db; Query OK, 1 row affected (0.00 sec) mysql> use test_db; Database changed mysql> create table test_table(id int,name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> insert into test_table values(1,"zhangsan"); Query OK, 1 row affected (0.00 sec)
(1).查看字符集
1）查看Mysql客户端、Mysql服务器和Mysql数据库的默认字符集
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%';
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支持的字符集
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 [数据库名];
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 '[表名]';
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 [数据库名].[表名];
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 [数据库名].[表名];
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 [参数名]=[编码];
//建立连接使用的编码 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。
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 [字符集];
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 [字符集];
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 [字符集];
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)
参考：MySQL查看和修改字符集的方法，作者：明王不动心
