テクニカルアーティストのためのデータベース入門 (7) SQLのはじめの一歩
テーブル内のデータの操作方法、とりあえずこれだけ覚えておけば、最初のうちはあまり困らないと思う。
最初に書いたとおりMySQLを前提に進めてみる。とりあえず試すなら MySQL Installer でセットアップするのが一番楽。Server Only でインストールして、クライアントは HeidiSQL を。
SQL は大文字アルファベットで書かれることが多いけど、（少なくとも MySQL は）大文字小文字を区別しないので、個人的には小文字でも別に構わないと思ってる。ただし、エスケープはちゃんとしたほうがいい。MySQL の場合、テーブル名やカラム名はバッククォート（`）で、文字列はクォート（'）で囲むことでエスケープする。
クエリの書き方をぐぐるとエスケープしてない人が多いけど、こういうふうに書くと意図しないバグにつながることも多いので、そこは気を付けたい。（冒頭に挙げたチートシートなんかはわかりやすさが最優先だろうし、エスケープとかしてなくてもまぁそんなもんかなとは思う。）
テーブルの作成と削除
クライアントの GUI を使うほうが安全で便利だから、SQL は使わない。外部キーについてはあとからいくらでも追加できるのだけど、事故ってからでは遅いので、可能な限り、テーブル作成時にまとめて設定してしまうことを強くおすすめしたい。
models
textures
files
model_texture_maps
modelsの外部キー
texturesの外部キー
model_texture_mapsの外部キー
データの追加
実行速度を考えると、複数個のデータ追加にはバルクインサートを使うがいい。ただ、スクリプトとかの書きやすさを考えると、ループでまわしながら1個ずつインサートも捨てがたい。たとえば C# なんかだとかなり手軽に並列化ができるので、いっぱいスレッドを立てて力技でインサートしてしまうのも、その後のメンテナンスを考えると悪くない方法*1だとは思う。
あと、SQL では文の区切りにセミコロン（;）を使う。途中に改行をいれても無視されるので、文が読みやすいように適宜改行したほうがいいかもしれない。
1つずつ追加する（インサート）
insert into `models`
(`name`, `operator`)
values ('model01', 'user01')`models`.`id` は auto increment なので自動で設定されるから、SQL から設定してはいけない。`models`.`file_id` を設定してないけど、何もしなければ勝手に null が設定されることになる。
まとめて追加する（バルクインサート）
insert into `models`
(`name`, `operator`)
values ('model01', 'user01'), ('model02', 'user02'), ('model03', 'user03')values の続きをカンマ区切りでつなげていくだけ。普通のインサートだと、データを1個追加するごとにネットワーク通信が1回発生して、DB上でそれが実行される。その点バルクインサートでは、何個追加しても通信と実行が1回しか行われないので、大抵のケースで速くなる。
データの検索
1つのテーブルから検索
select `name`, `width`, `height` from `textures` where `operator` = 'user01' and `width` <= 1024 and `height` <= 1024
`textures` の中から、'user01' が担当していてサイズが 1K 以下のデータの、`name`, `width`, `height` を検索する。検索条件を複数指定したい場合は、そのまま and や or でつなげればいい。*2
`name` や `width` に限らずすべての項目が欲しいときは select * にすればいいけど、本当にすべてが必要なケースはそれほど多くない。やりたいことを明確にする意味でも、欲しい項目を明示的に指定するほうがいいと思う。同じように、検索条件はできるだけちゃんと指定するようにしたい。
必要なものを、必要なだけ。
ちなみに「'user01' か 'user02' のどちらかが担当してるテクスチャがほしい」みたいなときは、where のところを = じゃなくて in にすればいい。*3
select `name`, `operator`
from `textures`
where `operator` in ('user01', 'user02')
複数のテーブルから検索
複数のテーブルを繋げて1つの大きなテーブルとして扱うことを、「join する」という。join の方法にはいくつかあるけど、まぁ left join と inner join だけ覚えておけばだいたい問題ない。というか、管理の複雑さを考えると、その 2 つだけで収まるようにテーブルを構成するくらいがちょうどいい。
select `t`.`name`, `f`.`path` from `textures` as `t` left join `files` as `f` on `f`.`id` = `t`.`file_id` where `t`.`operator` = 'user01'
'user01' さんの担当テクスチャの名前とファイルパスを検索する。ファイルパスは `files` テーブルに記録されてるからそっちと繋げるんだけど、繋げる用のキーとして `file_id` を使う。イメージ的には、`textures` テーブルを左側に置いて、右側には「`id` が `textures`.`file_id` に一致する `files` のデータの中身」を並べるかんじ。一致するデータがない場合は、null が右側に並ぶことになる。
select `t`.`name`, `f`.`path` from `textures` as `t` inner join `files` as `f` on `f`.`id` = `t`.`file_id` where `t`.`operator` = 'user01'
inner join の場合、`textures`.`file_id` に一致する `files`.`id` がなかったら、検索結果には何も出てこない。片方のテーブルにしかないデータを取ってきたいときは left join、両方にあるやつしかほしくないときは inner join を使えばいい。
イメージ的にはこんな↓かんじ。
left join の結果
| `files`.`name` | `files`.`path` |
| texture01 | texture01.tex |
| texture02 | null |
| texture03 | texture03.tex |
inner join の結果
| `files`.`name` | `files`.`path` |
| texture01 | texture01.tex |
| texture03 | texture03.tex |
データの更新
update `models` set `name` = `new_name` where `id` = 1
説明は特に必要ない、はず。*4
さて、プロジェクトの途中でフォルダ構成の変更があって、モデルやテクスチャのパスを一括変更しなきゃいけなくなった。パスは `files`.`path` に文字列で記録されてるけど、文字列の一括置換に便利なのが replace 関数。1つずつ更新するよりも断然速いし、ぜひとも覚えておきたい。
update `files` set `path` = replace(`path`, 'resources/', 'new_resources/')
データの削除
条件を指定して削除する
delete from `models` where `name` = 'model01'
これもまぁ、特に説明はいらないはず。where のところに = じゃなくて in が使えるのも select とかと同じ。
*1:そういうやりかたを嫌がるプログラマは多い、けどTAはプログラマじゃない。無理なくできることをやればいい。
*2:条件指定には括弧も使える。たとえば where (`width` <= 1024 or `height` <= 1024) and `bit_depth` = 8 みたいな。
*3:ちなみに文字列の部分一致には like を使う。たとえば「'path' = 'models/*'」という条件を指定するには、MySQL では「`path` like 'models/%'」と書く。
*4:replace into ってのもあるけど、これは「置換」じゃなくて「削除＋追加」なのでちょっと違う。必要なケースもそこまで多くないので、ひとまず見なかったことに。
*5:というかそれ以外のケースでは滅多に使いみちがない。
*6:DBサーバを分けておくのが一番安心ではあるけど、サーバマシンの用意とかも色々面倒なので、とりあえずは同じサーバ内に複数のDBを用意するくらいでも、まずは十分だと思う。
*7:大抵のクライアントツールには「データの一括エクスポート・インポート」機能があるので、テスト環境を充実させるために、定期的に本番用から開発用にデータをコピーしておこう。
