[MySQL] 一時表権限との格闘記

このエントリーをはてなブックマークに追加

見よう見まねでMySQL触るのはよくないね!
一時表の権限設定でちょっと詰まったのでメモ。

やりたかったこと

複数の値を指定すると、その値のいずれかを含む列を取得するSQLを作りたい。

リストのいずれかに一致する行を取得

調べてみたところ、少なくとも2種類の方法があるらしい。

  • IN句のカッコの中に全部放り込む
    • WHERE name IN('apple', 'banana', 'grapes', ...)
  • 一時表を使う
    • WHERE name IN( SELECT name FROM tmp_table )

ただ、一つ目のIN句は色々問題があるようなのだ。

IN内の列挙問題1: SQLがやたら長くなる

指定する要素数によっては、SQLの上限引っかかってしまいそうで不安だ。そもそもIN句はどれくらいの数まで書けるんだろうか。
軽く調べたところ、IN句内の指定はオラクルは上限1000個だが、MySQLは無制限(設定依存)らしい。

MySQLなのでOKなんだけど、制限ギリギリを攻めるようなことはしたくないな。

IN内の列挙問題2: プリペアドステートメントで可変長引数は使いづらい

プリペアドステートメントをざっくりいうと、いきなり文字列結合でSQL文作るんじゃなくて、テンプレート作ってそこに必要な情報はめ込みましょうね、というもの。
事前コンパイルで高速化、SQLインジェクションに強いといった利点もろもろ。

ただこれ、少なくともmysqliでは文字列配列のプレースホルダを作ることはできない。なので、IN句の要素数分だけプレースホルダを作る必要があるのがモニョる。他のライブラリならできるのかもしれないが…

プリペアドステートメント捨てればいいけど、個人的にはセキュリティ良くわからんので安全側に振らせたい。手放すのは惜しい。

IN内の列挙問題3: 動作が遅い?

確かめてないので良くわからないが、一時表の方が動作が速いらしい。
色々探し回ったので言及してるサイトを失念。

とまあ、以上の理由によって、IN句内の列挙じゃなくて、一時表を使うことにした。そしたら権限設定で見事にハマってしまった。

格闘記

エラーと戦った数時間の記録。
時系列で書いているので、結論だけ見たい人は飛ばしてね。

コケはじめ

まずはさくっと、データが入ってるテーブルへの権限を設定する。

で、一時表を作成しようとしたら、一時表を作る権限がありませんと怒られた。
調べてみたら、一時表用の権限があるようだ。
一時表なんてどうせ消えるんだから、自由につくってもいいじゃん!

データテーブルに一時表権限追加してコケる

そこで、何も考えず CREATE TEMPORARY TABLES 権限を追加した。

設定できずに怒られた。
良く考えたら、このテーブルと一時表って全く関係ないね。一時表はその場で作るものだから、テーブルではなくDB単位で権限を設定するのだろう。

任意名で一時表権限を追加、INSERTでコケる

CREATE TEMPORARY TABLES 権限をDB内の任意テーブルに変えた。

これで一時表を作れるようになった。やった!
喜び勇んで一時表にデータを挿入しようと思ったら、INSERT権限がないと怒られた。CREATE TEMPORARY TABLES 権限はまさしく作成する権限だけしかなくて、編集は別途権限が必要なようだ。
自分が作った一時表すらいじれないなんて!

一時表に対し編集権限を追加してコケる

一時表の名前を使って、また新しく権限を作成。

tmp_tableがありませんと怒られる。知ってるよ!一時表なんだから!
どうやら存在しない表に対して各権限を付与するには、CREATE TABLE 権限が必要らしい。そんな権限なくても一時表は作れてるのに!

完成形

CREATE TABLE 権限を付与したら動くようになった。

ちなみに最後の一時表への権限に CREATE TEMPORARY TABLES をつけたらやっぱり怒られる。GRANT文は3つとも必要っぽい。
仕様がよくわからん…

一時表権限のまとめ

あってるのか自信ないけど。

  • 一時表「作成」には CREATE TEMPORARY TABLES 権限が必要
  • 一時表「作成」権限はDB全体(db_name.*)につける
  • 一時表「編集」権限は別なので、一時表のテーブル名で別途設ける
  • 一時表「編集」権限を設定するとき、ついでにCREATE権限を追加する
  • (flush privilegesを忘れない)

やりたかったことできた

疲れた!

このエントリーをはてなブックマークに追加

コメントを残す

メールアドレスが公開されることはありません。* が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">