Mysql学习二 INSERT、INSERT IGNORE和REPLACE

最新推荐文章于 2023-07-21 10:10:28 发布
最新推荐文章于 2023-07-21 10:10:28 发布
阅读量439 收藏 1
点赞数
文章标签： Mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yanyubang/article/details/98763064
版权

先准备测试表和几条测试数据

CREATE TABLE IF NOT EXISTS `employees_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) NOT NULL DEFAULT "",
  `last_name` varchar(40) NOT NULL DEFAULT "",
  `position` varchar(25) NOT NULL DEFAULT "",
  `home_address` varchar(50) NOT NULL DEFAULT "",
  `home_phone` varchar(12) NOT NULL DEFAULT "",
  `employee_code` varchar(25) NOT NULL DEFAULT "",
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_code` (`employee_code`),
  KEY `phone` (home_phone),
  KEY `full_name` (`first_name`,`last_name`)
) ;

INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
  VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1');

INSERT

#插入主键相同的数据
mysql> INSERT INTO `employees_example` (`id`,`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
VALUES (3,'Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1');
1062 - Duplicate entry '3' for key 'PRIMARY'
#插入唯一索引相同的数据
mysql> INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
VALUES ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1');
1062 - Duplicate entry 'BM1' for key 'employee_code'

可以看到，插入主键或唯一索引相同的数据会出现错误。

INSERT IGNORE

我们先看下现有的数据

mysql> select * from employees_example;
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
| id | first_name | last_name | position                | home_address          | home_phone   | employee_code |
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
|  1 | Mustapha   | Mond      | Chief Executive Officer | 692 Promiscuous Plaza | 326-555-3492 | MM1           |
|  2 | Henry      | Foster    | Store Manager           | 314 Savage Circle     | 326-555-3847 | HF1           |
|  3 | Bernard    | Marx      | Cashier                 | 1240 Ambient Avenue   | 326-555-8456 | BM1           |
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
3 rows in set

执行之前的插入语句，将insert替换成insert ignore。

mysql> INSERT IGNORE `employees_example` (`id`,`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
VALUES (3,'Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1');
Query OK, 0 rows affected

mysql> INSERT IGNORE `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
VALUES ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1');
Query OK, 0 rows affected

查询表中数据，数据没有新增，但是不会报错。

但是我们再插入一条与主键和唯一索引不冲突的数据，然后查看表中数据。

mysql> INSERT IGNORE `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`) VALUES ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1');
Query OK, 1 row affected

mysql> select * from employees_example;
+----+------------+-----------+-------------------------+---------------------------+--------------+---------------+
| id | first_name | last_name | position                | home_address              | home_phone   | employee_code |
+----+------------+-----------+-------------------------+---------------------------+--------------+---------------+
|  1 | Mustapha   | Mond      | Chief Executive Officer | 692 Promiscuous Plaza     | 326-555-3492 | MM1           |
|  2 | Henry      | Foster    | Store Manager           | 314 Savage Circle         | 326-555-3847 | HF1           |
|  3 | Bernard    | Marx      | Cashier                 | 1240 Ambient Avenue       | 326-555-8456 | BM1           |
|  5 | Lenina     | Crowne    | Cashier                 | 281 Bumblepuppy Boulevard | 328-555-2349 | LC1           |
+----+------------+-----------+-------------------------+---------------------------+--------------+---------------+
4 rows in set

发现表中的自增主键：id 不连续了。

如果表中有自增主键，INSERT IGNORE 插入无主键且唯一索引冲突的数据，会使AUTO_INCREMENT发生变化。

REPLACE INTO

replace into的三种形式

1） replace into tbl_name(col_name, ...) values(...)
2） replace into tbl_name(col_name, ...) select ...
3） replace into tbl_name set col_name = value, ...

我们先试着执行一下几条sql

#替换唯一索引冲突的数据
mysql> REPLACE INTO `employees_example` (`first_name`, `last_name`, `employee_code`) VALUES ('Lenina', 'Crowne', 'LC1');
Query OK, 2 rows affected

mysql> SELECT * FROM employees_example;
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
| id | first_name | last_name | position                | home_address          | home_phone   | employee_code |
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
|  1 | Mustapha   | Mond      | Chief Executive Officer | 692 Promiscuous Plaza | 326-555-3492 | MM1           |
|  2 | Henry      | Foster    | Store Manager           | 314 Savage Circle     | 326-555-3847 | HF1           |
|  3 | Bernard    | Marx      | Cashier                 | 1240 Ambient Avenue   | 326-555-8456 | BM1           |
|  6 | Lenina     | Crowne    |                         |                       |              | LC1           |
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
4 rows in set

#替换主键冲突的数据
mysql>REPLACE INTO `employees_example` (`id`,`first_name`, `last_name`) VALUES (6,'Lenina', 'Crowne');
Query OK, 2 rows affected

mysql> SELECT * FROM employees_example;
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
| id | first_name | last_name | position                | home_address          | home_phone   | employee_code |
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
|  1 | Mustapha   | Mond      | Chief Executive Officer | 692 Promiscuous Plaza | 326-555-3492 | MM1           |
|  2 | Henry      | Foster    | Store Manager           | 314 Savage Circle     | 326-555-3847 | HF1           |
|  3 | Bernard    | Marx      | Cashier                 | 1240 Ambient Avenue   | 326-555-8456 | BM1           |
|  6 | Lenina     | Crowne    |                         |                       |              |               |
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
4 rows in set

#替换唯一索引和主键均冲突的数据
mysql> REPLACE INTO `employees_example` (`id`,`first_name`, `last_name`, `employee_code`) VALUES (6,'Henry', 'Foster', 'HF1');
Query OK, 3 rows affected

mysql> SELECT * FROM employees_example;
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
| id | first_name | last_name | position                | home_address          | home_phone   | employee_code |
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
|  1 | Mustapha   | Mond      | Chief Executive Officer | 692 Promiscuous Plaza | 326-555-3492 | MM1           |
|  3 | Bernard    | Marx      | Cashier                 | 1240 Ambient Avenue   | 326-555-8456 | BM1           |
|  6 | Henry      | Foster    |                         |                       |              | HF1           |
+----+------------+-----------+-------------------------+-----------------------+--------------+---------------+
3 rows in set

可以根据结果看到，REPLACE INTO会删除掉主键或唯一索引发生重复的行，然后再插入新的数据。

The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.

REPLACE语句返回受影响的行数的值，这个值是删除和插入的行的总和。如果单行的计数为1， REPLACE则插入一行并且不删除任何行。如果计数大于1，则在插入新行之前删除一个或多个旧行。如果表包含多个唯一索引，并且新行为不同唯一索引中的不同旧行重复值，则单行可以替换多个旧行。

 Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.
 To use REPLACE, you must have both the INSERT and DELETE privileges for the table.

所有REPLACE语句中列的值都应该被指定。任何缺少的列值都被设置为默认值，就像INSERT一样。并且不能从当前行引用值并在新行中使用它们。如果使用SET col_name = col_name + 1这样的赋值，那么对右边列名的引用将被视为默认值，因此赋值相当于SET col_name = DEFAULT(col_name) + 1。
要使用REPLACE，必须同时具有表的插入和删除特权。

REPLACE INTO在数据库主从切换后，可能会出现一些问题，具体可以参考MySQL Replace Into & Replication

INSERT INTO … ON DUPLICATE KEY UPDATE

mysql> INSERT INTO `test`.`employees_example` (`first_name`, `employee_code`) VALUES ('Helmholtz', 'HF1') ON DUPLICATE KEY UPDATE first_name = 'Helmholtz';
Query OK, 2 rows affected

这种方法也能实现REPLACE INTO的功能，且可以引用当前行的值。

mysql>INSERT INTO `test`.`employees_example` (`first_name`, `employee_code`) VALUES ('Mustapha', 'MM1') ON DUPLICATE KEY UPDATE first_name = CONCAT(first_name,' ','test');
Query OK, 2 rows affected

mysql> select * from employees_example limit 1;
+----+---------------+-----------+-------------------------+-----------------------+--------------+---------------+
| id | first_name    | last_name | position                | home_address          | home_phone   | employee_code |
+----+---------------+-----------+-------------------------+-----------------------+--------------+---------------+
|  1 | Mustapha test | Mond      | Chief Executive Officer | 692 Promiscuous Plaza | 326-555-3492 | MM1           |
+----+---------------+-----------+-------------------------+-----------------------+--------------+---------------+
1 row in set

如果表中有多个唯一索引，如employees_example表中的id和employee_code，INSERT INTO … ON DUPLICATE KEY UPDATE只会更新其中的一行。

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

对于ON DUPLICATE KEY UPDATE，如果将行作为新行插入，则每行的受影响行值为1;如果更新现有行，则为2，如果现有行设置为其当前值，则为0。

但是发现使用INSERT INTO … ON DUPLICATE KEY UPDATE也会使表中的AUTO_INCREMENT也会增加。

可能发生问题：INSERT IGNORE，REPLACE INTO，INSERT INTO … ON DUPLICATE KEY UPDATE都会使表的AUTO_INCREMENT发生变化，是不是都会出现主从数据库AUTO_INCREMENT不一致的问题？？？

参考
REPLACE Syntax
INSERT … ON DUPLICATE KEY UPDATE Syntax

aBang686
关注
pandas写入mysql replace into_MySQL "replace into" 的坑以及insert相关操作
weixin_28308325的博客
01-20 807
下面我们主要说一下在插入时候的几种情况：1：insert ignore2：replace into3：ON DUPLICATE KEY UPDATE关于insert ignore：关于replace into:关于ON DUPLICATE KEY UPDATE ：MySQL 对 SQL 有很多扩展，有些用起来很方便，但有一些被误用之后会有性能问题，还会有一些意料之外的副作用，比如 REPLACE ...
MariaDB/MySQL防止重复插入相同记录:INSERT IGNORE或者REPLACE
个人博客
03-29 829
MySQL防止重复插入相同记录有2种常用的方法。 1 使用 INSERT IGNORE 使用 INSERT IGNORE 可以在插入具有相同主键的数据时不做重复插入。 mysql> SELECT * FROM books LIMIT 3; +----+-------------------------+---------------------+----------------+ | id ...
INSERT IGNORE 与 INSERT INTO的区别
weixin_34023863的博客
01-06 439
例 insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据； insert ignore into table(name)  select  name from table2 例 INSERT INTO有无数据都插入，如果主键则不插入 1.insert语句一次可以插入多组值，每组值用一对圆括号括起来，用逗号分隔，如下： insert into `news`(titl...
INSERT INTO 和 INSERT IGNORE INTO
竹毛言己
06-16 1315
INSERT INTO 和 INSERT IGNORE INTO 都是插入操作。 INSERT INTO向数据库中插入一条新的数据，会检测主键，如果有已经存在的主键，则会报错。 INSERT IGNORE INTO 会忽略数据库中已存在的记录，如果数据库没有重复的主键，则直接新增一条数据，如果已有同样主键的数据，会保留已有数据，新增一条新的数据？ SQL语句： INSERT IGNORE INTO table_name (field1,field2) values (value1,value2); ..
mysql insert replace, insert ignore语法
qq_41067796的博客
07-18 700
在MySQL中，当我们使用INSERT语句尝试向一个具有唯一约束（UNIQUE constraint）或主键（PRIMARY KEY）的列中插入数据时，如果发现该数据已经存在，将会触发重复插入错误。使用REPLACE语句可以在发现重复数据时删除已有数据并插入新数据，类似于UPDATE和INSERT的结合。ON DUPLICATE KEY UPDATE语句，可以在发现重复数据时执行更新操作而不是插入新数据。如果不确定数据是否已经存在，且希望插入时不报错，可以选择使用INSERT IGNORE。
insert ignore 与 insert 与 replace
Kirito_j的博客
09-22 429
insert into表示插入数据，数据库会检查主键（PrimaryKey），如果出现重复会报错； replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样； REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REP...
INSERT IGNORE INTO 与INSERT INTO的区别
xiyuanda的博客
11-26 768
INSERT IGNORE 与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。 insert into表示插入数据，数据库会检查主键，如果出现重复会报错； sert ignore表示，如果中已经存在相同的记录，则忽略当前新数据； ...
mysql inser into on_数据库避免插入重复数据需求解决：MySQL之insert、insert ignore、replace和insert into on duplicate key ...
05-23
在 MySQL 中，我们可以使用以下几种方法来避免插入重复数据： 1. INSERT IGNORE INTO：如果插入的数据与现有数据冲突，则忽略该插入操作。 例如： ``` INSERT IGNORE INTO table1 (id, name) VALUES (1, 'John');...
MySQL的insert ignore into, replace into性能对比
最新发布
04-13
MySQL中的INSERT IGNORE INTO和REPLACE INTO是两种常用的数据插入方式，它们在性能上有一些区别。 1. INSERT IGNORE INTO： INSERT IGNORE INTO语句在插入数据时，如果发生主键冲突或唯一索引冲突，会忽略这条冲突...
mysql insert if not exists防止插入重复记录的方法
12-15
在MySQL中，当我们需要向数据库表中插入数据时，但又希望避免插入已经存在的记录，可以采用`INSERT IGNORE`、`REPLACE INTO`或者结合`SELECT`与`NOT EXISTS`子句的方式来实现。这里我们将详细探讨`NOT EXISTS`子句在...
mysql 限定符_我可以使用多个字段作为MySQL中的IGNORE限定符来执行INSERT IGNORE吗？...
weixin_32643811的博客
02-27 223
我有一个MySQL表,看起来像这样：`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`company_id` int(8) unsigned NOT NULL,`term_type` varchar(255) NOT NULL DEFAULT '',`term` varchar(255) NOT NULL DEFAULT '',我希望能够做到这一点……I...
MySQL的ignore与replace的使用
lixianhe的博客
04-27 1117
MySQL的ignore与replace的使用
mysql中insert, replace, insert ignore用法区别
weixin_43964833的博客
01-27 2422
insert, replace, insert ignore用法区别 mysql中经常用到的三种插入数据的语句如下： insert into 表示插入数据，数据库会检查主键或者唯一索引，若出现重复则会报错，错误信息如下： Duplicate entry 'xxx(出现重复的数据)’ for key ‘xxx(主键或唯一索引)’ 为了演示，我们将创建一个名为学生(students)的新表。 CREATE TABLE IF NOT EXISTS students ( id INT PRIMARY
MySQL中ignore,replace的使用
跟派大星学编程
07-18 5743
insert ignore 当出现主键或唯一索引重复之后，插入会失败，但不会报错，会忽略此次插入 用法： #id为主键 insert into user(id,name,age)values(1,"小华",21) #由于主键冲突，这个插入将会被忽略 insert ignore into user(id,name,age)values(1,"小明",21) replace to 当出现主键或...
mysql 避免重复写入数据的三种方式 和insert ...on duplicate updt... 死锁
这里是标题
02-18 2639
mysql在存在主键冲突或者唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法。 1、insert ignore 2、replace into 3、insert on duplicate key update 注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用以上三个语句没有意义，与使用单纯的INSERT INTO相同。 一、insert ignore insert ...
数据的增加、修改、删除（四）
。。。
07-21 297
INSERT的用法：语法，方言，子查询，IGNORE；UPDATE和DELETE的语法及表连接用法
INSERT的几个语法 IGNORE|REPLACE|LOW_PRIORITY | DELAYED
stay hungry ! stay foolish!
12-17 554
INSERT IGNORE 与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。 REPLACE 运行与INSERT很相像。只有一点除外，如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。请参见13.2.4节，“INSERT语法”。 注意，除非表有一.
Mysql replace into insert ignore 用法
zhang199563的专栏
04-15 565
insert ignore: 根据主键或者唯一索引插入数据,如果当前数据存在,则不会报错,返回结果0,如果插入成功则返回为1,其实与Update语句意思差不多,不过一个是更新,一个是插入.注意 一定要有唯一索引或者主键ID,如果没有,则使用insert ignore毫无意义。 有一张如下: CREATE TABLE `able_test` ( `id` int(8) NOT NULL ...
mysql unique不起作用_使用UNIQUE KEY的mySQL INSERT IGNORE SELECT不起作用
weixin_28873663的博客
02-07 444
尝试使用 INSERT INTO SELECT 填充空MySQL表并连接两个源表 . 希望 IGNORE 基于定义为 UNIQUE KEY 的两个目标表列插入重复行，但由于某种原因，仍会插入基于这两列的重复行 .目的地表定义：CREATE TABLE `item` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`item_id` int(10) un...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值