问小白 wenxiaobai
资讯
历史
科技
环境与自然
成长
游戏
财经
文学与艺术
美食
健康
家居
文化
情感
汽车
三农
军事
旅行
运动
教育
生活
星座命理

MySQL大小写敏感设置完全指南

创作时间:
作者:
@小白创作中心

MySQL大小写敏感设置完全指南

引用
CSDN
1.
https://blog.csdn.net/weixin_49114503/article/details/136523591

本文详细介绍了MySQL在不同操作系统下的大小写敏感规则，包括如何设置数据库及表名的大小写敏感、列名的大小写敏感特性、lower_case_table_name参数与校对规则的关系，以及如何设置字段内容的大小写敏感。通过具体的SQL示例和配置方法，帮助读者全面理解并掌握MySQL大小写敏感的设置和应用。

一、MySQL大小写敏感规则

在不同操作系统下，MySQL的大小写敏感规则有所不同：

  • Linux下：

  • 数据库名与表名是严格区分大小写的；

  • 表的别名是严格区分大小写的；

  • 列名与列的别名在所有的情况下均是忽略大小写的；

  • 变量名也是严格区分大小写的；

  • Windows下：

  • 都不区分大小写。

  • Mac OS下，文件系统类型HFS+，非UFS卷：

  • 都不区分大小写。

MySQL中控制数据库名和表名的大小写敏感由参数 lower_case_table_names 控制，为0时表示区分大小写，为1时，表示将名字转化为小写后存储，不区分大小写。在不同的操作系统上，这个参数的默认值不同：

  • Unix（包括Linux）下，默认值为0，表示对数据库名和表名的大小写是敏感的。
  • Windows下，默认值为1，表示不敏感，即在存储和查找时会将名称转换为小写。
  • macOS下，默认值为2，表示存储区分大小写，但在查询时会转换为小写。

官网介绍：https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

lower_case_table_names：

  • 0：使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母在硬盘上保存表名和数据库名。名称比较对大小写敏感。在大小写不敏感的操作系统如windows或Mac OS x上我们不能将该参数设为0，如果在大小写不敏感的文件系统上将此参数强制设为0，并且使用不同的大小写访问MyISAM表名，可能会导致索引破坏。

  • 1：表名在硬盘上以小写保存，名称比较对大小写不敏感。MySQL将所有表名转换为小写在存储和查找表上。该行为也适合数据库名和表的别名。该值为Windows的默认值。

  • 2：表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母进行保存，但MySQL将它们转换为小写在查找表上。名称比较对大小写不敏感，即按照大小写来保存，按照小写来比较。注释：只在对大小写不敏感的文件系统上适用innodb表名用小写保存。

在大小写不敏感的操作系统（Windows和macOS）中，不能将 lower_case_table_names 设置为0。如果需要在这些系统中设置大小写敏感，可以通过修改配置文件 my.ini 来更改 lower_case_table_names 的值。例如，在Windows下，可以将 lower_case_table_names 设置为2，并重启MySQL服务。

在创建表时，可以通过设置字段属性 binary 或使用 collate 规则来控制字段内容的大小写敏感性。例如，将字段标记为 binary 或设置 collate 为 utf8_bin 可以实现二进制大小写敏感。如果建表时未指定 collate 规则，也可以通过修改字段的 collate 属性来实现大小写敏感。

总结如下：

  • 在Unix系统中，数据库名和表名的大小写是敏感的。
  • 在Windows系统中，数据库名和表名的大小写不敏感，存储和查找时会转换为小写。
  • 在macOS系统中，数据库名和表名的大小写是敏感的，但在查询时会转换为小写。
  • 在大小写不敏感的系统中（Windows和macOS），不能将 lower_case_table_names 设置为0。
  • 可以通过修改配置文件或设置字段属性来控制大小写敏感性。

二、设置数据库及表名大小写敏感

2.1、查询库名及表名是否大小写敏感

可以通过 show variables like "%case%"; 查看当前MySQL关于大小写的设置，下面是在Windows下执行的结果：

根据上面对 lower_case_table_names 属性的讲解，我们发现Windows中的值为1不区分大小写，而且创建的表名在数据库中是以小写存储的。

