MySQLスクリプトのテスト中に発生した問題
16660 ワード
最近、筆者はMySQLスクリプトの移植とテストをしています.この過程でMySQLデータベースに存在するいくつかの最適化すべき点を発見し、関連プロジェクトの開発者の参考にするためにこの文書を特筆した.
一、記憶中に使用したパラメータ名が間違っている問題例えば、MySQLデータベースに次の表tb_を新規作成するtestnum:
同時に、次のようなストレージプロシージャpr_を作成するdealtestnum:
注意、「select count(*)into p_boxnumcount from tb_testnum where boxnumber=p_boxnumbe;文のパラメータ「p_boxnumbe」は、入力パラメータ「p_boxnumber」とは異なり(rが1つ足りない)、このパラメータは記憶中に定義されていない.
ストアド・プロシージャpr_dealtestnum pr_に置くdealtestnum.sqlファイルで、コマンドラインを使用してスクリプトファイルを実行すると、MySQLデータベースがエラーを報告していないことがわかりました.
次に、MySQLデータベースでこのストアド・プロシージャを呼び出すとエラーが発生し、「p_boxnumbe」が存在しないことを示すメッセージが表示されます.
これで問題が発生し、MySQLデータベースはストレージ中に使用されるパラメータ名のチェックが厳しくないのではないでしょうか.
二、保存中に使用するパラメータ名の前に余分な記号が存在する問題この問題は最初の問題と類似しているが、「パラメータ名エラー」が「パラメータ名の前に余分な記号がある」になっただけである.
たとえば、問題1のテーブルtb_を使用します.testnum、テーブルにデータを挿入します.
同時に、次のようなストレージプロシージャpr_を作成するdealtestnum:
注意、「select count(*)into p_boxnumcount from tb_testnum where boxnumber=@p_boxnumber;」文中のパラメータ「@p_boxnumber」は、入力パラメータ「p_boxnumber」の前に@記号が追加されています.
ストアド・プロシージャpr_dealtestnum pr_に置くdealtestnum.sqlファイルで、コマンドラインを使用してスクリプトファイルを実行すると、MySQLデータベースがエラーを報告していないことがわかりました.
次に、MySQLデータベースでこのストレージ・プロシージャを呼び出すと、エラーは発生しませんでしたが、出力された結果は正しくありません.
私たちは前に表tb_に向かっているからです.testnumにデータが挿入されているので、正しい出力は0ではなく1であるべきです.
「select count(*)into p_boxnumcount from tb_testnum where boxnumber=@p_boxnumber;」文の「@p_boxnumber」の@記号を削除してMySQLデータベースで実行すると、「call pr_dealtestnum(‘2344273522’);その後出力された結果が正しい.
これは、MySQLデータベースがストレージ中に使用するパラメータ名のチェックが厳しくないことを示しています.
三、ストレージ中にテーブルに余分なデータを挿入する問題例えば、前の2つの問題のテーブルtb_を使用するtestnum、テーブルに余分なデータを直接挿入すると、MySQLデータベースがエラーを報告します.
エラーの原因は表tb_testnumは2列しかありませんが、3列のデータを挿入したいです.
次に、次のようなストレージプロシージャpr_を作成するdealtestnum:
「insert into tb_testnum(boxnumber,usertype)values(p_boxnumber,p_usertype,1);文のテーブルの列数と挿入データの列数が一致しません.
ストアド・プロシージャpr_dealtestnum pr_に置くdealtestnum.sqlファイルで、コマンドラインを使用してスクリプトファイルを実行すると、MySQLデータベースがエラーを報告していないことがわかりました.
次に、MySQLデータベースでストアド・プロシージャを呼び出すときにエラーが発生し、プロンプト・カラムが一致しません.
このようにして、MySQLデータベースがストレージ中のデータ挿入文に対して前後の列数が一致しているかどうかを判断しないという問題が発生しました.
四、ストレージ中のselect文の作成問題例えば、前のテーブルtb_を使用するtestnumは、次のストレージ・プロシージャpr_を作成します.dealtestnum:
なお、「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データベースでストアド・プロシージャを呼び出し、次のように出力します.
以上の結果は私たちが予想した結果とは程遠い.
このようにして、MySQLデータベースはストレージ中の各文に対して厳格な構文検証を行わないのかという問題が発生しました.
五、記憶過程における整数値の問題例えば、記憶過程pr_を作成するcalculate:
この記憶過程において、「(p_intnum 1+p_intnum 2)/10*10」の結果を整数変数p_に付与したいresult.
ストアド・プロシージャpr_calculateをpr_に置くcalculate.sqlファイルでは、コマンドラインを使用してスクリプトファイルを実行します.結果は次のとおりです.
次に、MySQLデータベースでストアド・プロシージャを呼び出し、次のように出力します.
以上の出力結果は、「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データベース上で直接次の文を実行する.
「8/6」は1.5未満であるため、整列後の値は1であることがわかる.一方、「9/6」は1.5に等しいため、整列した値は2になります.これにより、小数を整数に変換する過程で、MySQLデータベースは「四捨五入」の原則に従っていることがわかります.
七、MySQLは人気のあるオープンソースデータベースとして、現在多くの中小サイトに採用されており、データベース市場で25%以上の市場シェアを占めている.しかし、本明細書で説明したように、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が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データベースでこのストアド・プロシージャを呼び出すとエラーが発生し、「p_boxnumbe」が存在しないことを示すメッセージが表示されます.
mysql> call pr_dealtestnum('2344273522');
ERROR 1054 (42S22): Unknown column 'p_boxnumbe' in 'where clause'これで問題が発生し、MySQLデータベースはストレージ中に使用されるパラメータ名のチェックが厳しくないのではないでしょうか.
二、保存中に使用するパラメータ名の前に余分な記号が存在する問題この問題は最初の問題と類似しているが、「パラメータ名エラー」が「パラメータ名の前に余分な記号がある」になっただけである.
たとえば、問題1のテーブルtb_を使用します.testnum、テーブルにデータを挿入します.
insert into tb_testnum(boxnumber,usertype) values('2344273522',1);同時に、次のようなストレージプロシージャ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 ok次に、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にデータが挿入されているので、正しい出力は0ではなく1であるべきです.
「select count(*)into p_boxnumcount from tb_testnum where boxnumber=@p_boxnumber;」文の「@p_boxnumber」の@記号を削除してMySQLデータベースで実行すると、「call pr_dealtestnum(‘2344273522’);その後出力された結果が正しい.
これは、MySQLデータベースがストレージ中に使用するパラメータ名のチェックが厳しくないことを示しています.
三、ストレージ中にテーブルに余分なデータを挿入する問題例えば、前の2つの問題のテーブル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は2列しかありませんが、3列のデータを挿入したいです.
次に、次のようなストレージプロシージャ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 1このようにして、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 ok次に、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_intnum 1+p_intnum 2)/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 ok次に、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は、より多くのソフトウェア製品を使用するために最適化されることを期待しています.