【MySQL】数据库字符校对规则

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 一 什么是字符校对规则 使用MySQL的大多数都知道字符集是一套符号和编码,而对校验本规则不太熟悉。校对规则是在字符集内用于比较字符的一套规则,可以控制 select 查询时where 条件大小写是否敏感的规则.如字段 col 在表中的值为 'abc','ABC','AbC' 在不同的校对规则下,where col='ABC'会有不同的结果。
一 什么是字符校对规则
使用MySQL的大多数都知道字符集是一套符号和编码,而对校验本规则不太熟悉。校对规则是在字符集内用于比较字符的一套规则,可以控制 select 查询时where 条件大小写是否敏感的规则.如字段 col 在表中的值为 'abc','ABC','AbC' 在不同的校对规则下,where col='ABC'会有不同的结果。
系统常用的字符校对规则
_
校对规则有如下特征:
a 两个不同的字符集不能有相同的校对规则。
b 每个字符集有一个默认校对规则。例如,utf8默认校对规则是utf8generalci
c 存在校对规则命名约定:它们以其相关的字符集名开始,通常包括一个语言名比如utf8,并且以ci(大小写不敏感)、或bin(二元)结束 ,如 utf8_bin。

提示:
官方文档上说是有 cs(大小写敏感) 但是show collation like 'utf8%'; 并没有cs结尾的校对规则。

二 如何使用字符校对规则
MySQL 提供四种默认级别的字符集和校验规则:服务器级、数据库级、表级,和连接级,一般字段级别的不常用。
2.1 服务器级
MySQL服务器有一个服务器字符集和一个服务器校对规则,collationserver的默认字符集是在编译mysql的时候编译好的,比如
shell> ./configure --with-charset=utf8
或者:
shell> ./configure --with-charset=utf8 \
--with-collation=utf8generalci
如要要修改默认的字符校对规则,我们可以通过以下几种方式:
a 在/etc/my.cnf 的[mysqld]中添加
collationserver = utf8bin
修改之后必须重启
root@rac2 [(none)]> show variables like 'collation%';
+----------------------+-----------------+
| Variablename | Value |
+----------------------+-----------------+
| collationconnection | utf8generalci |
| collationdatabase | utf8bin |
| collationserver | utf8bin |
+----------------------+-----------------+
3 rows in set (0.00 sec)

b 通过mysqld 命令行添加 --character-set-server=utf8 --collation-server=utf8generalci
/usr/sbin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/home/mysql/data3306/data --log-error=/home/mysql/data3306/log/master-error.log --pid-file=/home/mysql/data3306/data/rac3.pid --socket=/tmp/mysql.sock --port=3306 --character-set-server=utf8 --collation-server=utf8generalci &

注意:这篇文章描述的并不准确
https://dev.mysql.com/doc/refman/5.1/zh/charset.html#charset-column
通过
shell> mysqld --default-character-set=utf8 \
--default-collation=utf8generalci
方式启动会报错
140430 9:34:56 InnoDB: 1.1.8 started; log sequence number 1628178
140430 9:34:56 [ERROR] /usr/sbin/mysqld: unknown variable 'default-character-set=utf8'
140430 9:34:56 [ERROR] Aborting

服务器级别字符集校对规则
root@rac2 [(none)]> show variables like 'collation%';
+----------------------+-----------------+
| Variablename | Value |
+----------------------+-----------------+
| collationconnection | utf8generalci |
| collationdatabase | utf8bin |
| collationserver | utf8_bin |
+----------------------+-----------------+
root@rac2 [dba]> create table t1(col varchar(5)) engine=innodb ;
Query OK, 0 rows affected (0.10 sec)
root@rac2 [dba]> insert into t1 values('abc'),('ABC'),('AbC');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@rac2 [dba]> select * from t1 where col='ABC';
+------+
| col |
+------+
| ABC |
+------+
1 row in set (0.01 sec)
root@rac2 [dba]> create table t2(col varchar(5)) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.11 sec)
root@rac2 [dba]> insert into t2 values('abc'),('ABC'),('AbC'); 
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@rac2 [dba]> select * from t2 where col='ABC';
+------+
| col |
+------+
| abc |
| ABC |
| AbC |
+------+
3 rows in set (0.00 sec)

2.2 数据库级别字符校对规则
每一个数据库有一个数据库字符集和一个数据库校对规则。CREATE DATABASE和ALTER DATABASE语句有一个可选的子句来指定数据库字符集和校对规则:
CREATE DATABASE dbname
[[DEFAULT] CHARACTER SET charsetname]
[[DEFAULT] COLLATE collation_name]
通常如果创建数据库的时候不指定db的字符集和校对规则,则使用服务器级别默认的校对规则。
如何修改数据库级别的字符校对规则:
   a 通过在创建数据库时指定 collationdatabase 字符集。
   b 通过ALTER DATABASE dbname [[DEFAULT] CHARACTER SET charsetname] [[DEFAULT] COLLATE collationname]
