banner
阿珏酱

阿珏酱

いつもとは逆の電車に乗り、見たこともない風景を見に行く
twitter
github
facebook
bilibili
zhihu
steam_profiles
youtube

Query SQL for common functions

Tips: When you see this prompt, it means that the current article has been migrated from the original emlog blog system. The publication time of the article is too long ago, and the formatting and content may not be complete. Please understand.

Query SQL for Some Common Functions

Date: 2019-4-10 Author: Ajue Views: 1462 Comments: 1

image

1. Scenario: Query the ranking of a player and the number of votes difference from the previous player#

Method 2 (Query the number of players with fewer votes by removing duplicates and sorting)#

$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'];
Unable to directly query the number of votes for the previous player
if ($temp['total+jewel_vote+forge_vote']) {
        // The number of votes difference from the first player is queried here
    $up = $temp['total+jewel_vote+forge_vote']-$competition['all'];
}

Method 1 (Define variables for cumulative sorting)#

$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) {
        // Current player's ranking
 $rank = $value['rank'];
     if ($up) {
             $up = $up - ($value['total'] +  $value['forge_vote'] + $value['jewel_vote']);
            }
 break;
    }
    // Number of votes difference from the previous player
    $up = $value['total'] +  $value['forge_vote'] + $value['jewel_vote'];
}

2. Scenario: Query the previous and next articles of a post, supporting missing numbers#

$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. Batch update multiple fields of multiple records in MySQL#

The MySQL update statement is very simple, updating a field of a record, usually written like this:#

UPDATE mytable SET myfield = 'value' WHERE id = '1';

If updating the same field to the same value, MySQL is also very simple, just modify the where clause:#

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

But if updating multiple records to different values, many people may write it like this:#

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

That is, updating one record at a time in a loop.
Updating one record at a time is inefficient and can easily cause blocking.

So, can we update multiple records with one SQL statement?
MySQL does not provide a direct method for batch updating, but it can be achieved with a little trick.

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)

This SQL statement means updating the display_order field:

  • If id=1, then the value of display_order is 3,
  • If id=2, then the value of display_order is 4,
  • If id=3, then the value of display_order is 5.
The conditions are written together.

The where part here does not affect the execution of the code, but it improves the efficiency of SQL execution.
Ensure that the SQL statement only executes the necessary number of rows to be modified. Here, only 3 rows are updated, and the where clause ensures that only 3 rows are executed.

Updating multiple values#

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)

Encapsulate into a PHP function, pass in the corresponding data, and generate SQL with one click#

/**
 * Batch update function
 * @param $data array Data to be updated, in a two-dimensional array format
 * @param array $params array Conditions where the values are the same, in a key-value pair one-dimensional array format
 * @param string $table array Table
 * @param string $field string Condition where the values are different, default is 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);

	// Get all values in the $field column as keys, add single quotes to the values, and save them in the $fields array
	// The array_column() function requires PHP 5.5.0+, if it is lower than this version, you can implement it yourself,
	// Reference: http://php.net/manual/en/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;
}

/**
 * Convert a two-dimensional array into CASE WHEN THEN batch update conditions
 * @param $data array Two-dimensional array
 * @param $field string Column name
 * @return string SQL statement
 */
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, ',');
}

/**
 * Parse where conditions
 * @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) : '';
}

Batch replace strings#

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

User Comments:

image Headline News 2 years ago (2019-04-11)
The article is very good, I really like it.

Loading...
Ownership of this post data is guaranteed by blockchain and smart contracts to the creator alone.