文章目录一、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.htmllower_case_table_names0使用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_namesmysql8.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/data2在配置文件中[mysqld]下添加lower_case_table_names 1vim /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-links0 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.sock3执行初始化命令/usr/local/mysql/bin/mysqld --initialize --usermysql --basedir/usr/local/mysql --datadir/usr/local/mysql/data --lower-case-table-names14检查变量的值跳过授权表启动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按一般方式比较字符串不区分大小写和重音符号。binbinary case sensitive binary 以二进制方式区分大小写ci即case insensitive不区分大小写即排序时 p 和 P相同 。cscase sensitive 区分大小写ai accent insensitive指口音不敏感不区分重音即排序时 eèéê 和 相同。asaccent 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 ;可以看出区分大小写之后两边的排序规则也不一样了。五、设置字段内容大小写敏感不管是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;COLLATE后缀含义_ci: case insensitive 不区分大小写_cscase sensitive 区分大小写_ai accent insensitive 不区分重音_as: accent sensitive 区分重音_binbinary case sensitive collationbinary 以二进制方式区分大小写所以上面这个表整体字段的默认编码是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 fieldid修改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 年龄, 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具体区别在哪里可以参考文章《 MYSQL8.0版本及以上- utf8utf8mb3和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