迹忆客 专注技术分享

在本文中，我们将学习使用 CASE 语句、IF() 函数、INSERT ... ON DUPLICATE KEY UPDATE 子句和 UPDATE with JOIN() 函数在 MySQL 中更新多行中具有不同值的多个列。

在 MySQL 中使用不同的值更新多条记录（行）中的多列

有时，我们需要用数据库中的不同值更新多行中的多个列。 如果表中有几条记录，可以使用多个 UPDATE 语句。

假设表中有数百万行。 下面列出了一些更新表格的方法。

1. 使用 CASE 语句。
2. 使用 IF() 函数。
3. 使用 INSERT ... ON DUPLICATE KEY UPDATE。
4. 将 UPDATE 与 JOIN() 结合使用。

要学习上述方法，请创建一个名为 students 的表，将 ID、JavaScore 和 PythonScore 作为属性（列），其中 ID 是主键。 您可以按照本教程使用以下查询来创建和填充表。

示例代码：

# create a table
CREATE TABLE students(
  ID INT NOT NULL,
  JavaScore INT NOT NULL,
  PythonScore INT NOT NULL,
  PRIMARY KEY (ID));

# insert data
INSERT INTO students (ID, JavaScore, PythonScore)
VALUES
(1, 70, 65),
(2, 75, 80),
(3, 81, 89),
(4, 50, 70);

# display table data
SELECT * FROM students;

输出:

ID    JavaScore    PythonScore
1    70    65
2    75    80
3    81    89
4    50    70

创建并填充学生表后，我们可以使用上述方法。

使用 CASE 语句

示例代码：

UPDATE students
    SET JavaScore = (case
                    when ID = 1 then 75
                    when ID = 2 then 80
                    when ID = 3 then 86
                    when ID = 4 then 55
                    end),
        PythonScore = (case
                    when ID = 1 then 70
                    when ID = 2 then 85
                    when ID = 3 then 94
                    when ID = 4 then 75
                    end)
    WHERE ID in (1,2,3,4);

使用 SELECT 语句获取更新的结果。

SELECT * FROM students;

输出:

ID    JavaScore    PythonScore
1    75    70
2    80    85
3    86    94
4    55    75

我们使用 CASE 语句更新具有不同值的多行的多个列，该语句遍历所有条件并在满足第一个条件时输出一个项目（值）（如 if-then-else 语句）。 一旦条件为真，它就停止读取并返回相应的结果。

假设没有 TRUE 条件，则执行 ELSE 部分。 在没有 ELSE 部分的情况下，它返回 NULL。

如果有另一个 DATETIME 类型的字段，我们希望对所有记录保持不变，查询将如下所示。

示例代码：

UPDATE students
    SET JavaScore = (case
                    when ID = 1 then 75
                    when ID = 2 then 80
                    when ID = 3 then 86
                    when ID = 4 then 55
                    end),
        PythonScore = (case
                    when ID = 1 then 70
                    when ID = 2 then 85
                    when ID = 3 then 94
                    when ID = 4 then 75
                    end),
        DATEANDTIME = NOW()

    WHERE ID in (1,2,3,4);

使用 IF() 函数

示例代码：

UPDATE students SET
    JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))),
    PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL))))
WHERE ID IN (1,2,3,4);

执行 SELECT 命令以获取学生表的新值。

SELECT * FROM students;

输出:

ID    JavaScore    PythonScore
1    76    71
2    81    86
3    87    95
4    56    76

如果满足条件，我们使用 IF() 函数返回特定值。 否则，它返回另一个指定值。 它的语法是 IF(condition, TrueValue, FalseValue)。

你可能会有一个疑问，如果满足了 ID=1 的条件，那为什么还要去另一个 IF() 呢？ 我们使用如下嵌套的 IF() 函数来创建多个 IF。

IF(condition, TrueValue,
  IF(condition, TrueValue,
    IF(condition, TrueValue,
      IF(condition, TrueValue, FalseValue)
      )
    )
  )

让我们让它更容易理解。 在下面的代码片段中，我们有多个 IF，条件是否满足并不重要。

将检查每个 IF 条件并相应地设置值。 最后一个 IF 有 ELSE 部分，只有在第四个 IF 条件为 FALSE 时才会运行。

IF Condition
    TrueValue
IF Condition
    TrueValue
IF Condition
    TrueValue
IF Condition
    TrueValue
ELSE
    FalseValue

使用嵌套 IF() 函数的原因是用不同的值更新多行。

当需要更新多行中的多个列时，我们更喜欢使用 CASE 语句，因为它比嵌套的 IF() 函数更容易理解和管理。

在重复密钥更新时使用 INSERT ...

示例代码：

INSERT INTO students (ID, JavaScore, PythonScore)
VALUES
(1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77)
ON DUPLICATE KEY UPDATE
JavaScore = VALUES(JavaScore),
PythonScore = VALUES(PythonScore);

输出:

ID    JavaScore    PythonScore
1    77    72
2    82    87
3    88    96
4    57    77

此示例显示 INSERT ... ON DUPLICATE KEY UPDATE 。 通常，当我们 INSERT 到一个特定的表中时，它可能会导致 PRIMARY KEY 或 UNIQUE 索引中出现重复，这会导致错误。

但是，如果我们指定 ON DUPLICATE KEY UPDATE ，MySQL 会使用最新值更新现有记录。 如果在 PRIMARY KEY 中找到重复项，则该特定列的值将设置为其当前值。

虽然在编写本教程时 VALUES() 函数可以正常工作，但它会显示一条警告，指出 VALUES() 函数已被弃用，并将在未来的版本中删除。 您可以考虑 MySQL 文档以获得进一步的帮助。

将 UPDATE 与 JOIN() 结合使用

示例代码：

UPDATE students std
JOIN (
    SELECT 1 AS ID, 78 AS JavaScore, 73 AS PythonScore
    UNION ALL
    SELECT 2 AS ID, 83 AS JavaScore, 88 AS PythonScore
    UNION ALL
    SELECT 3 AS ID, 89 AS JavaScore, 97 AS PythonScore
    UNION ALL
    SELECT 4 AS ID, 58 AS JavaScore, 78 AS PythonScore
) temp
ON std.ID = temp.ID
SET std.JavaScore = temp.JavaScore, std.PythonScore = temp.PythonScore;

此解决方案仅在禁用安全模式时才有效。 我们可以在 MySQL Workbench 中禁用它，方法是转到“编辑”->“首选项”->“SQL 编辑器”并取消选中“安全模式”选项。

然后，重新启动 MySQL 服务器，执行上面给出的查询并使用 SELECT * FROM students; 命令得到如下结果。

输出：

ID    JavaScore    PythonScore
1    78    73
2    83    88
3    89    97
4    58    78

我们使用 SELECT 和 UNION ALL 在 JOIN() 中收集数据。 完成后，我们使用 JOIN() 连接所有数据，并在每个满足 ID 属性的条件下设置 JavaScore 和 PythonScore。