replace into..values/select和insert into ..values/select on duplicate update 异同
来源:互联网 发布:怎么看淘宝店铺粉丝 编辑:程序博客网 时间:2024/06/10 03:45
replace into..values/select和insert into ..values/select on duplicate update 异同?
MySQL5.5环境,t表结构:
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| c1 | char(5) | YES | | NULL | |
| c2 | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
初始化表数据:
insert into t(c1,c2)values('1','1');
insert into t(c1,c2)values('2','2');
insert into t(c1,c2)values('3','3');
总述:
id列无primary key/unique
1.2者和insert into ...values/select ...正常插入数据
2.全表加排他锁(x)
id列有primary key/unique
1.replace into t(id,c1) values(1,'11'),只更新了列c1值,c2被设置为NULL,处理方式相当于,delete
from t where id=1 加 insert into t(id,c1)values(1,'11');2条SQL,从而c2取默认值,因此,在实际
使用过程中,需要给定表中所有列名,以免数据丢失.
2.insert into t(id,c1) values(3,'33') on duplicate key update c2='333',当id有数据重复冲突时,
update之后的sql(c2='333'),否则,insert into t(id,c2) values(4,'44') on duplicate key update
c2='444',执行insert into t(id,c2) values(4,'44'),忽略update c2='444';
3.在更新的行加排他锁(x)
从http://www.tokutek.com/2010/07/why-insert-on-duplicate-key-update-may-be-slow-by-incurring-disk-seeks/
据悉,建议在日常中使用,replace into ...values/select
原因:insert into ... on pulicate key update...,插入数据较慢,insert处理过程中,通过表primary/unique key
查验需要插入记录是否已存在表中,在此过程中涉及磁盘数据扫描,这是慢的原因,没有重复冲突数据,直接插入;
此外,还有涉及持有的锁,也会相应的影响系统的并发度;
MySQL5.5环境,t表结构:
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| c1 | char(5) | YES | | NULL | |
| c2 | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
初始化表数据:
insert into t(c1,c2)values('1','1');
insert into t(c1,c2)values('2','2');
insert into t(c1,c2)values('3','3');
总述:
id列无primary key/unique
1.2者和insert into ...values/select ...正常插入数据
2.全表加排他锁(x)
id列有primary key/unique
1.replace into t(id,c1) values(1,'11'),只更新了列c1值,c2被设置为NULL,处理方式相当于,delete
from t where id=1 加 insert into t(id,c1)values(1,'11');2条SQL,从而c2取默认值,因此,在实际
使用过程中,需要给定表中所有列名,以免数据丢失.
2.insert into t(id,c1) values(3,'33') on duplicate key update c2='333',当id有数据重复冲突时,
update之后的sql(c2='333'),否则,insert into t(id,c2) values(4,'44') on duplicate key update
c2='444',执行insert into t(id,c2) values(4,'44'),忽略update c2='444';
3.在更新的行加排他锁(x)
从http://www.tokutek.com/2010/07/why-insert-on-duplicate-key-update-may-be-slow-by-incurring-disk-seeks/
据悉,建议在日常中使用,replace into ...values/select
原因:insert into ... on pulicate key update...,插入数据较慢,insert处理过程中,通过表primary/unique key
查验需要插入记录是否已存在表中,在此过程中涉及磁盘数据扫描,这是慢的原因,没有重复冲突数据,直接插入;
此外,还有涉及持有的锁,也会相应的影响系统的并发度;
0 0
- replace into..values/select和insert into ..values/select on duplicate update 异同
- mysql replace into和 insert into ... on duplicate update 区别
- REPLACE INTO 和 INSERT INTO ... ON DUPLICATE KEY UPDATE SET ...
- replace into 和 insert into *** on duplicate key update
- insert into replace into 和insert into .. on duplicate key update ..
- INSERT INTO ON DUPLICATE KEY UPDATE 与 REPLACE INTO 和 INSERT IGNORE INTO
- INSERT小记(insert into select和not in,values括号里不能放select)
- 批量INSERT INTO SELECT和批量REPLACE INTO SELECT
- 批量 insert into select 和 批量 replace into select
- Mysql中Insert into xxx on duplicate key update和REPLACE INTO使用
- Mysql中Insert into xxx on duplicate key update和REPLACE INTO使用
- Mysql replace into 与 insert into on duplicate key update 死锁和性能测试
- mybatis中使用replace into和 insert INTO … ON DUPLICATE KEY UPDATE批量操作
- mysql中insert into on duplicate key update 和replace into的区别
- replace into和insert into on duplicate key 区别
- REPLACE INTO 与 INSERT ... ON DUPLICATE KEY UPDATE
- select into 和 insert into
- insert into select 堵塞update
- Android Camera No space left on device
- iOS文件md5
- Dojo仪表盘
- PhysX3 学习笔记2.5
- MTK pump express 快速充电原理分析
- replace into..values/select和insert into ..values/select on duplicate update 异同
- tcp/ udp/ 轮询方式和推送方式?
- fragment中填充listView后退保存位置
- 如何定位My Silverlight 元素
- ACM Yogurt factory(挑战程序设计竞赛)
- BZOJ 2002 HNOI2010 弹飞绵羊 分块
- 持续集成之 Git Gerrit Jenkins Blade 工作流
- 使用shell 实现tree函数
- Hack 11 Adding text glowing effects Android led灯字体