筆記：mysql table name/db name 大小寫問題

lower_case_table_names = 0
Unix 相關系統 預設
table / database names 會區分大小寫檔名(case sensitive)

在這模式
建立 table 時 若名稱夾雜大小寫
則 table 實體檔名也會是大小寫：

Linux的測試

mysql>  CREATE TABLE `tab_PPP22` (
    ->   `fid` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `filename` char(30) CHARACTER SET utf8mb4 DEFAULT NULL,
    ->   PRIMARY KEY (`fid`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1087 ;
Query OK, 0 rows affected (0.05 sec)

$ ls -l /usr/local/mysql/data/mydb/tab_PPP22*
-rw-rw---- 1 mysql mysql  8596 Dec 18 09:00 /usr/local/mysql/data/icode/tab_PPP22.frm
-rw-rw---- 1 mysql mysql 98304 Dec 18 09:00 /usr/local/mysql/data/icode/tab_PPP22.ibd

寫 SQL 命令時 也要注意 table name 大小寫
例如：

mysql> select * FROM tab_PPP22;
Empty set (0.00 sec)

mysql> select * FROM tab_ppp22;
ERROR 1146 (42S02): Table 'icode.tab_ppp22' doesn't exist

lower_case_table_names = 1
Windows 預設
不區分大小寫(case insensitive)

建立 table / database names 時，mysql 都會自動轉為小寫檔名 
查詢時  MySQL會將 table / db name 轉為小寫
下 SQL 命令時，table name 用大小寫都可以

例如：

Linux的測試

建立含有大小寫名稱的 table：
 CREATE TABLE `tab_PPP` (  
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `filename` char(30) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1087 ;


$ ls -l /usr/local/mysql/data/mydb/tab_ppp*
-rw-rw---- 1 mysql mysql  8596 Dec 18 08:54 /usr/local/mysql/data/mydb/tab_ppp.frm  檔名都變為小寫
-rw-rw---- 1 mysql mysql 98304 Dec 18 08:54 /usr/local/mysql/data/mydb/tab_ppp.ibd


mysql> describe tab_ppp;
+----------+--------------------+------+-----+---------+----------------+
| Field    | Type               | Null | Key | Default | Extra          |
+----------+--------------------+------+-----+---------+----------------+
| id       | int(11) unsigned   | NO   | PRI | NULL    | auto_increment |
| filename | char(30)           | YES  |     | NULL    |                |
+----------+--------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tab_ppp;
Empty set (0.00 sec)

mysql> SELECT * FROM tab_PPP;
Empty set (0.00 sec)

特別注意，
若資料庫內之前建立的 table 已經含有大小寫檔名
當 mysqld 切換到  lower_case_table_names = 1  時
會造成讀不到資料的問題
例如

這是在  lower_case_table_names = 0  建立的 table 
$ ls -l /usr/local/mysql/data/icode/*PP*
-rw-rw---- 1 mysql mysql  8596 Dec 18 09:00 /usr/local/mysql/data/mydb/tab_PPP22.frm
-rw-rw---- 1 mysql mysql 98304 Dec 18 09:00 /usr/local/mysql/data/mydb/tab_PPP22.ibd

這是在  lower_case_table_names = 1 下的命令： 
mysql> SELECT * FROM tab_PPP22;
ERROR 1146 (42S02): Table 'icode.tab_ppp22' doesn't exist

mysql> SELECT * FROM tab_ppp22;
ERROR 1146 (42S02): Table 'icode.tab_ppp22' doesn't exist

lower_case_table_names = 2
Mac OS X 預設
table / database names 會區分大小寫檔名 （Mac OS X 也是 Unix 系列，對大小寫敏感)
table / database names 一律轉為小寫檔名

查詢時  MySQL 會將 table / db name 轉為小寫
 

MacOS X 的測試

mysql> CREATE TABLE `tab_MacOS` (
    -> `fid` int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> `filename` char(30) CHARACTER SET utf8mb4 DEFAULT NULL,
    -> PRIMARY KEY (`fid`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1087 ;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from tab_MacOS;
Empty set (0.00 sec)

mysql> select * from tab_macos;
Empty set (0.00 sec)

# ls -l  /usr/local/mysql/data/mydb/tab_*
-rw-r-----  1 _mysql  _mysql   8596 12 18 09:33 /usr/local/mysql/data/mydb/tab_MacOS.frm  這檔沒轉為小寫
-rw-r-----  1 _mysql  _mysql  98304 12 18 09:33 /usr/local/mysql/data/mydb/tab_macos.ibd

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.01 sec)

** MacOS X 中
APFS 區分大小寫檔名
HFS+ 不區分大小寫檔名




另外還有個變數
lower_case_file_system
這個是【唯讀/無法修改】的變數
告知目前的作業系統是否大小寫敏感
OFF = 大小寫敏感
ON  = 不區分大小寫

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

參考
Identifier Case Sensitivity
https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html