当前位置： 文江博客话题详情

如何在一个 MySQL 查询中组合两个 UPDATE 语句？

发布于 2024-10-10 19:42:00 字数 285 浏览 3 评论 0原文

您好，

如何在一个查询中执行两个 UPDATE 语句，例如：

UPDATE albums SET isFeatured = '0' WHERE isFeatured = '1'

结合

UPDATE albums SET isFeatured = '1' WHERE id = '$id'

基本上，当精选一张新专辑时，先前精选的专辑将切换回正常状态，而新精选的专辑将设置为活动状态。

谢谢！

原文

Greetings,

How would one go about performing two UPDATE statements in one query, for example:

UPDATE albums SET isFeatured = '0' WHERE isFeatured = '1'

combined with

UPDATE albums SET isFeatured = '1' WHERE id = '$id'

Basically, when a new album is featured, the previously featured album is switched back to normal and the newly featured one is set to active.

Thanks!

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（7）

做个ˇ局外人 2024-10-17 19:42:00

试试这个：

UPDATE albums SET isFeatured = IF(id!='$id', '0','1')

Try this:

UPDATE albums SET isFeatured = IF(id!='$id', '0','1')
回复 原文
深爱成瘾 2024-10-17 19:42:00

当您必须做此类事情时，这表明您的数据模型是错误的，可以进行一些修复。

因此，我建议添加一个单独的表 featured_albums （FK：int id_album）并使用它来确定专辑是否为精选专辑。

您的更新变为

DELETE FROM featured_album; INSERT INTO featured_album SET id_album = $id;

当选择加入表格时

SELECT album.id,
       album.name, 
       ( id_album IS NOT NULL ) AS isfeatured
FROM   album
LEFT JOIN featured_album ON id_album = album.id

按照上面的要求扩展基本上我建议添加一个表格，其中包含一行指示当前选定的专辑。这是一对一的关系，即album 表中的一条记录在feature_albums 表中具有一条相关记录。请参阅关系类型。

专辑架构图

您从专辑表中删除 isFeatured 字段并添加一个新表。

CREATE TABLE `featured_album` (
    `id_album` INTEGER NOT NULL,
    FOREIGN KEY (id_album) REFERENCES `album` (`id`)
);

DELETE FROM .. INSERT INTO 行通过在表中创建条目来设置特色专辑。

带有 LEFT JOIN 的 SELECT 语句将从专辑表中提取记录，并连接那些与featured_album表匹配的记录，在我们的例子中，只有一条记录会匹配，因此由于featured_album表中有一个字段，它将为所有记录返回NULL除精选专辑外的唱片。

因此，如果我们这样做，

SELECT album.id, album.name, featured_album.id_album as isFeatured0
FROM   album
LEFT JOIN featured_album ON id_album = album.id

我们会得到如下内容：

+----+----------------+------------+
| id | name           | isFeatured |
+----+----------------+------------+
|  1 | Rumours        |       NULL |
|  2 | Snowblind      |       NULL |
|  3 | Telegraph road |          3 |
+----+----------------+------------+

即 isFeatured 的 NULL 或 ID。

通过添加 ( id_album IS NOT NULL ) AS isfeatured 并使用第一个查询，我们得到

+----+----------------+------------+
| id | name           | isfeatured |
+----+----------------+------------+
|  1 | Rumours        |          0 |
|  2 | Snowblind      |          0 |
|  3 | Telegraph road |          1 |
+----+----------------+------------+

isfeatured 的即 0/1，这使得事情更具可读性，尽管如果您在 PHP 中处理结果，它不会'不会对你的代码产生影响。

When you have to do this sort of thing it is an indicator that your data model is wrong and could do with some fixing.

So, I'd recommend to add a seperate table featured_albums (FK: int id_album) and use that to determine if the album is featured.

Your update becomes

DELETE FROM featured_album; INSERT INTO featured_album SET id_album = $id;

When selecting join the tables

SELECT album.id,
       album.name, 
       ( id_album IS NOT NULL ) AS isfeatured
FROM   album
LEFT JOIN featured_album ON id_album = album.id

As requested to expand on the above basically I'm suggesting adding a table that will contain a row indicating the currently selected album. This is a 1 to 1 relationship, i.e. one record in the album table has one related record in the feature_albums table. See Types of Relationship.

Album Schema Diagram

You remove the isFeatured field from the album table and add a new table.

CREATE TABLE `featured_album` (
    `id_album` INTEGER NOT NULL,
    FOREIGN KEY (id_album) REFERENCES `album` (`id`)
);

