首页 数据库 MySQL 字符集、排序规则与查询关系详解

MySQL 字符集、排序规则与查询关系详解

MySQL 查询是否区分大小写及重音敏感,取决于创建时指定的字符集(character set)和排序规则(collation)。

(1)字符集(Character Set):规定可存储的字符,如 utf8、utf8mb4、latin1 等。

(2)排序规则(Collation):确定字符比较与排序规则,如是否区分大小写、重音等。

注意:

(1)每个排序规则必须对应特定的字符集,如 utf8mb4_bin 仅用于 utf8mb4 字符集。

(2)“重音敏感”(Accent Sensitive):指排序规则将带重音符号的字母视为与基本字母相同或不同的特性,如 'cafe' 与 'café'。

在 MySQL 5.7 中,默认字符集和排序规则是 latin1 和 latin1_swedish_ci。实际应用里,建议使用 utf8mb4 字符集以支持完整 Unicode 字符(含 emoji),并采用默认对应的 utf8mb4_general_ci 排序规则,该规则下查询不区分大小写。

一、常见排序规则
排序规则 对应字符集 大小写敏感 重音敏感 适用版本 说明
utf8_general_ci utf8 ❌ ❌ 所有版本 基本 Unicode 比较
utf8mb4_general_ci utf8mb4 ❌ ❌ 5.5.3+ 默认的 utf8mb4 排序规则
latin1_swedish_ci latin1 ❌ ❌ 所有版本 传统西欧默认排序规则
utf8mb4_unicode_ci utf8mb4 ❌ ✅ 5.5.3+ 符合 Unicode 标准的更准确排序
utf8mb4_0900_ai_ci utf8mb4 ❌ ❌ 8.0+ 基于 Unicode 9.0 标准
utf8_bin utf8 ✅ ✅ 所有版本 二进制比较(严格区分大小写)
utf8mb4_bin utf8mb4 ✅ ✅ 5.5.3+ utf8mb4 的二进制比较
utf8mb4_unicode_cs utf8mb4 ✅ ✅ 5.5.3+ 已弃用,改用 utf8mb4_bin
utf8mb4_0900_as_cs utf8mb4 ✅ ✅ 8.0+ 严格区分大小写和重音
表格说明:

(1)可通过排序规则后缀判断其是否支持大小写和重音敏感,ci、cs、ai、as 分别表示不区分大小写、区分大小写、不区分重音和区分重音。

(2)带 bin 后缀的排序规则基于二进制(Binary)比较,严格区分和重音。

