一、查看 MySQL 字符集
以下命令 Windows & Linux通用。
1. 服务器和数据库
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 |......
| character_set_server | utf8 |...... -- 服务器字符集
| character_set_system | utf8 |...... -- 系统元数据字符集,总是固定不变的,可以不管。
| character_sets_dir | D:\MySQL Server 5.0\share\charsets\ |......
+--------------------------+-------------------------------------+------
或者
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
Connection id: 222812
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.16-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
2. 表级
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. 列级
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 | .......
+----------------------+-------------+-----------------+--------
4. 当前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 |
+----------+-----------------------------+---------------------+--------+
二、 Liunx下MySQL字符集修改【谨慎操作】
1. 服务器级
修改my.cnf(先备份)的character_set_server参数,需要重启mysql服务。对新建数据库、表、列,新插入数据有效。
character_set_server = utf8 #设置字符集
2. 数据库级
对新建表、列,新插入数据有效。
alter database 数据库名 character set utf8 collate utf8_bin;
也可以创建数据库时指定数据库字符集
create database mydb character set utf-8;
3. 表级
对新建列,新插入数据有效。
ALTER TABLE 表名 DEFAULT CHARACTER SET utf8 COLLATE collate utf8_bin;
生成所有表修改字符集语句:
SELECT TABLE_NAME,CONCAT('ALTER TABLE ',TABLE_NAME,' DEFAULT CHARACTER SET ',a.DEFAULT_CHARACTER_SET_NAME,' COLLATE ',a.DEFAULT_COLLATION_NAME,';') executeSQL FROM information_schema.SCHEMATA a,information_schema.TABLES b
WHERE a.SCHEMA_NAME=b.TABLE_SCHEMA
AND a.DEFAULT_COLLATION_NAME!=b.TABLE_COLLATION
AND b.TABLE_SCHEMA='数据库名';
4. 修改列字符集
很少用,需要修改每个列,不方便,并且执行过程中会锁表
ALTER TABLE 表名 CHANGE 列名 列名 VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
生成所有列修改字符集语句:
select b.table_name,b.column_name,b.character_set_name,b.collation_name
,CONCAT('ALTER TABLE ',b.table_name,' MODIFY ',b.column_name,' ',b.DATA_TYPE,'(',b.CHARACTER_MAXIMUM_LENGTH,') ',CASE WHEN b.COLUMN_DEFAULT IS NULL THEN '' ELSE CONCAT('DEFAULT \'',b.COLUMN_DEFAULT,'\'') END,' COMMENT \'',b.COLUMN_COMMENT,'\';') executeSQL
from information_schema.TABLES a,information_schema.COLUMNS b where b.character_set_name IS NOT NULL and a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME
AND a.TABLE_COLLATION!=b.COLLATION_NAME
and a.TABLE_SCHEMA='数据库名';
该SQL只是找到需要修改的列,并不是最终的语句,具体的语句还需要show create table TABLE_NAME查看后,替换字符集部分。
如果表的数据量比较大的话,时间比较长,常规的修改方式会锁表,需要使用PT工具。
pt-online-schema-change --alter ' CHANGE name name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL' h=IP,u=root,P=3306,D=test,t=t1 --ask-pass --execute
pt-online-schema-change --alter ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin' h=IP,u=root,P=3306,D=test,t=t1 --ask-pass --execute
5. 一步修改表和列字符集
更加方便,相当于第3部分+第4部分。注意执行过程中会锁表。
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
参考
https://hyl198611.iteye.com/blog/1327723