Mysql 大小写敏感性问题

时间：2021-12-27 04:19:06

  该篇博客主要介绍的如下两方面的问题：

1. 数据库名，表名，表别名 的大小写敏感性如何控制

2. 字段值内容的大小写敏感性如何控制


一：数据库名，表名，表别名 的大小写敏感性如何控制

  我们通过设置lower_case_table_names参数来控制数据库名，表名，表别名的大小写敏感性，

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)
##lower_case_file_system是一个只读参数，无法被修改，这个参数是用来告诉你在当前的系统平台下，是否对文件名大小写敏感。

lower_case_table_names该参数可以设置如下值：

Value Meaning
0 Table and database names are stored on disk using the lettercase specified in the CREATE
TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2 Table and database names are stored on disk using the lettercase specified in the CREATE
TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

0：存储时按照指定的表名，比较时对大小写敏感。

1：存储时按照小写（不管表是否有大写），比较时对大小写不敏感。

2：存储时按给定的表名，比较时用小写。

 

NOTE:

1)对于windows和Mac OX S 这些文件系统对大小写不敏感的系统该参数不能设置为0，并且在5.6.27以后你如果在大小写不敏感的操作系统上设置该参数为0启动实例时会报错，并且退出。

2)linux系统默认值为0，windows默认设置为1，Mac OXS设置为2， lower_case_table_names=2只能用于大小写不敏感的系统，在linux系统上设置lower_case_table_names=2，启动后errorlog中会有如下提示：

[Warning] lower_case_table_names was set to 2,even though your the file system '/home/test/var/lib/data1/' is casesensitive.  Now settinglower_case_table_names to 0 to avoid future problems.

3) 为了避免不同平台之间迁移数据库时遇到大小写问题，建议所有平台上都设置lower_case_table_names=1

4)If you plan to set the lower_case_table_namessystem variable to 1 on Unix, you must first convert your old database andtable names to lowercase before stopping mysqld and restarting it with the newvariable setting. To do this for an individual table, use RENAME TABLE:

RENAME TABLE T1 TO t1;

在unix系统上，如果你准备把lower_case_table_names从0改成1，那么你的先把有大写的表名都改成小写(RENAME TABLE T1 TO t1;)，否则你访问之前有大写字母的表会报如下错误：

desc sysNumLimit_0408;

ERROR 1146 (42S02): Table 'kopc.sysnumlimit_0408'doesn't exist

##当然也可以在修改参数之前把库做导出，然后删除所有数据库(drop database),然后修改参数，最后重新导入


二：字段值内容的大小写敏感性如何控制

   字段值的大小写敏感性由mysql的校对规则(collate)来控制。提到校对规则，就不得不说字符集。字符集是一套符号和编码，collate指定了字符集中数据的排序和比对规则则，比如定义'A'<'B'这样的关系的规则。每种字符集都会对应一定数据库的collate，一般而言，collate是以其相关的字符集名开始，通常包括一个语言名，并且以_ci（ci是case insensitive的缩写）、_cs（cs是case sensitive的缩写）或_bin（二元）结束 。比如 latin1字符集的collate有latin1_general_ci(这个是utf8字符集默认的校对规则)，latin1_general_cs，latin1_bin表示二进制比较(区分大小写)。我们可以通过show collation;命令来查看字符集支持的校对规则有哪些:


     校对规则通过关键字collate指定，我们可以在数据库，表，列几个层面指定字符集和校对规，越往后优字符集和校对规则生效的优先级越高，如果没有为列或者表指定字符集和校对规则，那么它会继承上级的字符集和校对规则。
比如创建数据库d1，指定字符集为utf8，校对规则为utf8_bin


下面通过实验给大家演示collate对Mysql 字段值大小写敏感性的影响:

1. 创建字符集为utf8，校对规则为utf8_general_ci的数据库test
CREATE DATABASE test DEFAULT CHARACTER SET utf8;
#utf8字符集默认校对规则为utf8_general_ci

