qidao123.com技术社区-IT企服评测·应用市场

标题: MySQL大小写敏感、MySQL设置字段大小写敏感 [打印本页]

---

作者: 丝    时间: 5 天前
标题: MySQL大小写敏感、MySQL设置字段大小写敏感
文章目录

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

以下内容基于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

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、修改库名及表名大小写敏感

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

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

复制代码

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

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

复制代码

（3）执行初始化命令

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

复制代码

（4）检查变量的值
跳过授权表启动

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

复制代码

免密登录

1. mysql -uroot -p 不需要输入密码直接回车
3. 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、验证校对规则影响大小写敏感

1. 建表：
2. create table test1(name varchar(100), primary key(name));
3. create table test2(name varchar(100), primary key(name)) collate utf8_bin;   
5. 给表test1插入数据：
6. insert into test1(name) values('abc');
7. insert into test1(name) values('ABD');
8. insert into test1(name) values('ZBC');
10. 给表test2插入数据：
11. insert into test2(name) values('abc');
12. insert into test2(name) values('ABD');
13. insert into test2(name) values('ZBC');
15. 查表：
16. select * from test1 where name like 'a%';
17. select * from test2 where name like 'a%';

复制代码

可以看出表1大小写不敏感，所以查询时包罗a与A的都能查询出来；表2大小写敏感，所以只能查询出a的。
4.1、验证校对规则影响排序

1. 查表：
2. select * from test1;
3. select * from test2 ;

复制代码

可以看出区分大小写之后，双方的排序规则也不一样了。
五、设置字段内容大小写敏感

   不管是windows还是Linux体系，mysql在任何体系上字符串内容默认是不区分大小写的。若想要字段内容区分大小写，可以设置字段编码大小写敏感。
  5.1、检察和修改某个表的编码集

检察

1. show  create table  表名;
2. 或者
3. show table status from <数据库名/schema> like '<表名>';
5. 示例：show table status from test  like 'tbl_test';

复制代码

修改

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

复制代码

如下sql:

1. drop table if exists tbl_test;
2. create table tbl_test(
3.         id int primary key auto_increment,
4.         name varchar(30) CHARACTER SET utf8 COLLATE utf8_bin unique not null,
5.         age int comment '年龄',
6.         address varchar(50) comment '住址',
7.         update_time datetime default null
8. ) comment '测试表';
10. -- 查询建表ddl语句
11. 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、检察和修改某个字段的编码集

检察

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

复制代码

修改

1. alter table 表名   modify  字段名  字段数据类型  character set 字符编码 not null;
3. 例：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字段大小写敏感

1. drop table if exists tbl_test;
2. create table tbl_test(
3.         id int primary key auto_increment,
4.         name varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin unique not null,
5.         age int comment '年龄',
6.         address varchar(50) comment '住址',
7.         update_time datetime default null
8. ) comment '测试表';

复制代码

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

1. alter table 表名   modify  字段名  字段数据类型  character set 字符编码 not null;
3. 示例：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、查询时设置字段大小写敏感/不敏感

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

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

复制代码

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

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

复制代码

参考：
官网先容：https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html
创作不易，欢迎打赏，你的鼓励将是我创作的最大动力。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 qidao123.com技术社区-IT企服评测·应用市场 (https://dis.qidao123.com/)

Powered by Discuz! X3.4