30°

SQL_MODE

SQL_MODE是MYSQL的一个环境变量, 它定义了MYSQL应该支持的SQL语法,对数据的校验模式等等。

 

它有以下经常值:

ONLY_FULL_GROUP_BY:

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么将认为这个SQL是不合法的,因为列不在GROUP BY从句中

因为有only_full_group_by,select字段只能含有group by的字段,如果含有任何其他字段,都会报1055的错,即只能是

select col1, col2 from tb1 group by col1, col2;

实例:

mysql> select * from tt1;
+----+-------+--------+
| id | name  | gender |
+----+-------+--------+
|  1 | xiong |      0 |
|  2 | ying  |      0 |
|  3 | cai   |      0 |
|  4 | zhang |      0 |
|  5 | li    |      1 |
|  6 | wang  |      1 |
+----+-------+--------+
6 rows in set (0.00 sec)
mysql> select * from tt1 group by name;
ERROR 1055 (42000):
mysql> select name from tt1 group by name;
+-------+
| name  |
+-------+
| cai   |
| li    |
| wang  |
| xiong |
| ying  |
| zhang |
+-------+
6 rows in set (0.00 sec)

所以我们要正常使用group by 的话就必须删除掉only_full_group_by。

可以通过

set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

将only_full_group_by替换成空格。此时

mysql> select * from tt1 group by name;
+----+-------+--------+
| id | name  | gender |
+----+-------+--------+
|  3 | cai   |      0 |
|  5 | li    |      1 |
|  6 | wang  |      1 |
|  1 | xiong |      0 |
|  2 | ying  |      0 |
|  4 | zhang |      0 |
+----+-------+--------+
6 rows in set (0.00 sec)

但是这种方法只是做了session(会话)级别的暂时修改,我们可以通过更改配置文件my.ini来做永久修改。

sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES:

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做任何限制。

NO_ZERO_IN_DATE:

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

NO_ZERO_DATE:

在严格模式,不要将 '0000-00-00'作为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告。

ERROR_FOR_DIVISION_BY_ZERO:

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

NO_AUTO_CREATE_USER:

防止GRANT自动创建新用户,除非还指定了密码。

NO_ENGINE_SUBSTITUTION:

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

本文由【vinci321】发布于开源中国,原文链接:https://my.oschina.net/u/3412738/blog/3073068

全部评论: 0

    我有话说: