字符集

  • 查看支持的字符集: show character set;

  • 查看校对规则: show collation;, show collation like 'xxx', select * from infomation_schema.collations\G

_ci: 大小写不敏感, _cs: 大小写敏感, _bin:二进制比较.

  • 查看当前服务器的字符集: show variables like '%character%';

  • 查看当前服务器的校对规则: show variables like '%collation%'

  • 查看某表的字符集和校对规则: show create table tbname\G

参数说明

+--------------------------+------------------------------------------------------+
| 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/local/Cellar/mysql/5.7.10/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.02 sec)
  • character_set_client: 客户端字符集
  • character_set_connection: 连接字符集
  • character_set_results: 返回结果的字符集

设置my.conf

[mysql]
default-character-set=utf8

[mysqld]
default-charatcer-set=utf8

修改字符集

修改数据库默认的字符集

alter database test CHARACTER set 'gbk';: 这个不能对已有的数据进行修改, 而是对新的数据效.

alter table tbname character set 'gbk': 这个也不能修改已有数据的字符, 只是对新插入的数据生效.

mysql> show create table t_p;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------+
| t_p   | CREATE TABLE `t_p` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t_p character set 'gbk';
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_p;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| t_p   | CREATE TABLE `t_p` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果需要对已有数据进行调整, 需要先将数据导出, 然后按新的字符集再进行导入才行.