Mysql的Procedure參數(shù)如何為NULL問(wèn)題分析

195次閱讀

共計(jì) 4251 個(gè)字符，預(yù)計(jì)需要花費(fèi) 11 分鐘才能閱讀完成。

本篇文章給大家分享的是有關(guān) Mysql 的 Procedure 參數(shù)如何為 NULL 問(wèn)題分析，丸趣 TV 小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著丸趣 TV 小編一起來(lái)看看吧。

最近寫(xiě)過(guò)程時(shí)發(fā)現(xiàn)一個(gè)有趣的事情，Mysql 的 procedure 在傳參的過(guò)程中，遇到一些“非法”的參數(shù)是有自己獨(dú)特的處理方式。例如本來(lái)定義是 int 的參數(shù)，結(jié)果被傳入的是 null
，mysql 的 procedure 會(huì)正常執(zhí)行。
庫(kù)表結(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)大家可以看看聲明變量的語(yǔ)句，文檔給出了這樣的解釋：declare 這個(gè)語(yǔ)句被用來(lái)聲明局部變量。要給變量提供一個(gè)默認(rèn)值，請(qǐng)包含一個(gè) DEFAULT 子句。值可以被指定為一個(gè)表達(dá)式，不需要為一個(gè)常數(shù)。如果沒(méi)有 DEFAULT 子句，初始值為 NULL。

上面這樣又有了一個(gè)新的問(wèn)題：NULL=NULL+1? 哈哈，有點(diǎn)意思了，此時(shí)的 SET variable1 = parameter1 + 1; 會(huì)有一個(gè)怎樣合理的解釋呢？

這是王老師給的解釋(第二條很經(jīng)典呀~~~)：
1 null+1=null
因?yàn)?null 表現(xiàn)為“類(lèi)似指針”，也就是指向“0 地址的內(nèi)容”，如果這個(gè)內(nèi)容為“null”，則表現(xiàn)為 null。這就是指定 INT 也為空的原因。但是，如果“內(nèi)容”有值，則表現(xiàn)為不空，對(duì)于 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)在 lsquo; 當(dāng)前 rsquo; 替換，這樣 A 是誰(shuí)不重要，重要的是 B +1；
本想法沒(méi)有驗(yàn)證，主要是分離不了 SET，而 mysql5 的文檔，有支持這一說(shuō)法，但英文版本是用“替換”，不是中文的“設(shè)置”表達(dá)，感覺(jué)意思更為接近！（SET）

一個(gè)新的問(wèn)題：當(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 |

讀者注意沒(méi)有？這個(gè)也能運(yùn)行成功。其實(shí)這個(gè)問(wèn)題在 mysql 的 SQL 服務(wù)器模式參數(shù)細(xì)節(jié)中可以找到。
MySQL 服務(wù)器可以以不同的 SQL 模式來(lái)操作，并且可以為不同客戶端應(yīng)用不同模式。這樣每個(gè)應(yīng)用程序可以根據(jù)自己的需求來(lái)定制服務(wù)器的操作模式。
模式定義 MySQL 應(yīng)支持哪些 SQL 語(yǔ)法，以及應(yīng)執(zhí)行哪種數(shù)據(jù)驗(yàn)證檢查。這樣可以更容易地在不同的環(huán)境中使用 MySQL，并結(jié)合其它服務(wù)器使用 MySQL。
你可以用 –sql-mode= modes 選項(xiàng)啟動(dòng) d 來(lái)設(shè)置默認(rèn) SQL 模式。如果你想要重設(shè)，該值還可以為空(–sql-mode =)。
你還可以在啟動(dòng)后用 SET [SESSION|GLOBAL] sql_mode= modes 語(yǔ)句設(shè)置 sql_mode 變量來(lái)更改 SQL 模式。設(shè)置 GLOBAL 變量時(shí)需要擁有 SUPER 權(quán)限，并且會(huì)影響從那時(shí)起連接的所有客戶端的操作。設(shè)置 SESSION 變量只影響當(dāng)前的客戶端。任何客戶端可以隨時(shí)更改自己的會(huì)話 sql_mode 值。
Modesis 是用逗號(hào) (lsquo;，rsquo;) 間隔開(kāi)的一系列不同的模式。你可以用 SELECT @@sql_mode 語(yǔ)句查詢當(dāng)前的模式。默認(rèn)值是空(沒(méi)有設(shè)置任何模式)。
STRICT_TRANS_TABLES
為所有存儲(chǔ)引擎啟用嚴(yán)格模式。非法數(shù)據(jù)值被拒絕。后面有詳細(xì)說(shuō)明。
middot; STRICT_TRANS_TABLES
為事務(wù)存儲(chǔ)引擎啟用嚴(yán)格模式，也可能為非事務(wù)存儲(chǔ)引擎啟用嚴(yán)格模式。后面有詳細(xì)說(shuō)明。
嚴(yán)格模式控制 MySQL 如何處理非法或丟失的輸入值。有幾種原因可以使一個(gè)值為非法。例如，數(shù)據(jù)類(lèi)型錯(cuò)誤，不適合列，或超出范圍。當(dāng)新插入的行不包含某列的沒(méi)有顯示定義 DEFAULT 子句的值，則該值被丟失。
對(duì)于事務(wù)表，當(dāng)啟用 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES 模式時(shí)，如果語(yǔ)句中有非法或丟失值，則會(huì)出現(xiàn)錯(cuò)誤。語(yǔ)句被放棄并滾動(dòng)。
對(duì)于非事務(wù)表，如果插入或更新的第 1 行出現(xiàn)壞值，兩種模式的行為相同。語(yǔ)句被放棄，表保持不變。如果語(yǔ)句插入或修改多行，并且壞值出現(xiàn)在第 2 或后面的行，結(jié)果取決于啟用了哪個(gè)嚴(yán)格選項(xiàng)：
ERROR_FOR_DIVISION_BY_ZERO
在嚴(yán)格模式，在 INSERT 或 UPDATE 過(guò)程中，如果被零除(或 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，我們?cè)诳聪逻@個(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 Settings\Administrator net stop mysql
MySQL 服務(wù)正在停止.
MySQL 服務(wù)已成功停止。

C:\Documents and Settings\Administrator 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)容為空。
問(wèn)題到這里，讀者也知道這是為什么了。

以上就是 Mysql 的 Procedure 參數(shù)如何為 NULL 問(wèn)題分析，丸趣 TV 小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注丸趣 TV 行業(yè)資訊頻道。

正文完