Go to the first, previous, next, last section, table of contents.


5 MySQL はどのように標準互換か?

5.1 MySQL の ANSI SQL92 に対する拡張

MySQL は、他の SQL データベース内に見られないであろう、いくつか の拡張を含んでいます。あなたがそれらを使用する場合、コードは他の SQL サー バに移行できなくなるので注意してください。いくつかのケースでは、形式 /*! ... */ のコメントを使用することで、MySQL 拡張を含む 移行可能コードを書くことができます。この場合、MySQL はコメント 内のコードを実行しますが、ほかのSQLサーバーはこれを無視します。例えば:

SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...

If you add a version number after the '!', the syntax will only be executed if the MySQL version is equal or newer than the used version number:

CREATE /*!32302 TEMPORARY */ TABLE (a int);

The above means that if you have 3.23.02 or newer, then MySQL will use the TEMPORARY keyword.

MySQL 拡張を以下に示します:

5.2 Runnning MySQL in ANSI mode

If you start mysqld with the --ansi option, the following behaviour of MySQL changes.

5.3 MySQL differences compared to ANSI SQL92

We try to make MySQL follow the ANSI SQL standard and the ODBC SQL standard, but in some cases MySQL does some things differently:

5.4 MySQL に無い機能

次の機能が MySQL の現在のバージョンにはありません。新しい拡張の 優先度については、次を参考にしてください the MySQL TODO list。これはこのマニュアル中の TODO リストの最新バー ジョンです。 「F MySQL に将来加えたいもの (The TODO)」節参照。

5.4.1 Sub-selects

次は MySQL ではまだ働きません:

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);

However, in many cases you can rewrite the query without a sub select:

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL

For more complicated sub queries you can often create temporary tables to hold the sub query. In some cases, however this option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL does not support joins (except in sub-selects). For this situation there are two options available until subqueries are supported by MySQL.

The first option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the primary keys for the records to be deleted, and then use these values to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1, key2, ...)).

The second option is to use interactive SQL to contruct a set of DELETE statements automatically, using the MySQL extension CONCAT() (in lieu of the standard || operator). For example:

SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
  FROM tab1, tab2
 WHERE tab1.col1 = tab2.col2;

You can place this query in a script file and redirect input from it to the mysql command-line interpreter, piping its output back to a second instance of the interpreter:

prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb

MySQLINSERT ... SELECT ...REPLACE ... SELECT ... だけをサポートします。独立した sub-selects はおそらく 3.24.0 で有効になります。しかし今は他の文脈内で関数 IN() を使用できます。

5.4.2 SELECT INTO TABLE

MySQL はまだ Oracle SQL extension: SELECT ... INTO TABLE ... をサポートしません。 MySQLINSERT INTO ... SELECT ... をサポートします。 これは基本的に同じことです。

代わりに、INSERT INTO ... SELECT ...CREATE TABLE ... SELECT が使用できます。 「7.14 INSERT構文」節参照.

5.4.3 トランザクション

トランザクションはサポートされません。MySQL は間もなく、アトミッ クオペレーション(atomic operations)をサポートします。これはトランザクショ ンに似ていますが、ロールバックはありません。アトミックオペレーションでは、 INSERT/SELECT/whatever コマンドのグループを実行できます。そして、他のス レッドが邪魔しないことが保証されます。この文脈では、通常ロールバックは必 要ではありません。現在、LOCK TABLESUNLOCK TABLES コマ ンドの助けで他のスレッドからの衝突を防ぐことができます。 「7.24 LOCK TABLES/UNLOCK TABLES 構文」節参照.

5.4.4 ストアドプロシジャとトリガ

ストアドプロシジャは、サーバ内でコンパイルでき格納できる SQL コマンドの セットです。一度これが行なわれると、クライアントはクエリ全体の再発行を保 持する必要がなく、ストアドプロシジャを参照できます。これはさらにより速い速度を提 供します。クエリは一度だけ解釈され、より少ないデータがサーバとクライアン ト間で送信されるからです。サーバ内に関数ライブラリを持つことにより概念レ ベルを上げることもできます。

トリガは特別なイベントが発生した時に呼び出されるストアドプロシジャです。 例えば、トランザクションテーブルからレコードが削除される度にトリガされ、 トランザクションが削除された時に自動的に対応する顧客を顧客テーブルから削 除するというストアドプロシジャをインストールすることができます。

計画されている言語の更新はストアドプロシジャを処理できるようになりますが、 トリガは除きます。トリガは通常全てを、それらを必要としないクエリでさえも 遅くします。

MySQL がストアドプロシジャを得る時を知るには、 「F MySQL に将来加えたいもの (The TODO)」節 を参照 してください。

