やったことだけ書く備忘録

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

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



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


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



テーブル作成


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




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

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

-- 
テーブル2
CREATE TABLE 
IF NOT EXISTS `table2` (
  `
idint(11NOT NULL,
  `
value2varchar(60NOT 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もちゃんと取れてきますね。



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



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

« 前の記事 次の記事 »

1件のコメント

Socorro さん

How could any of this be better stated? It cotdln'u.

コメントを投稿する

 画像に表示されている文字を入力してください。