mysql 四大插入数据语句:insert into ; replace into ; insert ignore into ; insert into .... on duplicate key update
#################
一、INSERT INTO 语句:要求是不能违反主键或唯一索引,否则报错
一次插入一条数据:
INSERT INTO table_name (field1,field2) values (value1,value2);
一次插入多条数据:
INSERT INTO `iphone` VALUES (1,'iphone4','USA',1),(2,'iphone5','USA',1),(3,'iphone6','USA',1),(4,'iphone7','USA',1),(5,'iphone8','USA',1);
二、replace into语句:检测是否违反主键或唯一索引;
规则: 1)若只违反主键,或只违反唯一索引,或既违反主键也违反唯一索引,则先删掉违反约束的所有数据,再插入新的数据,影响的行数为删除的行数+插入的行数; 2)既不违反主键,也不违反唯一索引,则直接将数据插入数据库,影响的行数为插入的行数,此时与insert into的效果一样。
3)当表中的唯一索引字段可以为NULL的时候,则该字段插入相同NUll的时候被视为不同数据,即被视为不违反唯一索引,此时就是insert into语句,唯一索引没有not null限制的时候,唯一索引字段可以有多个相同NULL值的数据行 场景: 替换现有数据:如果一张表已经有了数据,现在又需要插入的一部分数据,如果新数据与旧的数据存在违反主键或唯一索引的情况,要求保留新数据,即新数据优先级更高,是一个原子操作。
传统做法:在一个事务里面:若存在主键相同或唯一索引字段相同,则先delete删除原有数据,再insert新数据
在执行REPLACE后,系统返回了所影响的行数: 1)如果返回1,说明在表中并没有重复的记录, 2)如果返回2,说明只有一行数据违反了主键或唯一约束,系统自动先调用了 DELETE删除这条记录,然后再记录用INSERT来插入这条记录。 3)如果返回的值大于2,说明多行违反了主键或唯一约束,这时候会先删除所有违反主键或唯一索引约束的行,然后再插入新数据,影响的函数则是:删除的行数+插入的行数。有多少个违反主键或唯一索引的行数就会有多少个主键或唯一索引,因为一张表可能有多个唯一索引存在,而插入的数据可能违反了多个唯一索引,这时候删除的数据个数大于插入的数据,会出现插入数据后,数据还变少的的现象。
一次插入一条数据:
REPLACE INTO table_name (field1,field2) values (value1,value2);
一次插入多条数据:
REPLACE INTO `iphone` VALUES (1,'iphone4','USA',1),(2,'iphone5','USA',1),(3,'iphone6','USA',1),(4,'iphone7','USA',1),(5,'iphone8','USA',1);
三、INSERT IGNORE INTO:检测是否违反主键或唯一索引
规则:
1)若只违反主键,或只违反唯一索引,或既违反主键也违反唯一索引,则会忽略掉插入数据;
2)只有既不违反主键,也不违反唯一索引,才会将数据插入数据库。
3)当表中的唯一索引字段可以为NULL的时候,则该字段插入相同NUll的时候被视为不同数据,即被视为不违反唯一索引,此时就是insert into语句,唯一索引没有not null限制的时候,唯一索引字段可以有多个相同NULL值的数据行
场景:
不影响现有数据:如果一张表已经有了数据,现在又需要插入的一部分数据,但是不要影响已经存在的数据,如果遇到违反主键或唯一索引的约束的数据不要报错,直接忽略即可
之前在介绍INSERT INTO命令时,曾经提到,该命令在执行数据插入操作时,会在数据库中对元组的主键进行检测,若没有存在,则执行插入动作,若存在,则会报错。
而INSERT IGNORE INTO命令与其不同的是,INSERT IGNORE会忽略数据库中已经存在的记录:
如果数据库没有数据,就插入新的数据;如果有数据的话就跳过这条数据。
这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。
需要说明的是,INSERT INTO和INSERT IGNORE INTO只根据“主键值”或“unique索引”进行判断,只要主键值已在数据库中存在,则认为即将插入重复记录。
一次插入一行数据:
INSERT IGNORE INTO table_name (field1,field2) values (value1,value2);
一次插入多行数据:
INSERT IGNORE INTO `iphone` VALUES (1,'iphone4','USA',1),(2,'iphone5','USA',1),(3,'iphone6','USA',1),(4,'iphone7','USA',1),(5,'iphone8','USA',1);
示例:
mysql> select * from staff_3; +----------+-------+ | name | slary | +----------+-------+ | liding | 2700 | | haofugui | 3500 | | xiaoli | 3600 | +----------+-------+ 3 rows in set (0.00 sec) mysql> describe staff_3; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(20) | NO | PRI | | | | slary | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into staff_3 (name,slary) values ('xiaoli',3200); ERROR 1062 (23000): Duplicate entry 'xiaoli' for key 'PRIMARY' mysql> insert ignore into staff_3 (name,slary) values ('xiaoli',3200); Query OK, 0 rows affected (0.00 sec) mysql> select * from staff_3; +----------+-------+ | name | slary | +----------+-------+ | liding | 2700 | | haofugui | 3500 | | xiaoli | 3600 | +----------+-------+ 3 rows in set (0.00 sec)
四、INSERT INTO .... ON DUPLICATE KEY UPDATE :根据是否违反主键或唯一索引;若违反则更新;若不违反则直接插入;若违反且新数据和旧数据相同则不更新。
在实际应用中,经常碰到导入数据的功能,当导入的数据不存在时则进行添加,有修改时则进行更新,在刚碰到的时候,一般思路是将其实现分为两块,分别是判断增加,判断更新,后来发现在mysql中有ON DUPLICATE KEY UPDATE一步就可以完成(Mysql独有的语法)。
ON DUPLICATE KEY UPDATE单个增加更新及批量增加更新的sql
在MySQL数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的惟一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作。
说通俗点就是数据库中存在某个记录时,执行这个语句会更新,而不存在这条记录时,就会插入。
注意点:
因为这是个插入语句,所以不能加where条件。
插入规则:
1)如果是插入操作,受到影响行的值为1; 2)如果更新操作,受到影响行的值为2; 3)如果更新的数据和已有的数据一样(就相当于没变,所有值保持不变),受到影响的行的值为0。
该语句是基于唯一索引或主键使用,比如一个字段a被加上了unique index,并且表中已经存在了一条记录值为1,
下面两个语句会有相同的效果:
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1;
ON DUPLICATE KEY UPDATE后面可以放多个字段,用英文逗号分割。
再现一个例子:
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
表中将更改(增加或修改)两条记录。
###########
####################
相关文章
- linux环境,通过rpm删除mysql包,报错:error reading information on service mysqld: Invalid argument
- Linux安装Mysql
- mysql timestamp类型字段的CURRENT_TIMESTAMP与ON UPDATE CURRENT_TIMESTAMP属性
- centos7 安装 mysql(tl)
- 在 MySQL 中处理日期和时间 - 第五章节
- 数千台MySQL数据库遭黑客比特币勒索,该怎么破?
- JDBC连接MySQL数据库小例子
- [译文]MySQL发生死锁肿么办?by何金龙
- [转]mysql如何利用Navicat 导出和导入数据库
- 详解 Spotlight on MySQL监控MySQL服务器
- mysql下出现Unknown column ‘xx‘ in ‘on clause‘的完全解决方法
- 【mysql问题】解决2003-Can‘t connect to MySQL server on ‘ ‘(10060“Unknown error“)
- Mysql的硬件优化和配置优化
- MySQL递归查询树状表的子节点、父节点
- mysql 驱动问题
- Mysql引擎innodb_pool的作用
- Mysql千万级大数据量查询优化
- ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist
- 浅析mysql报错:Error writing file '/tmp/MY4QWdUa' (Errcode: 28 - No space left on device) 磁盘空间不足的问题和解决思路
- 在centos中php 在连接mysql的时候,出现Can't connect to MySQL server on 'XXX' (13)
- mysql版本区别及版本号解释
- MySQL 5.7主从复制从零开始设置及全面详解——实现多线程并行同步,解决主从复制延迟问题!
- 【php】Apache无法自己主动跳转却显示文件夹与php无法连接mysql数据库的解决方式
- mysql主从同步某个表不一致,单表恢复
- mysql报错:Can’t start server: Bind on TCP/IP port: 通常每个套接字地址(协议/网络地址/端口)只允许使用一次。
- [转]MySQL远程连接ERROR 2003 (HY000):Can't connect to MySQL server on'XXXXX'(111) 的问题
- MySQL协议-认证握手过程
- MySQL TEXT数据类型的最大长度