lower_case_file_system

该参数是用于描述MySQL的数据目录data所在的操作系统是否为大小写敏感，该参数为boolean类型，但无法修改。

  • 0 – 大小写敏感
  • 1 – 大小写不敏感

比如跑在Linux上的都是OFF或者0。

Linux默认参数结果:

2.2、修改库名及表名大小写敏感

MySQL 8.0 更改 lower_case_table_names

MySQL 8.0 安装初始化之后是不能更改 lower_case_table_names 的。下面是官方文档的一段话：

In MySQL 8.0, the lower_case_table_names variable can only be configured when the MySQL server is initialized.

解决方法：

方法一：在初始化的时候就要设置好这个变量的值

方法二：删除数据目录，重新初始化（执行命令前切记先备份好数据，此方法并没有亲自试验过，切记慎重使用）

（1）删除数据目录 datadir=/usr/local/mysql/data

-- 切记先备份好数据
rm -rf /usr/local/mysql/data

（2）在配置文件中 [mysqld] 下添加 lower_case_table_names = 1

vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql                         #  基本路径
datadir=/usr/local/mysql/data                   #数据路径 （先在/usr/local/mysql建data目录）
socket=/usr/local/mysql/data/mysql.sock         # socket文件
symbolic-links=0
log-error=/usr/local/mysql/data/mysqld.log      #错误日志
pid-file=/usr/local/mysql/data/mysqld.pid       #pid文件
lower_case_table_names = 1  #添加这行
[mysql]
socket=/usr/local/mysql/data/mysql.sock
[mysqldump]
socket=/usr/local/mysql/data/mysql.sock

（3）执行初始化命令

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lower-case-table-names=1

（4）检查变量的值

跳过授权表启动

mysqld_safe  --defaults-file=/etc/my.cnf  --skip-grant-tables &

免密登录

mysql -uroot -p 不需要输入密码直接回车
show variables like '%lower_case_table_names%';

三、MySQL列名大小写不敏感

列名与列的别名在所有的情况下均是忽略大小写的，不管是Linux系统还是Windows系统下MySQL的列名都默认不区分大小写。

该测试在Windows下测试(配置为默认配置)

四、lower_case_table_name与校对规则

  • lower_case_table_name: 影响大小写敏感包括库名、表名、表别名等。
  • 校对规则（COLLATE）: 影响字段内容大小写敏感及排序规则。

MySQL常用排序规则有：utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin、utf8mb4_0900_ai_ci

_bin: 按二进制方式比较字符串，区分大小写和重音符号。

_ai_ci:按照特定语言或地区方式比较字符串，不区分大小写和重音符号。

_unicode_ci: 按 Unicode 标准方式比较字符串，不区分大小写和重音符号。

_general_ci:按一般方式比较字符串，不区分大小写和重音符号。

  • bin：binary case sensitive ，binary 以二进制方式，区分大小写
  • ci：即case insensitive，不区分大小写，即排序时 p 和 P相同 。
  • cs：case sensitive 区分大小写
  • ai： accent insensitive，指口音不敏感，不区分重音，即排序时 e，è，é，ê 和 ë 相同。
  • as：accent sensitive，即口音敏感，区分重音，也就是说，排序时 e，è，é，ê 和 ë 互不相同。
  • 0900： 是 Unicode 校对算法版本。

关于校对规则参考另一篇文章《MySQL字符集、字符编码、排序规则、MySQL设置字符编码、设置表字段编码》

大小写是否敏感 库名 表名 表别名 字段名 字段别名 字段内容 变量

lower_case_table_name = 0 utf8_general_ci 是 是 是 否 否 否 否

lower_case_table_name = 1 utf8_general_ci 否 否 否 否 否 否 否

lower_case_table_name = 0 utf8_bin 是 是 是 否 否 是 否

lower_case_table_name = 1 utf8_bin 否 否 否 否 否 是 否

4.1、验证校对规则影响大小写敏感

