mysql 批量插入

1. 表结构一样

 insert into 表1
  select * from 表2

 2. 表结构不一样(

insert into sys_user_role(user_id,role_id)
(select user_id,2 from sys_user)

)

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

insert into sys_dept_temp(ID,父ID,父到子之间级数,父到子路径)(SELECT dept_id AS ID,parent_id AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM (      SELECT dept_id,parent_id,      @le:= IF (parent_id = 0 ,0,           IF( LOCATE( CONCAT('|',parent_id,':'),@pathlevel)   > 0  ,                        SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',parent_id,':'),-1),'|',1) +1         ,@le+1) ) levels      , @pathlevel:= CONCAT(@pathlevel,'|',dept_id,':', @le ,'|') pathlevel       , @pathnodes:= IF( parent_id =0,',0',            CONCAT_WS(',',            IF( LOCATE( CONCAT('|',parent_id,':'),@pathall) > 0  ,                SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',parent_id,':'),-1),'|',1)               ,@pathnodes ) ,parent_id  ) )paths     ,@pathall:=CONCAT(@pathall,'|',dept_id,':', @pathnodes ,'|') pathall         FROM  sys_dept,     (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv     ORDER BY  parent_id,dept_id     ) src ORDER BY dept_id limit 0,300)

　　

  insert into 表1 (列名1,列名2,列名3)
  select  列1,列2,列3 from 表2

3、只从另外一个表取部分字段　

 

 

　　　

insert into 表1 (列名1,列名2,列名3) values(列1,列2,(select 列3 from 表2));