mysql频繁insert和delete_mysql update和delete和insert 操作多条数据的背后情况

结论:是的,但是可以一条sql语句操作多条数据,比如inset into table2 select * from talbe1

该问题比较理论偏基础.

以oracle为例(个人比较了解).

oracle执行sql语句的4个步骤:

解析(分为语法检查,语义分析,检查共享池)

优化

行源生成

执行语句

证明方法:

table1源表100条记录(id为1...999)插入到目标表table2

set serveroutput on;

declare

l_start number;

l_cnt number;

begin

delete from table2; --清空table2

l_start:=dbms_utility.get_time;        --获取当前系统时间

for i in 1..999

loop

execute immediate 'insert into table2 select * from table1

where id = '''||to_char(i)||'''';

end loop;

dbms_output.put_line('No Binds' || (dbms_utility.get_time-l_start)||' hsecs');

delete from table2; --清空table2

l_start:=dbms_utility.get_time;

for i in 1..999

loop

execute immediate 'insert into table2 select * from table1 where id=:x'

into l_cnt using to_char(i);

end loop;

dbms_output.put_line('Binding '||(dbms_utility.get_time-l_start)||' hsecs');

delete from table2; --清空table2

l_start:=dbms_utility.get_time;

insert into table2 select * from table1;

dbms_output.put_line('Direct '||(dbms_utility.get_time-l_start)||' hsecs');

end;

注意事项:需要清空共享池alter system flush shared_pool;否则第二次执行方法一时,数据库会记录方案.

解析:

方法一:不采用赋值变量,使得每次执行sql都要执行以上四个步骤,循环100次

方法二:采用赋值变量,使得数据库采用软解析(只需要执行第一步,跟第四步),循环100次

方法三:执行以上四个步骤,循环1次.