如何sql_mode参数以优化MySQL性能？
- 内容介绍
- 文章标签
- 相关推荐
在 MySQL 中， sql_mode 是一个至关重要的系统变量，它控制着服务器在施行 SQL 查询时的行为模式。合理设置 sql_mode 可以显著提升数据库性能、 确保数据一致性，并避免潜在的错误。本文将深入探讨 sql_mode 的各个选项及其影响，帮助您根据实际需求选择合适的配置方案，差点意思。。
什么是 SQL_MODE？
sql_mode 定义了一组规则和约束，MySQL 在处理 SQL 查询时会严格遵守这些规则。 啊这... 默认情况下MySQL 的 sql_mode 包括以下几个关键选项：
- ONLY_FULL_GROUP_BY确保在 `GROUP BY` 子句中所有非聚合列都出现在 `SELECT` 子句中。
- STRICT_TRANS_TABLES启用严格的转换模式，比方说不允许在 `INSERT` 或 `UPDATE` 操作中插入或更新无效的日期或时间值。
- NO_ZERO_IN_DATE禁止在 `WHERE` 子句中使用包含零的日期或时间值。
- NO_ZERO_DATE禁止使用零作为有效日期值。
- ERROR_FOR_DIVISION_BY_ZERO当除数为零时抛出错误，而不是返回 NULL 或警告。
- NO_AUTOCREATEUSER禁用自动创建用户的功能。
- NOENGINESUBSTITUTION阻止使用替代存储引擎。
SQL MODE 的配置方式
您可以以两种方式配置 sql_mode
- 全局配置修改 MySQL 服务器的配置文件 中的变量。这会影响所有连接到数据库的用户和应用程序。需要重启服务器生效.
- 会话配置使用 `SET GLOBAL sql_mode = '...'` 命令或 `SET SESSION sql_mode = '...'` 命令临时修改当前会话的
sql_mode设置。这只对当前连接有效。
常用 SQL MODE 选项详解
1. ANSI QUOTES
启用 ANSI 引号模式后字符串字面量必须用单引号 包围起来才能被识别为字符串常量,否则会被当做普通字符处理，是吧？。
2. PAD CHAR TO FULL LENGTH
此选项强制将字符字段的值填充到其指定的长度,从而防止截断问题.
3. REAL AS FLOAT
REAL AS FLOAT 将 real 类型转换为 float 类型,这对于避免精度损失非常有用.，精辟。
4. PIPES AS CONCAT
PIPES AS CONCAT 使用管道符 作为字符串连接符,类似于 Oracle 的语法.
5. HIGH NOT PRECEDENCE
HIGH NOT PRECEDENCE 将 NOT 累并充实着。 操作符放在比较表达式前面,从而改变表达式的优先级.
6. NO AUTO VALUE ON ZERO
INSERT INTO t VALUES ; -- ERROR: Cannot insert into table t -- 主要原因是aa是int类型不能插入NULL值, 但如果aa是int类型且允许NULL值，则可以插入了; 但如果aa是int类型且不能插入NULL值，那么insert的时候aa的值必须是数值型; 如果aa不是int类型而为varchar型则可以插入NULL值；但如果aa是非数字类型且不允许空值则无法插入；所以要注意数据类型和是否允许空值； 若启用此参数 则自增字段默认值为 zero 而不是auto increment ; 若不启用此参数 则自增字段默认值为 zero 而不是 auto increment; 若开启该参数后存在自增字段但是设置了非自增字段则报错；若不开启该参数后存在自增字段但是设置了非自增字段则正常；若开启该参数后不存在自增字段但存在非自增字段则正常；若不开启该参数后不存在自增字段但存在非自增字段则报错；-- 此语句说明了当指定了 auto increment 的表时 , 如果没有指定 auto increment 的 column 则会自动生成 primary key; 当没有指定 auto increment 的表时 , 需要手动指定 primary key 并进行赋值或者设置为 default value ; 所以注意表结构设计以及关联关系的设计 -- 比方说 : 表t 没有主键约束, 但是需要进行 auto increment 功能 ; 需要先创建主键约束 , 然后添加 auto increment 功能 ; -- 表t 有主键约束 , 但是没有指定 auto increment 功能 , 可以通过修改表结构来添加 auto increment 功能 ; -- 表t 没有主键约束并且没有指定 auto increment 功能 ; 这时候可以先创建一个与主键对应的 column 并设置 auto increment 功能; 然后再创建主键约束 ; -- 表t 有主键约束并且已经有自动递增功能; 直接添加新的列即可 -- 比方说 : 创建一个名为 id 的 int 类型列 , 并设置 primary key 和 auto increment 功能 -- 表t 有主键约束但是 id 列没有设置为 primary key 或者 autoincrement功能的话 , id 列将会被忽略或者被赋予一个default value; 当不考虑业务逻辑的情况下 , 通常建议按照以下步骤操作 : 1. 创建一张表 t ; 2. 创建一张表 t2 ; 3. 创建一张表 t3 ; -- 注意点 : * 自增字段默认取值为 zero 而不是autoincrement ; * 不使用autoincrement时, 需要手动进行赋值或者设置default value * 如果使用了autoinc属性之后又改回了nonautoincrement属性会导致报错; * 非autoincrement属性也需要进行赋值或者设default value -- 不同数据库对AUTOINCREMENT的支持不同 -- mysql支持AUTOINCREMENT语法 -- mariadb不支持AUTOINCREMENT语法 -- 但是可以使用IDENTITY/SEQUENCE代替 -- postgre 支持identity -- sqlserver支持identity --- 注意事项: * 通常来说AUTOINCREMENT是在表中定义了一个递增的主键列 * 如果你需要在表中添加一个新的递增的主键列 --------，你看啊...
在 MySQL 中， sql_mode 是一个至关重要的系统变量，它控制着服务器在施行 SQL 查询时的行为模式。合理设置 sql_mode 可以显著提升数据库性能、 确保数据一致性，并避免潜在的错误。本文将深入探讨 sql_mode 的各个选项及其影响，帮助您根据实际需求选择合适的配置方案，差点意思。。
什么是 SQL_MODE？
sql_mode 定义了一组规则和约束，MySQL 在处理 SQL 查询时会严格遵守这些规则。 啊这... 默认情况下MySQL 的 sql_mode 包括以下几个关键选项：
- ONLY_FULL_GROUP_BY确保在 `GROUP BY` 子句中所有非聚合列都出现在 `SELECT` 子句中。
- STRICT_TRANS_TABLES启用严格的转换模式，比方说不允许在 `INSERT` 或 `UPDATE` 操作中插入或更新无效的日期或时间值。
- NO_ZERO_IN_DATE禁止在 `WHERE` 子句中使用包含零的日期或时间值。
- NO_ZERO_DATE禁止使用零作为有效日期值。
- ERROR_FOR_DIVISION_BY_ZERO当除数为零时抛出错误，而不是返回 NULL 或警告。
- NO_AUTOCREATEUSER禁用自动创建用户的功能。
- NOENGINESUBSTITUTION阻止使用替代存储引擎。
SQL MODE 的配置方式
您可以以两种方式配置 sql_mode
- 全局配置修改 MySQL 服务器的配置文件 中的变量。这会影响所有连接到数据库的用户和应用程序。需要重启服务器生效.
- 会话配置使用 `SET GLOBAL sql_mode = '...'` 命令或 `SET SESSION sql_mode = '...'` 命令临时修改当前会话的
sql_mode设置。这只对当前连接有效。
常用 SQL MODE 选项详解
1. ANSI QUOTES
启用 ANSI 引号模式后字符串字面量必须用单引号 包围起来才能被识别为字符串常量,否则会被当做普通字符处理，是吧？。
2. PAD CHAR TO FULL LENGTH
此选项强制将字符字段的值填充到其指定的长度,从而防止截断问题.
3. REAL AS FLOAT
REAL AS FLOAT 将 real 类型转换为 float 类型,这对于避免精度损失非常有用.，精辟。
4. PIPES AS CONCAT
PIPES AS CONCAT 使用管道符 作为字符串连接符,类似于 Oracle 的语法.
5. HIGH NOT PRECEDENCE
HIGH NOT PRECEDENCE 将 NOT 累并充实着。 操作符放在比较表达式前面,从而改变表达式的优先级.
6. NO AUTO VALUE ON ZERO
INSERT INTO t VALUES ; -- ERROR: Cannot insert into table t -- 主要原因是aa是int类型不能插入NULL值, 但如果aa是int类型且允许NULL值，则可以插入了; 但如果aa是int类型且不能插入NULL值，那么insert的时候aa的值必须是数值型; 如果aa不是int类型而为varchar型则可以插入NULL值；但如果aa是非数字类型且不允许空值则无法插入；所以要注意数据类型和是否允许空值； 若启用此参数 则自增字段默认值为 zero 而不是auto increment ; 若不启用此参数 则自增字段默认值为 zero 而不是 auto increment; 若开启该参数后存在自增字段但是设置了非自增字段则报错；若不开启该参数后存在自增字段但是设置了非自增字段则正常；若开启该参数后不存在自增字段但存在非自增字段则正常；若不开启该参数后不存在自增字段但存在非自增字段则报错；-- 此语句说明了当指定了 auto increment 的表时 , 如果没有指定 auto increment 的 column 则会自动生成 primary key; 当没有指定 auto increment 的表时 , 需要手动指定 primary key 并进行赋值或者设置为 default value ; 所以注意表结构设计以及关联关系的设计 -- 比方说 : 表t 没有主键约束, 但是需要进行 auto increment 功能 ; 需要先创建主键约束 , 然后添加 auto increment 功能 ; -- 表t 有主键约束 , 但是没有指定 auto increment 功能 , 可以通过修改表结构来添加 auto increment 功能 ; -- 表t 没有主键约束并且没有指定 auto increment 功能 ; 这时候可以先创建一个与主键对应的 column 并设置 auto increment 功能; 然后再创建主键约束 ; -- 表t 有主键约束并且已经有自动递增功能; 直接添加新的列即可 -- 比方说 : 创建一个名为 id 的 int 类型列 , 并设置 primary key 和 auto increment 功能 -- 表t 有主键约束但是 id 列没有设置为 primary key 或者 autoincrement功能的话 , id 列将会被忽略或者被赋予一个default value; 当不考虑业务逻辑的情况下 , 通常建议按照以下步骤操作 : 1. 创建一张表 t ; 2. 创建一张表 t2 ; 3. 创建一张表 t3 ; -- 注意点 : * 自增字段默认取值为 zero 而不是autoincrement ; * 不使用autoincrement时, 需要手动进行赋值或者设置default value * 如果使用了autoinc属性之后又改回了nonautoincrement属性会导致报错; * 非autoincrement属性也需要进行赋值或者设default value -- 不同数据库对AUTOINCREMENT的支持不同 -- mysql支持AUTOINCREMENT语法 -- mariadb不支持AUTOINCREMENT语法 -- 但是可以使用IDENTITY/SEQUENCE代替 -- postgre 支持identity -- sqlserver支持identity --- 注意事项: * 通常来说AUTOINCREMENT是在表中定义了一个递增的主键列 * 如果你需要在表中添加一个新的递增的主键列 --------，你看啊...