IT系おじさんのチラシの裏
2018年10月～
当サイトの記事にはアフィリエイト広告のリンクが含まれる場合があります

PDOで連想配列からINSERT文を作る方法と速度比較

PHP
0
2020/07/15 (更新日：)

今回はPHPのPDOでINSERT文を実行する際に、連想配列からSQL文を組み立てるお話です。

うん、なんか文章で書くとややこしいですけど、普通MySQLやPostgreSQL、あるいはSQLiteでも何でも良いですけど、テーブルにINSERT文を発行するときって何かしら関数作ってSQL文を組み立てますよね。

そこでちょっと速度が気になったので計測してみたよって話です。

連想配列からINSERT文を組み立てる理由

この雑記ブログでは思いついたときに思いついたことを書いているため、PHP初級者が見ている可能性も考えて、一応INSERT文を組み立てる理由についてサラっと。

よくあるPDOのINSERTの例ではこんな感じだと思います。

//データベース作成
$pdo = new PDO('sqlite:test.db');
//テーブル作成
$pdo->query('CREATE TABLE test (column1,column2,column3,column4,column5)');

//レコードデータ準備
$row = array();
$row[':column1'] = 'A';
$row[':column2'] = 'B';
$row[':column3'] = 'C';
$row[':column4'] = 'D';
$row[':column5'] = 'E';

//SQL文準備
$st = $pdo->prepare('INSERT INTO test (column1,column2,column3,column4,column5)'.
					' VALUES (:column1,:column2,:column3,:column4,:column5)');

//INSERT文実行
$st->execute($row);

この書き方、最高にダルくないですか？

いったい何回同じ列名を書くんだよ、って話です。（3回だけどｗ）

カラム数が少なければ連想配列を使わず、VALUES以降を "?,?,?,?,?" として短くすることも出来ますが、そうすると配列の順番を厳密に決めておかないといけないので、保守性が悪くなります。

複数のプログラマーがいる環境ではもちろんのこと、自分ひとりで作っている小さなプロジェクトだとしても、1週間後の貴方は別人です。いやマジで。

少なくともぼくは1週間前に自分で書いたコードなんてほとんど他人が書いたコードと変わりませんｗ

特にシステム構築の初期段階ではテーブルのレイアウト変更もよくあるでしょうし、仕様書の見やすさを優先して、既存の項目の中間に新しい項目を追加することになるかも知れません。

そんなとき、先述の書き方では修正する箇所が3箇所もあって面倒くさいですよね。

修正箇所

1)レコードデータを準備している部分
2)SQL文の項目名部分 (column1,column2...)
3)SQL文のプレースホルダ部分 (:column1,:column2...)

この例ではcolumn1/2/3/4/5と単純な列名だし、INSERT文も単純だからあまり感じないかもですが、様々な列名が入り乱れて条件分岐で値をセットして、と複雑になってくると$rowの変更箇所ですらわかりづらい。

しかも「なかったらINSERTあったらUPDATE」のいわゆるUPSERTと呼ばれるON DUPLICATE文なども絡んでくると修正箇所が更に増えて発狂しそうになります。

そもそも、INSERTするレコードはもう連想配列$rowで作ってあるのだから、その$rowからINSERT文を生成すれば良いじゃん、ということです。

連想配列からINSERT文を組み立てる方法

色々やり方はあると思いますが、ぼくは見やすさを優先してこんな書き方をしてきました。

連想配列からINSERT文を生成する関数

function genQueryInsert($tablename, $row)
{
	$keys1 = '';
	$keys2 = '';
	foreach ($row as $key => $value) {
		$keys1 .= $key . ','; //項目名を列挙
		$keys2 .= ':' . $key . ','; //プレースホルダを列挙
	}
	$keys1 = substr($keys1, 0, -1);
	$keys2 = substr($keys2, 0, -1);
	return 'INSERT INTO '.$tablename.' ('.$keys1.') VALUES ('.$keys2.')';
}

