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