INSERT文やUPDATE文で更新する対象の表を、サブクエリで使うとエラーとなる。
┌──────────────────────────────────────┐
│#1093 - You can't specify target table 表 for update in FROM clause         │
└──────────────────────────────────────┘

例えば、「キー」と「所有者」の列からなる表について、
「所有者」が他に所有していない場合のみ、更新できるように条件文を
以下のように付加したとしたら、エラーとなってしまうわけだ。
┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│    SET 所有者 = 2                                                          │
│    WHERE キー = 1                                                          │
│      AND NOT EXISTS (SELECT *                                              │
│                          FROM 表                                           │
│                          WHERE 所有者 = 2);                                │
└──────────────────────────────────────┘

これの回避方法は一時表を使うこと。以下のような感じ。
┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│    SET 所有者 = 2                                                          │
│    WHERE キー = 1                                                          │
│      AND NOT EXISTS (SELECT *                                              │
│                          FROM (SELECT * FROM 表) AS 一時表                 │
│                          WHERE 所有者 = 2);                                │
└──────────────────────────────────────┘

循環参照となるので、コピーを一つ用意するような感じだ。

適当な欠番「id」に「値」を設定するとすると、以下のような感じになる。
┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│    SET `id` = 値                                                           │
│    WHERE `id` IS NULL                                                      │
│    ORDER BY rand()                                                         │
│    LIMIT 1;                                                                │
└──────────────────────────────────────┘

「id」がユニークキー(たいていは主キー)で、
ひょっとしてその値が既に設定されているとまずいので
既に存在しないことを条件追加して相関副問い合わせ化したら
以下のような感じになった。
┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│    SET `id` = 値                                                           │
│    WHERE `id` IS NULL                                                      │
│      AND NOT EXISTS (SELECT `id`                                           │
│                          FROM 表                                           │
│                          WHERE `id` = 値                                   │
│                     )                                                      │
│    ORDER BY rand()                                                         │
│    LIMIT 1;                                                                │
└──────────────────────────────────────┘
でも、エラーになります。
「#1093 - You can't specify target table '表' for update in FROM clause」

要は副問い合わせの中に主問い合わせと同じ表を使えないらしい。

仕方ないのでとりあえず、参照してから条件分岐で更新することにした。
なんかいい方法はないものか。

先日にも触れたが、「LEFT JOIN」って直積なのをついつい忘れがちになる。
メインデータを一回のSQL発行で取得して、
それをループして一件ずつサブデータをSQL発行で取得してマージしていく…、
みたいな処理を一回のSQL発行でできるのが「LEFT JOIN」なのかなと錯覚してしまう。
当然、名称取得などサブデータが一件の場合はその通りなのだが、
カウントした件数をマージしたりする場合は注意が必要だ。

これはOK。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       サブ表.サブ名称                                                      │
│    FROM メイン表                                                           │
│        LEFT JOIN サブ表                                                    │
│          ON サブ表.ID = メイン表.サブID;                                   │
└──────────────────────────────────────┘
サブ名称を参照するケースだ。

これはギリギリOK。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       COUNT(サブ表.ID) AS サブ該当件数                                     │
│    FROM メイン表                                                           │
│        LEFT JOIN サブ表                                                    │
│          ON サブ表.キーID = メイン表.サブキーID                            │
│    GROUP BY メイン表.ID;                                                   │
└──────────────────────────────────────┘
サブ表中にサブキーIDに該当するものが何件あるかをマージするケース。

でも、これはNG、件数が二つになったらNG。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       COUNT(サブ表1.ID) AS サブ1該当件数,                                  │
│       COUNT(サブ表2.ID) AS サブ2該当件数                                   │
│    FROM メイン表                                                           │
│        LEFT JOIN サブ表1                                                   │
│          ON サブ表1.キーID = メイン表.サブ1キーID                          │
│        LEFT JOIN サブ表2                                                   │
│          ON サブ表2.キーID = メイン表.サブ2キーID                          │
│    GROUP BY メイン表.ID;                                                   │
└──────────────────────────────────────┘
サブ1とサブ2で各々で件数を算出してくれるかと思いきや、直積により、
それぞれの該当件数が両者の掛け算の結果(つまり同一値)になってしまうのだ。

対抗策１、DISTINCTをつける。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       COUNT(DISTINCT サブ表1.ID) AS サブ1該当件数,                         │
│       COUNT(DISTINCT サブ表2.ID) AS サブ2該当件数                          │
│    FROM メイン表                                                           │
│        LEFT JOIN サブ表1                                                   │
│          ON サブ表1.キーID = メイン表.サブ1キーID                          │
│        LEFT JOIN サブ表2                                                   │
│          ON サブ表2.キーID = メイン表.サブ2キーID                          │
│    GROUP BY メイン表.ID;                                                   │
└──────────────────────────────────────┘
直積に対抗するにはDISTINCTとばかりに、各々にDISTINCTをつける。
こんなところにDISTINCTを置けるのか不安だったが意外にいける。

