MySQL大小写敏感、MySQL设置字段大小写敏感

怼烎@ 2024-04-06 15:36 17阅读 0赞

#### 文章目录 ####

*  一、MySQL大小写敏感规则
 *  二、设置数据库及表名大小写敏感
 *   *  2.1、查询库名及表名是否大小写敏感
     *  2.2、修改库名及表名大小写敏感
 *  三、MySQL列名大小写不敏感
 *  四、lower\_case\_table\_name与校对规则
 *   *  4.1、验证校对规则影响大小写敏感
     *  4.1、验证校对规则影响排序
 *  五、设置字段内容大小写敏感
 *   *  5.1、查看和修改某个表的编码集
     *  5.2、查看和修改某个字段的编码集
     *  5.3、设置字段大小写敏感

以下内容基于Mysql:8.0.20进行讲解。

所谓的大小写敏感指的是指的是大写与小写是两个两个值，而大小写不敏感指的是小写跟大写看做是一个值。

## 一、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][https_dev.mysql.com_doc_refman_8.0_en_identifier-case-sensitivity.html]  
![在这里插入图片描述][598a00fe0cbc47b4af8f7746a7a4e750.png]

**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下执行的结果  
![在这里插入图片描述][648a53dc3e154ca59fda36a2527e5ce4.png]  
根据上面对`lower_case_table_names`属性的讲解，我们发现windows中的值为1不区分大小写，而且创建的表名在数据库中是以小写存储的。

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

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

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

**Linux默认参数结果:**  
![在这里插入图片描述][db4a4786df1c48f5a4051c4ca1cb2054.png]

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

mysql8.0更改lower\_case\_table\_names

mysql8.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下测试(配置为默认配置)  
![在这里插入图片描述][0b007362ccb64d76b2bee66662b736ea.png]

## 四、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设置字符编码、设置表字段编码][MySQL_MySQL]》

<table> 
 <thead> 
  <tr> 
   <th>大小写是否敏感</th> 
   <th>库名</th> 
   <th>表名</th> 
   <th>表别名</th> 
   <th>字段名</th> 
   <th>字段别名</th> 
   <th>字段内容</th> 
   <th>变量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>lower_case_table_name = 0 utf8_general_ci</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>lower_case_table_name = 1 utf8_general_ci</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>lower_case_table_name = 0 utf8_bin</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
   <td>否</td> 
   <td>否</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>lower_case_table_name = 1 utf8_bin</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

### 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%';

![在这里插入图片描述][da8f1dce7e9f485aaf60ae0271a5262c.png]  
可以看出表1大小写不敏感，所以查询时包含a与A的都能查询出来；表2大小写敏感，所以只能查询出a的。

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

查表：
    select * from test1;
    select * from test2 ;

![在这里插入图片描述][40834ecf9ede40b7b6f4ecc0943b0457.png]  
可以看出区分大小写之后，两边的排序规则也不一样了。

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

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

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

查看

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;

![在这里插入图片描述][a8ef8fa37bd9439aa14a2eb616c26df6.png]  
**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设置字符编码、设置表字段编码][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;

![在这里插入图片描述][2bb930abd319457691234ef5bd4ab63d.png]

### 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 '年龄',
    	address varchar(50) comment '住址',
    	update_time datetime default null
    ) comment '测试表';

![在这里插入图片描述][8ffe4a2d7cde4270ba297a3dbc8af93b.png]  
**方式二：建表后修改字段大小写敏感**

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的含义和由来][MYSQL_8.0_- utf8_utf8mb3_utf8mb4]》

参考：  
官网介绍：[https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html][https_dev.mysql.com_doc_refman_8.0_en_identifier-case-sensitivity.html]

[https_dev.mysql.com_doc_refman_8.0_en_identifier-case-sensitivity.html]: https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html
[598a00fe0cbc47b4af8f7746a7a4e750.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/8d688ddda493465c824ef5ed44aafbc5.png
[648a53dc3e154ca59fda36a2527e5ce4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a4651073f42947d6a3e7a40e0db91aa8.png
[db4a4786df1c48f5a4051c4ca1cb2054.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/10c4912e079b426180b08146ccd4627e.png
[0b007362ccb64d76b2bee66662b736ea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/5f03d46eb76544b8b37465cdf9468a31.png
[MySQL_MySQL]: https://blog.csdn.net/weixin_49114503/article/details/136523537
[da8f1dce7e9f485aaf60ae0271a5262c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/2f3857c98d87438d9bd8d51547816deb.png
[40834ecf9ede40b7b6f4ecc0943b0457.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/8b067f12491942758da5dbafcea519ce.png
[a8ef8fa37bd9439aa14a2eb616c26df6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/7ed10f5652d44a68bac273c0adb195ee.png
[2bb930abd319457691234ef5bd4ab63d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/fda2898f38314968b4712ee9f8927a3c.png
[8ffe4a2d7cde4270ba297a3dbc8af93b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/66ff5badcf324fc19bc3d3bff5e9d2d6.png
[MYSQL_8.0_- utf8_utf8mb3_utf8mb4]: https://blog.csdn.net/weixin_49114503/article/details/136624441