mysql concat并插入不工作 [英] mysql concat and insert into not working

问题描述

我在存储过程中有一个代码将PID-"添加到id号，所以如果id号为1，结果应该是PID-1.但它不起作用.

i have a code in stored procedure which adds "PID-" to the id number, so if id number is 1, the result shoulb be PID-1. but it's not working.

代码如下:

DROP PROCEDURE `inserproducts`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `inserproducts`(pid int,pname varchar(50),pdesc varchar(50),psupp varchar(50),pdate date,pquant int)
begin
insert into products(productid,productname,proddescription,supplier,lastpurchasedate,quantityleft)
        values(select concat('PID',pid,pname),pdesc,psupp,pdate,pquant));

select pid=last_insert_id();
end

如何将 insert into 和 concat 连接在一起?请帮我解决这个问题.

how can i join insert into and concat together? Please help me with this one.

推荐答案

使用 INSERT INTO...SELECT

insert into products(productname,proddescription,supplier,lastpurchasedate,quantityleft)
select concat('PID',pid,pname),pdesc,psupp,pdate,pquant

如果是 AUTO_INCREMENT 列，您可以省略 productid 列.

You can omit the column productid if it is an AUTO_INCREMENT column.

我想知道为什么当 pid 是 IN 参数时你需要执行 select pid=last_insert_id();em>.

I was wondering why you need to execute select pid=last_insert_id(); when pid is an IN parameter.

更新 1

DROP PROCEDURE `inserproducts`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `inserproducts`
(
   pid int,
   pname varchar(50),
   pdesc varchar(50),
   psupp varchar(50),
   pdate date,
   pquant int
)
begin

    insert into products
          (productname,
           proddescription,
           supplier,
           lastpurchasedate,
           quantityleft)
    select concat('PID',pid,pname), pdesc, psupp, pdate, pquant;

    select last_insert_id();
end$$
DELIMITER ;

这篇关于mysql concat并插入不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！