新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT百科 > 数据库 > MYSQL

超详细的MySQL数据库字符集总结

时间:2019-09-11 13:41:38  来源:  作者:

概述

MySQL支持多种字符集(character set)提供用户存储数据,同时允许用不同排序规则(collation)做比较。下面基于MySQL5.7介绍一下字符集相关变量的使用。


一、字符集、字符序的概念与联系

在数据的存储上,MySQL提供了不同的字符集支持。而在数据的对比操作上,则提供了不同的字符序支持。MySQL提供了不同级别的设置,包括server级、database级、table级、column级,可以提供非常精准的设置。

什么是字符集、字符序?简单的来说:

  1. 字符集(character set):定义了字符以及字符的编码。
  2. 字符序(collation):定义了字符的比较规则。

举个例子:

有四个字符:A、B、a、b,这四个字符的编码分别是A = 0, B = 1, a = 2, b = 3。这里的字符 + 编码就构成了字符集(character set)。


二、MySQL支持的字符集、字符序

MySQL支持多种字符集 与 字符序。

  1. 一个字符集对应至少一种字符序(一般是1对多)。
  2. 两个不同的字符集不能有相同的字符序。
  3. 每个字符集都有默认的字符序。

1、查看支持的字符集

可以通过以下方式查看MYSQL支持的字符集。

SHOW CHARACTER SET;
select * from information_schema.HARACTER_SETS;
超详细的MySQL数据库字符集总结,值得收藏

 

2、查看支持的字符序

可以通过如下方式查看MYSQL支持的字符序。

SHOW COLLATION WHERE Charset = 'utf8';
SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME="utf8";
超详细的MySQL数据库字符集总结,值得收藏

 


三、系统的字符集(character_set_system)

character_set_system为元数据的字符集,即所有的元数据都使用同一个字符集。试想如果元数据采用不同字符集,INFORMATION_SCHEMA中的相关信息在不同行之间就很难展示。同时该字符集要能够支持多种语言,方便不同语言人群使用自己的语言命名database、table、column。MySQL选择UTF-8作为元数据编码,用源码固定。

1、查看system字符集

select @@global.character_set_system;
超详细的MySQL数据库字符集总结,值得收藏

 

因为很少设定,所以就不做指定介绍了。


四、server的字符集、字符序(character_set_server/collation_server)

当create database没有指定charset/collation就会用character_set_server/collation_server,这两个变量可以动态设置,有session/global级别。

在源码中character_set_server/collation_server实际对应一个变量,因为一个collation对应着一个charset,所以源码中只记录CHARSET_INFO结构的collation_server即可。当修改character_set_server,会选择对应charset的默认collation。对于其他同时有charset和collation的变量,源码记录也都是记录collation。

character_set_server、collation_server分别对应server字符集、server字符序。

1、查看server字符集、字符序

分别对应character_set_server、collation_server两个系统变量。

SET GLOBAL SHOW_COMPATIBILITY_56=ON;
SHOW VARIABLES LIKE "character_set_server"; 
SHOW VARIABLES LIKE "collation_server";
超详细的MySQL数据库字符集总结,值得收藏

 

2、启动服务时指定

可以在MySQL服务启动时,指定server字符集、字符序。如不指定,默认的字符序分别为latin1、latin1_swedish_ci

mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci

3、配置文件指定

除了在命令行参数里指定,也可以在配置文件里指定,如下所示。

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

4、运行时修改

例子:运行时修改(重启后会失效,如果想要重启后保持不变,需要写进配置文件里)

mysql> SET character_set_server = utf8 ;

5、编译时指定默认字符集、字符序

character_set_server、collation_server的默认值,可以在MySQL编译时,通过编译选项指定:

cmake . -DDEFAULT_CHARSET=latin1 -DDEFAULT_COLLATION=latin1_german1_ci

6、实例

通过设置session中不同的character_set_server使创建database的默认charset和collation不同。

set character_set_server='utf8';
create database d1;
select * from information_schema.schemata where SCHEMA_NAME='d1';
set character_set_server='latin1';
create database d2;
select * from SCHEMATA where SCHEMA_NAME='d2';
超详细的MySQL数据库字符集总结,值得收藏

 


五、database的字符集、字符序(character_set_database/collation_database)

指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库,可以分别指定不同的字符集/字符序。该变量值session级别表示当前database的charset/collation,在后面的源码版本中该变量可能修正为只读,不建议修改该值。其global级别变量后面也会移除。

