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 auto_ t;
create table t(c t2(
id int primary key auto_increment,
value float
);
創建procedure:
   delimiter //
CREATE PROCEDURE p14 (IN parameter1 INT)
BEGININ
DECLAREable1. value) VALUES (variable1);
END;
 //
delimiter ;
 
運作結果：
 
 
mysql> call p14(5);Querysql, 1 row affected (0.032 片機sql); +----+-------+
| id | value |
+----+-------+
| 2 |     6 |
+----+-- -----+
1 row in set (0.00 sec)
 
mysql> call p14(null);
Query OK, 1 row affected (0.04 sec)
 
mysql> selectlect from t--
 
mysql> select * -+-------+
| 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); 韎BEGIN
declare variable2 float(5,3); ;
END;
//
delimiter ;
 
執行結果：
 
mysql> call p15(0);
Query OK, 1 row affected (0.03 sec)
+-------+
| 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 SettingsAdministrator>net stop mysql
MySQL 服務正在停止.
MySQL 服務已成功停止。
 
 
C:Documents and SettingsAdministrator>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的內容為空。
問題到這裡，讀者也知道這是為什麼了。有空再試試別的資料庫，看看也是不是這樣