二、设置方法
1. 数据库实例级
# my.conf 或 my.ini
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
2. 数据库级
CREATE DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
3. 表级
CREATE TABLE case_sensitive_table (
id INT,
username VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
4. 列级
CREATE TABLE case_sensitive_table (
id INT,
username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
三、设置建议
1. 注意事项
(1)性能影响:使用区分大小写的比较可能会影响查询性能,混合使用不同字符集也可能影响查询优化

(2)存储空间:不同字符集占用的存储空间不同 (如 utf8mb4 比 latin1 占用更多空间)

(3)索引限制:某些情况下不同字符集的列不能直接比较或连接

(4)转换风险:修改已有数据的字符集前需谨慎,可能导致数据丢失或损坏

2. 最佳实践
(1)尽量保持各表及表内各列字符集和排序规则一致,除非有特殊需求

(2)修改已有数据的字符集前先备份数据

(3)对于需要区分大小写的列 (如用户名、验证码),明确指定使用_bin排序规则

(4)应文档化说明使用不同字符集/排序规则的原因

(5)MySQL 8.0 默认使用 utf8mb4 + utf8mb4_0900_ai_ci,它符合更新的 Unicode 标准且性能更优

四、查看方法
## 查看数据库字符集和排序规则

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

## 查看某表字符集和序规则
SHOW CREATE TABLE `table_name`; # CHARSET 后面为字符集
SHOW TABLE STATUS LIKE 'table_name'; # Collation 列表示排序规则
五、查询时如何强制区分大小写
1. 强制方法
如遇到不支持区分大小写的排序规则,可使用以下方法强制启用:

(1)使用 COLLATE 指定区分大小写的排序规则

SELECT * FROM table_name WHERE column_name COLLATE utf8mb4_bin = 'Value'; -- utf8mb4_bin 需适配字符集
(2)使用 BINARY 运算符

SELECT * FROM table_name WHERE BINARY column_name = 'Value';
(3)使用 LIKE BINARY 语法

SELECT * FROM table_name WHERE column_name LIKE BINARY 'Value';
(4)使用区分大小写的函数

SELECT * FROM table_name WHERE CAST(column_name AS BINARY) = CAST('Value' AS BINARY);
(5)修改列或表的字符集排序规则

-- 修改列的排序规则
ALTER TABLE table_name MODIFY column_name VARCHAR(255) COLLATE utf8mb4_bin;

-- 修改表的默认排序规则
ALTER TABLE table_name COLLATE utf8mb4_bin;
2. 各方法对比
方法 是否利用索引 持久性 字符集兼容性 推荐场景 缺点
使用 COLLATE 指定区分大小写的排序规则 ✅ 是 仅当前查询 需匹配列字符集 临时区分大小写查询 需显式指定,字符集需一致
使用 BINARY 运算符 ⚠️ 部分场景 仅当前查询 任意字符集 快速兼容性写法 某些版本无法利用索引
使用 LIKE BINARY 语法 ⚠️ 部分场景 仅当前查询 任意字符集 不推荐(语义混淆) 性能差,模糊匹配语义不清晰
使用区分大小写的函数 ⚠️ 部分场景 仅当前查询 任意字符集 极特殊场景 性能最差,语法冗长
修改列或表的字符集排序规则 ✅ 是 ✅ 永久生效 需匹配列字符集 需要长期区分大小写的字段 修改表结构可能锁表
3. 使用建议
如仅当前查询需区分大小写,建议使用 COLLATE 指定区分大小写的排序规则;如需永久生效,建议修改列或表的字符集排序规则。

站星网

MySQL 查询是否区分大小写及重音敏感,取决于创建时指定的字符集(character set)和排序规则(collation)..

为您推荐

Mysql跨库操作

在 MySQL 中,操作多数据源(例如从库 A 和库 B)进行联查的情况,可以分为以下两种场景:A 库和 B 库在同一个 MySQL 实例当 A 库和 B 库在同一个 MySQL 实例下时,可以直接使用跨库联查查询。示例如下:SELECT A.co..

MySQL下200GB大表备份,利用传输表空间解决停服发版表备份问题

问题背景在停服发版更新时,需对 200GB 大表(约 200 亿行数据)进行快速备份以预防操作失误。因为曾经出现过有开发写的发版语句里,UPDATE语句的WHERE条件写错了,原本只需要更新几行数据,最终导致更新了半张表的..

服务器安装数据库MySQL8.0版本,打包导入到MySQL5.6失败的结局方式

最近数据库升级为mysql8.0,在使用过程中发现一些问题,首先mysql8.0有很多新特性,对服务器配置要求较高,所有就考虑把数据库版本切换到MySQL5.6,经过多出测试处理发现在8.0数据库打包的数据导入到5.6总是报错,或..

MySQL查询建表规范

因为之前一直再查找一些比较好的数据库规范,以方便在开发时连接 MySQL 进行查询/建表的时候,能根据规范来执行,达到提高 查询速度 / 执行 SQL 的性能 和提升 MySQL 的整体性能, 这里主要是存放一些比较好的一些数..

交易系统:退款单模型设计详解

大家好,我是汤师爷~和退款单作为整个交易逆向系统的核心,支撑着售后管理环节。售后域核心概念模型1、退款单退款单是记录和跟踪退款处理过程的核心业务单据,包含以下关键信息:租户ID:标识所属商户或组织退款单ID..

JavaScript API 设计原则详解

前言本篇博文来自一次公司内部的前端分享,从多个方面讨论了在设计接口时遵循的原则,总共包含了七个大块。系卤煮自己总结的一些经验和教训。本篇博文同时也参考了其他一些文章,相关地址会在后面贴出来。很难做到详..

JavaScript 事件委托详解

基本概念事件委托,通俗地来讲,就是把一个元素响应事件(click、focus……)的函数委托到另一个元素;一般来讲,会把一个或者一组元素的事件委托到它的父层或者更外层元素上,真正绑定事件的是外层元素,当事件响应..

JavaScript 中的遍历详解

编程这么多年,要是每次写遍历代码时都用 for 循环,真心感觉对不起 JavaScript 语言~对象遍历为了便于对象遍历的测试,我在下面定义了一个测试对象obj。测试对象// 为 Object 设置三个自定义属性(可枚举)Object.p..

必须掌握的MySQL优化指南(2)

4|0表分区MySQL 在 5.1 版引入的分区是一种简单的水平拆分,用户需要在建表的时候加上分区参数,对应用是透明的无需修改代码。对用户来说,分区表是一个独立的逻辑表,但是底层由多个物理子表组成,实现分区的代码实..

mysql分表简单介绍

一、Mysql分表的原因1、当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会停在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。2、mysql中有一种机制是表锁定..

MySQL创建用户与授权

一, 创建用户: 命令:CREATE USER'username'@'host' IDENTIFIED BY 'password'; 说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, ..

必须掌握的MySQL优化指南(1)

1.单表优化除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度。一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的。而事实上很多时..

详解:订单履约系统规划

大家好,我是汤师爷~什么是订单履约系统?订单履约是从消费者下单支付到收到商品的全流程管理过程,包括订单接收、订单派单、库存分配、仓储管理和物流配送等环节,核心目标是确保商品准时、准确地送达消费者手中。..

Redis 数据结构详解

Redis 数据类型分为:字符串类型、散列类型、列表类型、集合类型、有序集合类型。Redis 这么火,它运行有多块?一台普通的笔记本电脑,可以在1秒钟内完成十万次的读写操作。原子操作:最小的操作单位,不能继续拆分..

Mysql查询的一些操作(查表名,查字段名,查当月,查一周,查当天)

查询数据库中所有表名select table_name from information_schema.tables where table_schema='tools' and table_type='base table';查询指定数据库中指定表的所有字段名column_nameselect column_n..

MySQL查看、修改字符集及Collation

前言在使用MySQL的过程中,可能会出现初始设计使用的字符集或Collation不符合当前需求的情况。如使用utf8的表(MySQL中的utf8即utf8mb3)要支持emoji,而utf8mb3不支持emoji(emoji需要4个字节,而utf8mb3最长只支持..

MySQL SQL调优之索引

本篇记录MySQL的索引知识学习笔记,也方便自己以后查找复习一、索引的概念MySQL官方给出的索引定义:索引(Index)是帮助MySQL高效获取数据的数据结构。所以说索引就是排好序的快速查找数据结构二、索引分类MySQL的索..

mysql随机获取一条或者多条数据

语句一:select * from users order by rand() LIMIT 1MYSQL手册里面针对RAND()的提示大概意思就是,在 ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描,导致效率相当相当的低,效率不行,切..

.NET C#中的Func、Predicate和Expression用法详解

在 .NET C# 中,Func、Predicate 和 Expression 是非常常见的委托类型和表达式树,广泛用于函数式编程、数据查询(如 LINQ)以及表达式编译等场景。以下是它们的详细用法和区别。1. FuncFunc 是一个通用委托,用于表..

Mysql子查询

子查询出现在select语句中的select语句,称为子查询或内查询。外部的select查询语句,称为主查询或外查询。子查询分类按结果集的行列数不同分为4种标量子查询(结果集只有一行一列)列子查询(结果集只有一列多行)..

发表回复

返回顶部

微信分享

微信分享二维码

扫描二维码分享到微信或朋友圈

链接已复制