MySQL新增数据，存在就更新，不存在就添加(转帖加实测)

参考链接:https://blog.csdn.net/tiantang_1986/article/details/78037804

https://blog.csdn.net/woshihaiyong168/article/details/75082668?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task

 

今天本来想插入一条数据，没有这条数据就插入，有这条数据就跟新，但以前的学习中，好像没有碰到过这种情况，就网上查了点资料。笔记学习下。

INSERT IGNORE 这个是插入的时候用的，如果插入的时候，主键冲突，可以用这个IGNORE来避免报错。

mysql> CREATE TABLE xman(name VARCHAR(50) PRIMARY KEY, age INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT ignore INTO xman(name, age) VALUES('sidian',18);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT ignore INTO xman(name, age) VALUES('sidian',18);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO xman(name, age) VALUES('sidian',18);
ERROR 1062 (23000): Duplicate entry 'sidian' for key 'PRIMARY'

 经过实测需要插入的字段中有主键或者唯一才能生效.

 

第二个是我今天主要学习的.

INSERT INTO  ON DUPLICATE KEY UPDATE

mysql> SELECT * FROM xman;
+--------+-----------+------+
| name   | addr      | age  |
+--------+-----------+------+
| sidian | hangzhou  |   30 |
| sidian | guangzhou |   30 |
+--------+-----------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO xman(name,addr,age) VALUES('sidian','jianqiao',35) ON DUPLICATE KEY UPDATE age=50;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM xman;
+--------+-----------+------+
| name   | addr      | age  |
+--------+-----------+------+
| sidian | hangzhou  |   30 |
| sidian | guangzhou |   30 |
| sidian | jianqiao  |   35 |
+--------+-----------+------+
3 rows in set (0.00 sec)

mysql> INSERT INTO xman(name,addr,age) VALUES('sidian','jianqiao',35) ON DUPLICATE KEY UPDATE age=50;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM xman;
+--------+-----------+------+
| name   | addr      | age  |
+--------+-----------+------+
| sidian | hangzhou  |   30 |
| sidian | guangzhou |   30 |
| sidian | jianqiao  |   50 |
+--------+-----------+------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE xman;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xman  | CREATE TABLE `xman` (
  `name` varchar(100) DEFAULT NULL,
  `addr` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  UNIQUE KEY `name` (`name`,`addr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

 从上面的测试可以看出来，在插入的时候，主要是根据唯一的索引来判断是否升级，如果唯一索引冲突了，执行后面的 UPDATE

如果条件比较复杂，可以设置多字段的唯一属性。

 

最后是一个REPLACE INTO，就是把INSERT 换成了REPLACE。

mysql> SELECT * FROM xman;
+--------+-----------+------+--------+
| name   | addr      | age  | hobby  |
+--------+-----------+------+--------+
| sidian | hangzhou  |   30 | 唱歌   |
| sidian | guangzhou |   30 | 唱歌   |
| sidian | jianqiao  |   50 | 唱歌   |
+--------+-----------+------+--------+
3 rows in set (0.00 sec)

mysql> REPLCAE INTO xman VALUES('sidian','hangzhou',99);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLCAE INTO xman VALUES('sidian','hangzhou',99)' at line 1
mysql> REPLACE INTO xman VALUES('sidian','hangzhou',99);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> REPLACE INTO xman VALUES('sidian','hangzhou',99,'跳舞');
Query OK, 2 rows affected (0.00 sec)

mysql> REPLACE INTO xman VALUES('sidian','hangzhou2',99,'跳舞');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO xman SET name='8sidian';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM xman;
+---------+-----------+------+--------+
| name    | addr      | age  | hobby  |
+---------+-----------+------+--------+
| sidian  | hangzhou  |   99 | 跳舞   |
| sidian  | guangzhou |   30 | 唱歌   |
| sidian  | jianqiao  |   50 | 唱歌   |
| sidian  | hangzhou2 |   99 | 跳舞   |
| 8sidian | NULL      | NULL | NULL   |
+---------+-----------+------+--------+
5 rows in set (0.00 sec)

mysql> REPLACE INTO xman SET name='sidian', addr='hangzhou';
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM xman;
+---------+-----------+------+--------+
| name    | addr      | age  | hobby  |
+---------+-----------+------+--------+
| sidian  | hangzhou  | NULL | NULL   |
| sidian  | guangzhou |   30 | 唱歌   |
| sidian  | jianqiao  |   50 | 唱歌   |
| sidian  | hangzhou2 |   99 | 跳舞   |
| 8sidian | NULL      | NULL | NULL   |
+---------+-----------+------+--------+
5 rows in set (0.00 sec)

mysql> 

 REPLACE同样也实根据唯一的索引来判断是否重复，但他比较狠的是，如果发现了位置索引冲突就把老的删除了，然后把新的用上。

感觉下来还实ON DUPLICATE KEY 比较好一点