参考链接: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 比较好一点