All Articles

MySQLで結合したテーブルに対して安易にSELECT *とかしないためのメモ

「えーこんなの常識でしょー」と言われるのを覚悟でメモしておきます。

取りたいデータがNULLになっちゃう

とある開発で、あるテーブル1とテーブル2とプライマリキーを条件に外部結合して、その結果のプライマリキーを取得するとNULLになってしまうことがありました。ちょっと言葉では説明しづらいので、以下の検証サンプルを。

テーブル作成

テーブルはこんな感じです(簡略化のためカラム名やインデックスなどは省略)。

-- テーブル1
CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `table1` (`id`, `value`) VALUES
(1, 'hoge'),
(2, 'huga');

-- テーブル2
CREATE TABLE IF NOT EXISTS `table2` (
  `id` int(11) NOT NULL,
  `value2` varchar(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `table2` (`id`, `value2`) VALUES
(1, 'hogehoge2');

テーブル1はidがプライマリキーで、テーブル2はそのidを外部キーとして保持する感じです。そして、実行するSQLは次の2つです:

クエリ1
SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.id) WHERE table1.id = 1;
クエリ2
SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.id) WHERE table1.id = 2;

クエリ1はテーブル1,2両方にレコードが存在するパターン、クエリ2はテーブル2にはリレーションしているidが存在していないパターンです。

この2つのSQLを、コンソール、mysql関数(PHP)、PDO(PHP)のそれぞれで実行します。

コンソールでの実行

クエリ1

クエリ2

特に問題なく、期待通りの結果ですね。

mysql関数、PDOでの実行

今回ハマったのはPHPなのですが、以下のようなコードを実行してみます。

// mysql関数
$db = mysql_connect('localhost', 'root', 'xxxxxx');
mysql_select_db('test', $db);
$result = mysql_query("SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.id) WHERE table1.id = 2", $db);
echo 'mysql() execute:' . PHP_EOL;
var_dump(mysql_fetch_object($result));
mysql_close($db);

// PDO
$db = new PDO('mysql:host=localhost;dbname=test', 'root', 'xxxxxx');
$stmt = $db--->query("SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.id) WHERE table1.id = 2");
echo 'PDO execute:' . PHP_EOL;
var_dump($stmt->fetch(PDO::FETCH_OBJ));

mysqli関数にしろよというツッコミは置いておきまして、実行結果は以下のようになります。

結合条件で指定したカラムが一つになる問題?

結果を見れば分かりますが、コンソールなどでは通常両方のidが取得されます(これはfetch条件を指定してないからですね)が、phpからfetchして取得したものは、クエリ2ではidがnull、つまり後ろに結合したテーブル2の方を取ってきてしまうようなんですねー。今回これを知らずにハマった次第です。

解決方法というか、対策というか

テーブルを外部結合していて、かつ他方に存在しないかもしれないキーを結合条件にするときは、 安易にSELECT *で終わらずに、SELECT *, table1.idとやるとidもちゃんと取れてきますね。

という、多分初歩的なつまづきでした。ハズカシイ…

そもそも、パフォーマンスとか考える皆様は*とか使わずカラム名をちゃんと指定されてるとおもいますが…(; ・`д・´)