求助：执行SET GLOBAL sql_mode命令后仍无法解决sql_mode=only_full_group_by兼容性错误

Hey there, let's tackle this frustrating error and get your queries running smoothly again.

First, let's break down why your existing global command didn't take effect right away:

• The SET GLOBAL change only applies to new database connections after you run it. Your current session (the one where you're testing queries) won't pick up the change unless you reconnect.
• Also, this is a temporary fix—if your MySQL server restarts, the setting will revert back to the default.

Here are your quick, actionable solutions based on your needs:

1. Immediate temporary fix (works right now, resets on restart) #

• First, update your current session to remove the mode immediately:

  SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
  

• Then run the global command to ensure new connections use the updated mode:

  SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
  

  Now any new queries in your current session and future connections should avoid the error.

2. Permanent fix (survives server restarts) #

If you don't want to reapply the setting every time MySQL restarts, modify the configuration file:

• Locate your MySQL config file:
  • Linux: Typically /etc/my.cnf, /etc/mysql/my.cnf, or /etc/mysql/mysql.conf.d/mysqld.cnf
  • Windows: Found in your MySQL installation directory (e.g., C:\Program Files\MySQL\MySQL Server X.X\my.ini)
• Open the file and find the [mysqld] section. Add or update the sql_mode line to exclude ONLY_FULL_GROUP_BY (keep other default modes as needed):

  [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"
  

• Restart your MySQL service:
  • Linux: sudo systemctl restart mysqld
  • Windows: Open Services, find MySQL, and click "Restart"

3. Quick workaround if you can't modify config/restart #

If you're stuck without access to change server settings, you can prepend the session-level SET command to each query. But since you mentioned there are too many queries to adjust one by one, this is a last resort—definitely go for the permanent fix if possible.

内容的提问来源于stack exchange，提问作者user15296676