mysql 大小写问题

一、表名大小写问题

表名大小写由参数 lower_case_table_names 控制，参数类型为 布尔值，0表示不忽略大小写（即区分大小写）， 1为忽略大小写（即不区分大小写），默认为0 即区分大小写。

如下测试：
当lower_case_table_names 设置为0时，person表和PERSON表结果不相同。
[root@mysql.sock][xhy]>>show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
[root@mysql.sock][xhy]>>select * from person where last_name='lee';
+----+------------+-----------+------+------------+
| id | first_name | last_name | sex  | birth      |
+----+------------+-----------+------+------------+
|  4 | Hale       | Lee       | M    | 1988-09-10 |
| 30 | Hale       | Lee       | M    | 1988-09-10 |
| 37 | Hale       | Lee       | M    | 1988-09-10 |
| 41 | Hale       | Lee       | M    | 1988-09-10 |
+----+------------+-----------+------+------------+
4 rows in set (0.00 sec)
--如下将表名设置为大写，则提示该表不存在
[root@mysql.sock][xhy]>>select * from PERSON;
ERROR 1146 (42S02): Table 'xhy.PERSON' doesn't exist

--将 lower_case_table_names设置为1 ，注意lower_case_table_names参数为只读参数，需要在配置文件修改配置，重启生效。
[root@mysql.sock][(none)]>>show variables like 'lower_case%'; 
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

[root@mysql.sock][(none)]>>use xhy;
Database changed
[root@mysql.sock][xhy]>>select * from person;
+----+------------+-----------+------+------------+
| id | first_name | last_name | sex  | birth      |
+----+------------+-----------+------+------------+
|  1 | huiyuan    | xie       | M    | 1990-10-10 |
|  2 | Jobs       | stevel    | M    | 1956-10-10 |
|  3 | Jacle      | ma        | M    | 1969-10-10 |
|  4 | Hale       | Lee       | M    | 1988-09-10 |
| 27 | huiyuan    | xie       | M    | 1990-10-10 |
| 28 | Jobs       | stevel    | M    | 1956-10-10 |
| 29 | Jacle      | ma        | M    | 1969-10-10 |
| 30 | Hale       | Lee       | M    | 1988-09-10 |
| 34 | huiyuan    | xie       | M    | 1990-10-10 |
| 35 | Jobs       | stevel    | M    | 1956-10-10 |
| 36 | Jacle      | ma        | M    | 1969-10-10 |
| 37 | Hale       | Lee       | M    | 1988-09-10 |
| 38 | huiyuan    | xie       | M    | 1990-10-10 |
| 39 | Jobs       | stevel    | M    | 1956-10-10 |
| 40 | Jacle      | ma        | M    | 1969-10-10 |
| 41 | Hale       | Lee       | M    | 1988-09-10 |
+----+------------+-----------+------+------------+
16 rows in set (0.03 sec)

[root@mysql.sock][xhy]>>select * from PERSON;
+----+------------+-----------+------+------------+
| id | first_name | last_name | sex  | birth      |
+----+------------+-----------+------+------------+
|  1 | huiyuan    | xie       | M    | 1990-10-10 |
|  2 | Jobs       | stevel    | M    | 1956-10-10 |
|  3 | Jacle      | ma        | M    | 1969-10-10 |
|  4 | Hale       | Lee       | M    | 1988-09-10 |
| 27 | huiyuan    | xie       | M    | 1990-10-10 |
| 28 | Jobs       | stevel    | M    | 1956-10-10 |
| 29 | Jacle      | ma        | M    | 1969-10-10 |
| 30 | Hale       | Lee       | M    | 1988-09-10 |
| 34 | huiyuan    | xie       | M    | 1990-10-10 |
| 35 | Jobs       | stevel    | M    | 1956-10-10 |
| 36 | Jacle      | ma        | M    | 1969-10-10 |
| 37 | Hale       | Lee       | M    | 1988-09-10 |
| 38 | huiyuan    | xie       | M    | 1990-10-10 |
| 39 | Jobs       | stevel    | M    | 1956-10-10 |
| 40 | Jacle      | ma        | M    | 1969-10-10 |
| 41 | Hale       | Lee       | M    | 1988-09-10 |
+----+------------+-----------+------+------------+
16 rows in set (0.00 sec)
-- 当区分大小写时，后台生成的表空间文件也是区分大小写的
[root@mysql.sock][xhy]>>create table AAA (id int auto_increment, name varchar(20), primary key(id));   
Query OK, 0 rows affected (0.11 sec)

[root@mysql.sock][xhy]>>create table aaa (id int auto_increment, name varchar(20), primary key(id));   
Query OK, 0 rows affected (0.03 sec)

[root@mysql.sock][xhy]>>show variables like 'lower_case%'; 
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)
[root@ora11g1 xhy]# ll
total 960
-rw-r----- 1 mysql mysql   8586 Aug 20 17:20 aaa.frm
-rw-r----- 1 mysql mysql   8586 Aug 20 17:20 AAA.frm
-rw-r----- 1 mysql mysql  98304 Aug 20 17:20 aaa.ibd
-rw-r----- 1 mysql mysql  98304 Aug 20 17:20 AAA.ibd

不区分大小写时，创建的表空间文件都以小写命名
[root@mysql.sock][(none)]>>show variables like 'lower_case%'; 
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
[root@mysql.sock][xhy]>>create table TTT (id int auto_increment, name varchar(20), primary key(id)); 
Query OK, 0 rows affected (0.13 sec)
[root@ora11g1 xhy]# ll
。。。
-rw-r----- 1 mysql mysql   8586 Aug 20 17:15 ttt.frm
-rw-r----- 1 mysql mysql  98304 Aug 20 17:16 ttt.ibd

