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

今から始める MySQL入門(4):MySQLの基礎を学ぼう [準備編] (2/3)

[鶴長鎮一,@IT]

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 正規化 図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テーブル

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。