MySQL中的校对集/大小写敏感/sql_mode实践

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL中的校对集/大小写敏感/sql_mode实践

校对集:数据比较方式。必须在无数据前进行声明,如果有了数据再进行数据修改,那么修改无效!

主要使用的有如下格式:

  • _bin:binary,二进制比较,取出二进制位,一位一位进行比较,区分大小写;
  • _cs:case sensitive,大小写敏感,区分大小写
  • _ci:case insensitive,大小写不敏感,不区分大小写。
  • _ai:accent insensitive,不区分重音;
  • _as:accent sensitive,区分重音;

【1】查看数据库所支持的校对集

show collation




20170507111636660.png

【2】对比不同校对集的效果

对比:使用utf8的_bin and _ci来验证不同校对集的效果;

① 创建两张使用不同校对集的表

CREATE TABLE `my_collate_bin` (
  `name` char(1) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE `my_collate_ci` (
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

② 分别插入数据

insert into my_collate_bin VALUES('a','A','B','b');
insert into my_collate_ci VALUES('a','A','B','b');

③ 比较

根据某个字段进行排序-order by 字段名[asc||desc] ;

select * from my_collate_bin ORDER BY name;
select * from my_collate_ci ORDER BY name;

20170507114120382.png

20170507114128194.png

可以看到图一是正常排序,图二未区分大小写(一般应用中,默认不区分大小写,使用utf8_general_ci)。


utf8_unicode_ci utf8_general_ci区别


二者对中文、英文来说没有实质区别。utf8_general_ci 校对速度快,但准确度稍差。utf8_unicode_ci 准确度高,但校对速度稍慢。一般情况下,用utf8_general_ci 就够了,但如果应用有德语、法语或者俄语,请一定使用utf8_unicode_ci 。

【3】大小写敏感

我们顺带说一下MySQL大小写敏感问题。主要涉及到环境变量:lower_case_table_names

show variables like '%lower_case_table_names%'


取值有三个:

  • 0:大小写敏感;
  • 1:大小写不敏感。创建的表、数据库都是以小写形式存放在磁盘上,对于SQL语句都是转换为小写对表和数据库进行查找。
  • 创建的表和数据库依据语句上格式存放,凡是查找都是转换为小写进行。

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的:

  • 数据库名、表名、表的别名、变量名是严格区分大小写的;
  • 关键字、函数名称在SQL中不区分大小写;
  • 列名与列的别名在所有的情况下都是忽略大小写的;

MySQL在window环境下全部不区分大小写。

如果Linux下想要设置大小写不敏感,那么需要在my.cnf文件中[mysqld]下添加如下配置:

lower_case_table_names=1

然后重启数据库服务,切记在重启数据库实例之前需要将原来的数据库和表转换为小写,否则将找不到数据库名。

这种操作适合于MySQL5.7不适合MySQL8。在MySQL8下禁止在重新启动MySQL服务时将 lower_case_table_names 修改为不同于初始化MySQL服务时设置的 lower_case_table_names 值。如果非要将MySQL8设置为大小写不敏感,具体步骤如下:

  • 停止MySQL服务;
  • 删除数据目录,通常删除 /var/lib/mysql
  • my.cnf文件中添加 lower_case_table_names=1
  • 重新启动MySQL服务

【4】sql_mode

sql_mode会影响MySQL支持的SQL语法以及它执行的数据验证检查。通过设置sql_mode 可以完成不同严格程度的数据校验,有效地保障数据准确性。


MySQL服务器可以在不同的SQL模式下运行,并且可以针对不同的客户端以不同的方式应用这些模式,具体取决于sql_mode系统变量的值。

MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的:

  • 5.6的sql_mode默认为空,即NO_ENGINE_SUBSTITUTION。其实表示的是一个空值,相当于没有什么模式设置,可以理解为宽松模式。在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。
  • 5.7的sql_mode是STRICT_TRANS_TABLES,也就是严格模式。用于进行数据的严格校验,错误数据不能插入,将会报error错误并产生回滚。

查看当前会话的sql_mode

select @@session.sql_mode;
select @@global.sql_mode;

临时设置sql_mode

# 当前会话
set session sql_mode='STRICT_TRANS_TABLES';
# 全局
set global sql_mode='STRICT_TRANS_TABLES';

永久设置sql_mode

同样是修改mysql的配置文件,在[mysqld]下增加配置:

sql_mode=STRICT_TRANS_TABLES

针对生产环境禁止重启MySQL服务,所以解决措施通常是临时方式+永久方式来解决线上问题,这样即使某天MySQL服务重启了,我们的配置仍然生效。

image.png



相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
5天前
|
SQL 存储 关系型数据库
【MySQL】SQL 优化
【MySQL】SQL 优化
23 0
|
1天前
|
SQL 关系型数据库 MySQL
查询mysql版本sql - 蓝易云
执行这个命令后,MySQL将返回当前正在运行的版本信息。
20 0
|
3天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之从MySQL到Flink 1.16.2 Flink-SQL的数据同步工作出现了一个异常如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
17 0
|
4天前
|
SQL 存储 数据处理
实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
12 1
|
5天前
|
SQL 存储 关系型数据库
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!(下)
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!
22 2
|
5天前
|
SQL 关系型数据库 MySQL
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!(上)
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!
20 2
|
5天前
|
SQL 关系型数据库 MySQL
【MySQL系列笔记】常用SQL
常用SQL分为三种类型,分别为DDL,DML和DQL;这三种类型的SQL语句分别用于管理数据库结构、操作数据、以及查询数据,是数据库操作中最常用的语句类型。 在后面学习的多表联查中,SQL是分析业务后业务后能否实现的基础,以及后面如何书写动态SQL,以及完成级联查询的关键。
204 6
|
5天前
|
SQL 关系型数据库 MySQL
【MySQL-4】简述SQLの通用语法及4种基本语句介绍(DDL/DML/DQL/DCL)
【MySQL-4】简述SQLの通用语法及4种基本语句介绍(DDL/DML/DQL/DCL)
|
5天前
|
SQL 存储 关系型数据库
【MySQL系列笔记】SQL优化
SQL优化是通过调整数据库查询、索引、表结构和配置参数等方式,提高SQL查询性能和效率的过程。它旨在减少查询执行时间、减少系统资源消耗,从而提升数据库系统整体性能。优化方法包括索引优化、查询重写、表分区、适当选择和调整数据库引擎等。
235 3