mysql字符集调整总结

        字符集是一套符号和编码的规则,不论是在oracle数据库还是在mysql数据库,都存在字符集的选择问题。对于数据库来说,字符集又是比较重要的,因为数据库存储的数据大部分都是各种文字,字符集对于数据库的存储、处理性能以及数据迁移都有重要的影响。

        如果在数据库创建阶段没有正确选择字符集,那么可能在后期需要更换字符集,而字符集的更换是代价比较高的操作,也存在一定的风险,所以我们建议在应用开始阶段,就按照需求正确的选择合适的字符集,尽量避免后期不必要的调整。

        mysql编译安装时,指定字符集的方法:

1
./configure --with-charset=utf8

        mysql的字符集有4个级别的默认设置:服务器级、数据库级、表级和字段级。分别在不同的地方设置,作用也不相同。

1、服务器字符集设定,在mysql服务启动的时候确定。

        可以在my.cnf中设置:

1
2
3
4
5
6
7
8
[mysql]
### 默认字符集为utf8
default-character-set=utf8
[mysqld]
### 默认字符集为utf8
default-character-set=utf8
### (设定连接mysql数据库时使用utf8编码,以让mysql数据库为utf8运行)
init_connect='SET NAMES utf8'

        或者在启动选项中指定:

1
mysqld --default-character-set=utf8

        如果没有特别的指定服务器字符集,默认使用latin1(ISO-8859-1的别名)作为服务器字符集。上面三种设置的方式都只指定了字符集,没有去做校对,我们可以用show variables like ‘char%’;命令查询当前服务器的字符
集和校对规则。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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 | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+

        注:如果增加default-character-set=utf8后,MYSQL启动报错。可以用character_set_server=utf8来取代default-character-set=utf8,就能正常启动了。这是因为MYSQL不同版本识别的问题。

2、数据库级

        创建数据库时指定字符集

1
mysql>CREATE DATABASE my_db default charset utf8 COLLATE utf8_general_ci;

        注意后面这句话 “COLLATE utf8_general_ci”,大致意思是在排序时根据utf8编码格式来排序
如果指定了数据库编码,那么在这个数据库下创建的所有数据表的默认字符集都会是utf8了

        修改MYSQL数据库编码,如果是MYSQL数据库编码不正确,可以在MYSQL执行如下命令:

1
ALTER DATABASE my_db DEFAULT CHARACTER SET utf8;

        以上命令就是将MYSQL的my_db数据库的编码设为utf8

3、表级

        创建表时指定字符集

1
mysql>create table my_table (name varchar(20) not null default '')type=myisam default charset utf8;

        这句话就是创建一个表,指定默认字符集为utf8

        修改MYSQL表的编码:

1
ALTER TABLE my_table DEFAULT CHARACTER SET utf8;

        以上命令就是将一个表my_table的编码改为utf8

4、字段级

1
alter table test add column address varchar(110) after stu_id;

        在stu_id后增加一个字段address

1
alter table test add id int unsigned not Null auto_increment primary key;

        修改字段的编码:

1
ALTER TABLE `test` CHANGE `name` `name` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

        以上命令就是将MYSQL数据库test表中name的字段编码改为utf8

        在命令行下插入汉字时如下代码:

1
2
set names utf8;有时候这一句很关键!
insert into charset values('王达');

        注意:alter修改的方法不能更新已有记录的字符集,只是对新创建的表和记录生效。对已有记录字符集的调整,需要先将数据导出,经过适当调整后重新导入才可以完全修改编码。

导出导入的字符调整方法

导出表结构

1
mysqldump -uroot -pmysql --default-character-set=latin1 -d my_db> createtab.sql

        手工修改createtab.sql表结构定义中的字符集为新的字符集

导出所有记录

1
mysqldump -uroot -pmysql --quick --no-create-info --extended-insert --default-character-set=latin1 --host=localhost my_db> data.sql

打开data.sql,将set names latin1修改成set names utf8

1
:%s/latin1/utf8/g

        全文替换

使用新的字符集创建新的数据库

1
create database mydata default charset utf8;

创建表,执行createtab.sql

1
mysql -uroot -pmysql mydata<creattab.sql

导入数据

1
mysql -uroot -pmysql mydata<data.sql

        注意一点就是目标字符集要大于等于源字符集,否则会丢失一部分不支持的汉字数据。

        附:旧数据升级办法

        以原来的字符集为latin1为例,升级成为utf8的字符集。原来的表: old_table (default charset=latin1),新表:new_table(default charset=utf8)。

        第一步:导出旧数据

1
mysqldump --default-character-set=latin1 -hlocalhost -uroot -B my_db --tables old_table > old.sql

        第二步:转换编码

1
iconv -t utf8 -f latin1 -c old.sql > new.sql

        在这里,假定原来的数据默认是latin1编码。

        第三步:导入

        修改old.sql,增加一条sql语句: “SET NAMES utf8;”,保存。

1
mysql -hlocalhost -uroot my_db < new.sql

        大功告成!

Mysql collate规则:

  • *_bin: 表示的是binary case sensitive collation,也就是说是区分大小写的
  • *_cs: case sensitive collation,区分大小写
  • *_ci: case insensitive collation,不区分大小写