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

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