前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL中的sql_mode参数

MySQL中的sql_mode参数

作者头像
AsiaYe
发布2019-11-06 16:42:14
1.5K0
发布2019-11-06 16:42:14
举报
文章被收录于专栏:DBA随笔
MySQL中的sql_mode参数

sql_mode参数详解

首先我们看看mysql中默认的sql_mode的值是什么:

代码语言:javascript
复制
root@localhost :(none)09:25:15>select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们可以看到,sql_mode一共有7个值,分别是

only_full_group_by

对于group by聚合操作,如果在select中的列,没有在group by中出现,那么将认为这个sql是不合法的,因为列不在group by从句中。这里我们通过一个例子来看:

代码语言:javascript
复制
root@localhost :DBAs09:31:38>select * from t5;
+----+-----+
| id | nam |
+----+-----+
|  1 | yyz |
|  2 | yyz |
|  3 | yyz |
|  4 | yyz |
|  5 | yyz |
|  6 | yyz |
+----+-----+
6 rows in set (0.00 sec)

root@localhost :DBAs09::>select * from t5 group by nam;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'DBAs.t5.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
root@localhost :DBAs09:31:54>select nam from t5 group by nam;
+-----+
| nam |
+-----+
| yyz |
+-----+
1 row in set (0.00 sec)

我们可以看到,当我们select*的时候,包含id和nam两列,sql_mode参数的存在不允许我们对个结果中的nam列进行group by,但是当我们select nam的时候,就可以进行group by操作。当我们去除

only_full_group_by这个选项的时候,可以发现:

代码语言:javascript
复制
root@localhost :DBAs09:35:08>SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.00 sec)

root@localhost :DBAs09:35:19>select * from t5 group by nam;
+----+-----+
| id | nam |
+----+-----+
|  1 | yyz |
+----+-----+
1 row in set (0.00 sec)

现在对于这种select * group by 操作,已经可以执行了。

strict_trans_tables:

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做任何限制。在STRICT_TRANS_TABLES模式下,插入数据时,mysql会严格的进行数据的校验,当发现插入列值未满足要求,直接报告error错误,保证了错误数据无法插入到数据库中。

代码语言:javascript
复制
root@localhost :DBAs09:53:10>insert into t5 (nam) values ('111111111111111111111');
ERROR 1406 (22001): Data too long for column 'nam' at row 1
root@localhost :DBAs09:53:16>select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost :DBAs09::>select @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode                                                            |
+-----------------------------------------------------------------------+
| ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost :DBAs09::>insert into t5 (nam) values ('111111111111111111111');
Query OK, 1 row affected, 1 warning (0.03 sec)

no_zero_in_date:

在严格模式,不接受月或日部分为0的日期。如果使用IGNORE选项,我们为类似的日期插入'1900-00-00'。在非严格模式,可以接受该日期,但会生成警告。举例子如下;

代码语言:javascript
复制
root@localhost :DBAs09:40:47>insert into t5 (tbl_date) values ('1980-00-00');
ERROR 1292 (22007): Incorrect date value: '1980-00-00' for column 'tbl_date' at row 1
root@localhost :DBAs09:40:54>select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost :DBAs09::>SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',''));
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost :DBAs09:41:31>insert into t5 (tbl_date) values ('1980-00-00');
Query OK, 1 row affected (0.02 sec)

no_zero_date:

在严格模式,不要将 '0000-00-00'做为合法日期,如果设置的话,直接报错。在非严格模式,可以接受该日期,但会生成警告。举例如下:

代码语言:javascript
复制
root@localhost :DBAs09:41:35>insert into t5 (tbl_date) values ('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'tbl_date' at row 1
root@localhost :DBAs09:43:58>^C
root@localhost :DBAs09:44:23>SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',''));
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost :DBAs09:45:25>insert into t5 (tbl_date) values ('0000-00-00');
Query OK, 1 row affected (0.01 sec)

no_auto_create_user

防止GRANT自动创建新用户,除非还指定了密码。当我们没有指定密码的时候,如果存在no_auto_create_user选项,则不能通过grant来创建没有密码的用户。

代码语言:javascript
复制
root@localhost :DBAs09:56:32>grant select on *.* to yeyz@'127.0.0.1';
ERROR 1133 (42000): Can't find any matching row in the user table
root@localhost :DBAs09:56:57>select @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode                                                            |
+-----------------------------------------------------------------------+
| ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost :DBAs09::>SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_AUTO_CREATE_USER',''));
Query OK, 0 rows affected, 2 warnings (0.00 sec)

root@localhost :DBAs09:57:52>grant select on *.* to yeyz@'127.0.0.1';
Query OK, 0 rows affected, 1 warning (0.02 sec)

no_engine_substitution:

如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常。

代码语言:javascript
复制
root@localhost :DBAs10:07:42>create table test(id int) ENGINE=FEDERATED;
ERROR 1286 (42000): Unknown storage engine 'FEDERATED'
root@localhost :DBAs10:07:45>SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ENGINE_SUBSTITUTION',''));
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost :DBAs10:08:14>create table test(id int) ENGINE=FEDERATED;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

root@localhost :DBAs10:08:16>show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1286 | Unknown storage engine 'FEDERATED'           |
| Warning | 1266 | Using storage engine InnoDB for table 'test' |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

error_for_division_by_zero:

在严格模式,在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-02-25,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档