二、字段名大小写问题

字段名不区分大小写，即不管列名是大写还是小写都能正确查到结果，但显示出来的字段名（select list的显示名）同查询时输入的字段名。
select id,name from test; select Id,Name from test; 都能正常查询到结果；
select id,name from test; 查询显示的列名是id,name
select Id,Name from test; 查询显示的列名则是Id,Name

如下测试：
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.01 sec)
如下建的表test(id,name)和Test(ID,Name)在查看表结构时是区分了大小写的，
mysql> create table test (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create table Test (Id int,Name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc Test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Id    | int         | YES  |     | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
当查询时则不区分大小写
select id,name from test; select Id,Name from test; 都能正常查询；

mysql> insert into test select 1,'test';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into Test select 1,'Test';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select id,name from test;
+------+------+
| id   | name |
+------+------+
|    1 | test |
+------+------+
1 row in set (0.00 sec)

mysql> select id,name from Test;
+------+------+
| id   | name |
+------+------+
|    1 | Test |
+------+------+
1 row in set (0.00 sec)

mysql> select Id,Name from test;
+------+------+
| Id   | Name |
+------+------+
|    1 | test |
+------+------+
1 row in set (0.00 sec)

mysql> select Id,Name from Test;
+------+------+
| Id   | Name |
+------+------+
|    1 | Test |
+------+------+
1 row in set (0.00 sec)

带双引号的查询是查常量
mysql> select "Id","Name" from Test;
+------+------+
| Id   | Name |
+------+------+
| Id   | Name |
+------+------+
1 row in set (0.00 sec)

三、字符值大小写问题

字符值大小写是由字符排序规则决定的。当排序规则为二进制时是区分大小写的，否则是不区分大小写的。

如下当排序规则为utf8mb4_unicode_ci 时，查询person 表 where last_name='lee'、where last_name='Lee' 和where last_name='LEe' 查询结果都是一样的。
[root@mysql.sock][xhy]>>show table status like 'person'\G;
*************************** 1. row ***************************
           Name: person
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 11
 Avg_row_length: 1489
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 49
    Create_time: 2020-08-20 16:55:17
    Update_time: 2020-08-20 16:55:17
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

[root@mysql.sock][xhy]>>select * from person where last_name='lee';
+----+------------+-----------+------+------------+
| id | first_name | last_name | sex  | birth      |
+----+------------+-----------+------+------------+
|  4 | Hale       | Lee       | M    | 1988-09-10 |
| 30 | Hale       | Lee       | M    | 1988-09-10 |
| 37 | Hale       | Lee       | M    | 1988-09-10 |
| 41 | Hale       | Lee       | M    | 1988-09-10 |
+----+------------+-----------+------+------------+
4 rows in set (0.00 sec)

[root@mysql.sock][xhy]>>select * from person where last_name='Lee';
+----+------------+-----------+------+------------+
| id | first_name | last_name | sex  | birth      |
+----+------------+-----------+------+------------+
|  4 | Hale       | Lee       | M    | 1988-09-10 |
| 30 | Hale       | Lee       | M    | 1988-09-10 |
| 37 | Hale       | Lee       | M    | 1988-09-10 |
| 41 | Hale       | Lee       | M    | 1988-09-10 |
+----+------------+-----------+------+------------+
4 rows in set (0.00 sec)

[root@mysql.sock][xhy]>>select * from person where last_name='LEe';
+----+------------+-----------+------+------------+
| id | first_name | last_name | sex  | birth      |
+----+------------+-----------+------+------------+
|  4 | Hale       | Lee       | M    | 1988-09-10 |
| 30 | Hale       | Lee       | M    | 1988-09-10 |
| 37 | Hale       | Lee       | M    | 1988-09-10 |
| 41 | Hale       | Lee       | M    | 1988-09-10 |
+----+------------+-----------+------+------------+
4 rows in set (0.00 sec)
-- 将排序规则设置为二进制，则如下匹配区分大小写
alter table person collate utf8_bin;
[root@mysql.sock][xhy]>>ALTER TABLE person CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 16 rows affected (0.12 sec)
Records: 16  Duplicates: 0  Warnings: 0
[root@mysql.sock][xhy]>  show create table person\G;
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `last_name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `sex` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `phone` varchar(11) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

[root@mysql.sock][xhy]>>show table status like 'person'\G;
*************************** 1. row ***************************
           Name: person
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 16
 Avg_row_length: 1024
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 49
    Create_time: 2020-08-20 16:57:05
    Update_time: 2020-08-20 16:57:05
     Check_time: NULL
      Collation: utf8mb4_bin
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

[root@mysql.sock][xhy]>>select * from person where last_name='LEe';
Empty set (0.00 sec)

[root@mysql.sock][xhy]>>select * from person where last_name='Lee';
+----+------------+-----------+------+------------+
| id | first_name | last_name | sex  | birth      |
+----+------------+-----------+------+------------+
|  4 | Hale       | Lee       | M    | 1988-09-10 |
| 30 | Hale       | Lee       | M    | 1988-09-10 |
| 37 | Hale       | Lee       | M    | 1988-09-10 |
| 41 | Hale       | Lee       | M    | 1988-09-10 |
+----+------------+-----------+------+------------+
4 rows in set (0.00 sec)
[root@mysql.sock][xhy]>>select * from person where last_name='lee';
Empty set (0.00 sec)