在测试MySQL脚本时所遇到的问题

MySQL作为一款广受欢迎的开源数据库，目前已被很多中小网站采用，在数据库市场上占据了25%以上的市场份额。但是，如在本文中所描述的那样，MySQL也并非是十全十美的。

近期，笔者在做MySQL脚本的移植和测试工作。在此过程中，发现了MySQL数据库所存在的一些有待优化的地方，特写下此文，供相关项目的开发人员参考。

一、存储过程中所使用的参数名错误的问题

例如，在MySQL数据库中新建如下表tb_testnum：

droptableifexiststb_testnum;
createtabletb_testnum
(
boxnumbervarchar(30)notnull,
usertypeintnotnull
);
createuniqueindexidx1_tb_testnumontb_testnum(boxnumber);

同时，创建如下存储过程pr_dealtestnum：

dropprocedureifexistspr_dealtestnum;
delimiter//
createprocedurepr_dealtestnum
(
inp_boxnumbervarchar(30)
)
pr_dealtestnum_label:begin
declarep_boxnumcountint;
selectcount(*)intop_boxnumcountfromtb_testnumwhereboxnumber=p_boxnumbe;
selectp_boxnumcount;
leavepr_dealtestnum_label;
end;
//
delimiter;
select'createprocedurepr_dealtestnumok';

注意，“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
createprocedurepr_dealtestnumok
createprocedurepr_dealtestnumok

接着，在MySQL数据库上调用该存储过程时报错，提示“p_boxnumbe”不存在：

mysql>callpr_dealtestnum('2344273522');
ERROR1054(42S22):Unknowncolumn'p_boxnumbe'in'whereclause'

这样，问题就出现了，难道MySQL数据库对存储过程中所使用的参数名的检查不严格?

二、存储过程中所使用的参数名前面存在多余符号的问题

这个问题和***个问题类似，只是“参数名错误”变成了“在参数名前面有多余的符号”。

例如，我们还是使用问题一中的表tb_testnum，并在表中插入数据：

insertintotb_testnum(boxnumber,usertype)values('2344273522',1);11

同时，创建如下存储过程pr_dealtestnum：

dropprocedureifexistspr_dealtestnum;
delimiter//
createprocedurepr_dealtestnum
(
inp_boxnumbervarchar(30)
)
pr_dealtestnum_label:begin
declarep_boxnumcountint;
selectcount(*)intop_boxnumcountfromtb_testnumwhereboxnumber=@p_boxnumber;
selectp_boxnumcount;
leavepr_dealtestnum_label;
end;
//
delimiter;
select'createprocedurepr_dealtestnumok';

注意，“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
createprocedurepr_dealtestnumok
createprocedurepr_dealtestnumok123123

接着，在MySQL数据库上调用该存储过程时无报错，但是输出的结果不正确：

mysql>callpr_dealtestnum('2344273522');
+---------------+
|p_boxnumcount|
+---------------+
|0|
+---------------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.00sec)

因为我们在前面已经向表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>insertintotb_testnum(boxnumber,usertype)values('2344273523',1,1);
ERROR1136(21S01):Columncountdoesn'tmatchvaluecountatrow1

报错的原因是表tb_testnum只有两列，但是欲向其中插入三列数据。

接着，创建如下存储过程pr_dealtestnum：

dropprocedureifexistspr_dealtestnum;
delimiter//
createprocedurepr_dealtestnum
(
inp_boxnumbervarchar(30),
inp_usertypeint
)
pr_dealtestnum_label:begin
insertintotb_testnum(boxnumber,usertype)values(p_boxnumber,p_usertype,1);
leavepr_dealtestnum_label;
end;
//
delimiter;
select'createprocedurepr_dealtestnumok';

注意，“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
createprocedurepr_dealtestnumok
createprocedurepr_dealtestnumok

然后，在MySQL数据库上调用该存储过程时报错，提示列不匹配：

