多表更新的 UPDATE 优先顺序 [英] Order of precedence of UPDATE on Multiple-table update

查看：113
本文介绍了多表更新的 UPDATE 优先顺序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如果我有一个 UPDATE 语句，我正在执行 SET p.old_email = u.email, u.email = NULL，将 p.old_email = u.email总是发生在 u.email = NULL 之前?我问的原因是我没有目睹这种行为.

mysql>显示触发器；更新空集(0.01 秒)mysql>mysql>更新->用户 u INNER JOIN people p ON p.id = u.id INNER JOIN 实体 e ON e.id = p.id->SET p.old_username = NULL, p.username = 'myusername',->p.old_email = NULL, u.email = 'myemail@example.com',->e.record_status = 'inactive', e.date_modified = NOW(), e.modified_by_id =506836355->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;查询正常，3 行受影响(0.00 秒)匹配行数:3 更改:3 警告:0mysql>mysql>选择 p.old_username, p.username, p.old_email, u.email->FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entity e ON e.id = p.id->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;+--------------+------------+-----------+----------------------+|旧用户名 |用户名 |old_email |电子邮件 |+--------------+------------+-----------+----------------------+|空 |我的用户名 |空 |myemail@example.com |+--------------+------------+-----------+----------------------+1 行(0.00 秒)mysql>mysql>更新->用户 u INNER JOIN people p ON p.id = u.id INNER JOIN 实体 e ON e.id = p.id->SET p.old_username = p.username, p.username = NULL->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735Query OK，1 行受影响(0.00 秒)；匹配行数:1 更改:1 警告:0mysql>mysql>选择 p.old_username, p.username, p.old_email, u.email->FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entity e ON e.id = p.id->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;+--------------+----------+-----------+---------------------+|旧用户名 |用户名 |old_email |电子邮件 |+--------------+----------+-----------+---------------------+|我的用户名 |空 |空 |myemail@example.com |+--------------+----------+-----------+---------------------+1 行(0.00 秒)mysql>mysql>更新->用户 u INNER JOIN people p ON p.id = u.id INNER JOIN 实体 e ON e.id = p.id->SET p.old_email = u.email, u.email = NULL->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;查询正常，1 行受影响(0.00 秒)行匹配:2 更改:1 警告:0mysql>mysql>选择 p.old_username, p.username, p.old_email, u.email->FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entity e ON e.id = p.id->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;+--------------+----------+-----------+-------+|旧用户名 |用户名 |old_email |电子邮件 |+--------------+----------+-----------+-------+|我的用户名 |空 |空 |空 |+--------------+----------+-----------+-------+1 行(0.00 秒)mysql>mysql>更新->用户 u INNER JOIN people p ON p.id = u.id INNER JOIN 实体 e ON e.id = p.id->SET p.old_username = NULL, p.username = 'myusername',->p.old_email = NULL, u.email = 'myemail@example.com',->e.record_status = 'inactive', e.date_modified = NOW(), e.modified_by_id =506836355->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;查询正常，2 行受影响(0.00 秒)匹配行数:3 更改:2 警告:0mysql>mysql>选择 p.old_username, p.username, p.old_email, u.email->FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entity e ON e.id = p.id->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;+--------------+------------+-----------+----------------------+|旧用户名 |用户名 |old_email |电子邮件 |+--------------+------------+-----------+----------------------+|空 |我的用户名 |空 |myemail@example.com |+--------------+------------+-----------+----------------------+1 行(0.00 秒)mysql>mysql>更新->用户 u INNER JOIN people p ON p.id = u.id INNER JOIN 实体 e ON e.id = p.id->SET p.old_username = p.username, p.username = NULL,->p.old_email = u.email, u.email = NULL,->e.record_status = 'inactive', e.date_modified = NOW(), e.modified_by_id =506836355->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;查询正常，2 行受影响(0.00 秒)匹配行数:3 更改:2 警告:0mysql>mysql>选择 p.old_username, p.username, p.old_email, u.email->FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entity e ON e.id = p.id->哪里 u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;+--------------+----------+-----------+-------+|旧用户名 |用户名 |old_email |电子邮件 |+--------------+----------+-----------+-------+|我的用户名 |空 |空 |空 |+--------------+----------+-----------+-------+1 行(0.00 秒)mysql>mysql>

解决方案

来自 MySQL 文档:

<块引用>

以下语句中的第二个赋值将 col2 设置为当前(更新后的) col1 值，而不是原始 col1 值.结果是 col1 和 col2 具有相同的值.此行为与标准 SQL 不同.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

<块引用>

单表UPDATE赋值一般从左到右求值.对于多表更新，不能保证按任何特定顺序执行分配.

您正在执行多表更新，并且列分配没有按照文档所述的特定顺序进行.

If I have an UPDATE statement where I am doing SET p.old_email = u.email, u.email = NULL, will p.old_email = u.email always happen before u.email = NULL? The reason I ask is I am not witnessing such behavior.

mysql> SHOW TRIGGERS;
UPDATE
Empty set (0.01 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_username = NULL, p.username = 'myusername',
    -> p.old_email = NULL, u.email = 'myemail@example.com',
    -> e.record_status = 'inactive', e.date_modified = NOW( ), e.modified_by_id =506836355
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+------------+-----------+---------------------+
| old_username | username   | old_email | email               |
+--------------+------------+-----------+---------------------+
| NULL         | myusername | NULL      | myemail@example.com |
+--------------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_username = p.username, p.username = NULL
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735Query OK, 1 row affected (0.00 sec);

Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+----------+-----------+---------------------+
| old_username | username | old_email | email               |
+--------------+----------+-----------+---------------------+
| myusername   | NULL     | NULL      | myemail@example.com |
+--------------+----------+-----------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_email = u.email, u.email = NULL
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+----------+-----------+-------+
| old_username | username | old_email | email |
+--------------+----------+-----------+-------+
| myusername   | NULL     | NULL      | NULL  |
+--------------+----------+-----------+-------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_username = NULL, p.username = 'myusername',
    -> p.old_email = NULL, u.email = 'myemail@example.com',
    -> e.record_status = 'inactive', e.date_modified = NOW( ), e.modified_by_id =506836355
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+------------+-----------+---------------------+
| old_username | username   | old_email | email               |
+--------------+------------+-----------+---------------------+
| NULL         | myusername | NULL      | myemail@example.com |
+--------------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_username = p.username, p.username = NULL,
    -> p.old_email = u.email, u.email = NULL,
    -> e.record_status = 'inactive', e.date_modified = NOW( ), e.modified_by_id =506836355
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+----------+-----------+-------+
| old_username | username | old_email | email |
+--------------+----------+-----------+-------+
| myusername   | NULL     | NULL      | NULL  |
+--------------+----------+-----------+-------+
1 row in set (0.00 sec)

mysql>
mysql>

解决方案

From the MySQL documentation:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

You are performing multi-table updates, and the column assignments are not happening in a particular order as the documentation states.

这篇关于多表更新的 UPDATE 优先顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