osx更新后的MySQL表名小写 [英] Mysql tablename lowercase after osx update

问题描述

我做了OSX更新:

https://support.apple.com/en-us/HT206770

之后，mysql似乎将所有新表都保存为小写.

After which it seems mysql saves all my new tables lowercase.

我使用MAMP 3.5.2. mysql设置是Mac OSX的默认设置

I use MAMP 3.5.2. The mysql settings is default for Mac OSX

/Applications/MAMP/Library/bin/mysqladmin variables | grep case
| lower_case_file_system                            | ON                                                                                                                     |
| lower_case_table_names                            | 2                                                                                                                      |

我注意到使用:

[mysqld]
lower_case_table_names=1

保存我的表camelCased，但是使用此设置不允许搜索camelCased表名，因为它将搜索小写名称. 有人知道吗?

Saves my table camelCased, yet, using this setting it does not allow to search for camelCased table names since it will search for lowercase names. Does anyone have a clue ?

推荐答案

OSX默认使用日志记录的，不区分大小写的文件系统.

OSX by default uses a journaled, case-insensitive file system.

对于lower_case_table_names = 1

表名以小写形式存储在磁盘上，并且名称比较不区分大小写. MySQL在存储和查找时将所有表名都转换为小写.此行为也适用于数据库名称和表别名.

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.

基于lower_case_table_names = 2

表名和数据库名使用CREATE TABLE或CREATE DATABASE语句中指定的字母大小写存储在磁盘上，但是MySQL在查找时将它们转换为小写.名称比较不区分大小写.这仅适用于不区分大小写的文件系统！ InnoDB表名称以小写形式存储，与lower_case_table_names = 1相同.

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.

请注意，使用此设置，即使在Mysql中启用了区分大小写，Mysql仍将innodb .ibd文件保存为小写(它会按您的选择保存.frm表(驼峰式).

Note that with this setting Mysql will still save the innodb .ibd files a lower case even if you enable case sensitivity in Mysql (it does save the .frm table as you choose (camel case).

因此，根据doc的说法，它的工作符合预期.

So, according to doc's it's acting as expected.

要点: 您无法始终控制文件系统(有人将您的架构移植到在Linux，Windows等操作系统上运行的mysql)，因此请始终假定其大小写不敏感，并在mysql中使用所有小写形式的表名，不要这样做.骆驼案.对于数据库名称和列名称，我也建议使用相同的名称.

The take away: You can't control the file system always (someone my port your schema to mysql running on linux, windows, etc...), so always assume its case insensitive and stick to all lower case for table names in mysql, an don't camel case. I'd also recommend the same for database and column names.

这篇关于osx更新后的MySQL表名小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！