ヒント：このヒントを見ると、現在の記事が元のemlogブログシステムから移行されたことを示しています。記事の公開日が古すぎて、レイアウトや内容が完全ではないかもしれません。ご了承ください。
一部の一般的な機能の SQL クエリ
日付：2019-4-10 阿珏 折腾代码 浏览：1462 回 コメント：1 件
1、シナリオ：選手の順位と前の選手との票数の差を検索する#
方法 2（重複を削除して自分より少ない数をソートして検索）#
$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'];
上位の選手の票数を直接検索できません
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;
}
//前の選手との票数の差
$up = $value['total'] + $value['forge_vote'] + $value['jewel_vote'];
}
2、シナリオ：記事の前後の記事を検索する、欠番をサポートする#
$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 の更新クエリは非常に簡単で、1 つのデータの 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 つのレコードを 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になります。
ここでの 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%'
ユーザーコメント：
ヘッドラインニュース 2 年前 (2019-04-11)
記事は非常に良いです、とても好きです。