userid的数据很多(几百到几千吧)如:1101,1102,44054406,7701,7709等。
1101,1102,44054406,7701,7709这些userid数据是从用户操作页面(如网页)返回的。
目前是利用下面的sql语句实现的,使用userid去user表中检索返回临时表(是叫临时表吧?),再重新取userid。
insert into dir( eid, dir_id, userid) select 1234, 440, aa.userid from (select userid from user where userid in (1101,1102,44054406,7701,7709)) as aa;有没有更好的方法,代替"select userid from user where userid in (1101,1102,44054406,7701,7709)"这个检索,我觉得这个检索很没有必要,但又没有想到好的方法。能不能直接把"1101,1102,44054406,7701,7709"放到from里,直接放肯定不行的,有没有什么可行的方法?
insert into dir( eid, dir_id, userid) select 1234, 440, aa.userid from ("1101,1102,44054406,7701,7709") as aa;
1101,1102,44054406,7701,7709这些userid数据是从用户操作页面(如网页)返回的。
目前是利用下面的sql语句实现的,使用userid去user表中检索返回临时表(是叫临时表吧?),再重新取userid。
insert into dir( eid, dir_id, userid) select 1234, 440, aa.userid from (select userid from user where userid in (1101,1102,44054406,7701,7709)) as aa;有没有更好的方法,代替"select userid from user where userid in (1101,1102,44054406,7701,7709)"这个检索,我觉得这个检索很没有必要,但又没有想到好的方法。能不能直接把"1101,1102,44054406,7701,7709"放到from里,直接放肯定不行的,有没有什么可行的方法?
insert into dir( eid, dir_id, userid) select 1234, 440, aa.userid from ("1101,1102,44054406,7701,7709") as aa;
select 1234, 440 userid from user
where userid in (1101,1102,44054406,7701,7709)
select 1234, 440, aa.userid from `user` aa where userid in (1101,1102,44054406,7701,7709);
把1101,1102,44054406,7701,7709作为一个字符串参数传进去:
drop procedure if exists P_test;
create procedure P_test (in i_str varchar(1000))
begin
declare v_i varchar(100);
set i_str = concat(i_str,',');
while instr(i_str,',') > 0 do
set v_i = select substring_index(i_str,',',1);
insert into dir( eid, dir_id, userid) values (1234,440,v_i);
set i_str = right(i_str,char_length(i_str)-instr(i_str,','));
end while;
end;调用:call P_test (1101,1102,44054406,7701,7709);
(1234,440,1101),
(1234,440,1102),
(1234,440,44054406),
(1234,440,7701),
(1234,440,7709;你可以生成这个字符串
没有事务要做好增量备份
你也可以单项插入
insert into dir( eid, dir_id, userid) select 1234, 440, aa.userid from (select userid from user where userid =1101) as aa;
insert into dir( eid, dir_id, userid) select 1234, 440, aa.userid from (select userid from user where userid =1102) as aa;
....