2. 在test数据库下创建测试表
create table test_case(name varchar(20));
#因为没有为表和列指定字符集，校对规则，它们会继承所在数据库的字符集和校对规则

create table test_case1(name varchar(20) character set utf8 collate utf8_bin);
#为列指定了字符集和校对规则(列的优先级高于表，高于数据库)
mysql> show create table test_case;
+-----------+---------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                      |
+-----------+---------------------------------------------------------------------------------------------------+
| test_case | CREATE TABLE `test_case` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table test_case1;
+------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                           |
+------------+----------------------------------------------------------------------------------------------------------------------------------------+
| test_case1 | CREATE TABLE `test_case1` (
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. 插入测试数据
insert into test_case select 'abc';
insert into test_case select 'ABC';

insert into test_case1 select 'abc';
insert into test_case1 select 'ABC';

mysql> select * from test_case;
+------+
| name |
+------+
| abc  |
| ABC  |
+------+
2 rows in set (0.00 sec)

mysql> select * from test_case1;
+------+
| name |
+------+
| abc  |
| ABC  |
+------+
2 rows in set (0.00 sec)

4. 查询测试
mysql> select * from test_case where name like 'ab%';
+------+
| name |
+------+
| abc  |
| ABC  |
+------+
2 rows in set (0.00 sec)

mysql> select * from test_case1 where name like 'ab%';
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.00 sec)
##因为test_case使用的utf8_general_ci校对规则，所以大小写不敏感，test_case1的name列使用的是utf8_bin，所以大小写敏感。

5. 添加主键测试
mysql> alter table test_case add primary key (name);
ERROR 1062 (23000): Duplicate entry 'abc' for key 'PRIMARY'

mysql> alter table test_case1 add primary key (name);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
##test_case因为大小写不敏感，所以认为表中的abc和ABC两条记录是一样的，所以添加主键时报错。


NOTE:
1)通过上面的测试，我们可以看到如果想某个字段值区分大小写，我们可以在建表时为该字段指定区别大小写的collate(_ci或者_bin)，对于已存在的表可以通过如下语句修改列的collate：
alter table test_case modify column name varchar(20) character set utf8 collate utf8_bin;
2)如果不想修改字段的collate，我们可以在查询的时候指定collate或者通过binary 进行字段转换
select * from test_case where name like 'ab%' collate utf8_bin;
select * from test_case where binary name like 'ab%';
select * from test_case where name = binary 'abc';

例子如下：

mysql> select * from test_case where name like 'ab%';
+------+
| name |
+------+
| abc  |
| ABC  |
+------+
2 rows in set (0.00 sec)
##没有指定collate，默认使用name列的collate(utf8_general_ci)，所以查询不区分大小写

mysql> select * from test_case where name like 'ab%' collate utf8_bin;
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.00 sec)
##查询时指定collate为utf8_bin,所以查询区分大小写

mysql> select * from test_case where binary name like 'ab%';
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.00 sec)
##通过使用binary关键字将字符串转换成二进制进行比较，这样也是能够区分大小写的。(实验发现对name列使用binary依然可以使用name列上的索引)

mysql> select * from test_case1 where name like 'ab%';
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.00 sec)
##没有指定collate，查询默认使用name列的collate(utf8_bin)，所以查询区分大小写

mysql> select * from test_case1 where name like 'ab%' collate utf8_general_ci;
+------+
| name |
+------+
| ABC  |
| abc  |
+------+
##查询指定collate为utf8_general_ci，所以查询区分大小写

mysql> select * from test_case where name = 'abc';
+------+
| name |
+------+
| abc  |
| ABC  |
+------+
2 rows in set (0.00 sec)
##没有指定区别大小写的collate也没有指定binary，所以查询是不区分大小写的

mysql> select * from test_case where name = binary 'abc';
+------+
| name |
+------+
| abc  |
+------+
1 row in set (0.00 sec)
##使用binary 将'abc'字符串转换成二进制进行比较


标签：

相关文章