よく使われる機能のクエリSQL

$temp = DB::fetch_first(“SELECT distinct total+jewel_vote+forge_vote ,COUNT(*)+1 AS RANK FROM " . DB::table(‘vote_competition’) .” WHERE total+jewel_vote+forge_vote>" . $competition[‘all’] . " and aid={$aid} ORDER by forge_vote desc"); $rank = $temp[‘RANK’]; 1つ上の順位の選手の票数を直接クエリすることはできない if ($temp[’total+jewel_vote+forge_vote’]) { //ここでクエリされるのは1位の選手との票差です $up = $temp[’total+jewel_vote+forge_vote’]-$competition[‘all’]; }

方法1（変数を定義して累加ソート）

$temp = DB::fetch_all(“SELECT a.cid,a.total,a.forge_vote,a.jewel_vote,(@rowNum:=@rowNum+1) AS rank FROM pre_vote_competition AS a, (SELECT (@rowNum :=0) ) b WHERE aid={$aid} ORDER BY (a.total+a.forge_vote+a.jewel_vote) DESC “); foreach ($temp as $key => $value) { if ($value[‘cid’] == $cid) { //現在の自分のランキング $rank = $value[‘rank’]; if ($up) { $up = $up - ($value[’total’] + $value[‘forge_vote’] + $value[‘jewel_vote’]); } break; } //1つ上の順位の選手との票差 $up = $value[’total’] + $value[‘forge_vote’] + $value[‘jewel_vote’]; }

2、シーン：ある記事の前後の記事をクエリする（IDの欠番をサポート）

$sql = " SELECT * FROM " . DB::table($this->_table) . " WHERE aid IN ( SELECT CASE WHEN SIGN(aid - {$id}) > 0 THEN MIN(aid) WHEN SIGN(aid - {$id}) < 0 THEN MAX(aid) END AS aid FROM pre_exe_article WHERE aid <> {$id} GROUP BY SIGN(aid - {$id}) ORDER BY SIGN(aid - {$id}) ) ORDER BY aid ASC”; return DB::fetch_all($sql);

3、MySQLで複数レコードの複数フィールドを一括更新する
MySQLの更新文は非常にシンプルです。1つのレコードの特定のフィールドを更新する場合、通常は次のように記述します：

UPDATE mytable SET myfield = ‘value’ WHERE id = ‘1’;

もし同じフィールドを同じ値に更新する場合も、MySQLでは簡単で、WHERE句を修正するだけです：

UPDATE mytable SET myfield = ‘value’ WHERE id in (1,2,3);

では、複数のレコードをそれぞれ異なる値に更新する場合、多くの人は次のように書くかもしれません：

foreach ($display_order as $id => $ordinal) { $sql = “UPDATE categories SET display_order = $ordinal WHERE id = $id”; mysql_query($sql); }

つまり、ループ処理で1レコードずつ更新していく方法です。
1レコードごとにUPDATEを1回実行するため、パフォーマンスが非常に悪く、ブロッキングも発生しやすくなります。

では、1つのSQL文で一括更新を実現することはできないのでしょうか？
MySQLには一括更新を直接実現するメソッドは提供されていませんが、ちょっとしたテクニックで実現できます。

UPDATE mytable SET myfield = CASE id WHEN 1 THEN ‘3’ WHEN 2 THEN ‘4’ WHEN 3 THEN ‘5’ END WHERE id IN (1,2,3)

このSQL文の意味は、display_orderフィールドを更新するということです：

• idが1の場合、display_orderの値を3にする
• idが2の場合、display_orderの値を4にする
• idが3の場合、display_orderの値を5にする

つまり、条件文を1つにまとめているのです。

ここでのWHERE句はコードの実行に影響を与えませんが、SQLの実行効率を向上させます。

SQL文が変更が必要な行数だけを実行するようにします。ここでは3つのデータのみが更新され、WHERE句によって3行のみが実行されることが保証されます。

複数値の更新

UPDATE categories SET display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END, title = CASE id WHEN 1 THEN ‘New Title 1’ WHEN 2 THEN ‘New Title 2’ WHEN 3 THEN ‘New Title 3’ END WHERE id IN (1,2,3)

PHP関数としてカプセル化し、対応するデータを渡すことで、ワンクリックでSQLを生成します

/** * 一括更新関数 * @param $data array 更新対象のデータ、2次元配列形式 * @param array $params array 値が同じ条件、キーと値が対応する1次元配列 * @param string $table array テーブル * @param string $field string 値が異なる条件、デフォルトはid * @return bool|string */ function batchUpdate($data, $field, $table ,$params = []) { if (!is_array($data) || !$field || !$table || !is_array($params)) { return false; }

$updates = parseUpdate($data, $field);
$where = parseParams($params);

// キー名が$fieldの列の値をすべて取得し、値の両端にシングルクォートを付けて$fields配列に保存する
// array_column()関数はPHP5.5.0+が必要。それより古いバージョンの場合は自前で実装可能。
// 参考URL：http://php.net/manual/zh/function.array-column.php#118831
$fields = array\_column($data, $field);
$fields = implode(',', array\_map(function($value) {
	return "'".$value."'";
}, $fields));

$sql = sprintf("UPDATE \`%s\` SET %s WHERE \`%s\` IN (%s) %s", $table, $updates, $field, $fields, $where);

return $sql; }

/** * 2次元配列をCASE WHEN THENの一括更新条件に変換する * @param $data array 2次元配列 * @param $field string カラム名 * @return string SQL文 */ function parseUpdate($data, $field) { $sql = ‘’; $keys = array_keys(current($data)); foreach ($keys as $column) {

	$sql .= sprintf("\`%s\` = CASE \`%s\` \\n", $column, $field);
	foreach ($data as $line) {
		$sql .= sprintf("WHEN '%s' THEN '%s' \\n", $line\[$field\], $line\[$column\]);
	}
	$sql .= "END,";
}

return rtrim($sql, ',');

}

/** * WHERE条件を解析する * @param $params * @return array|string */ function parseParams($params) { $where = []; foreach ($params as $key => $value) { $where[] = sprintf(”`%s` = ‘%s’", $key, $value); }

return $where ? ’ AND ’ . implode(’ AND ‘, $where) : ‘’; }

文字列の一括置換

UPDATE `emlog_blog` SET `content` = replace (`content`,‘ws2.sinaimg.cn’,‘cdn.sinaimg.cn.52ecy.cn’) WHERE `content` LIKE ‘%ws2.sinaimg.cn%’