注意 在my.cnf 中的[mysql]或者[mysqld]中配置 
collationdatabase=utf8_bin
会分别报错:
mysql: unknown variable 'collationdatabase=utf8_bin' 
140430 13:56:19 [ERROR] /usr/sbin/mysqld: unknown variable 'collation_database=utf8_bin'
140430 13:56:19 [ERROR] Aborting
例子 
root@rac2 [(none)]> show variables like 'collation%';
+----------------------+-----------------+
| Variablename | Value |
+----------------------+-----------------+
| collationconnection | utf8generalci |
| collationdatabase | utf8bin |
| collationserver | utf8_bin |
+----------------------+-----------------+
3 rows in set (0.00 sec)
root@rac2 [(none)]> create database dba01;
Query OK, 1 row affected (0.00 sec)
root@rac2 [(none)]> use dba01
Database changed
root@rac2 [dba01]> CREATE TABLE t1(col varchar(5)) ;
Query OK, 0 rows affected (0.09 sec)
root@rac2 [dba01]> insert into t1 values('abc'),('ABC'),('AbC');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@rac2 [dba01]> select * from t1;
+------+
| col |
+------+
| abc |
| ABC |
| AbC |
+------+
3 rows in set (0.00 sec)
root@rac2 [dba01]> select * from t1 where col='abc';
+------+
| col |
+------+
| abc |
+------+
1 row in set (0.00 sec)
MySQL这样选择数据库字符集和数据库校对规则:
  如果指定了CHARACTER SET X和COLLATE Y,那么采用字符集X和校对规则Y。
  如果指定了CHARACTER SET X而没有指定COLLATE Y,那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。否则,采用服务器字符集和服务器校对规则。


2.3 表级别的校对规则
   每一个表有一个表字符集和一个校对规则,为指定表字符集和校对规则,CREATE TABLE 和ALTER TABLE语句有一个可选的子句:
CREATE TABLE tblname (columnlist)
[DEFAULT CHARACTER SET charsetname [COLLATE collationname]]
ALTER TABLE tblname
[DEFAULT CHARACTER SET charsetname] [COLLATE collationname]
例子:
root@rac2 [dba00]> CREATE TABLE t3(col varchar(5)) DEFAULT CHARACTER SET utf8 COLLATE utf8bin ;
Query OK, 0 rows affected (0.12 sec)
root@rac2 [dba00]> insert into t3 values('abc'),('ABC'),('AbC');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@rac2 [dba00]> select * from t3;
+------+
| col |
+------+
| abc |
| ABC |
| AbC |
+------+
3 rows in set (0.00 sec)
root@rac2 [dba00]> select * from t3 where col='abc';
+------+
| col |
+------+
| abc |
+------+
1 row in set (0.01 sec)
MySQL按照下面的方式选择表字符集和 校对规则:
   如果指定了CHARACTER SET X和COLLATE Y,那么采用CHARACTER SET X和COLLATE Y。
   如果指定了CHARACTER SET X而没有指定COLLATE Y,那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。
   否则,采用服务器字符集和服务器校对规则。
   如果在列定义中没有指定列字符集和校对规则,则默认使用表字符集和校对规则。表字符集和校对规则是MySQL的扩展;在标准SQL中没有。


2.4 连接字符集和校对规则详见 
文档 https://dev.mysql.com/doc/refman/5.1/zh/charset.html#charset-connection。

三 总结
    数据库查询使用校对规则的优先级 列>表>数据库>服务器,缺省情况下会继承当前字符集所对应默认的字符集校对规则,对于想要在查询的时候区分大小写情况而使用校对规则的话,最好创建数据库和表的时候 就指定好期望的字符校对规则。
   对于云产品的RDS 小白客户,显然有些难了,需要使用文档来指引。
  当然作为程序员或者DBA 一定要使用自己熟悉的知识,或者使用之前一定要做好足够的了解,线上无小事。。
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
Cloud Native OLAP OLTP
在业务处理分析一体化的背景下,开发者如何平衡OLTP和OLAP数据库的技术需求与选型?
在业务处理分析一体化的背景下,开发者如何平衡OLTP和OLAP数据库的技术需求与选型?
153 4
|
12月前
|
SQL Cloud Native 关系型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
989 1
|
2月前
|
SQL 测试技术 OLAP
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
现代化实时数仓 SelectDB 在时隔两年后再次完成登顶,在全部近百款数据库和数十种机型中,性能位居总榜第一!
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
|
1月前
|
存储 关系型数据库 MySQL
云原生数据仓库AnalyticDB产品使用合集之是否支持rdb数据库实时同步
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
158 4
|
16天前
|
存储 SQL 运维
OLAP数据库选型指南:Doris与ClickHouse的深入对比与分析
OLAP数据库选型指南:Doris与ClickHouse的深入对比与分析
|
1月前
|
存储 缓存 测试技术
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
近日,在 ClickHouse 发起的分析型数据库性能测试排行榜 ClickBench(https://benchmark.clickhouse.com/)中,现代化实时数仓 SelectDB 时隔两年后再次登顶,在全部近百款数据库和数十种机型中,性能表现位居总榜第一!
72 1
|
2月前
|
SQL 分布式计算 关系型数据库
云原生数据仓库产品使用合集之可以把ADB MySQL湖仓版数据库做成页面查询的数据库吗
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
11月前
|
SQL Cloud Native OLAP
云原生AnalyticDB数据库
试玩serverless数据库,功能很完善,体验不错
123 0
|
2月前
|
SQL BI Apache
奇富科技基于阿里云数据库 SelectDB 版内核 Apache Doris 的统一 OLAP 场景探索实践
Apache Doris 作为整体 OLAP 场景,助力奇富科技信贷科技服务平台优化,使得报表分析场景 SLA 达标率提升至 99% 以上,平均查询耗时降低 50%,为营销活动、广告投放等提供强有力的数据支持。
奇富科技基于阿里云数据库 SelectDB 版内核 Apache Doris 的统一 OLAP 场景探索实践
|
9月前
|
SQL 监控 关系型数据库
数据库高效“体检”必备!详解阿里云AnalyticDB MySQL一键诊断功能
AnalyticDB MySQL诊断监控平台则是一套全自研的、从零开始搭建的并紧密贴合分析型系统特征的诊断平台,包含数据的采集(诊断日志和监控数据)、数据缓存(削峰填谷)、清洗转换以及存储等,最终以多种服务形式提供实时的告警、监控、负载洞察、查询分析和诊断等功能。