使い方

//レコードデータ準備
$row = array();
$row['column1'] = 'A';
$row['column2'] = 'B';
$row['column3'] = 'C';
$row['column4'] = 'D';
$row['column5'] = 'E';

$query = genQueryInsert('test', $row);
echo $query;

実行結果

INSERT INTO test (column1,column2,column3,column4,column5) VALUES (:column1,:column2,:column3,:column4,:column5)

解説

どうでしょう。最初のPDOの例に比べるとすごくシンプルじゃないですか。

列名を変更したり、セットする値を変える場合でも連想配列$rowをいじっているところを修正するだけです。

実際に使う場合は genQueryInsert という関数ではなく、$pdoも渡して実行まで任せちゃっても良いかも知れません。

システムによっては大量INSERTする場合もあるでしょうから、個人的には prepare までにしておいたほうが良いかなぁ、とは思いますけど。

連想配列からINSERT文を組み立てる方法 part2

長いこと使ってきた先述の方法ですが、「PHPで拡張子等の末尾文字列を比較するのに最も速い方法を検証してみた」という記事を書いたときに、あれ…文字列操作ってけっこう遅いんじゃないかな…と今更ながら不安になってきました。

implodeでINSERT文を生成する例

function genQueryInsert($tablename, $row)
{
	$keys = array_keys($row);
	$columns = implode(',', $keys);
	$values = ':'.implode(',:', $keys);
	return 'INSERT INTO '.$tablename.' ('.$columns.') VALUES ('.$values.')';
}

……………………見やすさを優先してforeach文で泥臭くSQL文を作っていましたが、むしろimplode関数使ったほうが見やすいのではないだろうかｗ

やばい。これで速度が同等ならarray_keysとimplode使ったほうが良いかも。

速度比較してみる

$row = array();
$row['column1'] = 'A';
$row['column2'] = 'B';
$row['column3'] = 'C';
$row['column4'] = 'D';
$row['column5'] = 'E';

$t1 = microtime(true);
for ($i = 0; $i < 100000; $i++) {
	$query = genQueryInsert('tablename', $row);
}
$t2 = microtime(true);
echo ($t2 - $t1).'秒';
速度が気になったので、従来のforeachでSQL文を作る方法と、implodeで組み立てる方法で速度を測ってみました。

結果

foreach0.12328910827637秒
implode0.06449294090271秒

……………わーい。圧倒的にarray_keysとimplode使ったほうが早いわｗｗｗ

試したのは第6世代くらいのCore i3マシンで、PHPのバージョンは7.4ですから、環境によっても違うとは思いますが、まぁそもそも後者のほうがコードとして見やすいよね…。

まとめ

  • INSERT文を作るために同じ列名をあちこちで使うのは保守性が悪い。
  • よって、連想配列からINSERT文を生成しようそうしよう。
  • その場合、foreachでぐりぐり作るより、implodeで結合したほうが速度が早かった。

ということでした。

一応、引数にPDOを加えたバージョンも掲載しておきます。

/************************************************
 * 連想配列からINSERT文を生成してPDOStatementを返す
 *
 ************************************************/
function pdoPrepareInsert($pdo, $tablename, $row)
{
	$keys = array_keys($row);
	$columns = implode(',', $keys);
	$values = ':'.implode(',:', $keys);
	$st = $pdo->prepare('INSERT INTO '.$tablename.' ('.$columns.') VALUES ('.$values.')');
	return $st;
}
/************************************************
 * 連想配列からINSERT文を生成してそのまま実行する
 *
 ************************************************/
function pdoInsert($pdo, $tablename, $row)
{
	$keys = array_keys($row);
	$columns = implode(',', $keys);
	$values = ':'.implode(',:', $keys);
	$st = $pdo->prepare('INSERT INTO '.$tablename.' ('.$columns.') VALUES ('.$values.')');
	$st->execute($row);
}

関連記事

コメント

新しいコメントを投稿する

[新規投稿]
 
TOP