当前位置:Gxlcms > mysql > Mysql的Procedure参数为NULL问题分析_MySQL

Mysql的Procedure参数为NULL问题分析_MySQL

时间:2021-07-01 10:21:17 帮助过:22人阅读

bitsCN.com

最近写过程时发现一个有趣的事情,Mysql 的procedure 在传参的过程中,遇到一些“非法”的参数是有自己独特的处理方式。例如本来定义是int的参数,结果被传入的是null
,mysql 的procedure会正常执行。
库表结构:
create database db5;

use db5;

drop table if exists t;
create table t(
id int primary key auto_increment,
value int
);

create table t2(
id int primary key auto_increment,
value float
);
创建procedure:
delimiter //
CREATE PROCEDURE p14 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
INSERT INTO t(value) VALUES (variable1);
END;
//
delimiter ;

运行结果:


mysql> call p14(5);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+----+-------+
| id | value |
+----+-------+
| 2 | 6 |
+----+-------+
1 row in set (0.00 sec)

mysql> call p14(null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t;
+----+-------+
| id | value |
+----+-------+
| 2 | 6 |
| 3 | NULL |
+----+-------+
2 rows in set (0.00 sec)


大家注意到没有,当参数parameter1传入等于5时,表插入6,数据正常。
当参数parameter1传入为null时,表插入NULL,这是为什么呢。

关于这点大家可以看看声明变量的语句,文档给出了这样的解释:declare这个语句被用来声明局部变量。要给变量提供一个默认值,请包含一个DEFAULT子句。值可以被指定为一个表达式,不需要为一个常数。如果没有DEFAULT子句,初始值为NULL。

上面这样又有了一个新的问题:NULL=NULL+1?哈哈,有点意思了,此时的SET variable1 = parameter1 + 1;会有一个怎样合理的解释呢?

这是王老师给的解释(第二条很经典呀~~~):
1 null+1=null
因为null表现为“类似指针”,也就是指向“0地址的内容”,如果这个内容为“null”,则表现为null。这就是指定INT也为空的原因。但是,如果“内容”有值,则表现为不空,对于MYSQL而言,是个“随机数”或0;当这个地址内容存储时,值就固定了;


2 如果A=B+1,只有B为null时,A才为NULL;SET A=B+1,是否可理解为SET (B+1),A已经在‘当前’替换,这样A是谁不重要,重要的是B+1;
本想法没有验证,主要是分离不了SET,而mysql5的文档,有支持这一说法,但英文版本是用“替换”,不是中文的“设置”表达,感觉意思更为接近!(SET)

一个新的问题:当A=1/B,B=0时,也能运行成功吗?

mysql>

delimiter //
CREATE PROCEDURE p15 (IN parameter1 INT)
BEGIN
declare variable2 float(5,3);
SET variable2 =1/ parameter1;
INSERT INTO t2(value) VALUES (variable2);
END;
//
delimiter ;

执行结果:

mysql> call p15(0);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t2;
+----+-------+
| id | value |
+----+-------+
| 1 | NULL |
+----+-------+
1 row in set (0.00 sec)

mysql> call p15(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t2;
+----+-------+
| id | value |
+----+-------+
| 1 | NULL |
| 2 | 1 |

读者注意没有? 这个也能运行成功。其实这个问题在mysql的SQL服务器模式参数细节中可以找到。
MySQL服务器可以以不同的SQL模式来操作,并且可以为不同客户端应用不同模式。这样每个应用程序可以根据自己的需求来定制服务器的操作模式。
模式定义MySQL应支持哪些SQL语法,以及应执行哪种数据验证检查。这样可以更容易地在不同的环境中使用MySQL,并结合其它数据库服务器使用MySQL。
你可以用--sql-mode="modes"选项启动mysqld来设置默认SQL模式。如果你想要重设,该值还可以为空(--sql-mode ="")。
你还可以在启动后用SET [SESSION|GLOBAL] sql_mode='modes'语句设置sql_mode变量来更改SQL模式。设置 GLOBAL变量时需要拥有SUPER权限,并且会影响从那时起连接的所有客户端的操作。设置SESSION变量只影响当前的客户端。任何客户端可以随时更改自己的会话 sql_mode值。
Modesis是用逗号(‘,’)间隔开的一系列不同的模式。你可以用SELECT @@sql_mode语句查询当前的模式。默认值是空(没有设置任何模式)。
STRICT_TRANS_TABLES
为所有存储引擎启用严格模式。非法数据值被拒绝。后面有详细说明。
· STRICT_TRANS_TABLES
为事务存储引擎启用严格模式,也可能为非事务存储引擎启用严格模式。后面有详细说明。
严格模式控制MySQL如何处理非法或丢失的输入值。有几种原因可以使一个值为非法。例如,数据类型错误,不适合列,或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值,则该值被丢失。
对于事务表,当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时,如果语句中有非法或丢失值,则会出现错误。语句被放弃并滚动。
对于非事务表,如果插入或更新的第1行出现坏值,两种模式的行为相同。语句被放弃,表保持不变。如果语句插入或修改多行,并且坏值出现在第2或后面的行,结果取决于启用了哪个严格选项:
ERROR_FOR_pISION_BY_ZERO
在严格模式,在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。
还有些其他参数,读者可以参详mysql的文档。

当我们给sql_mode 中加入ERROR_FOR_pISION_BY_ZERO参数时,重启mysql
mysql> show variables like 'sql_mode';
+---------------+---------------------------------------------------------------
----------------------------+
| Variable_name | Value
|
+---------------+---------------------------------------------------------------
----------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_pISION_BY_ZERO,NO_AUTO_CREATE_
USER,NO_ENGINE_SUBSTITUTION |
+---------------+---------------------------------------------------------------
----------------------------+
1 row in set (0.00 sec)

mysql> select 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

我们看到了1 warning,我们在看下这个warning:
mysql> show warnings;
+-------+------+---------------+
| Level | Code | Message |
+-------+------+---------------+
| Error | 1365 | Division by 0 |
+-------+------+---------------+
1 row in set (0.00 sec)

mysql> exit
Bye
我们把sql_mode 中去掉ERROR_FOR_pISION_BY_ZERO参数时,重启mysql,试试看:

C:/Documents and Settings/Administrator>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。


C:/Documents and Settings/Administrator>net start mysql

MySQL 服务已经启动成功。


mysql> select 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.02 sec)
这时warnings的内容为空。
问题到这里,读者也知道这是为什么了。有空再试试别的数据库,看看也是不是这样

本文出自 “无聊pp” 博客

bitsCN.com

人气教程排行