mysql - 如何在 Windows 中的(现有)mySQL 名称中使用大写字母

Question

有人知道我如何在表名中保留大小写吗(Win 10 使用 phpMyAdmin，最新的 xampp 发行版)。这可能是因为 xampp 使用 MariaDB 而不是 mySQL 吗？

表名被重写为全部小写。

在 phpMyAdmin 网站上阅读，要使用保留大小写，我应该添加:

'set-variable=lower_case_table_names=0'

这会停止 mySQL，并出现错误消息:

[错误] c:\xampp\mysql\bin\mysqld.exe:未知变量“set-variable=lower_case_table_names=0”

然后，在阅读此处的答案后，我了解到我应该使用 lower_case_table_names=0，因此将其添加到 my.ini 中，但仍然遇到相同的错误。

然后我花了 20 分钟尝试找出我的 set-variable=lower_case_table_names=0 的设置位置。 (认为​​它必须被缓存或复制或其他什么。)

无奈之下，我删除了lower_case_table_names=0，错误消失了。我把它放回去并得到了[错误] c:\xampp\mysql\bin\mysqld.exe: 未知变量 'set-variable=lower_case_table_names=0' 错误。 IE 它们似乎是错误报告翻译的别名。有点烦人。

Answer 1

您应该更仔细地阅读文档，特别是 identifier case sensitivity section .

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. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix.

Value Meaning

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

总结一下:在 Windows 上，不应将 lower_case_table_names 设置为 0。如果要在 Windows 上保留表名的字母大小写，请将 lower_case_table_names 设置为 2。

using system variables部分解释了如何以各种方式设置系统变量。