mysql中的INSERT ON DUPLICATE KEY UPDATE

平时我们在设计数据库表的时候总会设计 unique  或者 给表加上 primary key 的限制条件。


此时 插入数据的时候 ,经常会有以下这样的情况:


我们想向表中插入一条记录:

若表中存在以主键或唯一索引的记录,我们就更新该条记录,否则就插入一条新的记录。


代码上我们需要这么写:

$result = mysql_query('select * from xxx where id = 1');
$row    = mysql_fetch_assoc($result);

if($row)
{
    //执行SQL更新
}
else
{
    //执行SQL插入
}

但是这样写有两个问题:

    ①、效率太差,每次执行都要执行2个sql

    ②、高并发的情况下数据会出问题,不能保证原子性


还好MySQL为我们解决了这个问题:我们可以通过 ON DUPLICATE KEY UPDATE达到以上目的, 且能保证操作的原子性和数据的完整性。


ON DUPLICATE KEY UPDATE 可以达到以下目的

向表中插入一条记录的时候,若该数据的主键值或UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE 后面的操作,否则插入一条新的记录


INSERT ON DUPLICATE KEY UPDATE 闪亮登场

准备工作:创建一张表名字为 first_table

CREATE TABLE `first_table`  (
  `first_column` int(11) NULL DEFAULT NULL,
  `second_column` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  UNIQUE INDEX `first_column`(`first_column`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


正式开始:

对于主键或者有唯一性约束的列或列组合来说,新插入的记录如果和表中已存在的记录重复的话,我们可以选择的策略不仅仅是忽略该条记录的插入,也可以选择更新这条重复的旧记录。比如我们想在first_table表中插入一条记录,内容是(1, '哇哈哈'),我们想要的效果是:对于那些是主键或者具有UNIQUE约束的列或者列组合来说,如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值,那么就把待插入记录插到表中,否则按照规定去更新那条重复的记录中某些列的值。设计MySQL的大叔给我们提供了INSERT ... ON DUPLICATE KEY UPDATE ...的语法来实现这个功能:


mysql> INSERT INTO first_table (first_column, second_column) VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = '雪碧';
Query OK, 2 rows affected (0.00 sec)

mysql>

这个语句的意思就是,对于要插入的数据(1, '哇哈哈')来说,如果first_table表中已经存在first_column的列值为1的记录(因为first_column列具有UNIQUE约束),那么就把该记录的second_column列更新为'雪碧',看一下效果:

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 雪碧          |
|            2 | NULL          |
|            3 | ccc           |
|            4 | ddd           |
|            5 | NULL          |
|         NULL | fff           |
|            7 | ggg           |
|            8 | hhh           |
|            9 | iii           |
+--------------+---------------+
9 rows in set (0.00 sec)

mysql>


对于那些是主键或者具有UNIQUE约束的列或者列组合来说,如果表中已存在的记录中有与待插入记录在这些列或者列组合上重复的值,我们可以使用VALUES(列名)的形式来引用待插入记录中对应列的值,比方说下边这个INSERT语句:

mysql> INSERT INTO first_table (first_column, second_column) VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = VALUES(second_column);
Query OK, 2 rows affected (0.00 sec)

mysql>

其中的VALUES(second_column)就代表着待插入记录中second_column值,本例中就是'哇哈哈'。有的小伙伴儿就呵呵了,我直接写成下边这种形式不好么:

INSERT INTO first_table (first_column, second_column) VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = '哇哈哈';

嗯,是的,没有任何问题,但是在批量插入大量记录的时候该咋办呢?此时VALUES(second_column)就 派上了大用场:

mysql> INSERT INTO first_table (first_column, second_column) VALUES(2, '红牛'), (3, '橙汁儿') ON DUPLICATE KEY UPDATE second_column = VALUES(second_column);
Query OK, 4 rows affected (0.00 sec)
Records: 2  Duplicates: 2  Warnings: 0

mysql>

我们准备批量插入两条记录(2, '红牛')(3, '橙汁儿'),在遇到重复记录时把该重复记录的second_column列更新成待插入记录中second_column列的值就好了,所以效果是这样:

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 哇哈哈        |
|            2 | 红牛          |
|            3 | 橙汁儿        |
|            4 | ddd           |
|            5 | NULL          |
|         NULL | fff           |
|            7 | ggg           |
|            8 | hhh           |
|            9 | iii           |
+--------------+---------------+
9 rows in set (0.00 sec)

mysql>


注意:

①、ON DUPLICATE KEY UPDATE 是mysql的专有语句,sqlserver等数据库是没有该语句的。


②、如果INSERT ON DUPLICATE KEY UPDATE 语句执行的是插入操作,则受影响行的值显示1。如果执行的是更新操作,则受影响行的值显示2。为什么更新操作会导致受影响的行数是2呢?其实on duplicate key update语句更新操作并不是真正意义上的更新,而是先delete后,然后在执行了insert操作,所以受影响行数是2,如何证明?你可以insert一条不重复的记录,看一下主键的值和上次主键的值是否是连续起来的就知道了。



当我们根据自己和周围小团体的情况去推测整个社会时,会导致对社会的认知出现偏差。



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与