mysql>callpr_dealtestnum('2344273523',1);
ERROR1136(21S01):Columncountdoesn'tmatchvaluecountatrow11212

这样，又一个问题出现了，难道MySQL数据库对存储过程中的数据插入语句不判断前后列数是否匹配?

四、存储过程中的select语句的编写问题

例如，我们还是使用前面的表tb_testnum，并创建如下存储过程pr_dealtestnum：

dropprocedureifexistspr_dealtestnum;
delimiter//
createprocedurepr_dealtestnum
(
inp_boxnumbervarchar(30)
)
pr_dealtestnum_label:begin
declarep_boxnumcountint;
selectp_boxnumcount=count(*)fromtb_testnumwhereboxnumber=p_boxnumber;
selectp_boxnumcount;
leavepr_dealtestnum_label;
end;
//
delimiter;
select'createprocedurepr_dealtestnumok';

注意，“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
createprocedurepr_dealtestnumok
createprocedurepr_dealtestnumok123123

然后，在MySQL数据库上调用该存储过程，输出结果如下：

mysql>callpr_dealtestnum('2344273522');
+------------------------+
|p_boxnumcount=count(*)|
+------------------------+
|NULL|
+------------------------+
1rowinset(0.00sec)
+---------------+
|p_boxnumcount|
+---------------+
|NULL|
+---------------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.00sec)

以上结果与我们预期的结果相差甚远。

这样，又一个问题出现了，难道MySQL数据库对存储过程中的每条语句不作严格的语法校验?

五、存储过程中取整数值的问题

例如，我们创建如下存储过程pr_calculate：

dropprocedureifexistspr_calculate;
delimiter//
createprocedurepr_calculate
(
inp_intnum1int,
inp_intnum2int
)
pr_calculate_label:begin
declarep_resultint;
setp_result=(p_intnum1+p_intnum2)/10*10;
selectp_result;
leavepr_calculate_label;
end;
//
delimiter;
select'createprocedurepr_calculateok';

在此存储过程中，我们想把“(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
createprocedurepr_calculateok
createprocedurepr_calculateok123123

然后，在MySQL数据库上调用该存储过程，输出结果如下：

mysql>callpr_calculate(2,1);
+----------+
|p_result|
+----------+
|3|
+----------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.00sec)
mysql>callpr_calculate(2,3);
+----------+
|p_result|
+----------+
|5|
+----------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.00sec)
mysql>callpr_calculate(2,6);
+----------+
|p_result|
+----------+
|8|
+----------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.00sec)
mysql>callpr_calculate(2,9);
+----------+
|p_result|
+----------+
|11|
+----------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.00sec)
mysql>callpr_calculate(2,8);
+----------+
|p_result|
+----------+
|10|
+----------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.00sec)
mysql>callpr_calculate(3,13);
+----------+
|p_result|
+----------+
|16|
+----------+
1rowinset(0.00sec)
QueryOK,0rowsaffected(0.00sec)

以上输出结果与我们的预期不相符，如对于“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>selectconvert(8/6,signed);
+----------------------+
|convert(8/6,signed)|
+----------------------+
|1|
+----------------------+
1rowinset(0.00sec)
mysql>selectconvert(9/6,signed);
+----------------------+
|convert(9/6,signed)|
+----------------------+
|2|
+----------------------+
1rowinset(0.00sec)

可以看到，因为“8/6”小于1.5，所以对其取整后的值就为1;而因为“9/6”等于1.5，所以对其取整后的值就为2。这也可以看出，在将小数转换为整数的过程中，MySQL数据库遵循的是“四舍五入”的原则。

七、总结

MySQL作为一款广受欢迎的开源数据库，目前已被很多中小网站采用，在数据库市场上占据了25%以上的市场份额。但是，如在本文中所描述的那样，MySQL也并非是十全十美的。

期待MySQL会不断进行优化，让更多的软件产品来使用它。

【本文是清一色专栏作者周兆熊的原创文章，作者微信公众号：周氏逻辑(logiczhou)】