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)

)

 

 

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));