工作中的MySQL踩坑实录🔥

舒一笑 9/25/2024 MySQL踩坑记录

# MySQL的常见SQL模式

ONLY_FULL_GROUP_BY：在使用 GROUP BY 时，所有非聚合列必须出现在 GROUP BY 子句中，避免不明确的查询结果。
STRICT_TRANS_TABLES：在事务表中插入无效值时（如超出字段范围），会引发错误，而不是默默地将值更改为默认值。
NO_ZERO_IN_DATE：日期中不允许出现零（例如，2023-00-01 是无效的）。
NO_ZERO_DATE：不允许插入零日期（如 0000-00-00），会引发错误。
ERROR_FOR_DIVISION_BY_ZERO：在进行除以零的操作时，抛出错误，而不是返回默认值（如 NULL 或 0）。
NO_ENGINE_SUBSTITUTION：如果指定的存储引擎不可用，使用默认存储引擎，而不是返回错误。

# 如何查看当前MySQL的SQL模式

select @@global.sql_mode;

上述语句用于查询 MySQL 数据库的全局 SQL 模式。SQL 模式定义了数据库的行为和特性，例如如何处理空值、字符串比较以及日期和时间的格式等。通过分析返回的结果，可以了解当前数据库的配置，帮助开发者和管理员调整数据库以满足特定的需求或避免潜在的问题。

# 如何查看某个数据库SQL配置

使用以下 SQL 查询：

SELECT @@sql_mode;

当前命令将返回当前会话的 SQL 模式。如果你想查看特定数据库的 SQL 模式，可以使用以下方法：

切换到该数据库：
```
USE your_database_name;
```
查询 SQL 模式：
```
SELECT @@sql_mode;
```

请注意，SQL 模式是全局和会话级别的设置，因此在切换数据库后，你仍然会看到全局 SQL 模式。数据库本身没有独立的 SQL 模式，模式是针对整个 MySQL 实例的。

这里也可以偷懒使用数据库可视化工具DataGrip的功能

# 本次报错的异常

报错信息展示

SQL: SELECT             CAST((@row_number:=@row_number + 1) AS UNSIGNED) AS SEQKEY,             PERIOD_CODE,             b.ORG_TYPE,             INDEX_NAME,             d.INDEX_TYPE_NAME,             INDEX_UNIT,             RESP_ORG_NAME,             STANDARD_SCORE,             INDEX_CODE,             REPLACE(GROUP_CONCAT(CONCAT(a.ORG_CODE,'(',a.TARGET_VALUE,')')), ',', '/') AS ORG_CODE_LIST         FROM             (                 SELECT                     *                 FROM                     epm_perf_index_org             ) AS a                 LEFT JOIN epm_perf_base_suitunit_org AS b ON a.ORG_CODE = b.ORG_CODE                 LEFT JOIN epm_perf_index_type AS d ON a.INDEX_TYPE_KEY = d.INDEX_TYPE_CODE                 CROSS JOIN (SELECT @row_number := 0) AS init         WHERE             1=1                       AND             PERIOD_CODE = ?                     AND a.INDEX_TEMPLATE_CODE LIKE 'I100_%'           AND a.SUIT_UNIT_CODE = ?         GROUP BY INDEX_CODE,         PERIOD_CODE --         b.ORG_TYPE, --         INDEX_NAME, --         d.INDEX_TYPE_NAME, --         INDEX_UNIT, --         RESP_ORG_NAME, --         STANDARD_SCORE;
### Cause: java.sql.SQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dev_bsg_hb_perf_jx.b.ORG_TYPE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dev_bsg_hb_perf_jx.b.ORG_TYPE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

本次的执行查询SQL逻辑展示

 SELECT
            CAST((@row_number:=@row_number + 1) AS UNSIGNED) AS SEQKEY,
            PERIOD_CODE,
            b.ORG_TYPE,
            INDEX_NAME,
            d.INDEX_TYPE_NAME,
            INDEX_UNIT,
            RESP_ORG_NAME,
            STANDARD_SCORE,
            INDEX_CODE,
            REPLACE(GROUP_CONCAT(CONCAT(a.ORG_CODE,'(',a.TARGET_VALUE,')')), ',', '/') AS ORG_CODE_LIST
        FROM
            (
                SELECT
                    *
                FROM
                    epm_perf_index_org
            ) AS a
                LEFT JOIN epm_perf_base_suitunit_org AS b ON a.ORG_CODE = b.ORG_CODE
                LEFT JOIN epm_perf_index_type AS d ON a.INDEX_TYPE_KEY = d.INDEX_TYPE_CODE
                CROSS JOIN (SELECT @row_number := 0) AS init
        WHERE
            1=1
        <if test="periodCode != null and periodCode != '' ">
            AND
            PERIOD_CODE = #{periodCode}
        </if>
          AND a.INDEX_TEMPLATE_CODE LIKE 'I100_%'
          AND a.SUIT_UNIT_CODE = #{suitUnitCode}
        GROUP BY INDEX_CODE

# 原因分析

上述报错的原因就是由于MySQL5.7.44版本默认的SQL查询模式是**ONLY_FULL_GROUP_BY模式**，在当前模式下在，SELECT列表中且不被聚合函数包围的字段，必须在GROUP BY`子句中列出，并且要满足函数依赖关系。

描述一下常见的MySQL聚合函数：

COUNT(): 计数行数。

SUM(): 求和。

AVG(): 计算平均值。

MIN(): 找到最小值。

MAX(): 找到最大值。

GROUP_CONCAT(): 将多个值连接成一个字符串（在MySQL中使用）。

在上述查询的语句中，b.ORG_TYPE、INDEX_NAME、d.INDEX_TYPE_NAME、INDEX_UNIT、RESP_ORG_NAME和STANDARD_SCORE。这些字段由于ONLY_FULL_GROUP_BY模式必须在GROUP BY子句中列出，上述SQL在实现时候没有列出导致的报错。

修改完善的SQL展示

   SELECT
            CAST((@row_number:=@row_number + 1) AS UNSIGNED) AS SEQKEY,
            PERIOD_CODE,
            b.ORG_TYPE,
            INDEX_NAME,
            d.INDEX_TYPE_NAME,
            INDEX_UNIT,
            RESP_ORG_NAME,
            STANDARD_SCORE,
            INDEX_CODE,
            REPLACE(GROUP_CONCAT(CONCAT(a.ORG_CODE,'(',a.TARGET_VALUE,')')), ',', '/') AS ORG_CODE_LIST
        FROM
            (
                SELECT
                    *
                FROM
                    epm_perf_index_org
            ) AS a
                LEFT JOIN epm_perf_base_suitunit_org AS b ON a.ORG_CODE = b.ORG_CODE
                LEFT JOIN epm_perf_index_type AS d ON a.INDEX_TYPE_KEY = d.INDEX_TYPE_CODE
                CROSS JOIN (SELECT @row_number := 0) AS init
        WHERE
            1=1
        <if test="periodCode != null and periodCode != '' ">
            AND
            PERIOD_CODE = #{periodCode}
        </if>
          AND a.INDEX_TEMPLATE_CODE LIKE 'I100_%'
          AND a.SUIT_UNIT_CODE = #{suitUnitCode}
        GROUP BY INDEX_CODE,
        		# 这里补全上述遗失的字段
                 PERIOD_CODE,
                 b.ORG_TYPE,
                 INDEX_NAME,
                 d.INDEX_TYPE_NAME,
                 INDEX_UNIT,
                 RESP_ORG_NAME,
                 STANDARD_SCORE;

江湖码路人俱乐部

Choose mode