这有效：
mysql>从1_um中选择user_id，meta_value，其中meta_key ='1_caps';
我想要的是：
插入1_um（user_id，meta_key，meta_value）值（user_id = $ uid，meta_key ='2_caps'，meta_value = $ mval）;
但是我在SP的尝试中不断受到零行影响。 这就是我所拥有的：
分隔符//
创建过程import_um（）
开始
declare done int default FALSE; declare $uid bigint(20) unsigned default 0; declare $mval longtext default ""; declare curs cursor for select user_id, meta_value from 1_um where meta_key='1_caps'; declare continue handler for not found set done = TRUE; open curs; read_loop: LOOP fetch curs into $uid,$mval; insert into 1_um (user_id, meta_key, meta_value) values (user_id=$uid, meta_key='2_caps', meta_value=$mval); if done then leave read_loop; end if; end LOOP; close curs; END//移动“如果完成然后离开read_loop;结束if;” 直接在fetch语句下面（在insert语句之上）也没有效果。
看起来fetch变量var2不起作用，因为我尝试用select concat替换insert语句（'my var is'，$ uid）; 对STDOUT一无所知。
表1_um包含四列：umid（auto inc），user_id，meta_key，meta_value。 我是否必须将所有这些选择到光标中？
This works:
mysql> select user_id, meta_value from 1_um where meta_key='1_caps';
What I want is:
insert into 1_um (user_id, meta_key, meta_value) values (user_id=$uid, meta_key='2_caps', meta_value=$mval);
But I keep getting zero rows affected from my attempts at a SP. Here's what I have:
Delimiter //
create procedure import_um()
BEGIN
declare done int default FALSE; declare $uid bigint(20) unsigned default 0; declare $mval longtext default ""; declare curs cursor for select user_id, meta_value from 1_um where meta_key='1_caps'; declare continue handler for not found set done = TRUE; open curs; read_loop: LOOP fetch curs into $uid,$mval; insert into 1_um (user_id, meta_key, meta_value) values (user_id=$uid, meta_key='2_caps', meta_value=$mval); if done then leave read_loop; end if; end LOOP; close curs; END//moving "if done then leave read_loop; end if;" directly below the fetch statement (above the insert statement) has no effect, either.
It appears that the fetch into variable,var2 is not working, as I try to replace the insert statement with select concat('my var is', $uid); and get nothing to STDOUT.
the table 1_um contains four columns: umid (auto inc), user_id, meta_key, meta_value. Do I have to select all of them into the cursor?
最满意答案
为什么在代码中使用循环？ 只需使用insert . . . select insert . . . select insert . . . select ：
insert into 1_um (user_id, meta_key, meta_value) select user_id, '2_caps', meta_value from 1_um where meta_key = '1_caps';毫无疑问，你的问题的一部分是错误的VALUES子句（你有值的布尔表达式，这似乎不正确）。 您还应该学习如何正确使用VALUES 。 但是对于这个问题，请使用正确的SQL构造。
Why are you using a loop in your code? Just use insert . . . select:
insert into 1_um (user_id, meta_key, meta_value) select user_id, '2_caps', meta_value from 1_um where meta_key = '1_caps';No doubt a bit part of your problem is the misformed VALUES clause (you have boolean expressions for the values, which doesn't seem right). You should learn how to use VALUES correctly as well. But for this problem, use the correct SQL construct.
更多推荐
发布评论