如何将 Linux 服务器中的 MySQL 表名更改为不区分大小写？

How to change MySQL table names in Linux server to be case insensitive?

提问by rocklandcitizen

I'm working on an old website that used to be hosted on an Apple server. When it was migrated into a new Linux server it stopped working. I'm pretty sure it's because all the MySQL queries used in the php scripts have different case combinations for the table names (I don't know why the original developers didn't follow any conventions when they created the table names or the php scripts) and it didn't matter because both Mac and Windows MySQL servers are case insensitive by default when it comes to this. However, Linux is not.

我正在处理一个以前托管在 Apple 服务器上的旧网站。当它迁移到新的 Linux 服务器时，它停止工作。我很确定这是因为在 php 脚本中使用的所有 MySQL 查询对表名都有不同的大小写组合（我不知道为什么原始开发人员在创建表名或 php 脚本时没有遵循任何约定) 这并不重要，因为 Mac 和 Windows MySQL 服务器在默认情况下都不区分大小写。但是，Linux 不是。

Is there a way to change the Linux default on MySQL so it becomes case insensitive and it works like Mac or Windows? I've been looking but haven't found any answers that don't involve changing either the scripts or the table names or both. The website must have been generated using some CMS so there are dozens upon dozens of pages and include files with multiple queries in each and hundreds of tables. I began trying to implement this type of solution in the smartest way I could think of but if I touch the table names then other currently working pages stop working (I'm trying to avoid breaking the site further).

有没有办法更改 MySQL 上的 Linux 默认值，使其不区分大小写，并且像 Mac 或 Windows 一样工作？我一直在寻找，但没有找到任何不涉及更改脚本或表名或两者的答案。该网站必须是使用某些 CMS 生成的，因此有数十个页面，并且在每个和数百个表中包含具有多个查询的文件。我开始尝试以我能想到的最聪明的方式实现这种类型的解决方案，但是如果我触摸表名，则其他当前工作的页面将停止工作（我试图避免进一步破坏站点）。

There was a system variable (lower_case_table_names) in the MySQL Server console in Webmin in the Linux server that I read could be changed from 0 to 1 to tackle this issue, but Webmin won't let me change it because it's a "read-only" variable.

在 Linux 服务器的 Webmin 中的 MySQL 服务器控制台中有一个系统变量 (lower_case_table_names)，我读取它可以从 0 更改为 1 以解决此问题，但 Webmin 不会让我更改它，因为它是“只读” “ 多变的。

You'd think this would be an easily problem to solve, but so far I'm losing hope. I'm hoping someone's got an answer that maybe eludes me at the moment.

你会认为这将是一个很容易解决的问题，但到目前为止我正在失去希望。我希望有人能得到一个目前可能无法回答的答案。

回答by Keith Randall

You can't change the value of lower_case_table_nameswhile mysql is running - it needs to be set on startup. You will need to edit my.cnf(maybe in /etc, maybe somewhere else, not sure). Then restart mysql and you should be good.

您不能lower_case_table_names在 mysql 运行时更改 的值- 它需要在启动时设置。您将需要编辑my.cnf（可能在/etc，可能在其他地方，不确定）。然后重启mysql，你应该就好了。

回答by thkala

There is a MySQL server variable with the same name. You probably need to set that specific variable on system start-up, as described in this help page. You will have to find the location of the MySQL options file (mine is at /etc/my.cnf) for your DB server instance and edit/add this option to the [mysqld]section.

有一个同名的 MySQL 服务器变量。您可能需要在系统启动时设置该特定变量，如本帮助页面中所述。您必须/etc/my.cnf为您的数据库服务器实例找到 MySQL 选项文件的位置（我的位于），然后将此选项编辑/添加到该[mysqld]部分。

Don't forget to restart the MySQL daemon afterwards...

之后不要忘记重新启动 MySQL 守护进程...

回答by Kevin

MySQL's case sensitivity is by default handled by the file system, which is why you found this difference:

MySQL 的区分大小写默认由文件系统处理，这就是您发现此差异的原因：

9.2.2. Identifier Case Sensitivity

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). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. See Section 1.8.4, “MySQL Extensions to Standard SQL”.

9.2.2. 标识符区分大小写

在 MySQL 中，数据库对应于数据目录中的目录。数据库中的每个表对应于数据库目录中的至少一个文件（可能更多，取决于存储引擎）。因此，底层操作系统的区分大小写在数据库和表名称的区分大小写中起作用。这意味着数据库和表名在 Windows 中不区分大小写，而在大多数 Unix 版本中区分大小写。一个值得注意的例外是 Mac OS X，它基于 Unix，但使用不区分大小写的默认文件系统类型 (HFS+)。然而，Mac OS X 也支持 UFS 卷，就像在任何 Unix 上一样区分大小写。请参阅第 1.8.4 节，“标准 SQL 的 MySQL 扩展”。

Fortunately, the next sentence could help you:

幸运的是，下一句话可以帮助你：

The lower_case_table_namessystem variable also affects how the server handles identifier case sensitivity, as described later in this section.

所述的lower_case_table_names系统变量也影响服务器把手如何识别符的情况下的灵敏度，如在本节后面说明。

The lower_case_table_namesblurb:

该lower_case_table_names导语：

If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 9.2.2, “Identifier Case Sensitivity”.

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 Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.

如果设置为 0，则表名按指定存储，并且比较区分大小写。如果设置为 1，表名以小写形式存储在磁盘上，并且比较不区分大小写。如果设置为 2，则表名按给定存储，但以小写形式进行比较。此选项也适用于数据库名称和表别名。有关其他信息，请参阅第 9.2.2 节，“标识符区分大小写”。

如果您在具有不区分大小写的文件名的系统（例如 Windows 或 Mac OS X）上运行 MySQL，则不应将此变量设置为 0。如果在这样的系统上将此变量设置为 0 并使用不同的字母访问 MyISAM 表名，则可能会导致索引损坏。在 Windows 上，默认值为 1。在 Mac OS X 上，默认值为 2。

So it appears you should set lower_case_table_namesto 1in the MySQL config file.

所以看起来你应该在 MySQL 配置文件中设置lower_case_table_names为1。

回答by Sébastien

Edit the mysql configuration file

编辑mysql配置文件

nano /etc/mysql/my.cnf

or any other my.cnffile which is used to configure your mysql.

或my.cnf用于配置您的mysql.

Under

在下面

[mysqld]

add the line

添加行

lower_case_table_names=1

Run

跑

sudo service mysqld restart

You might want to reimport your windows database into your linux database. Preferably from scratch, with add table and insert statements.

您可能希望将 Windows 数据库重新导入到 linux 数据库中。最好从头开始，使用 add table 和 insert 语句。

It's good!

很好！

回答by Musa

The lower_case_table_names parameter should be set as part of a custom DB parameter group before creating a DB instance. You should avoid changing the lower_case_table_names parameter for existing database instances because doing so could cause inconsistencies with point-in-time recovery backups and Read Replica DB instances.

在创建数据库实例之前，应将lower_case_table_names 参数设置为自定义数据库参数组的一部分。您应该避免更改现有数据库实例的lower_case_table_names 参数，因为这样做可能会导致与时间点恢复备份和只读副本数据库实例不一致。