Mysql的Procedure 參數為NULL問題分析

最近寫過程時發現一個有趣的事情,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:
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參數時,重啟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” 博客

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *