首页 › MySQL论坛 › 详解Mysql的 sql_mode（SQL 模式）

论人类社会发展 發表於 2025-11-23 09:38:00

详解Mysql的 sql_mode（SQL 模式）

<p>&nbsp;</p>
<h1 class="header-iWP5WJ auto-hide-last-sibling-br">详解 MySQL 的 sql_mode（SQL 模式）</h1>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">一、sql_mode 核心概念</h2>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space"><code>sql_mode</code>&nbsp;是 MySQL 中语法校验、数据校验、行为兼容的核心配置，它定义了 MySQL 对 SQL 语法的解析规则、数据有效性的校验标准，以及与其他数据库（如 Oracle、SQL Server）的兼容策略。</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">简单来说：<code>sql_mode</code>&nbsp;决定了 MySQL 是 “宽松模式” 还是 “严格模式”，以及支持哪些 SQL 语法、拒绝哪些非法数据。</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">核心作用</h3>
<ol class="auto-hide-last-sibling-br">
<li>规范 SQL 语法：限制或支持特定的 SQL 语法（如是否允许非标准标识符引用）；</li>
<li>数据有效性校验：阻止无效数据插入 / 更新（如非法日期、除以零、字符串截断等）；</li>
<li>兼容其他数据库：模拟其他数据库的 SQL 行为（如 Oracle 的字符串连接符&nbsp;<code>||</code>）；</li>
<li>避免歧义行为：明确 SQL 执行逻辑（如分组查询的字段限制）。</li>
</ol>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">二、查看当前 sql_mode</h2>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">1. 查看会话级 sql_mode（当前连接生效）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">会话级仅对当前数据库连接有效，断开后失效：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="dms2779">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">SELECT @<span class="token variable">@SESSION.sql_mode<span class="token punctuation">;
<span class="token comment">-- 或简写
<span class="token keyword">SELECT @<span class="token variable">@sql_mode<span class="token punctuation">;
</span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">2. 查看全局级 sql_mode（所有新连接生效）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">全局级对所有新建立的连接生效，但不影响已存在的连接：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="klacopu">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">SELECT @<span class="token variable">@GLOBAL.sql_mode<span class="token punctuation">;
</span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">3. 配置文件位置（永久生效）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">MySQL 的默认配置文件（<code>my.cnf</code>&nbsp;或&nbsp;<code>my.ini</code>）中，<code>sql_mode</code>&nbsp;可通过配置项直接设置（后续详解）。</div>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">三、修改 sql_mode 的方式</h2>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">1. 会话级修改（临时生效，重启 / 断开连接失效）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">仅对当前连接有效，适合临时测试场景：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="qdfnlga">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token comment">-- 设置会话级 sql_mode（示例：严格模式+分组限制）
<span class="token keyword">SET <span class="token keyword">SESSION sql_mode <span class="token operator">= <span class="token string">'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'<span class="token punctuation">;
</span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">2. 全局级修改（需重启 MySQL 生效）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">对所有新连接生效，但需重启 MySQL 才能完全生效（或执行&nbsp;<code>FLUSH PRIVILEGES</code>&nbsp;刷新权限）：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="1bm3t4n">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token comment">-- 设置全局级 sql_mode
<span class="token keyword">SET <span class="token keyword">GLOBAL sql_mode <span class="token operator">= <span class="token string">'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'<span class="token punctuation">;

<span class="token comment">-- 刷新权限（无需重启，新连接立即生效）
FLUSH <span class="token keyword">PRIVILEGES<span class="token punctuation">;
</span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">3. 配置文件修改（永久生效，推荐生产环境）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">在 MySQL 配置文件（<code>my.cnf</code>&nbsp;或&nbsp;<code>my.ini</code>）中添加 / 修改&nbsp;<code>sql_mode</code>，重启 MySQL 后永久生效：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--ini hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="ey7ehsf">
<div class="text-OkYU_0">ini</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-ini"><code class="language-ini"><span class="token comment"># Linux/Mac（my.cnf 通常在 /etc/my.cnf 或 /etc/mysql/my.cnf）
<span class="token comment"># Windows（my.ini 通常在 MySQL 安装目录的 bin 文件夹）
<span class="token section"><span class="token punctuation">[<span class="token section-name selector">mysqld<span class="token punctuation">]
<span class="token key attr-name">sql_mode <span class="token punctuation">= <span class="token value attr-value">"<span class="token inner-value">ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
</span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">修改后重启 MySQL：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--bash hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="9auaoyt">
<div class="text-OkYU_0">bash</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">
<div class="hoverable-kRHiX2"><span class="semi-icon semi-icon-default text-14 mr-4">运行</span></div>
<div class="hoverable-kRHiX2" data-testid="code-block-copy" data-popupid="r60hl40">&nbsp;</div>
<div class="hoverable-kRHiX2" data-popupid="m7qsgld">&nbsp;</div>
<div class="hoverable-kRHiX2" data-popupid="paz2r08">&nbsp;</div>
<div class="hoverable-kRHiX2" data-popupid="cmz7gby">&nbsp;</div>
</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-bash"><code class="language-bash"><span class="token comment"># Linux 重启命令
systemctl restart mysqld
<span class="token comment"># 或
<span class="token function">service mysqld restart

