SQL批量删除与批量插入
批量删除:
DELETE FROM MyTable WHERE ID IN (1,2);
批量插入:
INSERT INTO MyTable(ID,NAME) VALUES(1,'123');
INSERT INTO MyTable(ID,NAME) VALUES(2,'456');
INSERT INTO MyTable(ID,NAME) VALUES(3,'789');
第二种方法,使用UNION ALL来进行插入操作:
INSERT INTO MyTable(ID,NAME)
SELECT 4,'000'
UNION ALL
SELECT 5,'001'
UNION ALL
SELECT 6,'002' ;
据说要比第一种要快!
第三种方法
INSERT INTO MyTable(ID,NAME) VALUES(7,'003'),(8,'004'),(9,'005');
Example:
表: leafjob(
leafnum INT NOT NULL PRIMARY KEY,
machine VARCHAR(15) );
删除:delete from leafjob where leafnum in (1,2,4);
插入:
INSERT INTO leafjob (leafnum, machine) values(1, 'r1leaf3'), (2, 'r1leaf22');
insert into leafjob(leafnum, machine) select 4,'000' union all select 1,'r1leaf3' union all select 2,'r1leaf22';
insert into leafjob(leafnum, machine) select 1,'r1leaf3' union select 2,'r1leaf22';
注:性能问题需要具体测试。示例在MySql下测试过,version: 4.1.20
删除A表的a列等于1的数据
代码如下 | 复制代码 |
Delete from A where exists (Select 1 from A where a=1) |
上面的方法只适用于简单小数据量的批量数据删除了,如果是大批量数据删除我们可参考下面方法
代码如下 | 复制代码 |
Create PROCEDURE Batch_Delete Set @PointerPrev = 1 |
posted on 2015-05-12 14:19 chengjunde 阅读(521) 评论(0) 编辑 收藏 举报