MySQL lower_case_table_names 设置为 2，Workbench 仍然不允许小写数据库名称

lower_case_table_names set to 2, Workbench still does not allow lowercase database name

提问by disasterkid

I have installed MySql Workbench 6.2with MySql version 5.6on my Windows 7 64-bit.

我已经在我的 Windows 7 64 位上安装MySql Workbench 6.2了MySql version 5.6。

I would like to use Capital letters in my database name and table names. So I need to set the variable lower_case_table_namesto 2. When I look at my Options file's General tab, it looks like the following: Clicking Apply opens a dialog that says "There Are No Changes". Regardless, when I try to create a database with a Capital letter, I get the warning:

我想在我的数据库名称和表名称中使用大写字母。所以我需要将变量设置lower_case_table_names为 2。当我查看我的选项文件的常规选项卡时，它看起来如下所示： 单击应用打开一个对话框，显示“没有更改”。无论如何，当我尝试使用大写字母创建数据库时，我收到警告：

The server is configured with lower_case_table_names=1 which only allows lowercase characters in schema and table names.

服务器配置为lower_case_table_names=1，它只允许模式和表名中的小写字符。

I have a feeling the my.inifile on the server differs from the one mentioned in the Options File configuration. When I try to add this variable manually inside my my.inifile, I see the text below:

我感觉my.ini服务器上的文件与选项文件配置中提到的文件不同。当我尝试在我的my.ini文件中手动添加此变量时，我看到以下文本：

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

This is how I have been stuck in creating my schema for a few days now.

这就是我几天来一直坚持创建架构的方式。

采纳答案by O. Jones

In Windows, table naming is case-insensitive. That is, your Customertable and your customertable will always be the same on Windows. That's a limitation of the NT File System. This applies when your MySQL serveris running on a Windows platform. It doesn't matter where your workbench client is running.

在 Windows 中，表命名不区分大小写。也就是说，您的Customer表和您的customer表在 Windows 上将始终相同。这是 NT 文件系统的限制。这适用于您的 MySQL服务器在 Windows 平台上运行的情况。您的工作台客户端在哪里运行并不重要。

(You can use mixed-case table names for different tables on Linux, BSD, and the like, but it's considered very bad practice: only do that if you want to drive your colleagues crazy. So be careful.)

（您可以在 Linux、BSD 等系统上为不同的表使用大小写混合的表名，但这被认为是非常糟糕的做法：只有在您想让您的同事发疯时才这样做。所以要小心。）

If you leave this lower_case_table_namessetting alone, you can use mixed case in your table names without problems.

如果lower_case_table_names单独保留此设置，则可以在表名中使用大小写混合，而不会出现问题。

The my.inifile the server actually uses when it starts is usually found in the datadirectory. The installation procedure can copy a preloaded version of that file, like my_large.inion top of my.inidepending on what you are trying to do.

my.ini服务器启动时实际使用的文件通常在data目录中。安装过程可以复制该文件的版本预装，像my_large.ini之上my.ini取决于你正在尝试做的。

回答by user10755024

You can't even start mysqld after changing the lower_case_table_namessetting other to anything other than 1, which is the default.

在lower_case_table_names将 other 设置更改为除 之外的任何设置后，您甚至无法启动 mysqld 1，这是默认设置。

0=> You should not set lower_case_table_namesto 0if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS). It is an unsupported combination that could result in a hang condition.

0=>如果您在数据目录驻留在不区分大小写的文件系统（例如在 Windows 或 macOS 上）的系统上运行 MySQL lower_case_table_names，0则不应设置为。这是一种不受支持的组合，可能会导致挂起情况。

But let's try changing it to 2:

但是让我们尝试将其更改为 2：

# Specifies the on how table names are stored in the metadata.
# If set to 0, will throw an error on case-insensitive operative systems
# 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.
# NOTE: Modify this value after Server initialization won't take effect.
lower_case_table_names=2

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 8.0\my.ini"

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 8.0\my.ini"

ERROR [MY-011087] [Server, Different lower_case_table_names settings for server ('2') and data dictionary ('1').
ERROR [MY-010020] [Server, Data Dictionary initialization failed.

After initialization, it is not allowed to change this setting. So lower_case_table_namesneeds to be set together with --initialize.

初始化后，不允许更改此设置。所以lower_case_table_names需要和 一起设置--initialize。

It is prohibited to start the server with a lower_case_table_namessetting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

禁止lower_case_table_names使用与服务器初始化时使用的设置不同的设置启动服务器。该限制是必要的，因为各种数据字典表字段使用的排序规则基于服务器初始化时定义的设置，并且使用不同的设置重新启动服务器会在如何排序和比较标识符方面引入不一致。

mysqld --initialize --console --lower_case_table_names=2

mysqld --initialize --console --lower_case_table_names=2

Then you will get following error in workbench after initilizing the server again with lower_case_table_names=2:

然后在使用以下命令再次初始化服务器后，您将在工作台中收到以下错误lower_case_table_names=2：

A server configuration problem was detected. The server is in a system that does not properly support the selected lower_case_table_names option value. Some problems may occur.

show variables like lower_case_table_names;

显示变量，如lower_case_table_names；

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 2     |

So conclusion: on Windows leave the setting to 1 because 0 or 2 won't work or as they put it: some problems may occur.

所以结论：在 Windows 上将设置保留为 1，因为 0 或 2 不起作用，或者正如他们所说：可能会出现一些问题。

I do however now have my database and table names showing up with Capital letters. Which doesnt really do much because comparison will always be :

然而，我现在有我的数据库和表名以大写字母显示。这并没有多大作用，因为比较总是：

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

Nicholas

尼古拉斯

回答by Myron Curtis

If your data files are on a drive different than the C: drive, you may actually have 2 "my.ini" files, one will be on the C: drive and when you edit the Options in Workbench, that is the file that gets changed while the "My.ini" your system is actually working from is left alone. Check C:\Program Data\MySQL\MySQL(server version), to see if there is an .ini file there. If so, you will probably find it has the changes at the bottom of the file that need to be written to the actual working .ini on the drive your data is really stored on.

如果您的数据文件位于与 C: 驱动器不同的驱动器上，则您实际上可能有 2 个“my.ini”文件，一个位于 C: 驱动器上，当您在 Workbench 中编辑选项时，该文件将获得当您的系统实际工作的“My.ini”被保留时发生了变化。检查 C:\Program Data\MySQL\MySQL(server version)，看看那里是否有 .ini 文件。如果是这样，您可能会发现文件底部的更改需要写入实际存储数据的驱动器上的实际工作 .ini。

回答by Carlos Henrique Ferracin

In workbench go to : management panel > Options File > General > System >

在工作台中转到：管理面板 > 选项文件 > 常规 > 系统 >

Check the "lower_case_table_names", put value 2.

检查“lower_case_table_names”，输入值 2。

Close Workbench. Restart Service MYSQL56

关闭工作台。重启服务MYSQL56

See img here How to enable

在这里查看 img如何启用

回答by sendon1982

1. Edit this file at /etc/mysql/my.cnf
2. Add following lines:

1. 编辑此文件在 /etc/mysql/my.cnf
2. 添加以下几行：

[mysqld]

[mysqld]

lower_case_table_names=1

lower_case_table_names=1

1. Restart mysql sudo /etc/init.d/mysql restart

3. 重启mysql sudo /etc/init.d/mysql restart