MySQL table name case selection

# View parameter settingsmysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+

# Create database mysql> create database TestDb;
Query OK, 1 row affected (0.01 sec)

mysql> create database testdb;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TestDb |
|mysql |
| performance_schema |
|sys|
|testdb|
+--------------------+

mysql> use testdb;
Database changed
mysql> use TestDb;
Database changed
mysql> use TESTDB;
ERROR 1049 (42000): Unknown database 'TESTDB'

# Create tablemysql> CREATE TABLE if not exists `test_tb` (
    -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
    -> `stu_id` int(11) NOT NULL COMMENT 'student number',
    -> `stu_name` varchar(20) DEFAULT NULL COMMENT 'Student name',
    -> PRIMARY KEY (`increment_id`),
    -> UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test_tb';
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE if not exists `Student_Info` (
    -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
    -> `Stu_id` int(11) NOT NULL COMMENT 'Student ID',
    -> `Stu_name` varchar(20) DEFAULT NULL COMMENT 'Student name',
    -> PRIMARY KEY (`increment_id`),
    -> UNIQUE KEY `uk_stu_id` (`Stu_id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Student_Info';
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| Student_Info |
| test_tb |
+------------------+

# Query tablemysql> select Stu_id,Stu_name from test_tb limit 1;
+--------+----------+
| Stu_id | Stu_name |
+--------+----------+
| 1001 | from1 |
+--------+----------+
1 row in set (0.00 sec)
mysql> select stu_id,stu_name from test_tb limit 1;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1001 | from1 |
+--------+----------+

mysql> select stu_id,stu_name from Test_tb;
ERROR 1146 (42S02): Table 'testdb.Test_tb' doesn't exist
mysql> select Stu_id,Stu_name from test_tb as A where A.Stu_id = 1001;
 +--------+----------+
| Stu_id | Stu_name |
+--------+----------+
| 1001 | from1 |
+--------+----------+
1 row in set (0.00 sec)
mysql> select Stu_id,Stu_name from test_tb as A where a.Stu_id = 1001;
ERROR 1054 (42S22): Unknown column 'a.Stu_id' in 'where clause'

# View the directories and files on the disk [root@localhost ~]#:/var/lib/mysql# ls -lh
total 616M
drwxr-x--- 2 mysql mysql 20 Jun 3 14:25 TestDb
...
drwxr-x--- 2 mysql mysql 144 Jun 3 14:40 testdb
[root@localhost ~]#:/var/lib/mysql# cd testdb/
[root@localhost ~]#:/var/lib/mysql/testdb# ls -lh
total 376K
-rw-r----- 1 mysql mysql 8.6K Jun 3 14:33 Student_Info.frm
-rw-r----- 1 mysql mysql 112K Jun 3 14:33 Student_Info.ibd
-rw-r----- 1 mysql mysql 8.6K Jun 3 14:40 TEST_TB.frm
-rw-r----- 1 mysql mysql 112K Jun 3 14:40 TEST_TB.ibd
-rw-r----- 1 mysql mysql 67 Jun 3 14:25 db.opt
-rw-r----- 1 mysql mysql 8.6K Jun 3 14:30 test_tb.frm
-rw-r----- 1 mysql mysql 112K Jun 3 14:30 test_tb.ibd

# Delete the above test library and change lower_case_table_names to 1 and restart the database mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+

# Create database mysql> create database TestDb;
Query OK, 1 row affected (0.02 sec)

mysql> create database testdb;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
|testdb|
+--------------------+
7 rows in set (0.00 sec)

mysql> use testdb;
Database changed
mysql> use TESTDB;
Database changed

# Create tablemysql> CREATE TABLE if not exists `test_tb` (
    -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
    -> `stu_id` int(11) NOT NULL COMMENT 'student number',
    -> `stu_name` varchar(20) DEFAULT NULL COMMENT 'Student name',
    -> PRIMARY KEY (`increment_id`),
    -> UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test_tb';
Query OK, 0 rows affected (0.05 sec)
mysql> create table TEST_TB (id int);
ERROR 1050 (42S01): Table 'test_tb' already exists

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test_tb |
+------------------+

# Query tablemysql> select stu_id,stu_name from test_tb limit 1;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1001 | from1 |
+--------+----------+
1 row in set (0.00 sec)

mysql> select stu_id,stu_name from Test_Tb limit 1;
       +--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1001 | from1 |
+--------+----------+
1 row in set (0.00 sec)

mysql> select stu_id,stu_name from test_tb as A where a.stu_id = 1002;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1002 | dfsfd |
+--------+----------+
1 row in set (0.00 sec)

# Rename the uppercase table to the lowercase table
 rename table TEST to test;

# If an uppercase database exists, you need to create a lowercase database first and then transfer the table in the uppercase database to the lowercase database. rename table TESTDB.test_tb to testdb.test_tb;

# Share two SQL statements that may be used
# Query the table with uppercase letters in the example SELECT
 TABLE_SCHEMA,
 TABLE_NAME
FROM
 information_schema.`TABLES`
 WHERE
 TABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )
  AND table_type = 'BASE TABLE'
  AND TABLE_NAME REGEXP BINARY '[AZ]'


   # Splice SQL to transfer the table in the uppercase database to the lowercase database SELECT
 CONCAT( 'rename table TESTDB.', TABLE_NAME, ' to testdb.', TABLE_NAME, ';' )
 FROM
 information_schema.TABLES
 WHERE
 TABLE_SCHEMA = 'TESTDB';