如何将列表传入mysql过程？

Question

你想在mysql程序中一次插入一对多的数据(数据列表)，你可以使用json方法来实现。然后，您必须通过mysql json格式将数据列表作为json进行传递。然后可以使用while循环插入列表数据。

Answer 1

json对象

{"billNo":16,"date":"2017-13-11 09:05:01","customerName":"Vikas","total":350.0,"fixedCharges":100,"taxAmount":25.78,"status":"paid","product":[{"productId":"MRR11","categoryId":72,"categoryName":"Parker Pen","cost":200,"quantity":2,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}},{"productId":"MRR12","categoryId":56,"categoryName":"Drawing Books","cost":150,"quantity":3,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}}]}

创建过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `json_example1`(json_object JSON)
  BEGIN

        DECLARE products json;
        DECLARE i INT DEFAULT 0;
        DECLARE categoryName VARCHAR(255) DEFAULT 0;
        /*assign json_object's value to products mysql json object*/
        SELECT json_object->"$.product" INTO products;

        loop1 : WHILE i < JSON_LENGTH(products) DO
                /*get categoryName from current products object in while loop */
                SET categoryName = JSON_EXTRACT(products,CONCAT('$[',i,'].categoryName'));
                /* insert categoryName into table */
                INSERT INTO product VALUES(NULL,SUBSTRING(categoryName,2,(LENGTH(categoryName)-2)));
                SET i = i + 1;
        END WHILE loop1;
    END

调用过程

mysql> call json_example1('{"billNo":16,"date":"2017-13-11 09:05:01","customerName":"Vikas","total":350.0,"fixedCharges":100,"taxAmount":25.78,"status":"paid","product":[{"productId":"MRR11","categoryId":72,"categoryName":"Parker Pen","cost":200,"quantity":2,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}},{"productId":"MRR12","categoryId":56,"categoryName":"Drawing Books","cost":150,"quantity":3,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}}]}');

输出

mysql> select * from product;
+------------+---------------+
| product_id | product_name  |
+------------+---------------+
|          1 | Parker Pen    |
|          2 | Drawing Books |
+------------+---------------+
2 rows in set (0.03 sec)

额外的-

创建表product ( product_id int(11) NOT NULL AUTO_INCREMENT，product_name varchar(255) DEFAULT NULL，PRIMARY KEY (product_id) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1；