如何摆脱 MySQL 中的 STRICT SQL 模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18762308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get rid of STRICT SQL mode in MySQL
提问by firedev
This is a follow up to this question MYSQL incorrect DATETIME format
这是对这个问题MYSQL 不正确的 DATETIME 格式的跟进
How to get rid of STRICT_TRANS_TABLES once and for all?
如何一劳永逸地摆脱 STRICT_TRANS_TABLES？
mysql --helpreports the following configs:
mysql --help报告以下配置：
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
$ ls /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
ls: /Users/pain/.my.cnf: No such file or directory
ls: /etc/mysql/my.cnf: No such file or directory
ls: /usr/local/etc/my.cnf: No such file or directory
/etc/my.cnf
$ cat /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
But this doesn't help. I have some legacy code and each time I reboot the computer I have to launch mysql and change sql_mode.
但这没有帮助。我有一些遗留代码，每次重新启动计算机时，我都必须启动 mysql 并更改 sql_mode。
Update
更新
So I gave up on Homebrew-installed MySQL and downloaded it from from mysql.com. But that didn't help either. Following the answers here: How to fix `unknown variable 'sql-mode=ANSI'`?I have tried different variations of /etc/my.cnf: [mysql], [mysqld], sql_mode, sql-mode– nothing helped.
所以我放弃了 Homebrew 安装的 MySQL 并从 mysql.com 下载它。但这也无济于事。遵循此处的答案：如何修复“未知变量 'sql-mode=ANSI'”？我尝试了以下不同的变体/etc/my.cnf：[mysql], [mysqld], sql_mode, sql-mode– 没有任何帮助。
采纳答案by firedev
So in the end I removed the MySQL Server I got from the mysql.com, reinstalled it via Homebrew and had to edit
所以最后我删除了我从 mysql.com 得到的 MySQL 服务器，通过 Homebrew 重新安装它并且不得不编辑
/usr/local/Cellar/mysql/5.6.xx/my.cnf
Where I could comment out the darned STRICT_TRANS_TABLES.
我可以在哪里评论该死的STRICT_TRANS_TABLES.
However this doesn't explain why the default config overrides the one from /etc/my.cnf, but I spent too much time on this already as it is. And by the way I am still not sure what to do with the mysql.com provided distribution.
然而，这并不能解释为什么默认配置会覆盖来自 的配置/etc/my.cnf，但我已经在这上面花费了太多时间。顺便说一下，我仍然不确定如何处理 mysql.com 提供的发行版。
回答by Paul Warren
This problem scuppered me for a while as well. None of the answers so far addressed the original problem but I believe mine does so I'll post it in case it helps anyone else.
这个问题也困扰了我一段时间。到目前为止，没有一个答案解决了最初的问题，但我相信我的答案是这样，所以我会发布它，以防它对其他人有帮助。
I have MySQL (from mysql.com) Community Edition 5.7.10 installed on OS X 10.10.3
我在 OS X 10.10.3 上安装了 MySQL（来自 mysql.com）社区版 5.7.10
In the end I created a /etc/mysql/my.cnfwith the following contents:-
最后我创建了一个/etc/mysql/my.cnf包含以下内容的：-
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
After restarting the server a SHOW VARIABLES LIKE 'sql_mode';gave me:-
重新启动服务器后SHOW VARIABLES LIKE 'sql_mode';给了我：-
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)
Finally, no strict mode!
最后，没有严格模式！
回答by vd1008
On Centos 6.5 i had to edit /usr/my.cnfand set (even though /etc/my.cnfexisted and bindings were successfully set there
在 Centos 6.5 上，我必须编辑 /usr/my.cnf和设置（即使/etc/my.cnf存在并且绑定在那里成功设置）
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
package was from:
包裹来自：
mysql-community-client.x86_64 5.6.16-1.el6 @mysql56-community
回答by ssnobody
According to MySQL Strict Mode on OS Xthe problematic setting is actually at /usr/local/mysql/my.cnfand can be commented out to stop this behavior.
根据OS X 上的MySQL 严格模式，有问题的设置实际上位于/usr/local/mysql/my.cnf并且可以被注释掉以阻止这种行为。
回答by Aleksandr Sobakar
Now you can`t set sql_mode to empty string, actual query is:
现在你不能将 sql_mode 设置为空字符串，实际查询是：
SET @@GLOBAL.sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
MySQL 5.7.16
MySQL 5.7.16
回答by thinkcomp
I tried every answer I could find on this issue using MySQL 5.7 on Mac OS 10.12 and ultimately got strict mode turned off not because of the location of my.cnf, which can presumably be in any of the places that MySQL says it checks, but thanks to a UNIX permissions issue.
我在 Mac OS 10.12 上使用 MySQL 5.7 尝试了我能找到的关于这个问题的所有答案，最终关闭了严格模式并不是因为 my.cnf 的位置，它可能位于 MySQL 说它检查的任何地方，但是感谢 UNIX 权限问题。
I used MySQL Workbench 6.2.3.12313 to create my.cnf initially. This caused two possible problems: first, it set the option to "sql-mode" instead of "sql_mode", and it made the file (located in /etc) readable and writable only for root. MySQL does not run as root when you install it the way I did, from the binary package on the MySQL web site--it runs as _mysql. So the _mysql user needs to be able to read /etc/my.cnf, or wherever you put it. In order for that to work, you need to run:
我最初使用 MySQL Workbench 6.2.3.12313 创建 my.cnf。这导致了两个可能的问题：首先，它将选项设置为“sql-mode”而不是“sql_mode”，并且它使文件（位于 /etc 中）仅对 root 可读和可写。当您按照我的方式从 MySQL 网站上的二进制包安装 MySQL 时，MySQL 不会以 root 身份运行——它以 _mysql 的身份运行。因此 _mysql 用户需要能够读取 /etc/my.cnf 或您放置的任何位置。为了使其工作，您需要运行：
sudo chmod o+r /etc/my.cnf
and for good measure you may also want to run:
为了更好地衡量，您可能还想运行：
sudo chmod g+r /etc/my.cnf
Then make sure to restart MySQL. (I have found that this works best through the System Preferences MySQL panel on Mac OS; using the command line is kind of messy and MySQL Workbench's functionality simply doesn't work.) So long as you have an sql_mode setting in my.cnf that does not involve strict mode, strict mode should be off.
然后确保重新启动MySQL。（我发现这通过 Mac OS 上的系统偏好设置 MySQL 面板效果最好；使用命令行有点混乱，而且 MySQL Workbench 的功能根本不起作用。）只要你在 my.cnf 中有一个 sql_mode 设置不涉及严格模式，严格模式应该关闭。
回答by Kjell
On Mac OS X El Capitan i created a file .my.cnf in the user home dir and set the settings for mysql under [mysqld]and then restarted mysql. Worked fine!
在 Mac OS X El Capitan 上，我在用户主目录中创建了一个文件 .my.cnf 并在下设置了 mysql 的设置[mysqld]，然后重新启动了 mysql。工作得很好！
