MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Таблица с прайсом

CREATE TABLE `prais_informations` (
  `id` mediumint UNSIGNED NOT NULL,
  `code` mediumint UNSIGNED NOT NULL,
  `stock` mediumint UNSIGNED NOT NULL,
  `price` decimal(7,2) NOT NULL,
  `trade_price` decimal(7,2) NOT NULL,
  `group_in_tree` smallint UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `prais_informations` (`id`, `code`, `stock`, `price`, `trade_price`, `group_in_tree`) VALUES
(1, 1, 100, '97.00', '0.00', 0),
(2, 2, 5000, '123.00', '0.00', 0);


ALTER TABLE `prais_informations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `code` (`code`);

ALTER TABLE `prais_informations`
  MODIFY `id` mediumint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `prais_informations`
  ADD CONSTRAINT `prais_informations_ibfk_1` FOREIGN KEY (`code`) REFERENCES `prais` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
-- Таблица, в которую нужно поместить данные

CREATE TABLE `sales` (
  `id` int UNSIGNED NOT NULL,
  `code` int UNSIGNED NOT NULL,
  `quantity` mediumint UNSIGNED NOT NULL,
  `price` decimal(7,2) NOT NULL,
  `sum` decimal(8,2) NOT NULL,
  `idCheck` int UNSIGNED NOT NULL,
  `data` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `sales`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idCheck` (`idCheck`);

ALTER TABLE `sales`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35;
-- Данные для помещения. MySQL не считает их валидными

SET @input := 'sales:  [  { id: 2, quantity: 5, price: 123, sum: 615 },  { id: 1, quantity: 9, price: 97, sum: 873 } ]';
SELECT @input;
SELECT JSON_VALID(@input);


@input


sales:  [  { id: 2, quantity: 5, price: 123, sum: 615 },  { id: 1, quantity: 9, price: 97, sum: 873 } ]




JSON_VALID(@input)


0


-- Преобразование исходных данных в валидный JSON

SET @input:=REPLACE(@input, ' ', '');
SET @input:=REPLACE(@input, 'sales:', '');
SET @input:=REGEXP_REPLACE(@input, '([{,])([a-z])', '$1"$2');
SET @input:=REGEXP_REPLACE(@input, '([a-z]):', '$1":');
-- Теперь они выглядят так. заодно проверяем валидность.

SELECT @input;
SELECT JSON_VALID(@input);


@input


[{"id":2,"quantity":5,"price":123,"sum":615},{"id":1,"quantity":9,"price":97,"sum":873}]




JSON_VALID(@input)


1


-- так выполняется парсинг данных на отдельные поля

SELECT *
FROM JSON_TABLE(@input,
                "$[*]"
                COLUMNS (id INT PATH "$.id",
                         quantity MEDIUMINT PATH "$.quantity",
                         price DECIMAL(7,2) PATH "$.price",
                         `sum` DECIMAL(8,2) PATH "$.sum"
                         )) parsed


id
quantity
price
sum


2
5
123.00
615.00

1
9
97.00
873.00


CREATE TEMPORARY TABLE new_tbl SELECT jtin.id AS jtin_id, jtin.quantity AS jtin_qty, pi.stock AS pi_qty, pi.price AS pi_price
FROM JSON_TABLE(@input,
                "$[*]"
                COLUMNS (id INT PATH "$.id" ,
                         quantity MEDIUMINT PATH "$.quantity",
                         price DECIMAL(7,2) PATH "$.price",
                         `sum` DECIMAL(8,2) PATH "$.sum"
                         )) AS jtin 
                LEFT JOIN prais_informations AS pi ON jtin.id  = pi.code;
-- Смотрим итог

SELECT * FROM new_tbl


jtin_id
jtin_qty
pi_qty
pi_price


2
5
5000
123.00

1
9
100
97.00


-- Парсинг исходных данных и помещение их в таблицу

SET @res := (SELECT count(*) FROM new_tbl WHERE new_tbl.jtin_qty > new_tbl.pi_qty);


SET @last_index := 100;


/*
  Инсертим содержимое массива заказа в таблицу регистрации
*/

INSERT INTO sales(`code`, `quantity`, `price`, `sum`, `idCheck`) SELECT nt.jtin_id, nt.jtin_qty, nt.pi_price, (nt.jtin_qty * nt.pi_price), @last_index FROM new_tbl AS nt;

INSERT INTO sales(`code`, `quantity`, `price`, `sum`, `idCheck`) SELECT nt.jtin_id, nt.jtin_qty, nt.pi_price, (nt.jtin_qty * nt.pi_price), @last_index FROM new_tbl AS nt;

INSERT INTO sales(`code`, `quantity`, `price`, `sum`, `idCheck`) SELECT nt.jtin_id, nt.jtin_qty, nt.pi_price, (nt.jtin_qty * nt.pi_price), @last_index FROM new_tbl AS nt;
SELECT * FROM sales;


id
code
quantity
price
sum
idCheck
data


35
2
5
123.00
615.00
100
2020-11-13 10:35:08

36
1
9
97.00
873.00
100
2020-11-13 10:35:08

38
2
5
123.00
615.00
100
2020-11-13 10:35:08

39
1
9
97.00
873.00
100
2020-11-13 10:35:08

41
2
5
123.00
615.00
100
2020-11-13 10:35:08

42
1
9
97.00
873.00
100
2020-11-13 10:35:08
id	code	quantity	price	sum	idCheck	data
35	2	5	123.00	615.00	100	2020-11-13 10:35:08
36	1	9	97.00	873.00	100	2020-11-13 10:35:08
38	2	5	123.00	615.00	100	2020-11-13 10:35:08
39	1	9	97.00	873.00	100	2020-11-13 10:35:08
41	2	5	123.00	615.00	100	2020-11-13 10:35:08
42	1	9	97.00	873.00	100	2020-11-13 10:35:08