但行好事
莫论前程❤

修改MySQL的字符集(服务器级,数据库级,表级,列级)

MySQL服务器可以支持多种字符集,在同一个服务器,同一个数据库甚至同一个表的不同字段都可以指定使用不同的字符集,相比Oracle等其他数据库管理系统,在同一个数据库只能使用相同的字符集,Mysql明显存在更大的灵活性.

查看所有可用字符集
show character set;

​ mysql字符集包括字符集(character)和校对规则(collation)两个概念,其中字符集用来定义MySql存储字符串的方式,校对规则用来定义比较字符串的方式.字符集和校对规则是一对多的关系,MySQL支持30多种字符集的70多种校对规则.
校对规则命名约定,它们以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感),_cs*(大小写敏感)或_bin(二元,即比较是基于字符编码的值而与lanauage无关)结束.

MySQL字符集的设置

MySQL的字符集和校对规则有四个级别的默认设置:

  • 服务器级
  • 数据库级
  • 表级
  • 字段级 (列级)

它们分别在不同的地方设置,作用也不相同.

1. 服务器字符集和校对规则

​ 服务器字符集和校对规则,可以在MySQL服务启动的时候确定.
可以在my.cnf(centos) 或者/etc/mysql/mysql.conf.d/mysqld.cnf(Ubuntu 16.4)中设置:

修改文件

修改MySQL的配置文件/etc/mysql/mysql.conf.d/mysqld.cnf;

[mysqld]
character_set_server = utf8

Ubuntu16.4 修改完之后保存,重启mysql服务

service mysql restart

第二种修改方法.在启动项中指定(重启后失效):

mysqld --character-set-server=utf8;

第三种方法,在编译时指定:

shell> cmake . -DEFAULT_CHARACTER=utf8;

如果没有特别指定服务器字符集,那么默认使用latin1作为服务器字符集.上面3种设置的方式都指定了字符集,没有指定校对规则,这样就意味着使用该字符集默认的校对规则.如果要使用非默认的校对规则,则需要在指定字符集的时候同时指定校对规则.

查询当前服务器的字符集和校对规则.
show variables like 'character_set_server';
show variables like 'collation_server';

2. 数据库字符集和校对规则

数据库的字符集和校对规则在创建数据库的时候指定,也可以在创建完数据库后通过alter database命令进行修改.需要注意的是,如果数据库里面已经存在数据,因为修改字符集并不能将已有的数据按照新的字符集进行存放,所以不能通过修改数据库的字符集直接修改数据的内容.
设置数据库字符集的规则如下:
如果指定了字符集和校对规则,则使用指定的.
如果指定了字符集没有指定校对规则, 则使用字符集对应的默认校对规则.
如果指定了校对规则没有指定字符集,则使用校对规则相关联的字符集.
如果没有指定字符集和校对规则,则使用服务器字符集和校对规则作为数据库的字符集和校对规则

推荐在创建数据库时明确指定字符集和校对规则,避免使用默认值的影响.

查看数据库字符集和校对规则
show VARIABLES like 'character_set_database';
# 或者直接模糊查询所有
show VARIABLES like 'character%';

show VARIABLES like 'collation_database';
修改数据库字符集 :
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.13 sec)

# 将所需要的变量修改为utf8
> set character_set_database=utf8;

character_set_client 为客户端编码方式;

character_set_connection 为建立连接使用的编码;

character_set_database 数据库的编码;

character_set_results 结果集的编码;

character_set_server 数据库服务器的编码;

修改当前数据库字符集:
alter database `db_name` default character set `character_name` [COLLATE ...];
alter database test default character set utf8;

#查看当前数据库编码:
show create database db_name;
# 或者
use db_name;
show variables like 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+

3. 表字符集和校对规则

​ 表的字符集和校对规则在创建表的时候指定,可以通过alter table命令进行修改,同样.如果表中已有记录,修改字符集对原有的记录并没有影响.不会按照新的字符集进行存放.表的字段仍然使用原来的字符集.
设置表的字符集的规则和上面的基本类似.

