温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

lower_case_table_names参数设置解决Error Code: 1146. Table doesn't exist?

发布时间:2020-08-10 17:32:09 来源:ITPUB博客 阅读:418 作者:guocun09 栏目:MySQL数据库
MariaDB 10.2.11 for windows中使用mysqldump导出DB,并导入Mysql 5.7.16 for Linux后,在程式执行时报错:Error Code: 1146. Table XXX doesn't exist

检查程式代码发现执行SQL :SELECT * FROM Base_User ... 报的错,但检查Mysql 5.7.16 for Linux 中table却存在。
尝试把SQL中驼峰式表名(Base_User)改为全小写表名SELECT * FROM base_user可以正常执行,原来是因为Mysql for Linux中默认大小敏感,而windows中默认大小写不敏感。

在my.cnf设置参数 lower_case_table_names 为1,并重启mysql后大小写不敏感

[mysqld]
lower_case_table_names=1

参考文档:
lower_case_table_names

Property Value
Command-Line Format --lower-case-table-names[=#]
System Variable lower_case_table_names
Scope Global
Dynamic No
Type integer
Default 0
Minimum 0
Maximum 2

If set to 0, table names are stored as specified and comparisons are case-sensitive. 
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. 

On Windows the default value is 1. On OS X, the default value is 2.

You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or OS X). 
It is an unsupported combination that could result in a hang condition when running an INSERT INTO ... SELECT ... FROM tbl_nameoperation with the wrong tbl_name letter case. With MyISAM, accessing table names using different letter cases could cause index corruption.

As of MySQL 5.7.9, an error message is printed and the server exits if you attempt to start the server with --lower_case_table_names=0 on a case-insensitive file system.

If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

The setting of this variable in MySQL 5.7 affects the behavior of replication filtering options with regard to case sensitivity.

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI