官方释义

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 --2lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.

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.

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. 文件系统的大写敏感

Windows:大小写不敏感

linux/unix:大小写敏感

macOS默认文件系统(HFS+):大小写不敏感

macOS (UFS volumes): 大小写敏感

下文都以linux系统论述

1. lower_case_table_names变量影响元素范围:

数据库名、表名、触发器名(trigger)

2. mysql中大小写始终不敏感的元素

列名(列别名)、索引名、存储过程、定时器名

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files.

3. 为避免大小写问题,对应用的建议

通常情况,用户都已小写来创建和使用库、表、触发器。.

4. 修改lower_case_table_names可能引发的问题

(1)修改方法

修改配置文件参数lower-case-table-names=1, 重启mysql进程

(2)lower-case-table-names=0 改为 lower-case-table-names=1

大写: 使用大、小写都无法访问

小写: 使用大、小写都可以访问

(3)lower-case-table-names=1 改为 lower-case-table-names=0

大写: 创建会自动转为小写

小写: 只能会用小写访问