MySQLの基礎を学ぼう［準備編］：今から始める MySQL入門（4）（2/3 ページ）

» 2007年03月26日 00時00分公開

MySQL5.0の追加機能

　先ほど紹介したMySQLの操作に加え、MySQL5.0では新たな機能が追加されています。

　ここでは、次回以降で使用する機能を中心に紹介します。MySQL5.0で追加された機能については「エンタープライズ市場に向かうMySQL 5.0」も参考にしてください。

テーブルタイプとキャラクターセットを指定

　MySQL5.0では、CREATE TABLEクエリー実行時に「テーブルタイプ」と「キャラクターセット」を設定できます。MySQLでは、テーブルタイプごとに使用できる機能が限定されます。

　例えば、トランザクションをサポートする「InnoDB」、高いパフォーマンスと高機能を兼ね備えた「MyISAM」、一時的な作業テーブルに使用される「MEMORY」など、数多くのテーブルタイプが選択できます。現在使用しているMySQLでサポートしているテーブルタイプを見る場合は、「SHOW ENGINES;」を実行します。

　次回紹介するサンプルでは、トランザクション処理を必要とするため、テーブルタイプInnoDBを指定します。また、キャラクターセットもPHPコード同様に「UTF-8」で統一します。

mysql> CREATE TABLE `test_table2` (

    ->   `id` int(10) default NULL,

    ->   `memo` char(240) default NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;　←テーブルタイプ「InnoDB」の指定とキャラクターセットUTF-8の指定

クライアント側の文字コードを設定

　MySQL5.0では、クラインアントとサーバで異なるキャラクターセットが利用されていた場合に自動変換機能が働きます。便利な機能ですが、自動変換が完全に動作しない場合があり、文字化けのような現象を引き起こすことがあります。そのため、クライアント側で使用する文字コードを明示的に設定します。

mysql> SET NAMES utf8;


mysql> SHOW VARIABLES LIKE 'character_%';　←キャラクターセットの確認

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | utf8                       |

| character_set_connection | utf8                       |

| character_set_database   | latin1                     |

| character_set_filesystem | binary                     |

| character_set_results    | utf8                       |

| character_set_server     | latin1                     |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

トランザクション処理

　通常のクエリーでは、自動コミットが有効になっているためクエリー入力後、すぐに反映されてしまいます。トランザクション処理を行うには、「BEGIN」クエリーを発行するか、「SET AUTOCOMMIT=0」で自動コミットを無効にします。

mysql> BEGIN;

mysql> 処理1

mysql> 処理2

mysql> COMMIT;　←COMMITで処理1、処理2が反映される

mysql> SET AUTOCOMMIT=0;

mysql> 処理1

mysql> 処理2

mysql> ROLLBACK;　←処理1、処理2はキャンセルされる

SELECT .... FOR UPDATE

　トランザクションで、「SELECT .... FOR UPDATE」クエリーを用いると、トランザクション終了までSELECTされた行に対して排他ロックが適用されます。

　下の例では、クライアントAの「COMMIT」が実行されるまで、クライアントBのSELECT FOR UPDATEは排他ロックの解放待ちになります。SELECT .... FOR UPDATEの具体的なメリットについては次回のサンプルで紹介します。

**1）クライアントA**
`mysql> BEGIN; mysql> SELECT * FROM test_table2 FOR UPDATE;`

　　　　　　↓

**2）クライアントB**
`mysql> SELECT * FROM test_table2 FOR UPDATE; 排他ロックの解放待ち（処理が中断）`

　　　　　　↓

**3）クライアントA**
`mysql> UPDATE test_table2 set id=id*2; mysql> COMMIT;　←排他ロックの解放`

　　　　　　↓

**4）クライアントB**
`処理が再開`

サンプル開発の下準備（1）

　ここでは、サンプル（簡易オンラインストア）で使用するデータベースやテーブル、必要なアクセス権限などを設定します（注）。

注：実際のサンプル開発は次回紹介します。

　サンプルでは3つのテーブルを使用します。住所も氏名も注文内容も同じテーブルで管理することを最初に思い付きますが、それでは商品アイテムが増えた際にテーブルに新たなカラムを加える必要があります（図3の上部）。

　そこで、正規化を用い、購入された商品を別途order_itemテーブルで管理します（図3の下部）。

図3　正規化

　テーブルの各カラムの型には、「int」と「datetime」と「varchar」を使用します。intでは、「unsigned」を指定することで、負数が使用できない代わりに「0〜4294967295」までの値を格納できます。

　datetimeには、日付と時刻を格納。varcharには、文字列を格納します。varchar(20)では最大20文字が格納可能です。20bytesではなく20文字であることに注意します。アルファベットなら1文字1byte、SHIFT JISやEUCでは2byte、UTF-8では1〜4byteの可変長。同じvarchar(20)でも文字コードによりデータサイズが異なります。

　countテーブルの「max_no（注文番号のカウントに使用）」とorder_mainテーブルの「order_no（注文番号）」カラムには、主キーを設定します。主キーやインデックスを設定することで検索パフォーマンスを改善します。また、データ挿入の際に、NULL値や同じ値では挿入ができないといった主キー制約を設けることができます。主キー制約により同じ注文番号ではデータを登録することができなくなり、二重発注を抑止できます。

　order_itemテーブルでは、「order_no（注文番号）」と「item（商品名）」に対して一意キーを設定します。一意キーは、同じ値を挿入できないという点で主キーと同じです。しかし、主キーは1テーブルに1つしか設定できませんが、一意キーにはそのような制限はありません。また、“単一カラムだけでなく「order_no」と「item」カラムの文字列を結合させた値が、重複しないようにする”といったカラムを複合させて一意キーを設定することも可能です。

　order_itemテーブルは、複数のアイテムが同時に購入された場合、同じ注文番号でデータが挿入されるため、order_noに主キーを設定することができません。しかし、注文番号＋商品名ならば、挿入されるデータは1件に限られます。そこで、一意キーをorder_noとitemに対して設定します。

　なお、MySQLサーバとPHPが実行されるApacheサーバは、同一ホストであることを前提にしています。


項目	内容
ユーザー名	php
パスワード	password
データベース	sample_db


カラム名	型	備考
max_no	int(10) unsigned	注文番号のカウント、主キーを設定
countテーブル


カラム名	型	備考
order_no	int(10) unsigned	注文番号、主キーを設定
order_date	datetime	受注日時
name	varchar(20)	氏名
address	varchar(100)	住所
order_mainテーブル


カラム名	型	備考
order_no	int(10) unsigned	注文番号、一意キーを設定
order_date	datetime	受注日時
item	varchar(20)	商品名、一意キーを設定
qty	int(2) unsigned	注文個数
order_itemテーブル