如何将 Linux 服务器中的 MySQL 表名更改为不区分大小写? [英] How to change MySQL table names in Linux server to be case insensitive?

问题描述

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

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.

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

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).

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

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.

您可能认为这很容易解决，但到目前为止我已经失去希望.我希望有人能得到一个目前我可能无法回答的答案.

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.

推荐答案

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

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

9.2.2.标识符区分大小写

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

幸运的是，下一句话可以帮助您:

Fortunately, the next sentence could help you:

lower_case_table_names 系统变量还会影响服务器处理标识符区分大小写的方式，如本节后面所述.

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

lower_case_table_names 简介:

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

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".

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

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.

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

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

这篇关于如何将 Linux 服务器中的 MySQL 表名更改为不区分大小写?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！