复制一个记录，并在mysql存储过程中做一些改变

Question

my table is:

+------+---------+----------+--------+---------+------------+----------+----------------+------------------+-----------+--------+--------+---------+-------+
| r_id | r_width | r_height | r_code | r_store | r_provider | r_parent | r_regiter_time | r_last_used_time | r_comment | r_type | r_open | r_usage | r_log |
+------+---------+----------+--------+---------+------------+----------+----------------+------------------+-----------+--------+--------+---------+-------+
|    1 |    2000 |     3500 | test 1 |       0 |          0 |        0 |              0 |                0 | nop       |   NULL |   NULL | 99.3618 | NULL  |
|    2 |    2000 |     3000 | usd22  |       2 |          1 |        0 |     1440915529 |       1440915529 | comment   |      2 |      0 | 13.0833 | NULL  |
|    3 |    3000 |     2500 | qb88   |       1 |          2 |        0 |     1440921466 |       1440921466 | commex    |      3 |      0 |       0 | NULL  |
+------+---------+----------+--------+---------+------------+----------+----------------+------------------+-----------+--------+--------+---------+-------+
3 rows in set (0.00 sec)

AND this my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `ROLE_SPLIT_V`(IN `id` INT, IN `top` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE exit handler for sqlexception
BEGIN
   SHOW ERRORS LIMIT 1 ;
   ROLLBACK;
END;
DECLARE exit handler for sqlwarning
 BEGIN
    SHOW WARNINGS LIMIT 1;
    ROLLBACK;
END;
START TRANSACTION;
  SET @MyRec =  (SELECT `r_width`,(`r_height` - top ) as 'r_height' ,CONCAT(`r_code`,'_',ROUND(RAND() * 1000)) as 'r_code' ,`r_store`,`r_provider`,
     `r_parent`,`r_regiter_time`,`r_last_used_time`,`r_comment`,`r_type`,`r_open`,`r_usage`,`r_log` FROM prfix_role WHERE r_id = id) ;
    -- UPDATE prfix_role SET prfix_role.r_height =  top WHERE prfix_role.r_id = id ;
    INSERT INTO prfix_role (`r_width`,`r_height`,`r_code`,`r_store`,`r_provider`,`r_parent`,`r_regiter_time`,`r_last_used_time`,`r_comment`,`r_type`,`r_open`,`r_usage`,`r_log`)
     VALUES (MyRec);
    SET @lst_id = LAST_INSERT_ID();
   -- UPDATE prfix_slice SET s_top =  s_top - top,s_role_id = lst_id WHERE s_role_id = id AND s_top > top ;
COMMIT;
END

但是在我的数据库中运行后没有看到任何变化，我有这个错误:

操作数应该包含1列

Answer 1

您没有正确使用会话变量。

相反，在您需要的情况下，我建议您使用局部变量从表中读取值。并与insert语句一起使用。

:

START TRANSACTION;
  BEGIN
    DECLARE _r_width INT; -- or whatever it be
    DECLARE _r_height INT; -- or whatever it be
    DECLARE _r_code VARCHAR(255);
    DECLARE _r_store INT; -- or whatever it be
    DECLARE _r_provider INT; -- or whatever it be
    DECLARE _r_width INT; -- or whatever it be
    --- all other required fields here
    -- now select values into above variables
    SELECT `r_width`, (`r_height` - top ) as 'r_height'
         , CONCAT(`r_code`,'_',ROUND(RAND() * 1000)) as 'r_code' 
         , `r_store`,`r_provider`
         , `r_parent`, `r_regiter_time`, `r_last_used_time`
         , `r_comment`, `r_type`, `r_open`
         , `r_usage`, `r_log` 
      INTO _r_width, _r_height, _r_code, _r_store
         -- rest of other field variables should go here
      FROM prfix_role WHERE r_id = id ;
    -- now use the variable values with insert statement
    INSERT INTO prfix_role ( `r_width`, `r_height`, `r_code`, `r_store`
         , `r_provider`, `r_parent`, `r_regiter_time`, `r_last_used_time`
         , `r_comment`, `r_type`, `r_open`, `r_usage`, `r_log` )
    VALUES ( _r_width, r_height, _r_code, _r_store, .... );
   -- if you want to use last inserted value, 
   -- you can directly call it in the statement
   UPDATE prfix_slice 
      SET s_top =  s_top - top
        , s_role_id = LAST_INSERT_ID() 
    WHERE s_role_id = id 
      AND s_top > top ;
  END; -- end transaction

Answer 2

我把我的代码改成这样并解决问题:

CREATE DEFINER=`root`@`localhost` PROCEDURE `ROLE_SPLIT_V`(IN `id` INT, IN `top` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE exit handler for sqlexception
BEGIN
   SHOW ERRORS LIMIT 1 ;
   ROLLBACK;
END;
DECLARE exit handler for sqlwarning
 BEGIN
    SHOW WARNINGS LIMIT 1;
    ROLLBACK;
END;
START TRANSACTION;
  -- SET @MyRec =  ;
    -- 
    INSERT INTO prfix_role SELECT NULL,`r_width`,(`r_height` - top ) as 'r_height' ,CONCAT(`r_code`,'_',ROUND(RAND() * 1000)) as 'r_code' ,`r_store`,`r_provider`,
     `r_parent`,`r_regiter_time`,`r_last_used_time`,`r_comment`,`r_type`,`r_open`,`r_usage`,`r_log` FROM prfix_role WHERE r_id = id ;
    UPDATE prfix_role SET prfix_role.r_height =  top WHERE prfix_role.r_id = id ;
    SET @lst_id = LAST_INSERT_ID();
    UPDATE prfix_role SET prfix_role.r_height =  top WHERE prfix_role.r_id = id ;
   UPDATE prfix_slice SET s_top =  s_top - top,s_role_id = @lst_id WHERE s_role_id = id AND s_top > top ;
COMMIT;
END