526互联

记录一个sql_mode的问题

发布时间 2023-12-25 15:19:37作者: 狗子的工具箱

错误记录:
Error 1140: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column '***'; this is incompatible with sql_mode=only_full_group_by

这个错误是因为SQL查询尝试在没有使用GROUP BY子句的情况下检索非聚合列.在MySQL中,当sql_mode设置为only_full_group_by时,SELECT列表中的所有列必须是聚合的(使用SUM/COUNT/MAX等函数)或包含在GROUP BY子句中.

要解决这个错误,可以将非聚合列包含在GROUP BY子句中,或者在SELECT列表中使用聚合函数.例如:

SELECT table_name.主键, COUNT(*) 
FROM table_name 
GROUP table_name.主键;

或者

SELECT MAX(table_name.主键), COUNT(*) 
FROM table_name;

也可以修改mysql的sql_mode.

在MySQL中,sql_mode是用来控制服务器的SQL模式的参数,它可以影响MySQL对SQL语句的解释和执行方式.sql_mode参数可以设置为多个值的组合,每个值代表一种特定的行为或规则.以下是一些常见的sql_mode设置:

  1. ONLY_FULL_GROUP_BY: 在GROUP BY聚合操作中，要求所有的SELECT列都必须在GROUP BY子句中出现，以避免模糊的查询结果.
  2. STRICT_TRANS_TABLES: 在严格模式下进行事务表处理，包括对非法值的插入、更新和截断操作的警告和错误.
  3. NO_ZERO_DATE: 禁止插入'0000-00-00'作为日期。如果启用此模式，将会禁止插入零日期值，并且会对此进行警告或错误.
  4. NO_AUTO_VALUE_ON_ZERO: 禁止将零值插入自增主键列，如果启用此模式，插入零值时将会产生警告或错误.
  5. STRICT_ALL_TABLES: 在严格模式下处理所有表，包括对非法值的插入、更新和截断操作的警告和错误.
  6. ERROR_FOR_DIVISION_BY_ZERO: 在发生除以零的情况时产生错误，而不是返回NULL.
  7. IGNORE_SPACE: 在比较操作符(=, <, >等)之后忽略空格字符.
  8. NO_ENGINE_SUBSTITUTION: 如果指定的存储引擎不可用，不要替换为其他可用的存储引擎，而是产生错误.
  9. PIPES_AS_CONCAT: 将"||"解释为字符串连接操作符而不是按位操作符.
  10. ANSI_QUOTES: 启用ANSI引号，要求双引号用于引用字符串，而单引号用于字符串字面值.
  11. HIGH_NOT_PRECEDENCE: NOT运算符的优先级高于AND和OR运算符.
    这些设置可以根据具体的数据库需求进行组合使用，以满足特定的SQL语句解释和执行要求.

要查看当前的SQL模式,可以在MySQL命令行或客户端中运行以下命令:

SELECT @@sql_mode;

这将返回当前的SQL模式设置.

要修改SQL模式,可以通过修改MySQL配置文件(如my.cnf或my.ini)来永久更改SQL模式.在配置文件中找到[mysqld]部分,并添加或修改sql_mode参数,如下所示:

[mysqld]
sql_mode = "新的SQL模式设置"

在这里,"新的SQL模式设置"是你想要设置的SQL模式值.修改完成后并保存,然后重启Mysql程序.

net stop mysql
net start mysql

另外,也可以在MySQL命令行或客户端中使用SET语句来临时修改SQL模式,例如：

mysql -u 用户名 -p
SET GLOBAL sql_mode = "新的SQL模式设置";

需要注意的是,修改了全局的SQL模式设置对新连接生效,已存在的连接不会受到影响.如果要立即应用修改,可以使用以下命令:

SET GLOBAL sql_mode = "新的SQL模式设置";
SET SESSION sql_mode = "新的SQL模式设置";

这样即可实时更改当前会话的SQL模式设置.

MySQL通常安装在以下默认位置:

  1. linux中,MySQL可执行文件和相关工具通常安装在 /usr/bin 或 /usr/sbin 目录下.MySQL的配置文件通常位于 /etc/mysql 或 /etc/my.cnf.数据库文件通常存储在 /var/lib/mysql 目录下.
  2. windows中,MySQL 可执行文件和相关工具通常安装在 C:\Program Files\MySQL\MySQL Server\ 目录下.