在 mysql 中设置全局 sql_mode
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2317650/
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
setting global sql_mode in mysql
提问by JPro
I am trying to set sql_mode in mysql but it throws an error.
我试图在 mysql 中设置 sql_mode 但它引发错误。
Command:
命令：
set global sql_mode='NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLE','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION'
Is this not the proper way to set multiple modes? What are the advantages of setting session and global modes? which is preffered? I have different users trying to update the database with different UNC values and insted od setting the session mode to 'NO_BACKSLASH_ESCAPES', I though it would make sense to et a gloabl mode for this. Does this make sense?
这不是设置多种模式的正确方法吗？设置会话和全局模式有什么好处？哪个是首选？我有不同的用户试图用不同的 UNC 值更新数据库，并插入 od 将会话模式设置为“NO_BACKSLASH_ESCAPE”，但我认为为此设置一个全局模式是有意义的。这有意义吗？
Please let me know.
请告诉我。
Thanks.
谢谢。
采纳答案by JPro
I resolved it.
我解决了。
the correct mode is :
正确的模式是：
set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
回答by Chadwick Meyer
BTW, if you set globals in MySQL:
顺便说一句，如果您在 MySQL 中设置全局变量：
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
This will not set it PERMANENTLY, and it will revert after every restart.
这不会将其设置为PERMANENTLY，并且每次重新启动后都会恢复。
So you should set this in your config file (e.g. /etc/mysql/my.cnfin the [mysqld] section), so that the changes remain in effect after MySQL restart:
所以你应该在你的配置文件中设置它（例如/etc/mysql/my.cnf在 [mysqld] 部分），这样更改在 MySQL 重启后仍然有效：
Config File: /etc/mysql/my.cnf
配置文件：/etc/mysql/my.cnf
[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
UPDATE: Newer versions of Mysql (e.g. 5.7.8 or above) may require slightly different syntax:
更新：较新版本的 Mysql（例如 5.7.8 或更高版本）可能需要稍微不同的语法：
[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
Make sure that there is a dash between sql-modenot an underscore, and that modes are in double quotes.
确保在sql-mode不是下划线之间有破折号，并且模式在双引号中。
Always reference the MySQL Docsfor yourversion to see the sql-mode options.
回答by Yashrajsinh Jadeja
Setting sql mode permanently using mysql config file.
使用 mysql 配置文件永久设置 sql 模式。
In my case i have to change file /etc/mysql/mysql.conf.d/mysqld.cnfas mysql.conf.dis included in /etc/mysql/my.cnf. i change this under [mysqld]
在我来说，我必须改变文件/etc/mysql/mysql.conf.d/mysqld.cnf为mysql.conf.d包含在/etc/mysql/my.cnf。我在 [mysqld] 下改变了这个
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
just removed ONLY_FULL_GROUP_BYsql mode cause it was causing issue.
刚刚删除了ONLY_FULL_GROUP_BYsql 模式，因为它导致了问题。
I am using ubuntu 16.04, php 7and mysql --version give me this mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper
我正在使用ubuntu 16.04，php 7而 mysql --version 给了我这个mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper
After this change run below commands
在此更改后运行以下命令
sudo service mysql stop
sudo service mysql start
Now check sql modes by this query SELECT @@sql_modeand you should get modes that you have just set.
现在通过这个查询检查 sql 模式SELECT @@sql_mode，你应该得到你刚刚设置的模式。
回答by Devraj Gupta
Copy to Config File: /etc/mysql/my.cnf OR /bin/mysql/my.ini
复制到配置文件： /etc/mysql/my.cnf OR /bin/mysql/my.ini
[mysqld]
port = 3306
sql-mode=""
MySQLrestart.
MySQL重新开始。
Or you can also do
或者你也可以这样做
[mysqld]
port = 3306
SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
MySQLrestart.
MySQL重新开始。
回答by Serhii Popov
For someone who googling this error for MySQL 8.
对于在谷歌上搜索 MySQL 8 错误的人。
MySQL 8.0.11 remove the 'NO_AUTO_CREATE_USER' from sql-mode.
MySQL 8.0.11 从 sql-mode 中删除了“NO_AUTO_CREATE_USER”。
MySQL 5.7: 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 deprecated. MySQL 8.0.11: 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.
MySQL 5.7：使用 GRANT 创建用户。相反，使用创建用户。遵循这种做法会使 NO_AUTO_CREATE_USER SQL 模式对于 GRANT 语句变得无关紧要，因此它也已被弃用。MySQL 8.0.11：使用 GRANT 创建用户。相反，使用创建用户。遵循这种做法会使 NO_AUTO_CREATE_USER SQL 模式对于 GRANT 语句变得无关紧要，因此它也被删除了。
Taken from here
取自这里
So, your sql_modecan be like this:
所以，你sql_mode可以是这样的：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Or if you're using Docker you can add next command to docker-compose.yml
或者，如果您使用的是 Docker，则可以将下一个命令添加到docker-compose.yml
mysql:
image: mysql:8.0.13
command: --sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ports:
- 13306:${MYSQL_PORT}
回答by Vineet Kumar
For Temporary change use following command
对于临时更改使用以下命令
set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
For permanent change : go to config file /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf and add following lines then restart mysql service
对于永久更改：转到配置文件 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 并添加以下行然后重新启动 mysql 服务
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
回答by Eric Korolev
Access the database as the administrator user (root maybe).
以管理员用户（可能是 root）访问数据库。
Check current SQL_mode
检查当前的 SQL_mode
mysql> SELECT @@sql_mode;
To set a new sql_mode, exit the database, create a file
设置新的sql_mode，退出数据库，创建文件
nano /etc/mysql/conf.d/<filename>.cnf
with your sql_mode content
使用您的 sql_mode 内容
[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Restart Mysql
重启Mysql
mysql> sudo service mysql stop
mysql> sudo service mysql start
We create a file in the folder /etc/mysql/conf.d/because in the main config file /etc/mysql/my.cnfthe command is written to include all the settings files from the folder /etc/mysql/conf.d/
我们在文件夹/etc/mysql/conf.d/ 中创建一个文件， 因为在主配置文件/etc/mysql/my.cnf 中，该命令被写入以包含文件夹/etc/mysql/conf 中的所有设置文件。 d/
回答by shashikant parmar
In my case i have to change file /etc/mysql/mysql.conf.d/mysqld.cnfchange this under [mysqld]
在我的情况下，我必须/etc/mysql/mysql.conf.d/mysqld.cnf在 [ mysqld]下更改文件更改
Paste this line on [mysqld] portion
将此行粘贴到 [ mysqld] 部分
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
回答by ASHOK MANGHAT
If someone want to set it only for the current session then use the following command
如果有人只想为当前会话设置它，请使用以下命令
set session sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
回答by Cobra_Fast
I just had a similar problem where MySQL (5.6.45) wouldn't accept sql_modefrom any config file.
我刚刚遇到了一个类似的问题，其中 MySQL (5.6.45) 不接受sql_mode任何配置文件。
The solution was to add init_file = /etc/mysql/mysql-init.sqlto the config file and then execute SET GLOBAL sql_mode = '';in there.
解决方案是添加init_file = /etc/mysql/mysql-init.sql到配置文件中，然后SET GLOBAL sql_mode = '';在那里执行。
