MYSQL - 插入连接字符串

Question

表格项目

表销售

最后:表SaleItems

程序PLAYER_CREATE_SALE:

输入参数:

• in_player_id int
• in_total 整数
• in_items_ids nvarchar(500)
• in_items_quantity nvarchar(500)

基本上，“用户”会看到上面的商品列表，选择任何商品，更改金额并购买。这应该像“购物车”一样工作。

例如:用户选择炸弹(id:1，数量:10)和装备(id:3，数量:2)

我的程序的参数如下所示:

in_player_id:750

总数量:390

in_items_ids:“1,3”

in_items_quantity:“10,2”

该过程实际上是这样进行的:

    INSERT INTO Sale(`player_id`, `total_coins_price`)
    VALUES(in_player_id, in_total);

    SET @sale_id = LAST_INSERT_ID();

    SET @squery = CONCAT("INSERT INTO SaleItems(`sale_id`, `item_id`, `quantity`) 
SELECT ", @sale_id, ", id, 9999 FROM Items WHERE id IN (", in_items_ids, ")");

    PREPARE stmt FROM @squery;

    EXECUTE stmt;

使用上面的代码，最终结果将是:

但是数量不对。我也不知道如何插入数量。应该是这样:

Answer 1

也许是这样的

DROP PROCEDURE IF EXISTS P;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `P`(
    IN `instring` varchar(255)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin
declare   tempstring varchar(100);
declare   outstring  varchar(100);
DECLARE   VAR_1 VARCHAR(100);
DECLARE      VAR_2 VARCHAR(100);
declare  checkit int;
DECLARE  LIMITOFFSET INT;

set checkit = 0;
SET VAR_2 = instring;
set tempstring = ltrim(rtrim(VAR_2));
SET LIMITOFFSET = 0;

looper: while   tempstring is not null and instr(tempstring,',') > 0 do
        set outstring = substr(tempstring,1,instr(tempstring, ','));
        set tempstring = ltrim(rtrim(replace(tempstring,outstring,'')));
        set outstring = replace(outstring,',','');
        set checkit = checkit + 1;
        insert into oitems
        select  CHECKIT,10,ID, outstring
         from  OItemsbase 
         order by id
         LIMIT LIMITOFFSET,1
         ;
         SET LIMITOFFSET = LIMITOFFSET + 1;

end while; 
    set outstring = tempstring; 
    set tempstring = ltrim(rtrim(replace(tempstring,outstring,'')));
    set outstring = replace(outstring,',','');
    set checkit = checkit + 1;
    insert into oitems
        select  CHECKIT,10,ID, outstring
         from  OItemsbase 
         order by id
         LIMIT LIMITOFFSET,1
         ;

end //

DELIMITER ;

MariaDB [sandbox]> DROP TABLE IF EXISTS OItemsbase;
Query OK, 0 rows affected (0.11 sec)

MariaDB [sandbox]> CREATE TABLE OItemsbase ( id INT, alpha_id INT, created_at VARCHAR(5));
Query OK, 0 rows affected (0.24 sec)

MariaDB [sandbox]> INSERT INTO OItemsbase VALUES
    -> (2, 100, 'today'),
    -> (6, 101, 'today'),
    -> (10, 101,'today');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> drop table if exists oitemss;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [sandbox]> create table oitems(id int, example_id int, item_base_id int, quantity int);
ERROR 1050 (42S01): Table 'oitems' already exists
MariaDB [sandbox]>
MariaDB [sandbox]> truncate table oitems;
Query OK, 0 rows affected (0.23 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> CALL P('20,30');
Query OK, 1 row affected (0.04 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> SELECT * FROM Oitems;
+------+------------+--------------+----------+
| id   | example_id | item_base_id | quantity |
+------+------------+--------------+----------+
|    1 |         10 |            2 |       20 |
|    2 |         10 |            6 |       30 |
+------+------------+--------------+----------+
2 rows in set (0.00 sec)