各种修改Mysql字符集
2024-08-28 21:28:26
以下方法最好在没有数据时操作，否则有可能导致乱码。如果已有数据，做好备份尝试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查看和修改字符集的方法，作者：明王不动心
最新文章
- Yii2 assets注册的css样式文件没有加载
- 【Alpha版本】冲刺阶段——Day 10
- 使用Angular和Nodejs搭建聊天室
- opengl基础学习专题 (二) 点直线和多边形
- IEngineEditor与IWorkspaceEdit，以及相关的事件监听
- iOS Safari 中点击事件失效的解决办法
- errno.h 错误码描述.
- HDU 3404&POJ 3533 Nim积（二维&三维）
- Linux系统优化脚本
- 我的IT之路这样走过
- jvm常见的面试题
- ef 某些字段更新 某些字段不更新
- ZCTF-2017 比赛总结
- nexus maven私服搭建
- FaceBook开源的词向量计算框架
- "\n" 与"\r" 区别
- 【51nod】1531 树上的博弈
- LINUX下PHP编译添加相应的动态扩展模块so（不需要重新编译PHP，以openssl.so为例）
- jquery与javescript的区别（一）
- Linux命令之pip