5.4.5 外部キー

注意: SQL の外部キーはテーブルを結合するためには使用できませんが、指示の 完全性の検査のために良く使用されます。SELECT ステートメントで複数 テーブルから結果を得たい場合、テーブルの結合によってこれを行ないます!

SELECT * from table1,table2 where table1.id = table2.id;

7.13 JOIN 構文」節参照. 「8.3.5 Using foreign keys」節参照.

MySQL 内での FOREIGN KEY 構文は、他の SQL ベンダの CREATE TABLE コマンドとの互換のためだけに存在します; これは何も行 ないません。ON DELETE ... がない FOREIGN KEY 構文は、目的 の作成のために主に使われます。いくつかの ODBC アプリケーションは、自動的 に WHERE 節を提供するために、これを使用しますが、しかしこれは通常 簡単に無効にできます。FOREIGN KEY は時々強制チェックとして使用さ れます。しかし、テーブルに正しい順で行が挿入される場合、このチェックは実 際には不要です。いくつかのアプリケーションがそれが存在することを要求する ため MySQL はこれらの節だけをサポートします(それが動作するかど うかに関わらず!)。

MySQL では、外部キーを持つテーブルからレコードを削除する時に適 切な DELETE ステートメントをアプリケーションに追加することで、 ON DELETE ... が実装されていないという問題を回避できます。実際に は、これは速く(いくつかの場合はより速く)そして外部キーの使用よりもさらに 可搬性があります。

近い将来、少なくとも情報が保存され、そして mysqldump と ODBC によっ て取り出されるように、我々は FOREIGN KEY 実装を拡張します。

5.4.5.1 外部キーを使用しない理由

我々がどこから始めるのかわからない FOREIGN KEY には多くの問題があ ります:

外部キーの良い面は、ODBC と他のいくつかのクライアントプログラムに、どの ようにテーブルが接続されるかを見る機能を与え、これを使用して接続図を示し、 生成アプリケーションの手助けをすることだけです。

MySQL は間もなく、クライアントがどのようにオリジナル接続が作成 されたかを問い合わせ、回答を受けられるように FOREIGN KEY 定義を格 納します。現在の `.frm' ファイル形式にはそれを置く場所はありません。

5.4.6 ビュー

MySQL はビューをサポートしません。しかしこれは TODO にあります。

5.4.7 コメント開始としての `--'

他のいくつかの SQL データベースは、`--' をコメントの開始のために使 用します。MySQL`#' をコメント開始文字とします。 mysql コマンドラインツールが `--' で始まる全ての行を削除した としてもです。MySQL では C コメントスタイル /* これはコメ ント */ も使用できます。 「7.29 コメント 構文」節参照。

MySQL 3.23.3 以上は `--' をサポートしません; この退化したコメントスタ イルは、次のコードのように !payment! の payment の値を自動的に挿 入するような何かを使用して自動的に生成される SQL クエリで多くの問題を引 き起こすためです:

UPDATE tbl_name SET credit=credit-!payment!

payment の値が負の場合に何が起きると思いますか?

1--1 は正しい SQL なので、我々は `--' をコメント開始と見なす ことはひどいことだと思います。

In MySQL 3.23 you can however use: 1-- This is a comment

The following discussing only concerns you if you are running an MySQL version earlier than 3.23:

テキストファイルの SQL プログラムが `--' コメントを含んでいる場合、 次を使用すべきです:

shell> replace " --" " #" < text-file-with-funny-comments.sql \
         | mysql database

以下の使用の代わりに:

shell> mysql database < text-file-with-funny-comments.sql

次の方法でも、コマンドファイル中の `--' コメントを `#' コメン トに変更できます:

shell> replace " --" " #" -- text-file-with-funny-comments.sql

それらは次のコマンドで戻してください:

shell> replace " #" " --" -- text-file-with-funny-comments.sql

5.5 MySQL が準拠している標準

Entry level SQL92. ODBC level 0-2.

5.6 COMMIT/ROLLBACK なしでうまくやる方法

MySQLCOMMIT-ROLLBACK をサポートしません。問題 は、COMMIT-ROLLBACK の効率的な処理は、MySQL が現 在使用しているものとは完全に異なったテーブルの形式を要求することです。 MySQL は、自動的にテーブルをクリーンアップする拡張スレッドも必 要とし、ディスク使用量はさらに大きくなります。これは MySQL を現 在よりも 2〜4 倍遅くしてしまいます。MySQL は他のほとんど全ての SQL データベースよりもとても速いです(概して少なくとも 2〜3 倍速い)。この 理由の一つは、COMMIT-ROLLBACK が欠けていることです。

