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


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

This chapter describes how MySQL relates to the ANSI SQL standards. MySQL has many extensions to the ANSI SQL standards, and here you will find out what they are, and how to use them. You will also find information about functionality missing from MySQL, and how to work around some differences.

5.1 MySQL の ANSI SQL92 に対する拡張

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

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

'!' の後ろにバージョン番号を追加すると、文法は MySQL バー ジョンが使用されるバージョン番号と等しいか大きい場合にのみ実行されます:

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

上記は バージョン 3.23.02 かそれ以上の場合という意味で、そのとき MySQLTEMPORARY キーワードを使用します。

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

5.2 ANSI モードでの MySQL の実行

--ansi オプションつきで mysqld を開始すると、その MySQL の 振舞いが変わります。

5.3 MySQL と ANSI SQL92 との違い

我々は MySQL を ANSI SQL 標準と ODBC SQL 標準に従うように試みてい ますが、いくつかのケースで MySQL は何か違いがあります:

5.4 MySQL に無い機能

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

5.4.1 Sub-selects

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

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);

しかし多くの場合、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

さらに複雑なサブクエリでは、サブクエリを保持するために一時テーブルを作成で きます。しかし、この方法でも動かない場合があります。このケースには、 DELETE ステートメントでもっとも頻繁に遭遇します。DELETE ステー トメントでは、標準 SQL では(sub select の中を除いて)join をサポートして いません。サブクエリが MySQL にサポートされるまで、この状況には2 つの選択肢があります。

最初の選択肢は、手続きプログラミング言語(Perl や PHP のような)を使用して、 SELECT クエリを発行して削除されるレコードのプライマリキーを獲得し、 それから DELETE ステートメント(DELETE FROM ... WHERE ... IN (key1, key2, ...))を構築するためにその値を使用することです。

二番目の選択肢は、対話型 SQL を使用し、DELETE ステートメントのセッ トを(標準 || オペレータの代わりに)MySQL 拡張 CONCAT() を使用して、自動的に組み立てることです。例えば:

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

このクエリをスクリプトファイル中に置き、入力を mysql コマンドライン インタプリタに切替え、その出力を二番目のインタプリタインスタンスにパイプし ます:

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

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

5.4.2 SELECT INTO TABLE

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

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

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

5.4.3 トランザクション

MySQL は現在トランザクションをサポートするため、次の議論は非トラ ンザクション安全テーブル型を使用する場合にだけ有効です。 「7.31 BEGIN/COMMIT/ROLLBACK 構文」節参照。

``なぜ MySQL はトランザクションのデータベースでないのか?'' とか ``なぜ MySQL はトランザクションをサポートしないのか?'' といった質問がしばしばなされます。

MySQL は、データの取り扱いに対し、他のパラダイム、 ``アトミック・オペレーション''をサポートする事を意図して決定しました。 アトミック・オペレーションが同等かあるいはより良いパフォーマンスを 提供するというのが、我々の経験、および考えです。 それでも、我々はトランザクション的データベースパラダイムとプランを評価し、 理解しています。次の数リリースのうちに、テーブル単位を基本に、トランザクショ ン安全テーブルを導入します。 その時我々は、ユーザーが、 スピード重視でアトミック・オペレーションを使用するのか、あるいは アプリケーションでトランザクションの機能を使うのか、 を、選択できるようにしようと考えています。

厳密に integrity 維持するには MySQL の機能をどのように使用するの でしょう? そして、トランザクション的パラダイムでこれらの機能をどのように 比較するのでしょう?

最初に、トランザクションのパラダイムでは、 もし、あなたのアプリケーションが 基本となる部分で ``commit'' の代わりに ``rollback'' の呼び出しに依存しているなら、 トランザクションはより便利になるでしょう。 おまけに、トランザクションは完了できなかった更新や不整合を起こした データが、確実にデータベースに登録されないようにできます。 そのサーバーには、自動的な rollback をする機会が与えられ、 あなたのデータは守られるでしょう。

MySQL はほとんどの場合、更新前の簡単なチェックを含ませることができたり、 あるいはデータベースの矛盾を検査して自動修復や警告の表示を行ったりする 簡単なスクリプトを実行することができるような、 ポテンシャルをあなたに提供します。 MySQL ログの使用や外部へのログの追加を行ったりする事で、 通常、データに不整合のおきたテーブルを完全に修復できることに注意してください。

さらに、``fatal'' transactional updates はアトミックで書き換えることができます。 実際、トランザクションが解決する全ての integrity problems は LOCK TABLES や atomic updates で置き換えることが出来、 さらにこれは、トランザクションのデータベースにある一般的な問題である、 データベースからの自動的な中断を得ること無しに、可能なはずです。 In fact,we will go so far as to say that all integrity problems that transactions solve can be done with LOCK TABLES or atomic updates, ensuring that you never will get an automatic abort from the database, which is a common problem with transactional databases. もしサーバーがダウンすればトランザクションでも全てを失うことを阻止できません。 この場合、トランザクションのシステムでもデータを全て失うでしょう。 The difference between different systems lies in just how small the time-lap is where they could lose data. No system is 100% secure, only ``secure enough''. Even Oracle, reputed to be the safest of transactional databases, is reported to sometimes lose data in such situations.