如果没有指定字符集和校对规则,则使用数据库字符集和校对规则作为数据库的字符集和校对规则
推荐在创建表时明确指定字符集和校对规则,避免使用默认值的影响.

查看表的字符集编码
show create table table_name \G;
修改表的字符集编码
# 只是修改表的默认字符集:
alter table `tablename` default character set utf8;

4. 列(字段)字符集和校对规则

​ MySQL可以定义列级别的字段集和校对规则,主要是针对相同的表不同字段需要使用不同的字符集的情况.应该说一般遇到这种情况的几率比较小.这只是mysql提供给我们一个灵活设置的手段.

列字符集和校对规则的定义可以在创建表的时候指定,或者在修改表时调整.,如果在创建表的时候没有特别指定,则默认使用表的字符集和校对规则.

查看字段(列)编码:
show full columns from tbl_name;
修改字段(列)的编码格式
# 修改指定字段的字符集:
ALTER TABLE `tablename` CHANGE `字段名1` `字段名2` VARCHAR(36) CHARACTER SET utf8 NOT NULL;
修改表和列的字符集(即修改表时调整)
alter table `tablename` convert to character set utf8;

5. 连接字符集和校对规则

​ 上面四种设置方式,确定的是数据保存的字符集和校对规则,对于实际的应用访问来说,还存在客户端和服务器之间交互的字符集和校对规则的设置.

​ 对于客户端和服务器的交互操作,MySQL提供了3个不同的参数:

  • character_set_client
  • character_set_connection
  • character_set_results

分别代表客户端、连接和返回结果的字符集.通常情况下,这三个字符集应该是相同的,才可以确保用户写入的数据可以正确的读出,特别是对于中文字符,不同的写入字符集和返回结果字符集将导致写入的记录不能正确读出.

通常情况下,不会单个去设置这三个参数.可以通过如下命令:

set names utf8;

来设置连接的字符集和校对规则.这个命令可以同时修改三个参数的值.这种方式修改,需要应用每次连接数据库后都执行这个命令.

另一种方式就是在my.cnf配置文件中修改.

[mysql]
default-character-set=utf8;

这样服务器启动后,所有连接默认就是使用utf8字符集进行连接的.

另外字符串常量的字符集也是由character_set_connetion参数来指定的.

字符集的修改步骤:

如果在应用开始阶段没有正确地设置字符集,在运行一段时间以后才发现存在不能满足要求需要调整.又不想丢弃这段时间的数据.就需要这么操作.已有记录的字符集调整,需要先将数据导出,经过适当的调整重新导入后才可完成.

以下为模拟将latin1字符集的数据库修改为utf8字符集的数据库的过程.

  • 导出表结构
mysqldump -uroot -p --default-character-set=utf8 -d databasename > createtab.sql

其中--default-character-set=utf8表示设置以什么字符集连接,

-d : 表示只导出表结构,不导出数据

  • 收工修改createtab.sql中表结构定义中的字符集为新的字符集.
  • 确保记录不再更新,导出所有记录.
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename > data.sql

--quick : 该选项用于转储大的表,它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行,并在输出前将它缓存到内存中.

--extend-insert : 使用包括几个VALUES列表的多行insert语法.这样使转储文件更小,重载文件时可以加速插入.

--no-create-info : 不导出每个转储表的create table 语句.

--default-character-set = latin1 : 按照原有的字符集导出所有数据,这样导出的文件中,所有中文都是可见的,不会保存成乱码.

  • 打开data.sql,将set names latin1 修改成set names utf8;
  • 使用新字符集创建新的数据库
create database databasename default charset utf8;
  • 创建表,执行createtab.sql
mysql -uroot -p databasename < createtab.sql 
  • 导入数据,执行data.sql
mysql -uroot -p databasename < data.sql 
赞(0) 打赏
未经允许不得转载:刘鹏博客 » 修改MySQL的字符集(服务器级,数据库级,表级,列级)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