The DELETE FROM .. INSERT INTO line sets the featured album by creating an entry in the table.

The SELECT statement with the LEFT JOIN will pull in the records from the album table and join those that match from the featured_album table, in our case only one record will match so as there is one field in the featured_album table it will return NULL for all records except the featured album.

So if we did

SELECT album.id, album.name, featured_album.id_album as isFeatured0
FROM   album
LEFT JOIN featured_album ON id_album = album.id

We'd get something like the following:

+----+----------------+------------+
| id | name           | isFeatured |
+----+----------------+------------+
|  1 | Rumours        |       NULL |
|  2 | Snowblind      |       NULL |
|  3 | Telegraph road |          3 |
+----+----------------+------------+

i.e. a NULL for isFeatured or an ID.

By adding the ( id_album IS NOT NULL ) AS isfeatured and using the first query we get

+----+----------------+------------+
| id | name           | isfeatured |
+----+----------------+------------+
|  1 | Rumours        |          0 |
|  2 | Snowblind      |          0 |
|  3 | Telegraph road |          1 |
+----+----------------+------------+

i.e. 0/1 for isfeatured which makes things more readable, although if you're processing the results in PHP it won't make a difference to your code.

回复 原文
葬シ愛 2024-10-17 19:42:00

您可以使用 CASE WHEN 语句和请记住在必要时设置原始值（下面的 ELSE 子句）并根据需要排序 CASE 条件（在下面的语句中，如果请求 id 的行也有 isFeatured = 1，则 isFeatured 将为 0，要更改它，请交换 WHEN 子句）。

UPDATE albums 
SET isFeatured = CASE 
  WHEN isFeatured = '1' THEN '0' 
  WHEN id = '$id' THEN '1'
  ELSE isFeatured
END

You can use CASE WHEN statement and remember to set original value where necessary (ELSE clause below) and order CASE conditions as required (in statement below isFeatured will be 0 if row having requested id also has isFeatured = 1, to change it swap WHEN clauses).

UPDATE albums 
SET isFeatured = CASE 
  WHEN isFeatured = '1' THEN '0' 
  WHEN id = '$id' THEN '1'
  ELSE isFeatured
END
回复 原文
§对你不离不弃 2024-10-17 19:42:00

你就是不能。您只能选择一组应更新的记录，然后只能对所有记录执行一项操作。 使用函数来解决此问题时要

 UPDATE x SET col1 = 1 WHERE col1 = 0 AND col1 = 0 WHERE col1 = 1;

小心，因为需要对每一行进行评估，这可能会变得非常昂贵。

You just can't. You can only select one group of records that should be updated and can then only perform one operation on all of them. It's not possible to do

 UPDATE x SET col1 = 1 WHERE col1 = 0 AND col1 = 0 WHERE col1 = 1;

Be careful when using functions to work around this, as they need to be evaluated for every row and this can become really expensive.

回复 原文
—━☆沉默づ 2024-10-17 19:42:00

当索引位于 if 函数内部时，MySQL 无法使用索引：
您需要该函数的索引，这在 MySQL 中是不可能的。

另请参阅： 如何在MySql中的DATETIME字段的日期部分创建索引

我正在使用员工测试数据库http://dev.mysql.com/doc/employee/en/employee.html

mysql> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.37 sec)

将所有性别设置为男性，以便它模仿问题。

mysql> update employees set gender = 'M';
Query OK, 1 row affected (9.11 sec)
Rows matched: 300024  Changed: 1  Warnings: 0

mysql>  select emp_no, gender from employees order by emp_no limit 2;
+--------+--------+
| emp_no | gender |
+--------+--------+
|  10001 | M      |
|  10002 | M      |
+--------+--------+
2 rows in set (0.00 sec)

将一名员工设置为女性。
（请注意，它使用索引并且几乎是即时的。）

mysql> update employees set gender = 'F' where emp_no = 10001;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

现在我们使用建议的答案。 （请注意，它不使用索引并触及每一行。）

mysql> update employees set gender = if(emp_no=10002, 'F', 'M');
Query OK, 2 rows affected (10.67 sec)
Rows matched: 300024  Changed: 2  Warnings: 0

索引有帮助吗？

> mysql> create index employees_gender_idx  on employees(gender);
Query OK, 300024 rows affected (21.61 sec)
Records: 300024  Duplicates: 0  Warnings: 0

> mysql> update employees set gender = if(emp_no=10001, 'F', 'M');
Query OK, 2 rows affected (9.02 sec)
Rows matched: 300024  Changed: 2  Warnings: 0

Nope.

