MySQL 使用 ON DUPLICATE KEY UPDATE 中的 IF()

在MySQL中，ON DUPLICATE KEY UPDATE语句可以用于更新已存在的行或插入新行。而IF()函数也是在MySQL中常用的一个函数，它可以在查询语句中生成条件。本文将讲解如何在ON DUPLICATE KEY UPDATE语句中使用IF()函数。

阅读更多：MySQL 教程

ON DUPLICATE KEY UPDATE 的使用

ON DUPLICATE KEY UPDATE语句的语法如下：

INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (value1, value2, value3, ...) 
ON DUPLICATE KEY UPDATE 
    column1 = value1, 
    column2 = value2, 
    ...

当在插入记录时，如果发现唯一索引或主键约束冲突，则会触发该语句执行UPDATE操作。可以为每个列提供一个新的值，或者利用已经存在的值，更新某个列的值。

举个例子，有一张名为students的表，其结构如下：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    gender VARCHAR(10) NOT NULL
);

如果想往该表插入一条数据，如果该数据的id已经存在，则更新该数据的其他列。可以使用以下SQL语句：

INSERT INTO students(id, name, age, score, gender) 
VALUES(1, '小明', 18, 87.50, '男') 
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    age = VALUES(age),
    score = VALUES(score),
    gender = VALUES(gender);

如果该表中已经有了id为1的数据，则会将该行数据的其他列更新为所指定的值，如果不存在，则会直接插入。

IF() 函数的使用

而IF()函数的语法如下：

IF(expr1, expr2, expr3)

如果expr1的值为真，则返回expr2的值，否则返回expr3的值。在查询语句中，可以将该函数用于生成条件表达式。

以下是一个例子，根据学生成绩的不同等级，将不同的积分赠送给该学生：

SELECT 
    IF(score >= 90, 5, IF(score >= 80, 3, IF(score >= 70, 2, IF(score >= 60, 1, 0)))) AS bonus_points
FROM students
WHERE id = 1;

在该例子中，IF()函数嵌套多层，其作用是：

如果学生的分数大于等于90，则赠送5个积分。
如果学生的分数在80到89之间，则赠送3个积分。
如果学生的分数在70到79之间，则赠送2个积分。
如果学生的分数在60到69之间，则赠送1个积分。
如果学生不满足以上条件，则不赠送积分。

在 ON DUPLICATE KEY UPDATE 中使用 IF()

使用IF()函数可以为ON DUPLICATE KEY UPDATE语句提供更加灵活的更新操作。在该语句中，可以将IF()函数作为目标列的新值，以实现更新的条件。

例如，当要向上面所示的students表中插入一行数据时，如果其中包含某个年龄和性别的学生，则将该学生的分数更新为新插入记录的分数。否则，将直接插入新记录。

可以使用以下语句实现：

INSERT INTO students(id, name, age, score, gender) 
VALUES(1, '小明', 18, 87, '男') 
ON DUPLICATE KEY UPDATE 
    score = IF(age = 18 AND gender = '男', VALUES(score), score);

在该语句中，如果id为1的数据已经存在，则以原数据的score值为条件进行更新，如果符合age=18 AND gender='男'这个条件，则将该记录的score更新为新插入记录的score值，否则不进行更新操作。

可以使用以下语句验证结果：

SELECT * FROM students WHERE id = 1;

输出结果如下：

+----+--------+-----+-------+--------+
| id | name   | age | score | gender |
+----+--------+-----+-------+--------+
|  1 | 小明   |  18 | 87.00 | 男     |
+----+--------+-----+-------+--------+

可以看到，原有的学生记录已经被更新，只有分数这一列发生了变化。

继续使用以下SQL语句插入一条新记录：

INSERT INTO students(id, name, age, score, gender)
VALUES(2, '小红', 16, 93, '女')
ON DUPLICATE KEY UPDATE
    score = IF(age = 18 AND gender = '男', VALUES(score), score);

在该语句中，新插入记录的ID为2，名称为小红，年龄为16岁，分数为93分，性别为女。因为ID为2的记录在表中并不存在，所以会直接插入新数据。

可以使用以下语句检查结果：

SELECT * FROM students;

输出结果如下：

+----+--------+-----+-------+--------+
| id | name   | age | score | gender |
+----+--------+-----+-------+--------+
|  1 | 小明   |  18 | 87.00 | 男     |
|  2 | 小红   |  16 | 93.00 | 女     |
+----+--------+-----+-------+--------+

可以看到，新记录已经成功插入。