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