mysql批量参加_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));