<span class="token comment"># Windows 重启命令（cmd 管理员模式）
net stop mysql
net start mysql
</span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">四、常见 sql_mode 取值详解</h2>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space"><code>sql_mode</code>&nbsp;支持多值组合（用逗号分隔），以下是最常用的模式值，按功能分类说明：</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">1. 严格模式相关（核心推荐生产启用）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">严格模式是数据校验的核心，阻止无效数据写入，避免脏数据。</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br mdbox-table-root table-container-cYf_5N" data-scroll-inline-overflow="false" data-scroll-inline-at-start="true" data-scroll-inline-start-overflow="false" data-scroll-inline-at-end="true" data-scroll-inline-end-overflow="false">
<div class="table-scroll-container-Gyf4hQ mdbox-table-scroll-container">
<table>
<thead>
<tr><th>模式值</th><th>作用说明</th></tr>
</thead>
<tbody>
<tr>
<td><code>STRICT_TRANS_TABLES</code></td>
<td>对事务表（如 InnoDB）启用严格模式：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
- 无效数据插入 / 更新直接报错；
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
- 非事务表（如 MyISAM）仍宽松（仅警告，数据截断）。</td>
</tr>
<tr>
<td><code>STRICT_ALL_TABLES</code></td>
<td>对所有表（事务 / 非事务）启用严格模式：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
- 无论表类型，无效数据均报错，不允许写入。</td>
</tr>
</tbody>
</table>
</div>
</div>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">示例：严格模式 vs 宽松模式</h4>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">假设表结构：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="9r3wwmr">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">CREATE <span class="token keyword">TABLE test_strict <span class="token punctuation">(
id <span class="token keyword">INT<span class="token punctuation">,
name <span class="token keyword">VARCHAR<span class="token punctuation">(<span class="token number">5<span class="token punctuation">)<span class="token comment">-- 姓名最长 5 个字符
<span class="token punctuation">) <span class="token keyword">ENGINE<span class="token operator">=<span class="token keyword">InnoDB<span class="token punctuation">;
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<ul class="auto-hide-last-sibling-br">
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">宽松模式（未启用&nbsp;<code>STRICT_TRANS_TABLES</code>）：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
插入超长字符串时，MySQL 自动截断并警告，数据仍写入：</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="5gxwkgb">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">INSERT <span class="token keyword">INTO test_strict <span class="token keyword">VALUES <span class="token punctuation">(<span class="token number">1<span class="token punctuation">, <span class="token string">'abcdefgh'<span class="token punctuation">)<span class="token punctuation">; <span class="token comment">-- 字符串长度 8 &gt; 5
<span class="token comment">-- 警告：Data truncated for column 'name' at row 1
<span class="token comment">-- 结果：name 字段值为 'abcde'（截断后）
</span></span></span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
</li>
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">严格模式（启用&nbsp;<code>STRICT_TRANS_TABLES</code>）：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
插入超长字符串直接报错，数据不写入：</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="z8f2jtf">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">INSERT <span class="token keyword">INTO test_strict <span class="token keyword">VALUES <span class="token punctuation">(<span class="token number">1<span class="token punctuation">, <span class="token string">'abcdefgh'<span class="token punctuation">)<span class="token punctuation">;
<span class="token comment">-- 报错：Data truncation: Data too long for column 'name' at row 1
</span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
</li>
</ul>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">2. 数据有效性校验相关</h3>
<div class="auto-hide-last-sibling-br mdbox-table-root table-container-cYf_5N" data-scroll-inline-overflow="false" data-scroll-inline-at-start="true" data-scroll-inline-start-overflow="false" data-scroll-inline-at-end="true" data-scroll-inline-end-overflow="false">
<div class="table-scroll-container-Gyf4hQ mdbox-table-scroll-container">
<table>
<thead>
<tr><th>模式值</th><th>作用说明</th></tr>
</thead>
<tbody>
<tr>
<td><code>NO_ZERO_IN_DATE</code></td>
<td>禁止日期中的 “月 / 日” 为 0（如&nbsp;<code>'2025-00-10'</code>、<code>'2025-01-00'</code>），严格模式下报错，宽松模式下警告。</td>
</tr>
<tr>
<td><code>NO_ZERO_DATE</code></td>
<td>禁止插入 “全零日期”（<code>'0000-00-00'</code>），严格模式下报错，宽松模式下警告。</td>
</tr>
<tr>
<td><code>ERROR_FOR_DIVISION_BY_ZERO</code></td>
<td>禁止 “除以零” 操作：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
- 整数除法（如&nbsp;<code>5/0</code>）直接报错；
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
- 浮点数除法（如&nbsp;<code>5.0/0</code>）返回&nbsp;<code>NULL</code>&nbsp;并警告（避免完全阻断查询）。</td>
</tr>
<tr>
<td><code>NO_AUTO_VALUE_ON_ZERO</code></td>
<td>插入自增字段时，禁止&nbsp;<code>0</code>&nbsp;作为自增值（仅允许&nbsp;<code>NULL</code>&nbsp;或不指定字段，自动生成自增值）。</td>
</tr>
</tbody>
</table>
</div>
</div>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">示例：禁止全零日期</h4>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">启用&nbsp;<code>NO_ZERO_DATE</code>&nbsp;+ 严格模式：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="uddofby">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">INSERT <span class="token keyword">INTO test_date <span class="token punctuation">(create_time<span class="token punctuation">) <span class="token keyword">VALUES <span class="token punctuation">(<span class="token string">'0000-00-00'<span class="token punctuation">)<span class="token punctuation">;
<span class="token comment">-- 报错：Invalid datetime value: '0000-00-00' for column 'create_time' at row 1
</span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">3. 语法兼容与规范相关</h3>
<div class="auto-hide-last-sibling-br mdbox-table-root table-container-cYf_5N" data-scroll-inline-overflow="true" data-scroll-inline-at-start="true" data-scroll-inline-start-overflow="false" data-scroll-inline-at-end="false" data-scroll-inline-end-overflow="true">
<div class="table-scroll-container-Gyf4hQ mdbox-table-scroll-container">
<table>
<thead>
<tr><th>模式值</th><th>作用说明</th><th>&nbsp;</th><th>&nbsp;</th></tr>
</thead>
<tbody>
<tr>
<td><code>ONLY_FULL_GROUP_BY</code></td>
<td>分组查询（<code>GROUP BY</code>）的严格限制：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
-&nbsp;<code>SELECT</code>&nbsp;后的字段必须是&nbsp;<code>GROUP BY</code>&nbsp;中的字段，或被聚合函数（<code>SUM</code>/<code>AVG</code>/<code>MAX</code>&nbsp;等）包裹；
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
- 避免 “非确定性分组”（即同一分组下非聚合字段的值不唯一）。</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><code>ANSI_QUOTES</code></td>
<td>启用后，字符串只能用单引号&nbsp;<code>'</code>&nbsp;包裹，双引号&nbsp;<code>"</code>&nbsp;视为标识符（如表名、字段名），兼容 SQL 标准。</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><code>PIPES_AS_CONCAT</code></td>
<td>把管道符 `</td>
<td>&nbsp;</td>
<td><code>视为字符串连接符（替代</code>CONCAT ()` 函数），兼容 Oracle 语法。</td>
</tr>
<tr>
<td><code>IGNORE_SPACE</code></td>
<td>允许函数名和括号之间有空格（如&nbsp;<code>SUM (1+2)</code>&nbsp;等同于&nbsp;<code>SUM(1+2)</code>），兼容部分数据库语法。</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><code>NO_ENGINE_SUBSTITUTION</code></td>
<td>当指定的存储引擎（如&nbsp;<code>ENGINE=MyISAM</code>）不存在时，直接报错，而非自动替换为默认引擎（如 InnoDB）。</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</tbody>
</table>
</div>
</div>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">示例 1：ONLY_FULL_GROUP_BY（重点）</h4>
<ul class="auto-hide-last-sibling-br">
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">禁用&nbsp;<code>ONLY_FULL_GROUP_BY</code>（宽松）：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
分组查询允许非分组字段出现在&nbsp;<code>SELECT</code>&nbsp;中，结果可能随机（同一分组下取第一条数据）：</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="wf6fo22">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">SELECT name<span class="token punctuation">, age <span class="token keyword">FROM <span class="token keyword">user <span class="token keyword">GROUP <span class="token keyword">BY name<span class="token punctuation">; <span class="token comment">-- age 未分组，未聚合
<span class="token comment">-- 结果：返回每个 name 对应的第一条 age（不确定）
</span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
</li>
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">启用&nbsp;<code>ONLY_FULL_GROUP_BY</code>（严格）：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
非分组字段必须用聚合函数包裹，否则报错：</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="p9qm6n9">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token comment">-- 正确：age 用聚合函数 AVG() 包裹
<span class="token keyword">SELECT name<span class="token punctuation">, <span class="token function">AVG<span class="token punctuation">(age<span class="token punctuation">) <span class="token keyword">FROM <span class="token keyword">user <span class="token keyword">GROUP <span class="token keyword">BY name<span class="token punctuation">;