1、设置数据的字符集/字符序

可以在创建、修改数据库的时候,通过CHARACTER SET、COLLATE指定数据库的字符集、排序规则。

--创建数据库:
CREATE DATABASE db_name
 [[DEFAULT] CHARACTER SET charset_name]
 [[DEFAULT] COLLATE collation_name]
--修改数据库:
ALTER DATABASE db_name
 [[DEFAULT] CHARACTER SET charset_name]
 [[DEFAULT] COLLATE collation_name]

例子:创建数据库test_schema,字符集设置为utf8,此时默认的排序规则为utf8_general_ci。

CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;

2、查看数据库的字符集/字符序

有3种方式可以查看数据库的字符集/字符序。

--查看test_schema的字符集、排序规则。(需要切换默认数据库)
mysql> use test_schema;
mysql> SELECT @@character_set_database, @@collation_database;
--查看test_schema的字符集、数据库(不需要切换默认数据库)
mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA WHERE schema_name="test_schema";
--查看创建数据库的语句,来查看字符集。
mysql> SHOW CREATE DATABASE test_schema;

六、table的字符集、字符序

创建表、修改表的语法如下,可通过CHARACTER SET、COLLATE设置字符集、字符序。

CREATE TABLE tbl_name (column_list)
 [[DEFAULT] CHARACTER SET charset_name]
 [COLLATE collation_name]]
ALTER TABLE tbl_name
 [[DEFAULT] CHARACTER SET charset_name]
 [COLLATE collation_name]

1、创建table并指定字符集/字符序

指定字符集为utf8,字符序则采用默认的。

CREATE TABLE `test_schema`.`test_table` (
 `id` INT NOT NULL COMMENT '',
 PRIMARY KEY (`id`) COMMENT '')
DEFAULT CHARACTER SET = utf8;

2、查看table的字符集/字符序

同样,有3种方式可以查看table的字符集/字符序。

方式一:通过SHOW TABLE STATUS查看table状态,注意Collation为utf8_general_ci,对应的字符集为utf8。
> SHOW TABLE STATUS FROM test_schema \G;
方式二:查看information_schema.TABLES的信息。
> USE test_schema;
> SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table";
方式三:通过SHOW CREATE TABLE确认。
> SHOW CREATE TABLE test_table;

3、table字符集、字符序如何确定

假设CHARACTER SET、COLLATE的值分别是charset_name、collation_name。如果创建table时:

  • 明确了charset_name、collation_name,则采用charset_name、collation_name。
  • 只明确了charset_name,但collation_name未明确,则字符集采用charset_name,字符序采用charset_name对应的默认字符序。
  • 只明确了collation_name,但charset_name未明确,则字符序采用collation_name,字符集采用collation_name关联的字符集。
  • charset_name、collation_name均未明确,则采用数据库的字符集、字符序设置。

七、column的字符集、排序

类型为CHAR、VARCHAR、TEXT的列,可以指定字符集/字符序,语法如下:

col_name {CHAR | VARCHAR | TEXT} (col_length)
 [CHARACTER SET charset_name]
 [COLLATE collation_name]

1、新增column并指定字符集/排序规则

例子如下:(创建table类似)

mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;

2、查看column的字符集/字符序

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column";
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME |
+--------------------+-----------------+
| utf8 | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

3、column字符集/排序规则确定

假设CHARACTER SET、COLLATE的值分别是charset_name、collation_name:

  • 如果charset_name、collation_name均明确,则字符集、字符序以charset_name、collation_name为准。
  • 只明确了charset_name,collation_name未明确,则字符集为charset_name,字符序为charset_name的默认字符序。
  • 只明确了collation_name,charset_name未明确,则字符序为collation_name,字符集为collation_name关联的字符集。
  • charset_name、collation_name均未明确,则以table的字符集、字符序为准。


Tags:MySQL 数据库字符   点击:()  评论:()
声明:本站部分内容来自互联网,内容观点仅代表作者本人,如有任何版权侵犯请与我们联系,我们将立即删除。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
概述MySQL支持多种字符集(character set)提供用户存储数据,同时允许用不同排序规则(collation)做比较。下面基于MySQL5.7介绍一下字符集相关变量的使用。一、字符集、字符序的概...【详细内容】
2019-09-11   MySQL 数据库字符  点击:(0)  评论:(0)  加入收藏
推荐资讯
相关文章
栏目更新
栏目热门