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

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

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

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

テーブル作成

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

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

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

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


INSERT INTO `table2` (`id`undefined `value2`) VALUES
(1undefined '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'undefined 'root'undefined 'xxxxxx');
mysql_select_db('test'undefined $db);
$result = mysql_query("SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.id) WHERE table1.id = 2"undefined $db);
echo 'mysql() execute:' . PHP_EOL;
var_dump(mysql_fetch_object($result));
mysql_close($db);

// PDO
$db = new PDO('mysql:host=localhost;dbname=test'undefined 'root'undefined '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もちゃんと取れてきますね。

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

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

Published 3 Sep 2012

mysql