在測試MySQL腳本時所遇到的問題
近期，筆者在做MySQL腳本的移植和測試工作。在此過程中，發現了MySQL數據庫所存在的一些有待優化的地方，特寫下此文，供相關項目的開發人員參考。
一、存儲過程中所使用的參數名錯誤的問題
例如，在MySQL數據庫中新建如下表tb_testnum：
- drop table if exists tb_testnum;
- create table tb_testnum
- (
- boxnumber varchar(30) not null,
- usertype int not null
- );
- create unique index idx1_tb_testnum on tb_testnum(boxnumber);
同時，創建如下存儲過程pr_dealtestnum：
- drop procedure if exists pr_dealtestnum;
- delimiter //
- create procedure pr_dealtestnum
- (
- in p_boxnumber varchar(30)
- )
- pr_dealtestnum_label:begin
- declare p_boxnumcount int;
- select count(*) into p_boxnumcount from tb_testnum where boxnumber=p_boxnumbe;
- select p_boxnumcount;
- leave pr_dealtestnum_label;
- end;
- //
- delimiter ;
- select 'create procedure pr_dealtestnum ok';
注意，“select count(*) into p_boxnumcount from tb_testnum where boxnumber=p_boxnumbe;”語句中的參數“p_boxnumbe”與輸入參數“p_boxnumber”不一樣(少了一個r)，該參數未在存儲過程中定義。
將存儲過程pr_dealtestnum放到pr_dealtestnum.sql文件中，使用命令行運行該腳本文件，發現MySQL數據庫居然不報錯：
- > mysql -uroot -p'root' -h10.10.10.10 -P3306 -Ddbtest<pr_dealtestnum.sql
- create procedure pr_dealtestnum ok
- create procedure pr_dealtestnum ok
接著，在MySQL數據庫上調用該存儲過程時報錯，提示“p_boxnumbe”不存在：
- mysql> call pr_dealtestnum('2344273522');
- ERROR 1054 (42S22): Unknown column 'p_boxnumbe' in 'where clause'
這樣，問題就出現了，難道MySQL數據庫對存儲過程中所使用的參數名的檢查不嚴格?
二、存儲過程中所使用的參數名前面存在多余符號的問題
這個問題和***個問題類似，只是“參數名錯誤”變成了“在參數名前面有多余的符號”。
例如，我們還是使用問題一中的表tb_testnum，并在表中插入數據：
- insert into tb_testnum(boxnumber,usertype) values('2344273522',1);11
同時，創建如下存儲過程pr_dealtestnum：
- drop procedure if exists pr_dealtestnum;
- delimiter //
- create procedure pr_dealtestnum
- (
- in p_boxnumber varchar(30)
- )
- pr_dealtestnum_label:begin
- declare p_boxnumcount int;
- select count(*) into p_boxnumcount from tb_testnum where boxnumber=@p_boxnumber;
- select p_boxnumcount;
- leave pr_dealtestnum_label;
- end;
- //
- delimiter ;
- select 'create procedure pr_dealtestnum ok';
注意，“select count(*) into p_boxnumcount from tb_testnum where boxnumber=@p_boxnumber;”語句中的參數“@p_boxnumber”是在輸入參數“p_boxnumber”的前面添加了@符號。
將存儲過程pr_dealtestnum放到pr_dealtestnum.sql文件中，使用命令行運行該腳本文件，發現MySQL數據庫居然不報錯：
- > mysql -uroot -p'root' -h10.10.10.10 -P3306 -Ddbtest<pr_dealtestnum.sql
- create procedure pr_dealtestnum ok
- create procedure pr_dealtestnum ok123123
接著，在MySQL數據庫上調用該存儲過程時無報錯，但是輸出的結果不正確：
- mysql> call pr_dealtestnum('2344273522');
- +---------------+
- | p_boxnumcount |
- +---------------+
- | 0 |
- +---------------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
因為我們在前面已經向表tb_testnum中插入了一條數據，所以正確的輸出應該是1，而不是0。
我們將“select count(*) into p_boxnumcount from tb_testnum where boxnumber=@p_boxnumber;”語句中的“@p_boxnumber”中的@符號去掉，再放到MySQL數據庫中運行，發現執行“call pr_dealtestnum(‘2344273522’);”之后輸出的結果就是正確的了。
這也說明了MySQL數據庫對存儲過程中所使用的參數名的檢查不嚴格。
三、存儲過程中向表中插入多余數據的問題
例如，我們還是使用前面兩個問題中的表tb_testnum，如果直接向表中插入多余的數據，則MySQL數據庫會報錯：
- mysql> insert into tb_testnum(boxnumber,usertype) values('2344273523',1,1);
- ERROR 1136 (21S01): Column count doesn't match value count at row 1
報錯的原因是表tb_testnum只有兩列，但是欲向其中插入三列數據。
接著，創建如下存儲過程pr_dealtestnum：
- drop procedure if exists pr_dealtestnum;
- delimiter //
- create procedure pr_dealtestnum
- (
- in p_boxnumber varchar(30),
- in p_usertype int
- )
- pr_dealtestnum_label:begin
- insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype,1);
- leave pr_dealtestnum_label;
- end;
- //
- delimiter ;
- select 'create procedure pr_dealtestnum ok';
注意，“insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype,1);”語句中表的列數和插入數據的列數不一致。
將存儲過程pr_dealtestnum放到pr_dealtestnum.sql文件中，使用命令行運行該腳本文件，發現MySQL數據庫居然不報錯：
- > mysql -uroot -p'root' -h10.10.10.10 -P3306 -Ddbtest<pr_dealtestnum.sql
- create procedure pr_dealtestnum ok
- create procedure pr_dealtestnum ok
然后，在MySQL數據庫上調用該存儲過程時報錯，提示列不匹配：
- mysql> call pr_dealtestnum('2344273523',1);
- ERROR 1136 (21S01): Column count doesn't match value count at row 11212
這樣，又一個問題出現了，難道MySQL數據庫對存儲過程中的數據插入語句不判斷前后列數是否匹配?
四、存儲過程中的select語句的編寫問題
例如，我們還是使用前面的表tb_testnum，并創建如下存儲過程pr_dealtestnum：
- drop procedure if exists pr_dealtestnum;
- delimiter //
- create procedure pr_dealtestnum
- (
- in p_boxnumber varchar(30)
- )
- pr_dealtestnum_label:begin
- declare p_boxnumcount int;
- select p_boxnumcount=count(*) from tb_testnum where boxnumber=p_boxnumber;
- select p_boxnumcount;
- leave pr_dealtestnum_label;
- end;
- //
- delimiter ;
- select 'create procedure pr_dealtestnum ok';
注意，“select p_boxnumcount=count() from tb_testnum where boxnumber=p_boxnumber;”語句是不符合MySQL語法規則的，正確的語句應該是“select count() into p_boxnumcount from tb_testnum where boxnumber=@p_boxnumber;”。
將存儲過程pr_dealtestnum放到pr_dealtestnum.sql文件中，使用命令行運行該腳本文件，發現MySQL數據庫居然不報錯：
- > mysql -uroot -p'root' -h10.10.10.10 -P3306 -Ddbtest<pr_dealtestnum.sql
- create procedure pr_dealtestnum ok
- create procedure pr_dealtestnum ok123123
然后，在MySQL數據庫上調用該存儲過程，輸出結果如下：
- mysql> call pr_dealtestnum('2344273522');
- +------------------------+
- | p_boxnumcount=count(*) |
- +------------------------+
- | NULL |
- +------------------------+
- 1 row in set (0.00 sec)
- +---------------+
- | p_boxnumcount |
- +---------------+
- | NULL |
- +---------------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
以上結果與我們預期的結果相差甚遠。
這樣，又一個問題出現了，難道MySQL數據庫對存儲過程中的每條語句不作嚴格的語法校驗?
五、存儲過程中取整數值的問題
例如，我們創建如下存儲過程pr_calculate：
- drop procedure if exists pr_calculate;
- delimiter //
- create procedure pr_calculate
- (
- in p_intnum1 int,
- in p_intnum2 int
- )
- pr_calculate_label:begin
- declare p_result int;
- set p_result = (p_intnum1+p_intnum2)/10*10;
- select p_result;
- leave pr_calculate_label;
- end;
- //
- delimiter ;
- select 'create procedure pr_calculate ok';
在此存儲過程中，我們想把“(p_intnum1+p_intnum2)/10*10”結果賦給整型變量p_result。
將存儲過程pr_calculate放到pr_calculate.sql文件中，使用命令行運行該腳本文件，結果如下：
- > mysql -uroot -p'root' -h10.10.10.10 -P3306 -Ddbtest<pr_calculate.sql
- create procedure pr_calculate ok
- create procedure pr_calculate ok123123
然后，在MySQL數據庫上調用該存儲過程，輸出結果如下：
- mysql> call pr_calculate(2,1);
- +----------+
- | p_result |
- +----------+
- | 3 |
- +----------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
- mysql> call pr_calculate(2,3);
- +----------+
- | p_result |
- +----------+
- | 5 |
- +----------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
- mysql> call pr_calculate(2,6);
- +----------+
- | p_result |
- +----------+
- | 8 |
- +----------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
- mysql> call pr_calculate(2,9);
- +----------+
- | p_result |
- +----------+
- | 11 |
- +----------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
- mysql> call pr_calculate(2,8);
- +----------+
- | p_result |
- +----------+
- | 10 |
- +----------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
- mysql> call pr_calculate(3,13);
- +----------+
- | p_result |
- +----------+
- | 16 |
- +----------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
以上輸出結果與我們的預期不相符，如對于“call pr_calculate(2,9);”，參數傳進去之后，表達式的值為“set p_result = (2+9)/10*10;”，按照以往的經驗，“(2+9)/10*10”的結果應該為10，即“(2+9)/10”應該為1，但是在MySQL中，該表達式的值卻為11。
這說明了在MySQL數據庫中，對于整型變量的計算規則有所不同。
六、“四舍五入”的問題
例如，直接在MySQL數據庫上執行如下語句：
- mysql> select convert(8/6, signed);
- +----------------------+
- | convert(8/6, signed) |
- +----------------------+
- | 1 |
- +----------------------+
- 1 row in set (0.00 sec)
- mysql> select convert(9/6, signed);
- +----------------------+
- | convert(9/6, signed) |
- +----------------------+
- | 2 |
- +----------------------+
- 1 row in set (0.00 sec)
可以看到，因為“8/6”小于1.5，所以對其取整后的值就為1;而因為“9/6”等于1.5，所以對其取整后的值就為2。這也可以看出，在將小數轉換為整數的過程中，MySQL數據庫遵循的是“四舍五入”的原則。
七、總結
MySQL作為一款廣受歡迎的開源數據庫，目前已被很多中小網站采用，在數據庫市場上占據了25%以上的市場份額。但是，如在本文中所描述的那樣，MySQL也并非是十全十美的。
期待MySQL會不斷進行優化，讓更多的軟件產品來使用它。
【本文是51CTO專欄作者周兆熊的原創文章，作者微信公眾號：周氏邏輯(logiczhou)】
