如何在MySql中强制区分大小写的表和列名？

Question

问题是我们正在使用Windows机器进行工作，一旦完成，我们将代码部署到Unix机器上。代码在Windows上运行正常，但在Unix上更改表名后，我们会遇到“不存在此表”的错误。在正确的情况下更改表名后，在Unix中它也可以正常工作。

实际上，在Windows中，默认情况下没有区分大小写的表名，但在Unix中有（读取MySQL表实际上是文件，在Unix中我们有区分大小写的文件名，但在Windows中没有）。解决方法可能是重新创建所有表，并且让表名小写。是的，我们也可以这样做，很好。

但是，我们是否可以强制MySql（Windows机器）对表名区分大小写？如果可以，请告诉我如何操作。

- Rakesh Juyal

---

这是一个有趣的问题。就像你在Windows中的SQL工作，而在基于Unix的系统中却无法工作一样，会带来麻烦。据我所知，目前还没有解决办法。 - Nishant

@greggles - Mac OS有不同的考虑。 - Rick James

Answer 1

该设置称为lower_case_table_names。如果将其设置为0，则比较区分大小写。

然而，

如果您在具有不区分大小写文件名的系统（如Windows或Mac OS X）上运行MySQL，则不应将此变量设置为0。如果您在不区分大小写的文件系统上使用--lower-case-table-names=0强制将此变量设置为0，并使用不同字母大小写访问MyISAM表名，则可能导致索引损坏。

在所有系统上（包括Linux）使所有表名小写，即将其设置为1，似乎是更好的选择：

表名以小写形式存储在磁盘上，并且名称比较不区分大小写。 MySQL在存储和查找时将所有表名转换为小写。此行为也适用于数据库名称和表别名。

- Pekka

---

问题是我可以在Windows机器上更改设置，但不能在Unix系统中更改。通过这个解决方案，如果我能够访问Unix中的my.ini文件，我就可以将此设置更改为1，这样问题就得到了解决。但我希望反过来。这意味着，与其使Unix中的表不区分大小写，我宁愿在Windows中使用区分大小写的表。 - Rakesh Juyal

@Rakesh 看看选项 0，但不建议使用。我不明白为什么这个问题会首先出现 - 你不能确保你的代码使用正确的名称吗？在部署到 Linux 时表名是否被更改了？ - Pekka

Answer 2

在Unix系统中，lower_case_table_names的默认值为0。在Windows系统中，默认值为1。在Mac OS X系统中，在MySQL 4.0.18之前默认为1，在4.0.18及以后版本中默认为2。

要解决这个问题，您可以在my.ini文件中查找设置：lower_case_table_names，该文件位于C:\Program Files\MySQL\MySQL Server 4.1或其附近，具体取决于您正在运行的版本。如果您没有找到该设置，可以像我一样将其添加到my.ini文件的末尾，例如：

lower_case_table_names=0

请在测试MySQL服务是否正常工作之前，记得重新启动MySQL服务。

如果您只在一个平台上使用MySQL，则通常不需要更改lower_case_table_names变量的默认值。但是，如果您想在文件系统大小写敏感性不同的平台之间传输表格，则可能会遇到困难。例如，在Unix上，您可以有两个不同的表名为my_table和MY_TABLE，但在Windows上，这些名称被视为相同。为了避免由于数据库或表名的字母大小写而导致的数据传输问题，您有两个选择：

在所有系统上使用lower_case_table_names = 1。这样做的主要缺点是，当您使用SHOW TABLES或SHOW DATABASES时，您看不到名称的原始字母大小写。

在Unix上使用lower_case_table_names = 0，在Windows上使用lower_case_table_names = 2。这将保留数据库和表名的大小写。这样做的缺点是，在Windows上，您必须确保您的语句始终使用正确的大小写引用您的数据库和表名。如果您将语句转移到大小写敏感的Unix上，如果大小写不正确，则它们将无法正常工作。

例外情况：如果您正在使用InnoDB表，并且正在尝试避免这些数据传输问题，则应在所有平台上将lower_case_table_names设置为1，以强制将名称转换为小写。

如果您计划在Unix上将lower_case_table_names系统变量设置为1，则必须先将旧的数据库和表名转换为小写，然后停止mysqld并使用新的变量设置重新启动它。

请查看MySQL网站以获取有关此问题的其他信息以及一些重要警告：http://dev.mysql.com/doc/refman/4.1/en/identifier-case-sensitivity.html。

- Pentium10

Answer 3

这个答案并没有给出解决问题的明确路径，但提供了更多的见解。

Mac OS 和 Windows 有不同的考虑因素：

在 MySQL 中，数据库对应于数据目录中的目录。每个数据库中的表对应于数据库目录中至少一个文件（根据存储引擎可能会有更多）。触发器也对应于文件。因此，底层操作系统的大小写敏感性在数据库、表和触发器名称的大小写敏感性中发挥作用。这意味着在 Windows 中这些名称不区分大小写，但在大多数 Unix 变体中是区分大小写的。值得注意的例外是 macOS，它基于 Unix，但使用默认的文件系统类型（HFS+）不区分大小写。然而，macOS 也支持 UFS 卷，它们与任何 Unix 一样区分大小写。请参阅第 1.8.1 节“MySQL 扩展到标准 SQL”。lower_case_table_names 系统变量也影响服务器如何处理标识符的大小写敏感性，稍后在本节中描述。

-- https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html 从一些错误报告中得知：

无法完全将表名转换为小写，在尝试重命名表时会导致后续错误。（特别是分区表）

在 macOS 上使用 lower_case_table_names=2 设置时，表名没有以小写字母进行比较，导致重新启动服务器后出现不稳定情况。（Bug #28170699，Bug #91204）

对于 MySQL 8.0 的一个重要说明：

现在禁止使用与初始化服务器时不同的 lower_case_table_names 设置启动服务器。这个限制是必要的，因为数据字典表字段所使用的字符集基于服务器初始化时定义的设置，使用不同的设置重新启动服务器会引入关于标识符排序和比较方式的不一致性。（Bug #27309094，Bug #89035）

当我最后面对这个问题时，我决定做繁重的工作，将所有表名转换为小写并更改所有代码。抱歉，我没有更好的方法。

- Rick James

Answer 4

您可以在JPA中编写代码，将所有物理、模式、序列和表名转换为小写，并在两个系统上使用相同的大小写，以便数据库可以从Windows导出并在Unix上导入而不会出现任何问题。

将此类添加到某个位置：

public class ImprovedNamingStrategy implements PhysicalNamingStrategy {

    @Override
    public Identifier toPhysicalCatalogName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    @Override
    public Identifier toPhysicalSchemaName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    @Override
    public Identifier toPhysicalSequenceName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    @Override
    public Identifier toPhysicalTableName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    /**
     * Converts table name.
     *
     * @param identifier
     *            the identifier.
     * @return the identifier.
     */
    private Identifier convert(Identifier identifier) {
        if (identifier == null || identifier.getText().trim().isEmpty()) {
            return identifier;
        }
        return Identifier.toIdentifier(identifier.getText().toLowerCase());
    }
}

并将此属性添加到您的persistence.xml文件中。

<property name="hibernate.physical_naming_strategy" value="ImprovedNamingStrategy" />

- K.Nicholas