建表：
create table test1(name varchar(100), primary key(name));
create table test2(name varchar(100), primary key(name)) collate utf8_bin;   
给表test1插入数据：
insert into test1(name) values('abc');
insert into test1(name) values('ABD');
insert into test1(name) values('ZBC');
给表test2插入数据：
insert into test2(name) values('abc');
insert into test2(name) values('ABD');
insert into test2(name) values('ZBC');
查表：
select * from test1 where name like 'a%';
select * from test2 where name like 'a%';

可以看出表1大小写不敏感，所以查询时包含a与A的都能查询出来；表2大小写敏感，所以只能查询出a的。

4.1、验证校对规则影响排序

查表：
select * from test1;
select * from test2 ;
![](https://wy-static.wenxiaobai.com/chat-rag-image/12318158461400289858)

可以看出区分大小写之后，两边的排序规则也不一样了。

五、设置字段内容大小写敏感

不管是Windows还是Linux系统，MySQL在任何系统上字符串内容默认是不区分大小写的。若想要字段内容区分大小写，可以设置字段编码大小写敏感。

5.1、查看和修改某个表的编码集

查看

![](https://wy-static.wenxiaobai.com/chat-rag-image/8233139425000768382)
show  create table  表名;
或者
show table status from <数据库名/schema> like '<表名>';
示例：show table status from test  like 'tbl_test';

修改

alter table 表名  character set  编码格式  collate    utf8_general_ci（编码规则）;

如下sql:

drop table if exists tbl_test;
create table tbl_test(
    id int primary key auto_increment,
    name varchar(30) CHARACTER SET utf8 COLLATE utf8_bin unique not null,
    age int comment '年龄',
    address varchar(50) comment '住址',
    update_time datetime default null
) comment '测试表';
-- 查询建表ddl语句
show create table  tbl_test;

COLLATE后缀含义：

  • _ci: case insensitive 不区分大小写
  • _cs：case sensitive 区分大小写
  • _ai： accent insensitive 不区分重音
  • _as: accent sensitive 区分重音
  • _bin：binary case sensitive collation，binary 以二进制方式，区分大小写

所以上面这个表整体字段的默认编码是utf8mb4_0900_ai_ci不区分大小写，但是其中的name字段编码为utf8mb3_bin区分大小写。

MySQL中的编码参考另一篇文章《MySQL字符集、字符编码、排序规则、MySQL设置字符编码、设置表字段编码》

5.2、查看和修改某个字段的编码集

查看

方式一：
show create table  表名;
示例见上面的图片
方式二：
show full  columns  from 表名 where field='字段名'
例：show full  columns from  tb  where  field='id'

修改

alter table 表名   modify  字段名  字段数据类型  character set 字符编码 not null;
例：alter table  tb  modify  name varchar(10)  character  set  utf8  not null;

5.3、设置字段大小写敏感

大小写敏感就是大写的A与小写的a是两个值，通过A查询时只能查询出A而不能查询出a。从上面4.1、4.2中我们也可以看出如何设置大小写敏感了，这里再总结一下。

方式一：建表时设置字段大小写敏感

如下sql: 设置name字段大小写敏感

drop table if exists tbl_test;
create table tbl_test(
    id int primary key auto_increment,
    name varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin unique not null,
    age int comment '年龄',
![](https://wy-static.wenxiaobai.com/chat-rag-image/1495362592817651647)
    address varchar(50) comment '住址',
    update_time datetime default null
) comment '测试表';

方式二：建表后修改字段大小写敏感

alter table 表名   modify  字段名  字段数据类型  character set 字符编码 not null;
示例：alter table tbl_test   modify  name varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin unique not null;

这里的字符编码用了utf8mb3_bin，还有一种编码是utf8mb4_bin，具体区别在哪里可以参考文章《MYSQL（8.0版本及以上）- utf8，utf8mb3和utf8mb4的含义和由来》

5.4、查询时设置字段大小写敏感/不敏感

方式一：查询时设置字段编码为敏感

SELECT * FROM test1 WHERE name like '%B%' COLLATE utf8mb4_bin;

方式二：查询时设置字段编码为不敏感

SELECT * FROM test1 WHERE name like '%B%' COLLATE utf8mb4_general_ci;

参考：

官网介绍：https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

热门推荐
© 2023 北京元石科技有限公司 ◎ 京公网安备 11010802042949号