MySQL 自4.1版以后开始支持INSERT … ON DUPLICATE KEY UPDATE语法,使得原本需要执行3条SQL语句(SELECT,INSERT,UPDATE),缩减为1条语句即可完成。
INSERT ... ON DUPLICATE KEY UPDATE,当插入的记录会引发主键冲突或者违反唯一约束时,则使用UPDATE更新旧的记录,否则插入新记录。
例如ipstats表结构如下:
CREATE TABLE
ipstats (
ip VARCHAR
(
15
)
NOT NULL
UNIQUE
,
clicks SMALLINT
(
5
)
UNSIGNED
NOT NULL
DEFAULT
'0'
)
;
原本需要执行3条SQL语句,如下:
IF
(
SELECT
* FROM
ipstats WHERE
ip='192.168.0.1'
)
{
UPDATE
ipstats SET
clicks=clicks+1
WHERE
ip='192.168.0.1'
;
}
else {
INSERT
INTO
ipstats (
ip, clicks)
VALUES
(
'192.168.0.1'
, 1
)
;
}
而现在只需下面1条SQL语句即可完成:
INSERT
INTO
ipstats VALUES
(
'192.168.0.1'
, 1
)
ON DUPLICATE KEY UPDATE
clicks=clicks+1
;
注意,要使用这条语句,前提条件是这个表必须有一个唯一索引或主键。
再看一例子:
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid | int(11) | NO | PRI | | |
| uname | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from test;
+-----+--------+
| uid | uname |
+-----+--------+
| 1 | uname1 |
| 2 | uname2 |
| 3 | me |
+-----+--------+
3 rows in set (0.00 sec)
mysql> INSERT INTO test values ( 3,'insertName' )
-> ON DUPLICATE KEY UPDATE uname='updateName';
Query OK, 2 rows affected (0.03 sec)
mysql> select * from test;
+-----+------------+
| uid | uname |
+-----+------------+
| 1 | uname1 |
| 2 | uname2 |
| 3 | updateName |
+-----+------------+
3 rows in set (0.00 sec)
mysql> create index i_test_uname on test(uname);
Query OK, 3 rows affected (0.20 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test VALUES ( 1 , 'uname2')
-> ON DUPLICATE KEY UPDATE uname='update2records';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test;
+-----+----------------+
| uid | uname |
+-----+----------------+
| 2 | uname2 |
| 1 | update2records |
| 3 | updateName |
+-----+----------------+
3 rows in set (0.00 sec)
插入时会与两条记录发生冲突,分别由主键和唯一索引引起。但最终只UPDATE了其中一条。这在手册中也说明了,有多个唯一索引(或者有键也有唯一索引)的情况下,不建议使用该语句。
分享到:
相关推荐
当你在`INSERT`语句后添加`ON DUPLICATE KEY UPDATE`,如果新插入的行违反了唯一性约束,MySQL将执行指定的`UPDATE`操作。这里的“唯一性”指的是任何具有唯一索引或主键的列。例如,如果你有一个名为`consume_locus...
- 示例中,当再次尝试`INSERT`一个`a=3`的记录时,由于`b`字段在`ON DUPLICATE KEY UPDATE`部分被指定更新为'r5',所以原有记录的`b`字段被更新,而`c`字段保持不变。 - 对于`AUTO_INCREMENT`字段,`INSERT INTO ....
INSERT INTO table(a, b, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1; UPDATE table SET c = c + 1 WHERE a = 1; ``` 当尝试插入的记录 `(1, 2, 3)` 中的 `a` 值已存在于表中时,`ON DUPLICATE KEY ...
在MySQL中,`REPLACE INTO` 和 `INSERT INTO ON DUPLICATE KEY UPDATE` 是两种处理数据插入时冲突的方法,主要用于处理当试图插入一条记录,而该记录的唯一键或主键已经在表中存在的情况。这两种方法都可以实现如果...
mysql “ON DUPLICATE KEY UPDATE” 语法如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列...
在MySQL数据库中,`ON DUPLICATE KEY UPDATE` 是一个非常实用的特性,它允许你在执行INSERT语句时,如果插入的数据违反了唯一性约束(即存在相同的唯一索引),则自动执行UPDATE操作来更新已有记录。这个功能在处理...
INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1; ``` 这将自动检测到`ip`列的唯一性约束,如果IP已存在,就更新`clicks`列,增加1次点击。 总结一下`ON DUPLICATE KEY ...
ON DUPLICATE KEY UPDATE`这两种语句。这两种SQL语句可以帮助我们高效地处理数据冲突,避免繁琐的循环操作,提高数据库操作的效率。 首先,我们来看`REPLACE`语句。`REPLACE`类似于`INSERT`,但它会在检测到主键或...
在MySQL数据库中,如果你需要执行一个操作,即当数据存在时进行更新,如果不存在则进行插入,你可以使用`INSERT INTO...ON DUPLICATE KEY UPDATE`语句。这个功能强大的语句结合了`INSERT`和`UPDATE`操作,允许你在...
ON DUPLICATE KEY UPDATE` 和 `REPLACE INTO`。这两种方法都可以实现“合并”操作,即在保持唯一键约束的同时,对重复的数据进行更新。 首先,我们来看一下`INSERT INTO ... ON DUPLICATE KEY UPDATE` 语句。这个...
INSERT INTO table (a, c) VALUES (1, 3) ON DUPLICATE KEY UPDATE c = c + 1; UPDATE table SET c = c + 1 WHERE a = 1; ``` 如果新插入的行`a=1`已经存在,`c`的值会被加1。如果行作为新记录插入,`c`的值将是3。...
ON DUPLICATE KEY UPDATE` 是 MySQL 数据库中的一种高效操作,它结合了插入新记录和更新已有记录的功能。这个语句主要用于处理唯一性约束冲突,如 PRIMARY KEY 或 UNIQUE 索引的情况。当试图插入的记录与现有记录在...
MySQL中的`ON DUPLICATE KEY UPDATE`语句是一个非常实用的功能,它允许你在插入新记录时检查是否存在冲突的唯一键。如果遇到冲突(即试图插入的键已经存在),它会更新已有记录而不是抛出错误。这在处理批量导入或...
MySQL中的`INSERT`语句是用于向数据库表中插入新数据的命令,它有多种变体,包括`DELAYED`, `IGNORE`, 和 `ON DUPLICATE KEY UPDATE`。这些选项提供了不同的处理策略,以适应不同场景的需求。 1. **DELAYED**: `...
INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE KEY UPDATE columnA=IF(columnB>0,1,columnA) 更新多个字段: INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE ...