読者です 読者をやめる 読者になる 読者になる

graphics.hatenablog.com

テクニカルアーティストの技術を書き殴るためのメモ帳

テクニカルアーティストのためのデータベース入門 (7) SQLのはじめの一歩

テーブル内のデータの操作方法、とりあえずこれだけ覚えておけば、最初のうちはあまり困らないと思う。

https://zeroturnaround.com/wp-content/uploads/2016/06/RebelLabs-SQL-cheat-sheet.png

最初に書いたとおりMySQLを前提に進めてみる。とりあえず試すなら MySQL Installer でセットアップするのが一番楽。Server Only でインストールして、クライアントは HeidiSQL を。

graphics.hatenablog.com


SQL は大文字アルファベットで書かれることが多いけど、(少なくとも MySQL は)大文字小文字を区別しないので、個人的には小文字でも別に構わないと思ってる。ただし、エスケープはちゃんとしたほうがいい。MySQL の場合、テーブル名やカラム名はバッククォート(`)で、文字列はクォート(')で囲むことでエスケープする。

クエリの書き方をぐぐるとエスケープしてない人が多いけど、こういうふうに書くと意図しないバグにつながることも多いので、そこは気を付けたい。(冒頭に挙げたチートシートなんかはわかりやすさが最優先だろうし、エスケープとかしてなくてもまぁそんなもんかなとは思う。)

テーブルの作成と削除

クライアントの GUI を使うほうが安全で便利だから、SQL は使わない。外部キーについてはあとからいくらでも追加できるのだけど、事故ってからでは遅いので、可能な限り、テーブル作成時にまとめて設定してしまうことを強くおすすめしたい。

models
f:id:hal1932:20161015083509j:plain

textures
f:id:hal1932:20161015083514j:plain

files
f:id:hal1932:20161015083522j:plain

model_texture_maps
f:id:hal1932:20161015084005j:plain

modelsの外部キー
f:id:hal1932:20161015083709j:plain

texturesの外部キー
f:id:hal1932:20161015083716j:plain

model_texture_mapsの外部キー
f:id:hal1932:20161015083728j:plain

データの追加

実行速度を考えると、複数個のデータ追加にはバルクインサートを使うがいい。ただ、スクリプトとかの書きやすさを考えると、ループでまわしながら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 とかと同じ。

全部まとめて削除する

truncate table `models`

これをやると、`models` の中身が全部からっぽになる。「条件指定のない delete」と思っておけばいい。

ただし、言うまでもなく超危険な操作なので、実際にやるときは細心の注意を払うこと。「開発用に使ってたゴミデータを本番リリース前に一括削除したい」みたいなときに使う……というふうに考えちゃうこともある*5んだけど、そもそも開発用と本番用ではDBを分けておく*6*7のが常套手段なので、やっぱり非常事態用の緊急操作くらいに捉えておくのがいいと思う。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13 SQL ステートメントの構文

*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:大抵のクライアントツールには「データの一括エクスポート・インポート」機能があるので、テスト環境を充実させるために、定期的に本番用から開発用にデータをコピーしておこう。