<span class="token comment">-- 错误：age 未分组且未聚合
<span class="token keyword">SELECT name<span class="token punctuation">, age <span class="token keyword">FROM <span class="token keyword">user <span class="token keyword">GROUP <span class="token keyword">BY name<span class="token punctuation">;
<span class="token comment">-- 报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
</li>
</ul>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">示例 2：PIPES_AS_CONCAT（兼容 Oracle）</h4>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">启用&nbsp;<code>PIPES_AS_CONCAT</code>&nbsp;后：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="vcuh7ug">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">SELECT <span class="token string">'Hello' <span class="token operator">|| <span class="token string">' ' <span class="token operator">|| <span class="token string">'MySQL' <span class="token keyword">AS result<span class="token punctuation">; <span class="token comment">-- 等同于 CONCAT('Hello', ' ', 'MySQL')
<span class="token comment">-- 结果：result = 'Hello MySQL'
</span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">4. 常用模式组合</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">MySQL 提供了一些预定义的模式组合（本质是多值拼接）：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br mdbox-table-root table-container-cYf_5N" data-scroll-inline-overflow="true" data-scroll-inline-at-start="true" data-scroll-inline-start-overflow="false" data-scroll-inline-at-end="false" data-scroll-inline-end-overflow="true">
<div class="table-scroll-container-Gyf4hQ mdbox-table-scroll-container">
<table>
<thead>
<tr><th>组合模式</th><th>包含的模式值</th><th>适用场景</th></tr>
</thead>
<tbody>
<tr>
<td><code>ANSI</code></td>
<td><code>REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE</code></td>
<td>兼容 SQL 标准，适合多数据库迁移</td>
</tr>
<tr>
<td><code>TRADITIONAL</code></td>
<td><code>STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION</code></td>
<td>“传统严格模式”，模拟严格的数据库行为</td>
</tr>
<tr>
<td><code>ALLOW_INVALID_DATES</code></td>
<td>仅校验日期格式（如&nbsp;<code>MM-DD-YYYY</code>），不校验日期有效性（如&nbsp;<code>2025-02-30</code>&nbsp;视为有效）</td>
<td>兼容旧系统的非法日期数据</td>
</tr>
</tbody>
</table>
</div>
</div>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">五、MySQL 不同版本的默认 sql_mode</h2>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">1. MySQL 5.7+（推荐生产版本）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">默认启用严格模式组合，核心包含：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--plaintext hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="c5x1kcj">
<div class="text-OkYU_0">plaintext</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-plaintext"><code class="language-plaintext">ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
</code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">（注：<code>NO_AUTO_CREATE_USER</code>&nbsp;在 MySQL 8.0 中被移除，因为 8.0 不再支持&nbsp;<code>GRANT</code>&nbsp;语句自动创建用户，必须显式执行&nbsp;<code>CREATE USER</code>）</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">2. MySQL 8.0+</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">默认模式简化（移除&nbsp;<code>NO_AUTO_CREATE_USER</code>）：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--plaintext hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="1bzr0rc">
<div class="text-OkYU_0">plaintext</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-plaintext"><code class="language-plaintext">ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
</code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">3. MySQL 5.6 及以下</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">默认是宽松模式（无&nbsp;<code>STRICT_*</code>、<code>ONLY_FULL_GROUP_BY</code>&nbsp;等），容易产生脏数据，不推荐直接使用。</div>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">六、常见问题与解决方案</h2>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">1. 报错：<code>this is incompatible with sql_mode=only_full_group_by</code></h3>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">原因</h4>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">分组查询中&nbsp;<code>SELECT</code>&nbsp;包含非分组、非聚合字段，违反&nbsp;<code>ONLY_FULL_GROUP_BY</code>&nbsp;规则。</div>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">解决方案</h4>
<ul class="auto-hide-last-sibling-br">
<li>优先方案：优化 SQL，将非分组字段用聚合函数（<code>SUM</code>/<code>AVG</code>/<code>MAX</code>/<code>ANY_VALUE()</code>）包裹：
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="udia4l9">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token comment">-- 用 ANY_VALUE() 取任意值（适合非核心字段）
<span class="token keyword">SELECT name<span class="token punctuation">, ANY_VALUE<span class="token punctuation">(age<span class="token punctuation">) <span class="token keyword">FROM <span class="token keyword">user <span class="token keyword">GROUP <span class="token keyword">BY name<span class="token punctuation">;
</span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
</li>
<li>不推荐方案：临时关闭&nbsp;<code>ONLY_FULL_GROUP_BY</code>（会牺牲数据一致性）：
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="llejcdg">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">SET <span class="token keyword">SESSION sql_mode <span class="token operator">= <span class="token punctuation">(<span class="token keyword">SELECT <span class="token keyword">REPLACE<span class="token punctuation">(@<span class="token variable">@sql_mode<span class="token punctuation">, <span class="token string">'ONLY_FULL_GROUP_BY'<span class="token punctuation">, <span class="token string">''<span class="token punctuation">)<span class="token punctuation">)<span class="token punctuation">;
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
</li>
</ul>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">2. 报错：<code>Invalid datetime value: '0000-00-00'</code></h3>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">原因</h4>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">启用了&nbsp;<code>NO_ZERO_DATE</code>&nbsp;+ 严格模式，禁止插入全零日期。</div>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">解决方案</h4>
<ul class="auto-hide-last-sibling-br">
<li>修正数据：将&nbsp;<code>'0000-00-00'</code>&nbsp;改为合法日期（如&nbsp;<code>'1970-01-01'</code>）；</li>
<li>临时关闭&nbsp;<code>NO_ZERO_DATE</code>（不推荐生产）：
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="i3j0i66">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">SET <span class="token keyword">SESSION sql_mode <span class="token operator">= <span class="token punctuation">(<span class="token keyword">SELECT <span class="token keyword">REPLACE<span class="token punctuation">(@<span class="token variable">@sql_mode<span class="token punctuation">, <span class="token string">'NO_ZERO_DATE'<span class="token punctuation">, <span class="token string">''<span class="token punctuation">)<span class="token punctuation">)<span class="token punctuation">;
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
</li>
</ul>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">3. 迁移 Oracle 数据时，<code>||</code>&nbsp;无法连接字符串</h3>
<h4 class="header-iWP5WJ auto-hide-last-sibling-br">解决方案</h4>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">启用&nbsp;<code>PIPES_AS_CONCAT</code>&nbsp;模式：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="4k05o49">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token keyword">SET <span class="token keyword">GLOBAL sql_mode <span class="token operator">= CONCAT<span class="token punctuation">(@<span class="token variable">@GLOBAL.sql_mode<span class="token punctuation">, <span class="token string">',PIPES_AS_CONCAT'<span class="token punctuation">)<span class="token punctuation">;
FLUSH <span class="token keyword">PRIVILEGES<span class="token punctuation">;
</span></span></span></span></span></span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">七、生产环境最佳实践</h2>
<ol class="auto-hide-last-sibling-br">
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">启用严格模式组合：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
推荐配置（兼容 MySQL 5.7/8.0）：</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--ini hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="3z7j1y1">
<div class="text-OkYU_0">ini</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-ini"><code class="language-ini"><span class="token key attr-name">sql_mode <span class="token punctuation">= <span class="token value attr-value">"<span class="token inner-value">ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
</span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">核心目的：阻止无效数据写入，保证数据一致性。</div>
</li>
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">避免随意关闭核心模式：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
如&nbsp;<code>ONLY_FULL_GROUP_BY</code>、<code>STRICT_TRANS_TABLES</code>&nbsp;等，关闭后可能导致数据歧义或脏数据。</div>
</li>
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">开发 / 测试环境与生产保持一致：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
避免开发环境宽松、生产环境严格导致的 “本地正常，线上报错”。</div>
</li>
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">迁移场景按需调整：</div>
<ul class="auto-hide-last-sibling-br">
<li>从 Oracle 迁移：添加&nbsp;<code>PIPES_AS_CONCAT, ANSI_QUOTES</code>；</li>
<li>从旧系统迁移（含非法日期）：临时关闭&nbsp;<code>NO_ZERO_IN_DATE, NO_ZERO_DATE</code>，同步后修正数据再启用。</li>
</ul>
</li>
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">通过配置文件永久设置：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
避免使用&nbsp;<code>SET GLOBAL</code>&nbsp;临时修改（MySQL 重启后失效），优先在&nbsp;<code>my.cnf/my.ini</code>&nbsp;中配置。</div>
</li>
<li>临时设置模式组合：&nbsp;&nbsp;set @@sql_mode = 'ANSI';&nbsp; 或者&nbsp;&nbsp;set @@sql_mode = 'TRADITIONAL,STRICT_ALL_TABLES';&nbsp;</li>
</ol>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">总结</h2>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space"><code>sql_mode</code>&nbsp;是 MySQL 数据质量和语法兼容的核心配置，生产环境建议启用严格模式 + 必要的兼容模式，通过规范 SQL 语法和校验数据有效性，避免脏数据和歧义行为。理解各模式值的作用，结合业务场景（如迁移、旧系统兼容）灵活调整，是 MySQL 运维和开发的关键技能。</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">==================================================</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">MySQL 默认&nbsp;<code>sql_mode</code>&nbsp;不包含&nbsp;<code>STRICT_ALL_TABLES</code>（即不完整套用&nbsp;<code>TRADITIONAL</code>&nbsp;组合），本质是&nbsp;“平衡严格性与兼容性、适配主流场景”；而模式组合（如&nbsp;<code>TRADITIONAL</code>、<code>ANSI</code>）的核心用处是&nbsp;“提供预定义的、场景化的配置模板，简化用户选型成本”。</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">下面分两部分详细拆解你的问题：</div>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">一、为什么 MySQL 默认&nbsp;<code>sql_mode</code>&nbsp;缺少&nbsp;<code>TRADITIONAL</code>&nbsp;中的&nbsp;<code>STRICT_ALL_TABLES</code>？</h2>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">要理解这个设计，必须先回顾两个关键前提：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<ol class="auto-hide-last-sibling-br">
<li><code>STRICT_TRANS_TABLES</code>&nbsp;vs&nbsp;<code>STRICT_ALL_TABLES</code>&nbsp;的核心差异（之前提到过，这里聚焦 “默认选择逻辑”）：
<ul class="auto-hide-last-sibling-br">
<li><code>STRICT_TRANS_TABLES</code>：仅对&nbsp;事务表（如 InnoDB）&nbsp;严格（无效数据报错），对非事务表（如 MyISAM）宽松（仅警告、截断数据）；</li>
<li><code>STRICT_ALL_TABLES</code>：对&nbsp;所有表（事务 / 非事务）&nbsp;严格（无效数据均报错）。</li>
</ul>
</li>
<li>MySQL 的&nbsp;默认存储引擎演变：
<ul class="auto-hide-last-sibling-br">
<li>MySQL 5.5 后默认引擎改为&nbsp;<code>InnoDB</code>（事务表），至今仍是主流；</li>
<li>早期非事务表（MyISAM）逐渐被淘汰，但仍有旧系统依赖。</li>
</ul>
</li>
</ol>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">基于这两个前提，MySQL 默认不选&nbsp;<code>STRICT_ALL_TABLES</code>&nbsp;的原因的是&nbsp;“避免过度严格导致的副作用，兼顾主流场景与历史兼容”：</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">1. 主流场景已被&nbsp;<code>STRICT_TRANS_TABLES</code>&nbsp;覆盖</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">现在绝大多数业务用的是&nbsp;<code>InnoDB</code>&nbsp;事务表，<code>STRICT_TRANS_TABLES</code>&nbsp;已经能满足 “严格校验数据、阻止脏数据” 的核心需求 —— 事务表支持回滚，一旦数据无效，报错后整个事务回滚，不会出现 “部分数据写入成功、部分失败” 的情况，数据一致性有保障。</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">而&nbsp;<code>STRICT_ALL_TABLES</code>&nbsp;针对的是&nbsp;非事务表（MyISAM），但这类表现在极少用，没必要为了小众场景让所有用户承担 “过度严格” 的成本。</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">2.&nbsp;<code>STRICT_ALL_TABLES</code>&nbsp;对非事务表存在 “数据一致性风险”</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">非事务表的特性是&nbsp;不支持回滚，如果启用&nbsp;<code>STRICT_ALL_TABLES</code>，会出现严重问题：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
假设用&nbsp;<code>MyISAM</code>&nbsp;表批量插入 100 条数据，前 99 条有效，第 100 条无效 —— 此时&nbsp;<code>STRICT_ALL_TABLES</code>&nbsp;会直接报错，但前 99 条数据已经写入表中（无法回滚），导致 “部分数据成功、部分失败” 的脏数据状态。</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">而&nbsp;<code>STRICT_TRANS_TABLES</code>&nbsp;对非事务表的处理是 “宽松模式（警告 + 截断）”，虽然会允许部分不严重的无效数据（如字符串截断），但避免了 “批量插入中断导致的数据碎片化”—— 这是 MySQL 权衡后的选择：对小众的非事务表，优先保证 “插入不中断”，而非 “绝对严格”。</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">3. 历史兼容：避免升级后旧系统大面积报错</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">MySQL 5.6 及以下默认是 “完全宽松模式”，很多旧系统（尤其是依赖 MyISAM 表的系统）可能存在 “字符串截断、无效日期” 等不规范数据插入逻辑。
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
如果默认启用&nbsp;<code>STRICT_ALL_TABLES</code>，这些旧系统升级后会直接大面积报错，迁移成本极高。而&nbsp;<code>STRICT_TRANS_TABLES</code>&nbsp;只针对 InnoDB 严格，对旧系统的非事务表影响极小，兼顾了 “逐步收紧严格性” 和 “历史系统兼容”。</div>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">二、模式组合（如&nbsp;<code>TRADITIONAL</code>、<code>ANSI</code>）的实际用处是什么？</h2>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">模式组合的本质是&nbsp;“MySQL 官方预定义的、经过场景优化的&nbsp;<code>sql_mode</code>&nbsp;集合”—— 它不强制用户使用，但能帮用户&nbsp;“快速选型、减少配置错误”，核心用处有 3 点：</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">1. 简化配置：一键启用 “场景化规则”，不用手动拼接</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">如果没有模式组合，用户要启用 “严格模式”，需要手动拼接 5-6 个模式值：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="ue5u8wf">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token comment">-- 手动拼接严格模式（容易漏写、写错）
<span class="token keyword">SET <span class="token keyword">GLOBAL sql_mode <span class="token operator">= <span class="token string">'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'<span class="token punctuation">;
</span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">而用&nbsp;<code>TRADITIONAL</code>&nbsp;组合，一句话就能实现 “近似效果”（官方已帮你拼好核心严格规则）：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--sql hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="r8jjrzr">
<div class="text-OkYU_0">sql</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-sql"><code class="language-sql"><span class="token comment">-- 一键启用“传统严格模式”（包含上述所有规则+STRICT_ALL_TABLES）
<span class="token keyword">SET <span class="token keyword">GLOBAL sql_mode <span class="token operator">= <span class="token string">'TRADITIONAL'<span class="token punctuation">;
</span></span></span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">对新手或快速部署场景，模式组合能避免 “漏配关键模式”（如忘记加&nbsp;<code>ERROR_FOR_DIVISION_BY_ZERO</code>），降低配置门槛。</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">2. 场景化适配：快速对齐目标行为（兼容其他数据库 / 严格校验）</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">模式组合是为特定场景设计的 “模板”，用户可以根据需求直接选用，不用逐个研究单个模式的作用：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br mdbox-table-root table-container-cYf_5N" data-scroll-inline-overflow="true" data-scroll-inline-at-start="true" data-scroll-inline-start-overflow="false" data-scroll-inline-at-end="false" data-scroll-inline-end-overflow="true">
<div class="table-scroll-container-Gyf4hQ mdbox-table-scroll-container">
<table>
<thead>
<tr><th>组合模式</th><th>核心场景</th><th>解决的问题</th><th>&nbsp;</th><th>&nbsp;</th></tr>
</thead>
<tbody>
<tr>
<td><code>TRADITIONAL</code></td>
<td>需要 “极致严格” 的场景（如金融）</td>
<td>模拟 Oracle/SQL Server 的严格行为，拒绝任何无效数据，适合对数据一致性要求极高的业务</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><code>ANSI</code></td>
<td>多数据库迁移（如从 SQL Server 迁移）</td>
<td>对齐 SQL 标准语法（如双引号当标识符、`</td>
<td>&nbsp;</td>
<td>` 连接字符串），减少 SQL 改写成本</td>
</tr>
<tr>
<td><code>ALLOW_INVALID_DATES</code></td>
<td>兼容旧系统非法日期数据</td>
<td>只校验日期格式（如&nbsp;<code>MM-DD-YYYY</code>），不校验有效性（如&nbsp;<code>2025-02-30</code>），避免旧数据导入报错</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">举个实际例子：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
如果你的业务需要从 Oracle 迁移到 MySQL，直接启用&nbsp;<code>ANSI + PIPES_AS_CONCAT</code>&nbsp;组合，就能直接使用 Oracle 的&nbsp;<code>||</code>&nbsp;字符串连接语法，不用把所有&nbsp;<code>a||b</code>&nbsp;改成&nbsp;<code>CONCAT(a,b)</code>，极大降低迁移成本。</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">3. 统一规范：团队 / 系统间保持一致的 SQL 行为</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">模式组合是 “官方认证” 的配置模板，比团队手动约定的&nbsp;<code>sql_mode</code>&nbsp;更权威、更合理。
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
比如团队约定 “所有环境启用&nbsp;<code>TRADITIONAL</code>&nbsp;模式”，就能避免出现 “开发环境用宽松模式、测试环境用严格模式” 的不一致问题，减少 “本地正常、线上报错” 的排查成本。</div>
<h3 class="header-iWP5WJ auto-hide-last-sibling-br">补充：模式组合不是 “固定不变” 的，支持自定义扩展</h3>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">模式组合是 “基础模板”，用户可以根据需求修改 —— 比如你需要&nbsp;<code>TRADITIONAL</code>&nbsp;的严格性，但不想用&nbsp;<code>STRICT_ALL_TABLES</code>（因为有少量 MyISAM 表），可以这样配置：</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="code-block-element-R6c8c0 light custom-code-block-container--ini hide-indicator disable-theme-style custom-code-block-container">
<div class="code-area-yxsM36 code-area" dir="ltr">
<div class="header-wrapper-Mbk8s6">
<div class="header-IAeXdE">
<div class="title-TXcgFG clickable-cKrA4B" data-popupid="9kx9q54">
<div class="text-OkYU_0">ini</div>
<div class="icon-UKNQeW">&nbsp;</div>
</div>
<div class="action-ysQCxz">&nbsp;</div>
</div>
</div>
<div class="content-y8qlFa code-content light-scrollbar-FFk5j5">
<pre class="container-S2LAkl language-ini"><code class="language-ini">-- 基于 TRADITIONAL 组合，去掉 STRICT_ALL_TABLES
<span class="token key attr-name">sql_mode <span class="token punctuation">= <span class="token value attr-value">"<span class="token inner-value">TRADITIONAL,STRICT_TRANS_TABLES"
</span></span></span></span></code></pre>
</div>
<div class="mask-wrapper-ujeSdZ">&nbsp;</div>
</div>
</div>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">（原理：后配置的模式会覆盖组合中冲突的规则，最终等效于&nbsp;<code>TRADITIONAL</code>&nbsp;去掉&nbsp;<code>STRICT_ALL_TABLES</code>，保留其他严格规则）</div>
<h2 class="header-iWP5WJ auto-hide-last-sibling-br">总结</h2>
<ol class="auto-hide-last-sibling-br">
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">默认&nbsp;<code>sql_mode</code>&nbsp;缺少&nbsp;<code>STRICT_ALL_TABLES</code>&nbsp;的原因：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
为了 “适配主流 InnoDB 事务表、避免非事务表的数据一致性风险、兼容旧系统”，MySQL 选择了 “适度严格” 的&nbsp;<code>STRICT_TRANS_TABLES</code>，而非 “过度严格” 的&nbsp;<code>STRICT_ALL_TABLES</code>，是平衡后的最优解。</div>
</li>
<li>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">模式组合的核心用处：
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
提供 “场景化、预定义的配置模板”，帮用户&nbsp;简化配置、快速适配业务场景（如迁移、严格校验）、统一环境规范，同时支持自定义扩展，兼顾 “便捷性” 和 “灵活性”。</div>
</li>
</ol>
<div class="container-Uxvbjy md-box-line-break wrapper-GYqxgQ undefined">&nbsp;</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">简单说：模式组合是 “给用户的快捷选项”，而默认&nbsp;<code>sql_mode</code>&nbsp;是 “MySQL 为大多数用户选的最优默认选项”—— 两者互补，用户可根据自身场景（如是否用非事务表、是否需要兼容其他数据库）选择直接用组合模式，或基于默认值微调。</div>
<div class="auto-hide-last-sibling-br paragraph-pP9ZLC paragraph-element br-paragraph-space">&nbsp;</div>
</div>

</div>
<div id="MySignature" role="contentinfo">
    <h3>
<br>

<fieldset>
    <legend>版权声明</legend>
<br>
本文原创发表于 博客园，作者为 阿K .    &nbsp;&nbsp;&nbsp;
本文欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则视为侵权。
<br>

欢迎关注本人微信公众号：觉醒的码农，或者扫码进群：<br>
<img src="https://files.cnblogs.com/files/FlyAway2013/wechat111.bmp">
<img src="https://files-cdn.cnblogs.com/files/FlyAway2013/qrcode_for_gh_3f0d03520497_128.bmp">

</fieldset>

</h3><br><br>
来源：https://www.cnblogs.com/FlyAway2013/p/19259520

查看完整版本: 详解Mysql的 sql_mode（SQL 模式）