如何在MySQL 8的my.cnf中设置sql_mode? [英] How to set sql_mode in my.cnf in MySQL 8?

问题描述

我正在运行MySQL 8.0.11社区版本.我需要将sql_mode设置为在my.cnf中排除ONLY_FULL_GROUP_BY，以便安全重启.我尝试了以下变体:

I'm running MySQL 8.0.11 community version. I need to set sql_mode to exclude ONLY_FULL_GROUP_BY in my.cnf so that it's restart safe. I tried the following variants:

sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

无论变量名为"sql_mode"还是"sql-mode"，它们都以相同的方式失败:

They all fail in the same manner whether the variable is named 'sql_mode' or 'sql-mode':

mysqld --verbose --help | grep "sql[-_]mode"
2018-06-19T15:22:51.667734Z 0 [ERROR] [MY-011071] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'
    --sql-mode=name     Syntax: sql-mode=mode[,mode[,mode...]]. See the manual
2018-06-19T15:22:51.675498Z 0 [ERROR] [MY-010119] [Server] Aborting

sql模式

似乎mysqld处理my.cnf并将'sql_mode'或'sql-mode'转换为'sql_mode'，然后它拒绝了！

It would seem that mysqld process my.cnf and converts 'sql_mode' or 'sql-mode' to 'sql_mode', which then it rejects!

问题是如何解决这个问题?

The question is how to get around this?

推荐答案

SQL模式NO_AUTO_CREATE_USER已在MySQL 8.0中删除，并且不再被识别.

The SQL mode NO_AUTO_CREATE_USER was removed in MySQL 8.0, and it's no longer recognized.

https://dev. mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-deprecations 说:

与帐户管理相关的以下功能已删除:

The following features related to account management are removed:

• 使用GRANT创建用户.而是使用CREATE USER.遵循这种做法使得NO_AUTO_CREATE_USER SQL模式对于以下情况不重要 GRANT语句，因此也将其删除.

• Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.

将您的sql_mode更改为"STRICT_TRANS_TABLES，NO_ZERO_IN_DATE，NO_ZERO_DATE，ERROR_FOR_DIVISION_BY_ZERO，NO_ENGINE_SUBSTITUTION".我在8.0.11的沙盒实例上对其进行了测试，并且可以正常工作.

Change your sql_mode to "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION". I tested this on my sandbox instance of 8.0.11 and it worked.

sql-mode或sql_mode的拼写都很好.

Either spelling of sql-mode or sql_mode are both fine.

使用引号或省略引号都可以.

Using quotes or omitting quotes are both fine.

这篇关于如何在MySQL 8的my.cnf中设置sql_mode?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！