SIOS Tech. Lab | エンジニアのタメになるOSS・クラウド・認証の技術トピックス 
こんにちは。サイオステクノロジー OSS サポート担当 山本 です。
今回は前回に引き続き、関係データベース管理システム (RDBMS) の MySQL についてもう少し見ていこうと思います。
目次
■RDB の特徴について
前回は MySQL の使い方について見てきましたが、ここで一旦改めて Relational DataBase (関係データベース) というものがどんなものなのか、キーワードをいくつか確認してみたいと思います。
■関係モデル
RDB という名前についてですが、そもそも一体どのあたりが “Relational (関係)” なのでしょう？
RDB における “関係” というのは、「同じ “属性” の組み合わせを持つ、複数の “組” で構成されるもの」とのことです。厳密にはもっとたくさん細かい条件があります
さて、ちょっと違う表現をしていましたが、前回を読んでくださった方は既にこれを見ているはずです。
「同じ “カラム (要素)” を持つ複数の “データ” の集合」。そう、”テーブル” です。
改めて、RDB というのは “テーブル (=関係)” というデータ形式、つまり関係モデルを扱うデータベースということになります。
■SQL
SQL (Structured Query Language) は、RDB を操作するのに使用する “言語 (人間にわかる形で書けるコンピュータへの命令文)” です。
例えば前回扱った INSERT / SELECT / UPDATE / DELETE … などがそれです。
RDBMS には原則として、この SQL が実装されています。
そのため一度 SQL を覚えてしまえば、他の RDBMS でも最低限の操作はできるようになるはずです。
とは言え、各 RDBMS はあくまで SQL の標準仕様に沿って各々が実装を行なっているものなので、RDBMS ごとに得手不得手などの若干の差異があったり、あるいは別途独自の操作が実装されている可能性もあります。実際に本格的に使用する前には、使用する RDBMS のマニュアルは一通り確認しましょう。
■関係モデルと正規化
ところで、関係モデルでは主にデータを効率よく管理することなどを目的に、複数のテーブルをうまく使ってデータを保存するのがよい、とされます。
例えばですが、会員制の通信販売の販売情報…のようなものを架空で作って、RDB に保存してみようと考えたとします。
必要なのは…
・会員 (購入者) の情報 (会員番号・氏名・住所・電話番号・メールアドレス)
・販売日時
・売れたもの
… (以下省略)
としてみましょう。ついでに管理用 ID を付けるとこんな感じになるでしょうか。
+------+----------+--------------+--------------------+------------+------------------+------------+-----------------+ | ID | memberID | name | address | tel | mail | date | goods | +------+----------+--------------+--------------------+------------+------------------+------------+-----------------+ | 1 | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | 2022-07-01 | 冷蔵庫 | | 2 | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | 2022-07-01 | 電子レンジ | | 3 | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | 2022-07-07 | 洗濯機 | +------+----------+--------------+--------------------+------------+------------------+------------+-----------------+
さて、1つのテーブルでただ並べてみましたが……一応このままでも使えないことはないかもしれませんが、例えばこの例の1行目と3行目の情報を見てみましょう。
会員の情報である「会員番号・住所・氏名・電話番号・メールアドレス」が同じになっています。これは偶然起きるものではなく、同じ会員が購入すれば当然同じになるべき情報のはずです。
ところでこの部分ですが、別途「会員情報を保存するテーブル」があった場合、“会員番号” だけわかれば他の “住所・氏名・電話番号・メールアドレス” の情報は「会員情報を保存するテーブル」を見ればわかるはずです。
試しにこの部分を分けてみましょう。
+----------+--------------+--------------------+------------+------------------+ | ID | name | address | tel | mail | +----------+--------------+--------------------+------------+------------------+ | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | +----------+--------------+--------------------+------------+------------------+ +------+----------+------------+-----------------+ | ID | memberID | date | goods | +------+----------+------------+-----------------+ | 1 | 00000001 | 2022-07-01 | 冷蔵庫 | | 2 | 00000002 | 2022-07-01 | 電子レンジ | | 3 | 00000001 | 2022-07-07 | 洗濯機 | +------+----------+------------+-----------------+
この例で言えば、他には例えば、別途商品一覧のために作られた「商品のテーブル」があれば “売れたもの” はその「商品のテーブル」の管理番号に置き換えることなんかもできそうです。
「直感的に見にくくなってない？」と思う方もいるかもしれませんが、複数のテーブルを繋げて表示する方法などもあるので、(適切にテーブルを繋げられるようにデータを作れれば) そこは特に気にしなくても大丈夫です。テーブルを繋げる方法の一例は後述します
さて、このようにテーブルを分けるメリットについてですが…例えば、この例で言えば「会員の情報を修正したい」と考えたとします。
テーブルを分けなかった場合、会員の情報は「販売情報のテーブル」に複数点在することになります。この状態で会員の情報を修正する場合、該当する全ての行のデータ（レコード）を同時に修正しなければ、同じ会員のはずなのに異なる情報が登録されているというデータの不整合が起こりえます。
また、不要な重複がある (=冗長な) データが保存されることは、RDB が消費する必要ストレージ容量の観点でも非効率的です。
対してテーブルを分けた場合には、会員の情報は「会員情報のテーブル」にのみ存在するようにできるため、先に説明したような不整合は起こりませんし容量の効率もよくなりやすいはずです。
このように、テーブルを適切に分割することで、不要なデータ重複やデータの不整合を防ぎ、データの一貫性や信頼性を保ちつつ効率的にデータを保存できるようになります。
ただし当然ながら、闇雲にただテーブルを分割すればよい、というものではありません。
“どういったデータを保存するのか“、”データ同士の関連はどこにどの程度あるのか“、”このデータを使用するシステムで達成するべき目標 (要件) は何か“…などなどを考慮した上で、どうすれば効率的で信頼性の高い状態を保てるかをそのシステムごとに考える必要があります。
こういった、いかにテーブルを分割すべきかという理論、あるいは実際に適切なテーブル分割を行うことを、正規化と呼んだりします。
正規化の方法論は多数存在し、また先述のとおり実際に適切な正規化を行う場合にはデータやそのデータを使うシステムのことも熟考する必要があります。
一方で、前回お話ししたとおり RDB では事前に決めたテーブルの内容に従う形でしかデータを保存できません。
そのため、RDB を使い始める前にどれだけこの正規化を適切に行えるかが、上手く RDB を活用するためのカギになります。
後でテーブルの内容を変更したりテーブルを追加することも勿論できなくはないですが、それ自体が不整合や RDB を利用するプログラム等の動作不良の原因になる可能性もあるので、可能な限り事前に行うほうがよいです。
■ちょっとだけ特殊な SQL
ここでは複数のテーブルを使う場合に役立つ操作やユーザ追加など、ちょっとだけ踏み込んだ SQL の使い方をいくつか見ていきます。
■複数のテーブルを繋げる：JOIN 句
先にお話しした “正規化” などで分けた複数のテーブルの情報を繋げて表示したいという場合に使えるのが JOIN 句です。
JOIN 句は基本的に SELECT 文などと一緒に使うものになります。
JOIN 句は更に数種類に分かれますが、ここでは “INNER JOIN” と “LEFT JOIN” の使い方を見てみます。
mysql> SELECT
<表示するカラムの名前1>, <表示するカラムの名前2>,…
FROM <テーブル名1>
INNER JOIN <テーブル名2>
ON <繋げ方の条件>;
“LEFT JOIN” を使う場合は、”INNER JOIN” の箇所を “LEFT JOIN” にすれば OK です。
注意点としてこの JOIN などのように 1つの SQL で複数のテーブルを扱う場合、カラムを指定する場合には “<テーブル名>.<カラム名>” の形で指定する必要があります。
これを踏まえた上で、仮想の “販売情報” と “会員情報” のテーブルを繋げてみます。
mysql> SELECT * FROM member; +----------+--------------+--------------------+------------+------------------+ | ID | name | address | tel | mail | +----------+--------------+--------------------+------------+------------------+ | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | +----------+--------------+--------------------+------------+------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM sales; +------+----------+------------+-----------------+ | ID | memberID | date | goods | +------+----------+------------+-----------------+ | 1 | 00000001 | 2022-07-01 | 掃除機 | | 2 | 00000002 | 2022-07-03 | 電子レンジ | | 3 | 00000001 | 2022-07-10 | 洗濯機 | | 4 | 00000003 | 2022-07-14 | テレビ | +------+----------+------------+-----------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM sales LEFT JOIN member ON sales.memberID = member.ID; +------+----------+------------+-----------------+----------+--------------+--------------------+------------+------------------+ | ID | memberID | date | goods | ID | name | address | tel | mail | +------+----------+------------+-----------------+----------+--------------+--------------------+------------+------------------+ | 1 | 00000001 | 2022-07-01 | 掃除機 | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 2 | 00000002 | 2022-07-03 | 電子レンジ | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | | 3 | 00000001 | 2022-07-10 | 洗濯機 | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 4 | 00000003 | 2022-07-14 | テレビ | NULL | NULL | NULL | NULL | NULL | +------+----------+------------+-----------------+----------+--------------+--------------------+------------+------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM sales INNER JOIN member ON sales.memberID = member.ID; +------+----------+------------+-----------------+----------+--------------+--------------------+------------+------------------+ | ID | memberID | date | goods | ID | name | address | tel | mail | +------+----------+------------+-----------------+----------+--------------+--------------------+------------+------------------+ | 1 | 00000001 | 2022-07-01 | 掃除機 | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 2 | 00000002 | 2022-07-03 | 電子レンジ | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | | 3 | 00000001 | 2022-07-10 | 洗濯機 | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | +------+----------+------------+-----------------+----------+--------------+--------------------+------------+------------------+ 3 rows in set (0.01 sec)
このように、JOIN 句によって複数のテーブルを繋げることができます。
見ていただいたとおり、”LEFT JOIN” と “INNER JOIN” はいずれも同じようにテーブルを繋げますが、
“INNER JOIN” は指定した条件で繋げることのできたデータ (レコード) のみを表示するのに対し、
“LEFT JOIN” は先に指定したテーブルのデータ (レコード) を全て表示し、指定条件で繋げられなかったレコードについては後に指定したテーブルのカラムの部分が入っていない状態になっていることが確認できるかと思います。
どの JOIN 句が便利なのかは状況によって異なるかと思いますので、とりあえず「こういうものもあるんだなぁ」と頭の片隅に置いておくといつか幸せになるときがくる…かもしれません。
■複数の SQL での一括更新：トランザクション
例えばですが、通信販売のデータを保存する架空の RDB を例に言えば、「購入情報の登録」「在庫数の変更」「ポイント関連の反映」…などのように、「複数の SQL による更新が、全て確実に、一括で行われないと困る」という状況に面することもあるかもしれません。
そういった場合にはトランザクションを使うと安心です。
トランザクションを使うと、一時的に RDB に保存されているデータを直接変更せずに、自分にだけ変更された内容が表示される状態になります。
その後、トランザクションを開始してから行なった変更を、全て RDB の保存データに反映するか、全ての変更を破棄するかを選ぶことができます。
トランザクションは以下のように使うことができます。。
# (トランザクション開始) mysql> START TRANSACTION; [複数の任意の SQL] # (変更を反映する場合) mysql> COMMIT; # (変更を破棄する場合) mysql> ROLLBACK;
先のテーブルを使って反映/破棄の両方の動きを確認してみます。
まずは変更を破棄する ROLLBACK から。
mysql> SELECT * FROM member; +----------+--------------+--------------------+------------+------------------+ | ID | name | address | tel | mail | +----------+--------------+--------------------+------------+------------------+ | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | +----------+--------------+--------------------+------------+------------------+ 2 rows in set (0.00 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM member WHERE ID = "00000001"; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM member; +----------+--------------+-----------------+------------+-----------------+ | ID | name | address | tel | mail | +----------+--------------+-----------------+------------+-----------------+ | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | +----------+--------------+-----------------+------------+-----------------+ 1 row in set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM member; +----------+--------------+--------------------+------------+------------------+ | ID | name | address | tel | mail | +----------+--------------+--------------------+------------+------------------+ | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | +----------+--------------+--------------------+------------+------------------+ 2 rows in set (0.00 sec)
変更を反映する COMMIT だとこんな感じになります。
mysql> SELECT * FROM member; +----------+--------------+--------------------+------------+------------------+ | ID | name | address | tel | mail | +----------+--------------+--------------------+------------+------------------+ | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 00000002 | サンプル三郎 | foo市bar町baz | 0123498765 | mail@sample.com | +----------+--------------+--------------------+------------+------------------+ 2 rows in set (0.00 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE member SET name = "実験１号" WHERE ID = "00000002"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM member; +----------+--------------+--------------------+------------+------------------+ | ID | name | address | tel | mail | +----------+--------------+--------------------+------------+------------------+ | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 00000002 | 実験１号 | foo市bar町baz | 0123498765 | mail@sample.com | +----------+--------------+--------------------+------------+------------------+ 2 rows in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM member; +----------+-----------------+--------------------+------------+------------------+ | ID | name | address | tel | mail | +----------+-----------------+--------------------+------------+------------------+ | 00000001 | テスト太郎 | hoge市huga町piyo | 0123456789 | mail@example.com | | 00000002 | 実験１号 | foo市bar町baz | 0123498765 | mail@sample.com | +----------+-----------------+--------------------+------------+------------------+ 2 rows in set (0.00 sec)
■新規ユーザの作成
その RDB を複数人で利用するようになる場合、「ユーザ毎にアクセス範囲を制限したい」「データを見れるけど編集できないアカウントが必要」といったことにもなるでしょう。そういった場合は、新規ユーザを作成することで対応できます。
新規ユーザの作成は以下のようなコマンドで行います。
mysql> CREATE USER '<ユーザ名>'@'<接続元のホスト名 or IP>' IDENTIFIED BY '<パスワード>';
新規作成するアカウントは、基本的に SQL を実行する権限がない状態で作成されます。
そのため、合わせて以下のようなコマンドで権限を付与することを忘れないようにしましょう。
mysql> GRANT <許可する操作> ON <データベース名>.<テーブル名> TO '<ユーザ名>'@'<接続元のホスト名 or IP>';
例えば、ローカル環境からアクセスするユーザ名「newuser」パスワード「password」という新規アカウントを作成し、データベース “test” 上でのあらゆる操作をできる権限を与える場合は以下のようにすれば OK です。。
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; mysql> GRANT ALL ON test.* TO 'newuser'@'localhost';
■最後に
今回は前回に引き続き比較的出番の多そうな MySQL の操作方法をいくつかと、RDB ってどんなもの？というあたりについて少し細かくお話ししてみました。
説明が長ったらしくなってしまった箇所もありますが、なんとなく伝わっていれば幸いです。
前回もお話ししましたが、前回・今回にお話しした内容は MySQL の使い方の基本の一部に過ぎませんので、本格的に使用していく場合にはドキュメントなどの情報に一度きちんと目を通しておくことをお勧めします。
次回以降は、別のデータベースについても見てみたいと思っています。
[他の回]
わからないなりに理解したいデータベース①：RDB編：MySQL①
(今回) わからないなりに理解したいデータベース②：RDB編：MySQL②
わからないなりに理解したいデータベース③：RDB編：MariaDB
わからないなりに理解したいデータベース④：RDB編：PostgreSQL
Be the first to comment