[分享]Mysql的Procedure 参数为NULL问题分析_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2168 | 回复: 0

主题： [分享]Mysql的Procedure 参数为NULL问题分析

gunrose

注册用户

等级：新兵
经验：36
发帖：79
精华：0
注册：2011-7-21
状态：离线
发送短消息息给gunrose

加好友发送短消息息给gunrose

发消息

发表于：

2014-12-2 9:58:50 | [全部帖] [楼主帖]

楼主

最近写过程时发现一个有趣的事情，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_DIVISION_BY_ZERO

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

还有些其他参数，读者可以参详mysql的文档。

当我们给sql_mode 中加入ERROR_FOR_DIVISION_BY_ZERO参数时,重启mysql

mysql> show variables like 'sql_mode';
+---------------+---------------------------------------------------------------
----------------------------+
| Variable_name | Value
|
+---------------+---------------------------------------------------------------
----------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_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:

我们把sql_mode 中去掉ERROR_FOR_DIVISION_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的内容为空。

问题到这里，读者也知道这是为什么了。有空再试试别的数据库，看看也是不是这样。