今、我々は SQL サーバ言語(ストアドプロシジャのようなもの)の実装にさらに 賛成です。これで、本当に COMMIT-ROLLBACK を必要とするのは めったにありません。これはさらに良い性能も与えるでしょう。

トランザクションを必要とするループは、通常は LOCK TABLES の助けで コード化でき、そして fly 上でレコードの更新時にはカーソルは必要としませ ん。

TODO 上にトランザクションとカーソルがありますが、全く優先されていません。 それが実装される場合は、CREATE TABLE のオプションとしてでしょう。 これは COMMIT-ROLLBACK はそのテーブルだけで働き、そのテー ブルだけが遅くなるということを意味します。

我々 TcX で本当に必要としているのは、100% 標準のデータベースではなくて、 本当に速いデータベースです。速度低下なしでそれらの機能を実装する方法を見つけた ときには、我々はそれを行なうでしょう。しばらくは行なうべきさらに重要なこ とが多くあります。今の我々の優先度については TODO をチェックしてください。 高レベルのサポートを持つ顧客はこれを変えることができ、再び優先順位づけが 行なわれます。

現在の問題は実際には ROLLBACK です。ROLLBACK なしでも LOCK TABLESCOMMIT アクションのいくつかの種類を行なうこ とができます。ROLLBACK をサポートするためには、更新される全ての古 いレコードを格納し、ROLLBACK が発行された場合に開始位置に全てを戻 すように、MySQL を変更しなければなりません。単純な場合には、こ れを行なうには難しくありません(現在の isamlog をこの目的に使用し ます)。しかし、ALTER/DROP/CREATE TABLE での ROLLBACK の実 装を行うことはとても困難です。

ROLLBACK の使用の回避のために、次の方法を使用することができます:

  1. LOCK TABLES ... をアクセスしたいテーブルの全てをロックするために 使用します
  2. 条件のテスト。
  3. 全て OK なら更新。
  4. UNLOCK TABLES をロックの解除に使用します

これは普通は ROLLBACK 可能なトランザクションの使用よりも速いです が、常にではありません。この解が処理できない状況は、更新中に誰かがスレッ ドを kill する時だけです。この場合、全てのロックはリリースされますが、い くつかの更新は実行されません。

1回のオペレーション中でレコードを更新する関数も使用できます。次のテクニック によってとても効率的なアプリケーションを得ることができます。

例えば、いくつかの顧客情報で更新を行なっている時、我々は変更された顧客デー タだけを更新し、変更されていないデータはテストせず、変更されたデータに依 存して変更されたデータがオリジナルの行と比較されます。変更のテストは UPDATE ステートメント内の WHERE 節で行われます。レコードが 更新されなかった時は、我々はクライアントにメッセージ: "Some of the data you have changed has been changed by another user" を与え、そしてそれか ら古い行と新しい行をウィンドウ内で表示します。ユーザは顧客レコードのどち らのバージョンを使用すべきかを決定できます。

これは ``column locking'' に似たものを我々に与えますが、実際には十分です。 なぜなら、我々はそれらの現在の値に関連した値を持つフィールドだけを更新するから です。これは、典型的な UPDATE ステートメントが次のように見えると いうことを意味します:

UPDATE tablename SET pay_back=pay_back+'relative change';

UPDATE customer
  SET
    customer_date='current_date',
    address='new address',
    phone='new phone',
    money_he_owes_us=money_he_owes_us+'new_money'
  WHERE
    customer_id=id AND address='old address' AND phone='old phone';

見ての通り、他のクライアントが pay_back または money_he_owes_us フィールドの値を変更したとしても、これはとても効率的で 働きます。

多くの場合、ユーザは ROLLBACK そして/または LOCK TABLES が いくつかのテーブルでユニークな識別子を管理することを望みます。これは、 AUTO_INCREMENT フィールドと SQL LAST_INSERT_ID() 関数や C API 関 数 mysql_insert_id の使用によって、さらに効率的に処理できます。 「20.4.29 mysql_insert_id()」節参照.

TcX では、我々はいつでもそれを回避してコード可能なので、我々は行レベルロッ クを必要としていません。本当に行ロックを必要とするケースもありますが、し かしこれは非常にまれです。行レベルロックを望むのなら、テーブル内でフラグ 項目を使用して、次のようにして行なえます:

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

行が見つかり、オリジナル行内で row_flag が既に 1 でない場合、 MySQL は影響された行数として 1 を返します。

You can think of it as MySQL changed the above query to:

UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;


Go to the first, previous, next, last section, table of contents.