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