MySQL区分大小写表转换 [英] MySQL Case Sensitive Tables Conversion
问题描述
我有一个PHP代码库，用于处理混合大小写的MySQL表.例如，xar_intakeformgenerator_ChangeLog.
I have a PHP codebase that was written to address our MySQL tables in mixed case. For example, xar_intakeformgenerator_ChangeLog.
我们的代码也可以在Windows上运行，并且在我们不了解之前，我们已经将多个数据库导入到Windows服务器上.这导致Windows MySQL将所有表名更改为小写. (xar_intakeformgenerator_changelog).现在，我们知道如何防止这种情况发生在新数据库中. (Set lower_case_table_names)而且，该代码在Windows服务器上运行良好，因为MySQL不在乎Windows上表的大小写.
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服务器给我们带来了痛苦，我们需要将所有数据库移到Linux服务器上.由于所有表名都转换为小写，因此该代码在Linux上将不起作用.幸运的是，Xaraya创建了表映射.因此，从理论上讲，我可以为这些数据库建立一个新的代码库，并更改每个模块的映射以使用小写表.或者，我们可以在将表名导入Linux机器后手动更改表名，以使表大小写正确.
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.
更改lower_case_table_names不会更正设置该标志之前损坏的数据库.这些都有小写的表名.
changing lower_case_table_names does not correct the databases that were mangled before the flag was set. These all have the lower case table names.
我对这两种选择都不陌生.有人知道处理这个问题的巧妙方法吗?
I'm not wild about either option. Does anybody know an ingenious way to handle this?
推荐答案
确定.我找到了答案.
在Linux服务器上，我需要运行以下命令以将Linux生成的数据库中的所有表名更改为小写:
On the Linux server, I needed to run the following to change all the table names in my Linux generated databases to lower case:
-
如何生成一个SQL脚本，该脚本将模式中的所有表重命名为小写形式:
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';
将phpmyadmin中的数据库重命名为小写名称.
Renamed the databases in phpmyadmin to lowercase names.
在Linux服务器上将my.cnf修改为使用lower_case_table_names=1
Modified the my.cnf on the Linux server to use lower_case_table_names=1
重新启动mysql.
在此之后，我的代码将使用小写的表名.因此，我能够导入Windows，并在两者上具有相同的代码库工作.
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.
这篇关于MySQL区分大小写表转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
