Mysql的Procedure參數如何為NULL問題分析

本篇文章給大家分享的是有關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"選項啟動d來設置默認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的內容為空。
問題到這里，讀者也知道這是為什么了。

以上就是Mysql的Procedure參數如何為NULL問題分析，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。