MacOS上不区分大小写的文件系统的MySQL区分大小写表名称 [英] MySQL case sensitivity table name on MacOS with case insensitive file system
问题描述
我已经做了很多研究，并且我了解到使数据库表名称敏感，您必须设置变量lower_case_table_names = 0. 我在osX上.我在my.cnf中进行了此更改. 之后，如果我运行
I have researched a lot and what I understand to make database tables name sensitive, you have to set the variable lower_case_table_names=0. Im on osX. I did this change in my.cnf. After that, if I run
select * from users
我得到结果.如果我跑步:
I get results. While if I run:
select * from Users
我收到错误消息，说表格不存在.
I get error saying table doesn't exist.
但是，对于特定的数据库，区分大小写不受影响.我可以在任何情况下都不会收到错误.为什么?我可以看一下用于导入数据库的大型sql文件，并尝试找出是否存在忽略大小写敏感性(?)的特定指令. 无论如何，为什么您认为区分大小写适用于所有数据库，但不适用于我感兴趣的那个数据库? InnoDB是区分大小写的一种.虽然我不关心这个的是MyIsam.可能是原因吗?在这种情况下可以解决吗?
However, for a particular database, the case sensitivity doesnt affect. I can use any case I will never receive errors. Why? I could have a look at the big sql-file used to import the database and try to find out if there are specific directives to ignore case sensitivity (?). Anyway, why you think the case sensitivity applies for all database but not the one Im interested in? One of those that does case sensitivity is InnoDB. While the one that doesnt care about this is MyIsam. Could it be the reason? Any work around in that case?
推荐答案
表和列在Linux中是区分大小写！要使它们不区分大小写，请按照以下步骤操作:
Tables and Columns are Case Sensitive in Linux! To make them case insensitive, follow this:
打开终端并编辑
/etc/mysql/my.cnf
sudo nano /etc/mysql/my.cnf
在[mysqld]部分下，添加:
lower_case_table_names = 1
重新启动mysql
sudo /etc/init.d/mysql restart
然后在此处检查:
mysqladmin -u root -p variables
仅更改lower_case_table_names设置是不够的.在导入数据库之前，需要完成此操作.
Just altering the lower_case_table_names setting isn't enough. It needs to be done before you import your database(s).
MySQL 5.7文档列表在Windows和Linux/UNIX之间移动的过程.该参考中有关Mac OSX的注释:
The MySQL 5.7 documentation lists a procedure for moving between Windows and Linux/UNIX. A note about Mac OSX from that reference:
一个值得注意的例外是OS X，它是基于Unix的，但使用默认 不区分大小写的文件系统类型(HFS +).但是，OS X也 支持UFS卷，这与任何Unix一样都区分大小写.
One notable exception is OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, OS X also supports UFS volumes, which are case sensitive just as on any Unix.
查看手册页，以确保遵循所需的强制区分大小写的规则.查看并确认您以正确的顺序执行了这些步骤:
Review the manual page to ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct order:
要转换一个或多个整个数据库，请在设置之前转储它们 lower_case_table_names，然后删除数据库，并在之后重新加载它们 设置lower_case_table_names:
To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:
1-使用mysqldump转储每个数据库:
1 - Use mysqldump to dump each database:
mysqldump-数据库db1> db1.sql
mysqldump --databases db1 > db1.sql
mysqldump-数据库db2> db2.sql
mysqldump --databases db2 > db2.sql
...对必须重新创建的每个数据库执行此操作.
... Do this for each database that must be recreated.
2-使用DROP DATABASE删除每个数据库.
2 - Use DROP DATABASE to drop each database.
3-停止服务器，在\etc\mysql\my.cnf文件的[mysqld]部分中设置lower_case_table_names，然后重新启动服务器.
3 - Stop the server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.
4-重新加载每个数据库的转储文件.因为lower_case_table_names 设置后，每个数据库和表名都将转换为小写形式，如下所示 它是重新创建的:
4 - Reload the dump file for each database. Because lower_case_table_names is set, each database and table name will be converted to lowercase as it is recreated:
mysql< db1.sql
mysql < db1.sql
mysql< db2.sql
mysql < db2.sql
Concerning the MySQL System Variable lower_case_table_names Server Variable (or setting):
其他参考:
- MySQL case sensitive table names on Linux
- How to make MySQL table name case insensitive in Ubuntu?
这篇关于MacOS上不区分大小写的文件系统的MySQL区分大小写表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
