如何摆脱 MySQL 中的 STRICT SQL 模式

2022-01-03 00:00:00 macos homebrew configuration mysql sql-mode

这是对这个问题的跟进MYSQL 不正确的 DATETIME 格式

如何一劳永逸地摆脱 STRICT_TRANS_TABLES?

How to get rid of STRICT_TRANS_TABLES once and for all?

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

但这无济于事.我有一些遗留代码,每次重新启动计算机时,我都必须启动 mysql 并更改 sql_mode.

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.

更新

所以我放弃了 Homebrew 安装的 MySQL 并从 mysql.com 下载它.但这也无济于事.按照这里的答案:How to fix `unknown variable 'sql-mode=ANSI'`? 我尝试了 /etc/my.cnf 的不同变体:[mysql][mysqld]sql_mode, sql-mode - 没有任何帮助.

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.

推荐答案

所以最后我把我从 mysql.com 得到的 MySQL Server 删除了,通过 Homebrew 重新安装,不得不编辑

So in the end I removed the MySQL Server I got from the mysql.com, reinstalled it via Homebrew and had to edit

/usr/local/Cellar/mysql/5.6.xx/my.cnf

我可以在哪里注释掉该死的STRICT_TRANS_TABLES.

Where I could comment out the darned STRICT_TRANS_TABLES.

然而,这并不能解释为什么默认配置会覆盖 /etc/my.cnf 中的配置,但我已经在这上面花费了太多时间.顺便说一下,我仍然不确定如何处理 mysql.com 提供的发行版.

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.

相关文章