也有人说 MySQL 只会查看需要更改的行。

mysql> update employees set gender = 'M';
Query OK, 1 row affected (8.78 sec)
Rows matched: 300024  Changed: 1  Warnings: 0

猜猜不是。如果使用 WHERE 子句会怎样？

mysql> update employees set gender = 'M' where gender ='F';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 0  Changed: 0  Warnings: 0

哎呀，这么快，现在它使用了索引。

Mysql 不知道 IF 函数将返回什么，并且必须执行全表扫描。请注意，WHERE 确实表示“哪里”，而 SET 确实表示“设置”。您不能指望数据库只是安排所有子句以获得良好的性能。

正确的解决方案是发出两次更新（如果使用索引，这几乎是即时的。）

注意，有人说 MySQL 会神奇地知道只更新它需要更改的行。

MySQL is unable to use the index when it is inside an if function:
You need an index on the function which is not possible in MySQL.

see also: How does one create an index on the date part of DATETIME field in MySql

I am using the employee test database http://dev.mysql.com/doc/employee/en/employee.html

mysql> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.37 sec)

Set all genders to male so it mimics the question.

mysql> update employees set gender = 'M';
Query OK, 1 row affected (9.11 sec)
Rows matched: 300024  Changed: 1  Warnings: 0

mysql>  select emp_no, gender from employees order by emp_no limit 2;
+--------+--------+
| emp_no | gender |
+--------+--------+
|  10001 | M      |
|  10002 | M      |
+--------+--------+
2 rows in set (0.00 sec)

Set one employee to female.
(Notice it uses the index and is almost instant.)

mysql> update employees set gender = 'F' where emp_no = 10001;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now we use the suggested answer. (Notice it does not use the index and touches every row.)

mysql> update employees set gender = if(emp_no=10002, 'F', 'M');
Query OK, 2 rows affected (10.67 sec)
Rows matched: 300024  Changed: 2  Warnings: 0

Will an index help?

> mysql> create index employees_gender_idx  on employees(gender);
Query OK, 300024 rows affected (21.61 sec)
Records: 300024  Duplicates: 0  Warnings: 0

> mysql> update employees set gender = if(emp_no=10001, 'F', 'M');
Query OK, 2 rows affected (9.02 sec)
Rows matched: 300024  Changed: 2  Warnings: 0

Nope.

It was also said that MySQL is only going to look at the rows that need to be changed.

mysql> update employees set gender = 'M';
Query OK, 1 row affected (8.78 sec)
Rows matched: 300024  Changed: 1  Warnings: 0

Guess not. What if use a WHERE clause?

mysql> update employees set gender = 'M' where gender ='F';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Gee that fast, now it used the index.

Mysql has no idea what the IF function will return and must do a full table scan. Notice that WHERE really does mean where and SET really does mean set. You can't expect the DB to just arrange all your clauses to get good performance.

The correct solution is to issue two updates (which if use indexes will be almost instant.)

Notice, it was said elsewhere that MySQL will magically know only update the rows it needs to change.

回复 原文
无敌元气妹 2024-10-17 19:42:00

在提供的优秀答案中添加替代方法
@too 代替 CASE IF 语句 -

UPDATE album
    -> SET isFeatured = IF (
    -> isFeatured = '1', '0', IF (
    -> id = '$id', '1', isFeatured
    -> ));

Adding an alternate method to the excellent answer provided by
@too where instead of CASE IF statement is used -

UPDATE album
    -> SET isFeatured = IF (
    -> isFeatured = '1', '0', IF (
    -> id = '$id', '1', isFeatured
    -> ));
回复 原文
万水千山粽是情ミ 2024-10-17 19:42:00

我认为你不能，或者至少不能以一种简洁或实用的方式。

如果您想从 php/whatever 进行一次调用，那么您可以用分号分隔它们，如下所示：

UPDATE albums SET isFeatured = '0' WHERE isFeatured = '1';UPDATE albums SET isFeatured = '1' WHERE id = '$id';

I don't think you can, or at least not in a neat or practical way.

If you're wanting to do one call from php/whatever then you can seperate them with semicolons thus:

UPDATE albums SET isFeatured = '0' WHERE isFeatured = '1';UPDATE albums SET isFeatured = '1' WHERE id = '$id';
回复 原文
~没有更多了~

关于作者

不再让梦枯萎

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

烙印

文章 0 评论 0

singlesman

文章 0 评论 0

给自己一个微笑

文章 0 评论 0

独孤求败

文章 0 评论 0

晨钟暮鼓

文章 0 评论 0

我是自愿种绣球花的

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文