Mysql的Procedure 參數(shù)為NULL問題分析

?最近寫過程時發(fā)現(xiàn)一個有趣的事情，mysql 的procedure 在傳參的過程中，遇到一些“非法”的參數(shù)是有自己獨特的處理方式。例如本來定義是int的參數(shù)，結(jié)果被傳入的是null
，mysql 的procedure會正常執(zhí)行。
庫表結(jié)構(gòu)：
??? 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
);
創(chuàng)建procedure:
?? delimiter //
create procedure p14 (in parameter1 int)
begin
declare variable1 int;
set variable1 = parameter1 + 1;
insert into t(value) values (variable1);
end;
?//
delimiter ;
?
運行結(jié)果：
?
?
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)
?
?
大家注意到?jīng)]有，當參數(shù)parameter1傳入等于5時，表插入6,數(shù)據(jù)正常。
當參數(shù)parameter1傳入為null時，表插入null,這是為什么呢。
?
關于這點大家可以看看聲明變量的語句，文檔給出了這樣的解釋：declare這個語句被用來聲明局部變量。要給變量提供一個默認值，請包含一個default子句。值可以被指定為一個表達式，不需要為一個常數(shù)。如果沒有default子句，初始值為null。
?
上面這樣又有了一個新的問題：null=null+1?哈哈，有點意思了，此時的set variable1 = parameter1 + 1;會有一個怎樣合理的解釋呢？
?
這是王老師給的解釋(第二條很經(jīng)典呀~~~)：
1 null+1=null
因為null表現(xiàn)為“類似指針”，也就是指向“0地址的內(nèi)容”，如果這個內(nèi)容為“null”，則表現(xiàn)為null。這就是指定int也為空的原因。但是，如果“內(nèi)容”有值，則表現(xiàn)為不空，對于mysql而言，是個“隨機數(shù)”或0；當這個地址內(nèi)容存儲時，值就固定了；
?
?
2 如果a=b+1，只有b為null時，a才為null；set a=b+1，是否可理解為set （b+1），a已經(jīng)在‘當前’替換，這樣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 ;
?
執(zhí)行結(jié)果：
?
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服務器模式參數(shù)細節(jié)中可以找到。
mysql服務器可以以不同的sql模式來操作，并且可以為不同客戶端應用不同模式。這樣每個應用程序可以根據(jù)自己的需求來定制服務器的操作模式。
模式定義mysql應支持哪些sql語法，以及應執(zhí)行哪種數(shù)據(jù)驗證檢查。這樣可以更容易地在不同的環(huán)境中使用mysql，并結(jié)合其它數(shù)據(jù)庫服務器使用mysql。
你可以用–sql-mode=”modes”選項啟動mysqld來設置默認sql模式。如果你想要重設，該值還可以為空(–sql-mode =””)。
你還可以在啟動后用set [session|global] sql_mode=’modes’語句設置sql_mode變量來更改sql模式。設置 global變量時需要擁有super權(quán)限，并且會影響從那時起連接的所有客戶端的操作。設置session變量只影響當前的客戶端。任何客戶端可以隨時更改自己的會話 sql_mode值。
modesis是用逗號(‘，’)間隔開的一系列不同的模式。你可以用select @@sql_mode語句查詢當前的模式。默認值是空(沒有設置任何模式)。
strict_trans_tables
為所有存儲引擎啟用嚴格模式。非法數(shù)據(jù)值被拒絕。后面有詳細說明。
· strict_trans_tables
為事務存儲引擎啟用嚴格模式，也可能為非事務存儲引擎啟用嚴格模式。后面有詳細說明。
嚴格模式控制mysql如何處理非法或丟失的輸入值。有幾種原因可以使一個值為非法。例如，數(shù)據(jù)類型錯誤，不適合列，或超出范圍。當新插入的行不包含某列的沒有顯示定義default子句的值，則該值被丟失。
對于事務表，當啟用strict_all_tables或strict_trans_tables模式時，如果語句中有非法或丟失值，則會出現(xiàn)錯誤。語句被放棄并滾動。
對于非事務表，如果插入或更新的第1行出現(xiàn)壞值，兩種模式的行為相同。語句被放棄，表保持不變。如果語句插入或修改多行，并且壞值出現(xiàn)在第2或后面的行，結(jié)果取決于啟用了哪個嚴格選項：
error_for_division_by_zero
在嚴格模式，在insert或update過程中，如果被零除(或mod(x，0))，則產(chǎn)生錯誤(否則為警告)。如果未給出該模式，被零除時mysql返回null。如果用到insert ignore或update ignore中，mysql生成被零除警告，但操作結(jié)果為null。
還有些其他參數(shù)，讀者可以參詳mysql的文檔。
?
當我們給sql_mode 中加入error_for_division_by_zero參數(shù)時,重啟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:
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_division_by_zero參數(shù)時,重啟mysql，試試看：
?
c:documents and settingsadministrator>net stop mysql
mysql 服務正在停止.
mysql 服務已成功停止。
?
?
c:documents and settingsadministrator>net start mysql
?
mysql 服務已經(jīng)啟動成功。
?
?
mysql> select 1/0;
+——+
| 1/0 |
+——+
| null |
+——+
1 row in set (0.00 sec)
?
mysql> show warnings;
empty set (0.02 sec)
這時warnings的內(nèi)容為空。
問題到這里，讀者也知道這是為什么了。有空再試試別的數(shù)據(jù)庫，看看也是不是這樣