MySQL を安全にするには、バックアップを取ることと、 更新ログを有効にすることだけです。 これで、あなたが他のトランザクションを持つデータベースで行っているような いかなる場面での修復が可能です。 もちろん、バックアップをとることは、どのデータベースを使用しているかに 関わらず、常に良いことです。

The transactional paradigm has its benefits and its drawbacks. Many users and application developers depend on the ease with which they can code around problems where an abort appears to be, or is necessary, and they may have to do a little more work with MySQL to either think differently or write more. If you are new to the atomic operations paradigm, or more familiar or more comfortable with transactions, do not jump to the conclusion that MySQL has not addressed these issues. Reliability and integrity are foremost in our minds. Recent estimates indicate that there are more than 1,000,000 mysqld servers currently running, many of which are in production environments. We hear very, very seldom from our users that they have lost any data, and in almost all of those cases user error is involved. This is, in our opinion, the best proof of MySQL's stability and reliability.

Lastly, in situations where integrity is of highest importance, MySQL's current features allow for transaction-level or better reliability and integrity. If you lock tables with LOCK TABLES, all updates will stall until any integrity checks are made. If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks. With INSERT DELAYED you can queue inserts into a local queue, until the locks are released, without having the client wait for the insert to complete. 「7.21.2 INSERT DELAYED syntax」節参照.

``Atomic,'' in the sense that we mean it, is nothing magical. It only means that you can be sure that while each specific update is running, no other user can interfere with it, and there will never be an automatic rollback (which can happen on transaction based systems if you are not very careful). MySQL also guarantees that there will not be any dirty reads. You can find some example of how to write atomic updates in the commit-rollback section. 「5.6 COMMIT/ROLLBACK なしでうまくやる方法」節参照.

We have thought quite a bit about integrity and performance, and we believe that our atomic operations paradigm allows for both high reliability and extremely high performance, on the order of three to five times the speed of the fastest and most optimally tuned of transactional databases. We didn't leave out transactions because they are hard to do. The main reason we went with atomic operations as opposed to transactions is that by doing this we could apply many speed optimizations that would not otherwise have been possible.

Many of our users who have speed foremost in their minds are not at all concerned about transactions. For them transactions are not an issue. For those of our users who are concerned with or have wondered about transactions vis-a-vis MySQL, there is a ``MySQL way'' as we have outlined above. For those where safety is more important than speed, we recommend them to use the BDB, GEMINI or InnoDB tables for all their critical data. 「8 MySQL Table types」節参照.

One final note: We are currently working on a safe replication schema that we believe to be better than any commercial replication system we know of. This system will work most reliably under the atomic operations, non-transactional, paradigm. Stay tuned.

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

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

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

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

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

5.4.5 外部キー

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

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

7.20 JOIN 構文」節参照. 「9.5.6 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' ファイル形式にはそれを置く場所はありません。 At a later stage we will implement the foreign key constraints for application that can't easily be coded to avoid them.

5.4.6 ビュー

MySQL はビューをサポートしません。 しかし 4.1 で組み込むことを考えています。

Views are mostly useful for letting users access a set of relations as one table (in read-only mode). Many SQL databases don't allow one to update any rows in a view, but you have to do the updates in the separate tables.

As MySQL is mostly used in applications and on web system where the application writer has full control on the database usage, most of our users haven't regarded views to be very important. (At least no one has been interested enough in this to be prepared to finance the implementation of views).

One doesn't need views in MySQL to restrict access to columns as MySQL has a very sophisticated privilege system. 「6 MySQL のユーザー権限はどのように動くか?」節参照.

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

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

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

UPDATE tbl_name SET credit=credit-!payment!

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

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

しかし MySQL バージョン 3.23 では次を使用できます: 1-- これはコメント

以降の説明では、バージョン 3.23 よりも前の MySQL を実行している場 合だけが対象です:

テキストファイルの 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 levels 0-2.

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

The following mostly applies only for ISAM, MyISAM, and HEAP tables. If you only use transaction-safe tables (BDB, GEMINI or InnoDB tables) in an an update, you can do COMMIT and ROLLBACK also with MySQL. 「7.31 BEGIN/COMMIT/ROLLBACK 構文」節参照.

The problem with handling COMMIT-ROLLBACK efficiently with the above table types would require a completely different table layout than MySQL uses today. この型のテーブルは、自動的にテーブルをクリーンアップする拡張スレッドも必要 とし、ディスク使用量はさらに大きくなります。 これは MySQL を現在 よりも 2〜4 倍遅くしてしまいます。

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

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

我々 MySQL AB で本当に必要としているのは、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 の使用によって、さらに効率的に処理できます。 「24.1.3.126 mysql_insert_id()」節参照.

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

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

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

MySQL が上述のクエリを次に変更したと考えることが可能です:

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.