対抗策２、サブクエリとする。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       (SELECT COUNT(サブ表1.ID)                                            │
│            FROM サブ表1                                                    │
│            WHERE サブ表1.キーID = メイン表.サブ1キーID                     │
│       ) AS サブ1該当件数,                                                  │
│       (SELECT COUNT(サブ表2.ID)                                            │
│            FROM サブ表2                                                    │
│            WHERE サブ表2.キーID = メイン表.サブ2キーID                     │
│       ) AS サブ2該当件数                                                   │
│    FROM メイン表;                                                          │
└──────────────────────────────────────┘
メインデータに対してサブデータをマージするというイメージには、
こっちの方が近いのだろう。
対抗策１だと直積で一旦メインデータを増殖させて、GROUP BYで収束させているが
これだとメインデータはそのままだ。
それにしても()の中に()の外側のメイン表が突然出てくる感じがして
若干違和感があった(JOINしてないから)、でもサブクエリってそんなもの。

対抗策３、UNIONを使う力業。
┌──────────────────────────────────────┐
│SELECT 新メイン表.ID,                                                       │
│       SUM(新メイン表.サブ1該当件数) AS サブ1該当件数,                      │
│       SUM(新メイン表.サブ2該当件数) AS サブ2該当件数                       │
│    FROM (SELECT メイン表.ID,                                               │
│                 NULL              AS サブ1該当件数,                        │
│                 NULL              AS サブ2該当件数                         │
│               FROM メイン表                                                │
│          UNION ALL                                                         │
│          SELECT メイン表.ID,                                               │
│                 COUNT(サブ表1.ID) AS サブ1該当件数,                        │
│                 NULL              AS サブ2該当件数                         │
│               FROM メイン表,                                               │
│                    サブ表1                                                 │
│               WHERE メイン表.サブ1キーID = サブ表1.キーID                  │
│               GROUP BY メイン表.ID                                         │
│          UNION ALL                                                         │
│          SELECT メイン表.ID,                                               │
│                 NULL              AS サブ1該当件数,                        │
│                 COUNT(サブ表2.ID) AS サブ2該当件数                         │
│               FROM メイン表,                                               │
│                    サブ表2                                                 │
│               WHERE メイン表.サブ2キーID = サブ表2.キーID                  │
│               GROUP BY メイン表.ID                                         │
│         ) AS 新メイン表                                                    │
│    GROUP BY 新メイン表.ID;                                                 │
└──────────────────────────────────────┘
こんな感じかな(動作確認してないからちょっと違うかも)。
対抗策３はあくまで余談です。

「id」が「0」(欠番)の適当な5件を「1」にするSQL文は以下の通り。
┌──────────────────────────────────────┐
│UPDATE `表`                                                                 │
│    SET `id` = '1'                                                          │
│    WHERE `id` = '0'                                                        │
│    ORDER BY rand()                                                         │
│    LIMIT 5;                                                                │
└──────────────────────────────────────┘
UPDATEでも「ORDER BY rand()」と「LIMIT」が使えるのは便利かも。

ある列がNULLでないものを抽出するSQL文。

┌──────────────────────────────────────┐
│SELECT * FROM 表 WHERE 列 IS NOT NULL;                                      │
└──────────────────────────────────────┘
これだとOK。

でも、これだと(NULLでない行があっても)「該当なし」になってしまう。
┌──────────────────────────────────────┐
│SELECT * FROM 表 WHERE 列 <> NULL;                                          │
└──────────────────────────────────────┘

NULLじゃないんだからいいと思ったんだけどなぁ…
やっぱり、「IS NOT NULL」ってのがあるんだから、そう書かないとダメみたいね。
気をつけないと。

「ID=0」のつもりで「ID=NULL」を変数で使ってるケースは結構あるもんだ。
こんな場合、SQL文を使う瞬間は注意です。

MySQLで、HTML側もDB側も「UTF-8」に統一されている環境なのに、
insertやupdateした時に、日本語文字が「????」とかに文字化けすることがある。

文字コードの設定が不完全だかららしい。
その設定方法にはいくつかある。
┌──────────────────────────────────────┐
│(1) mysql_set_charset('utf8');                                              │
├──────────────────────────────────────┤
│(2) $dbh->exec('SET CHARACTER SET utf8;');                                  │
├──────────────────────────────────────┤
│(3) $dbh->exec('SET NAMES utf8;');                                          │
└──────────────────────────────────────┘

最新でもっとも安全な策は(1)。
最新の環境であれば、(1)で解決。

…だが、PDOとは相性がよろしくないみたい(設定方法の問題か？)。
それと、PHPやMySQLのバージョンが古いと使えない。

ということで、(2)。(ま、これでいいんじゃないかな(本当か？))

(3)も結構有名だが、セキュリティ的にちょっと問題があるらしい。
いってみれば全体的に足並みを揃えての変更(設定)ではないため、
一部でボロが出てしまう恐れがある(つまり脆弱性)みたいなのだ。
但し、Shift-JIS系での話なのでUTF-8では気にしなくてもいいかも。
＃ただ、UTF-8にしようとしてるけど環境がShift-JIS系とかいう場合に
  問題なのだろうか、よくわからない

もう少し勉強します。。。

なぜ、この問題にぶち当たったかというと、データベースの設定で
照合順序を「utf8_general_ci」にせず、デフォルトにしていたため、
DB側が「UTF-8」に統一されていなかった(らしい)というオチだったのだが。

このように前提条件が狂っている中だと、(2)を設定すると更新が文字化けして
逆に設定しないと参照が文字化けするという進退窮まることになってしまう。
基本は大事だね。