前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >技术分享 | MySQL--测试--排序规则 collation

技术分享 | MySQL--测试--排序规则 collation

原创
作者头像
爱可生开源社区
发布2022-10-19 13:40:21
1.2K0
发布2022-10-19 13:40:21
举报

作者:姚嵩

外星人...

本文来源:原创投稿

*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


摘抄:

https://dev.mysql.com/doc/refman/8.0/en/charset-general.html

https://dev.mysql.com/doc/refman/8.0/en/charset-server.html

https://dev.mysql.com/doc/refman/8.0/en/charset-database.html

https://dev.mysql.com/doc/refman/8.0/en/charset-table.html

https://dev.mysql.com/doc/refman/8.0/en/charset-literal.html

背景:

客户反应查询返回的结果不符合预期,读取时想要实现⼤⼩写区分;

简介:

排序规则 collation 是⼀个字符集的字符进⾏⽐较的⼀组规则;

⾄少会有两个规则:

1) 是否区分⼤⼩写;

2) 编码;

常⽤的规则是是否区分⼤⼩写。

字符集和排序规则的默认选择:

如果你仅指定字符集,⽽不指定排序规则,则排序规则为字符集默认的排序规则;

如果你仅指定排序规则,不指定字符集,则字符集为排序规则对应的字符集;

例外项⻅:

"设置对象的字符集和排序规则" 中 "说明"列中包含的内容。

查看字符集与其默认的排序规则:

SHOW CHARACTER SET ;

或者

select * from INFORMATION_SCHEMA.CHARACTER_SETS ;

查看字符集包含的排序规则:

SHOW COLLATION WHERE Charset = 'utf8mb4'; -- 这⾥的utf8mb4是具体的字符集

查看数据库的默认字符集和排序规则:

USE db_name;

SELECT @@character_set_database, @@collation_database;

或者

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME

FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

设置对象的字符集和排序规则:

对象

字符集

排序规则

说明

server

character_set_server

collation_server

如果create database时未带上字符集和排序规则,则使⽤server中声明的作为默认值;

database

建库时的CHARACTER SET ⼦句;character_set_database

建库时的COLLATE ⼦句;collation_database

如果create table时未带上字符集和排序规则,则使⽤数据库中声明的作为默认值;如果load data时未带上character set⼦句,则使⽤character_set_database作为默认值;如果在创建routine时未带上字符集和排序规则,则使⽤数据库中声明的作为默认值;

table

建表时的CHARACTER SET ⼦句;

建表时的COLLATE ⼦句;

如果未在单个列上指定字符集和排序规则,则将表中声明的作为默认值;

column

建表时字段定义上的CHARACTER SET ⼦句;

建表时字段定义上的 COLLATE ⼦句;

字符串

SELECT _utf8mb4'abc' ;

SELECT 'abc' COLLATE utf8_general_ci;

如果未指定字符集但指定了排序规则,则字符集使⽤ character_set_connection;如果未指定字符集和排序规则,则使⽤character_set_connection和collation_connection作为默认值;

说明:

如果客户是查询表中的数据,那么寻找数据时是否忽略⼤⼩写,取决于对应字段上的COLLATE⼦句中定义的排序规则;

如果客户是直接查询字符串,那么寻找数据时是否忽略⼤⼩写,取决于collation_connection的值;

数据存⼊不受collation影响,数据查询受collation影响。

测试案例:

测试环境:

MySQL Version:8.0.28

基础环境:
代码语言:txt
复制
create database if not exists test_db ;
use test_db;
drop table if exists test_db.a ;
create table test_db.a(i char(1)) engine=InnoDB default charset=utf8mb4 ;
测试:确认collate是否影响数据的存⼊

查询表中的数据时,值是否返回受字段上的collation影响

步骤:

代码语言:txt
复制
alter table test_db.a change i i char(1) collate utf8mb4_0900_ai_ci ; -- 设置列上忽略⼤⼩写
insert into test_db.a values('a'),('A'); 
select * from test_db.a ; -- 存⼊的值含 "a" "A"
select * from test_db.a where i='a'; -- 返回 "a" "A"
truncate table test_db.a ; 
alter table test_db.a change i i char(1) collate utf8mb4_0900_as_cs ; -- 设置列上区分⼤⼩写
insert into test_db.a values('a'),('A'); 
select * from test_db.a ; -- 存⼊的值含"a" "A"
select * from test_db.a where i='a'; -- 返回 "a"
truncate table test_db.a ;
结果截图:

说明:

数据存⼊不受表字段上的collate影响;

但是搜索记录时会受表字段上的collate影响。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 摘抄:
  • 背景:
  • 简介:
  • 说明:
  • 测试案例:
    • 测试环境:
      • 基础环境:
        • 测试:确认collate是否影响数据的存⼊
          • 结果截图:
          • 说明:
          相关产品与服务
          数据库
          云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
          领券
          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档