«

MySQL默认的sql mode怎么设置

时间:2024-7-20 13:07     作者:韩俊     分类: Mysql


本文小编为大家详细介绍“MySQL默认的sql mode怎么设置”,内容详细,步骤清晰,细节处理妥当,希望这篇“MySQL默认的sql mode怎么设置”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧。

SQL mode

MySQL 5.7默认的SQL mode包含

ONLY_FULL_GROUP_BY
,
STRICT_TRANS_TABLES
,
NO_ZERO_IN_DATE
,
NO_ZERO_DATE
,
ERROR_FOR_DIVISION_BY_ZERO
,
NO_AUTO_CREATE_USER
, and
NO_ENGINE_SUBSTITUTION

这是MySQL官网的原文描述:“These modes were added to the default SQL mode in MySQL 5.7: The

ONLY_FULL_GROUP_BY
and
STRICT_TRANS_TABLES
modes were added in MySQL 5.7.5. The
NO_AUTO_CREATE_USER
mode was added in MySQL 5.7.7. The
ERROR_FOR_DIVISION_BY_ZERO
,
NO_ZERO_DATE
, and
NO_ZERO_IN_DATE
modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7.”

show sql mode

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

set sql mode

设置为GLOBAL,那么所有的客户端都会受到影响,不过要拥有SUPER权限才能进行设置,也就是root用户,设置SESSION,那么受影响的只是当前的连接会话。

SET GLOBAL sql_mode ='ONLY_FULL_GROUP_BY'

SET SESSION sql_mode ='ONLY_FULL_GROUP_BY'

下面我们就针对默认设置的这几种SQL mode进行详细的讲解,其他的哪些大家可以去官网参考。

docs.oracle.com/cd/E17952_0…

默认的SQL mode

ONLY_FULL_GROUP_BY

设置了这个值,如果使用GROUP BY,在SELECT后面出现的字段,在GROUP BY后面必须出现,不然报错如下

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'blue.shop.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如下使用的是MySQL默认的sql_mode

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

那么下面的语句就会报错,因为GROUP BY后面只有一个字段,而SELECT * 是查出所有字段,所以就报错。

SELECT * FROM shop GROUP BY article

这样写就不会报错

SELECT * FROM shop GROUP BY article , dealer , price

不过我们不可能使用一个GROUP BY,后面还要跟着所有字段,显然不合理,那么就应该将其关闭,只需要将其去掉就行

STRICT_TRANS_TABLES

严格模式控制MySQL如何处理数据更改语句中的无效或缺失值,如INSERT或UPDATE。一个值可能因多种原因无效。例如,它可能具有列的错误数据类型,或者它可能超出了范围。如果要插入的新行不包含定义中没有显式DEFAULT子句的非null列的值,则该值缺失。

比如我们的某个字段设置不能为NULL,而我们插入的数据这个字段为NULL,那么就不能通过,就会报错如下:

1364 - Field 'dealer' doesn't have a default value

那么这个问题要怎么解决呢?我相信这个问题大家经常遇到,一般是我们在插入数据的时候实体的属性没有赋值,所以导致这个问题,所以我们会去检查代码,然后给属性赋值,另外一种做法就是去除

STRICT_TRANS_TABLES
,这样就不会进行校验,不过是极其不推荐这样做的,因为要我们要保证数据的完整性,所以必须在代码层面做好工作。

NO_ZERO_IN_DATE

NO_ZERO_IN_DATE模式会影响服务器是否允许年部分不为零但月或日部分为0的日期。(该模式影响日期,如“2010-00-01”或“2010-01-00”,但不影响“0000-00-00”。要控制服务器是否允许'0000-00-00',请使用NO_ZERO_DATE模式。)NO_ZERO_IN_DATE的效果还取决于是否启用严格SQL模式,如果没有启用严格SQL模式

STRICT_TRANS_TABLES
,那么启用了NO_ZERO_IN_DATE也没用。

如下SQL的日期月和日为0,启用了严格模式

STRICT_TRANS_TABLES
和NO_ZERO_IN_DATE,那么就会报错。

INSERT INTO `blue`.`shop` (`article`, `dealer` ,`price`,`date`) VALUES ('商品5', '5', 5.00, '2022-00-00');

1292 - Incorrect datetime value: '2022-00-00' for column 'date' at row 1

去除严格模式

STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
就不会报错。

NO_ZERO_DATE

上面的NO_ZERO_IN_DATE可以插入'0000-00-00',如果使用了严格模式

STRICT_TRANS_TABLES
和NO_ZERO_DATE,那么就不可以插入'0000-00-00'。

ERROR_FOR_DIVISION_BY_ZERO

对于INSERT或者UPDATE中,如果被除数为0,那么就会产生错误,数据无法插入,MOD(N,M)也是一样

INSERT INTO `blue`.`shop` (`article`,dealer ,`price`,`date`) VALUES ('商品5', '5', MOD(10,0), '0000-00-00');

对于SELECT,如果被除数为0,那么就会返回NULL,MOD(N,M)也一样。

SELECT price / 0  FROM shop

报错信息: 1365 - Division by 0

NO_AUTO_CREATE_USER

不能使用grant命令创建密码为空的用户。

NO_ENGINE_SUBSTITUTION

如果指定了NO_ENGINE_SUBSTITUTION,我们在创建表或者修改表的时候,如果去指定了不存在或者不支持的存储引擎,那么就会报错,无法创建和修改,如果没有配置NO_ENGINE_SUBSTITUTION,那么就会将我们指定的存储引擎(不支持或者不存在)的存储引擎替换为默认的存储引擎,MySQL5.7后的默认存储引擎为InnoDB,所以就会自动设置为InnoDB。

如下我们创建表,将存储引擎设置为一个不存在的

InnoDBTest
,因为我们去除了NO_ENGINE_SUBSTITUTION,所以不会报错,并且会替换成默认的InnoDB

创建sql

CREATE TABLE store ( `name` VARCHAR ( 255 ) DEFAULT NULL ) ENGINE = InnoDBTest

查看创建过程

SHOW CREATE TABLE store

结果

CREATE TABLE `store` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

MySQL存储引擎

SHOW ENGINES;

标签: mysql

热门推荐