windows MySQL 区分大小写的表转换

MySQL Case Sensitive Tables Conversion

提问by Amy Anuszewski

I have a PHP codebase that was written to address our MySQL tables in mixed case. For example, xar_intakeformgenerator_ChangeLog.

我有一个 PHP 代码库，用于解决混合大小写的 MySQL 表。例如，xar_intakeformgenerator_ChangeLog。

Our code also runs on windows, and before we knew any better, we imported several databases on to a Windows server. This resulted in Windows MySQL changing all the table names to lower case. (xar_intakeformgenerator_changelog). We now know how to prevent that from happening to new databases. (Set lower_case_table_names) And, the code runs fine on the Windows servers because MySQL just doesn't care about the case of the tables on Windows.

我们的代码也在 Windows 上运行，在我们知道更好之前，我们将几个数据库导入到 Windows 服务器上。这导致 Windows MySQL 将所有表名更改为小写。( xar_intakeformgenerator_changelog). 我们现在知道如何防止这种情况发生在新数据库上。( Set lower_case_table_names) 而且，代码在 Windows 服务器上运行良好，因为 MySQL 不关心 Windows 上表的大小写。

Here's the problem. The Windows server is giving us grief and we need to move all of the databases off to a Linux server. Because all of the table names have converted to lower case, the code will NOT work on Linux. Luckily, Xaraya creates table mappings. So, in theory, I could set up a new code base for these databases and change the mappings of every module to use the lower case tables. Or, we could manually change the table names after we import them onto the Linux machine to get the table case correct.

这就是问题所在。Windows 服务器让我们感到悲痛，我们需要将所有数据库迁移到 Linux 服务器。由于所有表名都已转换为小写，因此该代码将无法在 Linux 上运行。幸运的是，Xaraya 创建了表映射。因此，理论上，我可以为这些数据库设置一个新的代码库，并更改每个模块的映射以使用小写表。或者，我们可以在将表名导入 Linux 机器后手动更改表名，以使表大小写正确。

changing lower_case_table_namesdoes not correct the databases that were mangled before the flag was set. These all have the lower case table names.

更改lower_case_table_names不会更正在设置标志之前损坏的数据库。这些都有小写的表名。

I'm not wild about either option. Does anybody know an ingenious way to handle this?

我对这两种选择都不感冒。有人知道处理这个问题的巧妙方法吗？

回答by Amy Anuszewski

OK. I found my answer.

好的。我找到了我的答案。

On the Linux server, I needed to run the following to change all the table names in my Linux generated databases to lower case:

在 Linux 服务器上，我需要运行以下命令将 Linux 生成的数据库中的所有表名更改为小写：

1. How to produce a SQL script that renames all tables in a schema to its lower case form:

   select concat('rename table ', table_name, ' to ' , lower(table_name) , ';') 
   from information_schema.tables where table_schema = 'your_schema_name';
   

2. Renamed the databases in phpmyadminto lowercase names.

3. Modified the my.cnfon the Linux server to use lower_case_table_names=1

4. Restarted mysql.

1. 如何生成将架构中的所有表重命名为其小写形式的 SQL 脚本：

   select concat('rename table ', table_name, ' to ' , lower(table_name) , ';') 
   from information_schema.tables where table_schema = 'your_schema_name';
   

2. 将数据库重命名phpmyadmin为小写名称。

3. 修改了my.cnf在Linux服务器上使用lower_case_table_names=1

4. 重启了mysql。

After this, my code would work with the lower case table names. So, I was able to import the Windows ones and have the same code base work on both.

在此之后，我的代码将使用小写表名。因此，我能够导入 Windows 并在两者上使用相同的代码库。

回答by Pascal MARTIN

If I remember correctly (had the same kind of problem a while back -- but I stopped working on that project before we decided which solution to adopt...), there is a configuration option which says how tablenames should be used (case-sensitive or not case-sensitive).

如果我没记错的话（不久前遇到了同样的问题——但在我们决定采用哪种解决方案之前我停止了该项目的工作......），有一个配置选项说明应该如何使用表名（案例-敏感或不区分大小写）。

Here's what I found : Identifier Case Sensitivity

这是我发现的：标识符区分大小写

Quoting that page :

引用该页面：

If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_namesvariable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_tableand MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

• Use lower_case_table_names=1on all systems. The main disadvantage with this is that when you use SHOW TABLESor SHOW DATABASES, you do not see the names in their original lettercase.

  • Use lower_case_table_names=0on Unix and lower_case_table_names=2on Windows. This preserves the lettercase of database and table names.

如果您仅在一个平台上使用 MySQL，则通常不必更改该lower_case_table_names变量的默认值。但是，如果要在文件系统区分大小写不同的平台之间传输表，可能会遇到困难。例如，在 Unix 上，您可以有两个名为my_tableand 的 不同表MY_TABLE，但在 Windows 上，这两个名称被认为是相同的。为避免因数据库或表名的字母大小写引起的数据传输问题，您有两种选择：

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

  • 使用lower_case_table_names=0在Unix和lower_case_table_names=2Windows上。这保留了数据库和表名的字母大小写。

(There is more I didn't copy-paste ; so going to read that page might be a good idea ;-) )

（还有更多我没有复制粘贴；所以阅读该页面可能是个好主意；-)）

Hope this will help...

希望这会有所帮助...

回答by Andreas

lower_case_table_names

lower_case_table_names

For Windows, it defaults to "make everything lower case" (1). Set it to 2:"Case insensitive but keep the case as it is".

对于 Windows，它默认为“使所有内容都小写”(1)。将其设置为 2：“不区分大小写，但保持大小写不变”。

These changes go into your my.cnf

这些更改会进入您的 my.cnf