MySQL INSERT INTO table SELECT FROM another_table，在一个 PROCEDURE 里面 [英] MySQL INSERT INTO table SELECT FROM another_table, inside a PROCEDURE
本文介绍了MySQL INSERT INTO table SELECT FROM another_table，在一个 PROCEDURE 里面的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有这个 MySQL 程序，它将一些循环放入 2 个表中，将数据准备到 tblResults 中，然后在删除 2 之间找到的所有行后将所有数据(如果没有错误)插入一个大表中日期.
I have this MySQL procedure, that makes some loops into 2 tables, prepares the Data into tblResults and then insert all data (if no errors) into a huge table after delete all rows found between 2 dates.
问题是程序完成运行后，我收到此警告:
The problem is that after the procedure finishes to run, I receive this warning:
使用语句格式写入二进制日志的不安全语句，因为 BINLOG_FORMAT = STATEMENT.从另一个表中选择后写入具有自动增量列的表的语句是不安全的，因为检索行的顺序决定了将写入哪些(如果有)行.这个顺序是无法预测的，可能在主从上不同.
因此，如果我在 tblResults 表中有 250 个不同的行，则该过程将 250 个相同的行插入到最终表中.(通常是在 tblResults 中找到的第一行或最后一行，乘以 tblResults 中的记录数).
and as a result, if I have 250 distinct rows into tblResults table, the procedure insert 250 identical rows into final table. (usualy first or last row found in tblResults, multiplied by number of records from tblResults).
SQL 代码如下所示:
The SQL code look like this:
BEGIN
-- DECLARE bDone INT;
DECLARE CustomerId INT;
DECLARE LocationId INT;
DECLARE ContractId INT;
DECLARE DatePeriod DATE;
DECLARE SerialWeight DOUBLE;
DECLARE Serial VARCHAR(150);
DECLARE EnergyZone VARCHAR(5);
DECLARE ConsDay DATE;
DECLARE T0 DOUBLE;
DECLARE T1 DOUBLE;
DECLARE T2 DOUBLE;
DECLARE T3 DOUBLE;
DECLARE T4 DOUBLE;
DECLARE T5 DOUBLE;
DECLARE T6 DOUBLE;
DECLARE T7 DOUBLE;
DECLARE T8 DOUBLE;
DECLARE T9 DOUBLE;
DECLARE T10 DOUBLE;
DECLARE T11 DOUBLE;
DECLARE T12 DOUBLE;
DECLARE T13 DOUBLE;
DECLARE T14 DOUBLE;
DECLARE T15 DOUBLE;
DECLARE T16 DOUBLE;
DECLARE T17 DOUBLE;
DECLARE T18 DOUBLE;
DECLARE T19 DOUBLE;
DECLARE T20 DOUBLE;
DECLARE T21 DOUBLE;
DECLARE T22 DOUBLE;
DECLARE T23 DOUBLE;
DECLARE QtyEstimated DECIMAL(20,4);
DECLARE QtyMeasured DECIMAL(20,4);
DECLARE POD VARCHAR(50);
DECLARE Quantity DECIMAL(20,4);
DECLARE LocationCode VARCHAR(50);
DECLARE rCustomerId INT;
DECLARE rLocationId INT;
DECLARE rContractId INT;
DECLARE rDate DATE;
DECLARE rTime INT;
DECLARE rQtyEstimated DECIMAL(20,4);
DECLARE rQtyPredicted DECIMAL(20,4);
DECLARE rQtyMeasured DOUBLE;
DECLARE rCreateUser INT;
DECLARE rUpdateUser INT;
DECLARE rFirmaId INT;
DECLARE curs CURSOR FOR
select
ec.CustomerId,
ec.LocationId,
ec.Id as ContractId,
els.Date as DatePeriod,
els.SerialWeight,
ets.Serial,
ets.EnergyZone,
ets.Date as ConsDay,
ets.T0,
ets.T1,
ets.T2,
ets.T3,
ets.T4,
ets.T5,
ets.T6,
ets.T7,
ets.T8,
ets.T9,
ets.T10,
ets.T11,
ets.T12,
ets.T13,
ets.T14,
ets.T15,
ets.T16,
ets.T17,
ets.T18,
ets.T19,
ets.T20,
ets.T21,
ets.T22,
ets.T23,
CASE substr(els.Date, 6, 2)
WHEN '01' THEN ec.Estimated1
WHEN '02' THEN ec.Estimated2
WHEN '03' THEN ec.Estimated3
WHEN '04' THEN ec.Estimated4
WHEN '05' THEN ec.Estimated5
WHEN '06' THEN ec.Estimated6
WHEN '07' THEN ec.Estimated7
WHEN '08' THEN ec.Estimated8
WHEN '09' THEN ec.Estimated9
WHEN '10' THEN ec.Estimated10
WHEN '11' THEN ec.Estimated11
WHEN '12' THEN ec.Estimated12
END as QtyEstimated
from EnergyLocationSeries els
left join EnergyTimeSeries ets ON ets.Serial = els.Serial and concat(substr(ets.Date, 1, 7), '-01') = els.Date
left join EnergyLocation el ON el.Code2 = els.LocationCode
left join EnergyContract ec ON (el.Id = ec.LocationId AND el.Codep = '') OR (ec.LocationId = (SELECT max(Id) FROM EnergyLocation WHERE Code2 = el.Codep) AND Codep !='') -- AND ec.`Status` != 'Reziliat'
where els.Date = MTH and els.EnergyZone = ZONE
order by ets.Date ASC LIMIT 10;
DECLARE pods_cursor CURSOR FOR
SELECT els.LocationCode, els.Quantity
FROM EnergyLocation el
RIGHT JOIN EnergyLocationSeries els ON els.LocationCode = el.Code2 OR els.LocationCode = el.Codep
LEFT JOIN EnergyContract ec on ec.LocationId = el.Id
WHERE el.Code2 IS NULL;
DECLARE result CURSOR FOR
SELECT `CustomerId`, `LocationId`, `ContractId`, `Date`, `Time`, `QtyEstimated`, `QtyPredicted`, `QtyMeasured`, `CreateUser`, `UpdateUser`, `FirmaId`
FROM tblResults ORDER BY CustomerId, ContractId ASC;
DROP TABLE IF EXISTS tblResultsErrors;
CREATE TABLE IF NOT EXISTS tblResultsErrors (
`POD` INT(11) NULL DEFAULT NULL,
`QtyMeasured` DECIMAL(20,4) NULL DEFAULT NULL
);
DROP TABLE IF EXISTS tblResults;
CREATE TABLE IF NOT EXISTS tblResults (
`CustomerId` INT(11) NULL DEFAULT NULL,
`LocationId` INT(11) NULL DEFAULT NULL,
`ContractId` INT(11) NULL DEFAULT NULL,
`Date` DATE NULL DEFAULT NULL,
`Time` SMALLINT(6) NULL DEFAULT NULL,
`QtyEstimated` DECIMAL(20,4) NULL DEFAULT NULL,
`QtyPredicted` DECIMAL(20,4) NULL DEFAULT NULL,
`QtyMeasured` DOUBLE NULL DEFAULT NULL,
`CreateUser` VARCHAR(32) NULL DEFAULT NULL,
`UpdateUser` VARCHAR(32) NULL DEFAULT NULL,
`FirmaId` INT(11) NULL DEFAULT NULL
);
OPEN curs;
BEGIN
DECLARE bDone INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
SET bDone = 0;
REPEAT
FETCH curs INTO
CustomerId,
LocationId,
ContractId,
DatePeriod,
SerialWeight,
Serial,
EnergyZone,
ConsDay,
T0,
T1,
T2,
T3,
T4,
T5,
T6,
T7,
T8,
T9,
T10,
T11,
T12,
T13,
T14,
T15,
T16,
T17,
T18,
T19,
T20,
T21,
T22,
T23,
QtyEstimated;
IF bDone = 0 THEN
INSERT INTO tblResults VALUES
(CustomerId,LocationId,ContractId,ConsDay,1,QtyEstimated,0,(T0 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,2,QtyEstimated,0,(T1 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,3,QtyEstimated,0,(T2 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,4,QtyEstimated,0,(T3 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,5,QtyEstimated,0,(T4 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,6,QtyEstimated,0,(T5 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,7,QtyEstimated,0,(T6 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,8,QtyEstimated,0,(T7 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,9,QtyEstimated,0,(T8 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,10,QtyEstimated,0,(T9 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,11,QtyEstimated,0,(T10 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,12,QtyEstimated,0,(T11 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,13,QtyEstimated,0,(T12 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,14,QtyEstimated,0,(T13 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,15,QtyEstimated,0,(T14 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,16,QtyEstimated,0,(T15 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,17,QtyEstimated,0,(T16 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,18,QtyEstimated,0,(T17 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,19,QtyEstimated,0,(T18 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,20,QtyEstimated,0,(T19 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,21,QtyEstimated,0,(T20 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,22,QtyEstimated,0,(T21 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,23,QtyEstimated,0,(T22 * (SerialWeight / 100)),'root','root',0),
(CustomerId,LocationId,ContractId,ConsDay,24,QtyEstimated,0,(T23 * (SerialWeight / 100)),'root','root',0);
END IF;
UNTIL bDone END REPEAT;
END;
CLOSE curs;
OPEN pods_cursor;
BEGIN
DECLARE bDone INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
SET bDone = 0;
REPEAT
FETCH pods_cursor INTO POD, QtyMeasured;
IF bDone = 0 THEN
INSERT INTO tblResultsErrors VALUES
(LocationCode, Quantity);
END IF;
UNTIL bDone END REPEAT;
END;
CLOSE pods_cursor;
IF NOT EXISTS ( SELECT * FROM tblResultsErrors ) THEN
BEGIN
DELETE FROM EnergyIbdRecord WHERE Date BETWEEN MTH AND LAST_DAY(MTH);
OPEN result;
BEGIN
DECLARE bDone INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
SET bDone = 0;
REPEAT
FETCH result INTO rCustomerId,rLocationId,rContractId,rDate,rTime,rQtyEstimated,rQtyPredicted,rQtyMeasured,rCreateUser,rUpdateUser,rFirmaId;
IF bDone = 0 THEN
INSERT INTO EnergyIbdRecord (`CustomerId`, `LocationId`, `ContractId`, `Date`, `Time`, `QtyEstimated`, `QtyPredicted`, `QtyMeasured`, `CreateUser`, `UpdateUser`, `FirmaId`)
VALUES (rCustomerId,rLocationId,rContractId,rDate,rTime,rQtyEstimated,rQtyPredicted,rQtyMeasured,rCreateUser,rUpdateUser,rFirmaId);
END IF;
UNTIL bDone END REPEAT;
END;
CLOSE result;
END;
ELSE
BEGIN
SELECT * FROM tblResultsErrors;
END;
END IF;
END
有什么建议可以摆脱插入 tblResults 包含的内容吗?也许有必要运行 2 个独立的程序?这是一种方法吗?
Any suggestion to get rid of inserting what tblResults contains?
Maybe there is necessary to run 2 separated procedures? Would be this an approach?
推荐答案
尽可能避免使用 CURSORs.SQL 旨在批量处理，而不是一次处理一行.
Wherever possible, avoid using CURSORs. SQL is designed to do things in bulk, not one row at a time.
研究结构如
INSERT INTO ... SELECT ...;
CREATE TABLE ... SELECT ...;
例如，pods_cursor 可能可以通过以下方式消除:
For example, pods_cursor can probably be eliminated via:
INSERT INTO tblResultsErrors
(POD, QtyMeasured)
SELECT els.LocationCode, els.Quantity
FROM EnergyLocation el
RIGHT JOIN EnergyLocationSeries els
ON els.LocationCode = el.Code2 OR els.LocationCode = el.Codep
LEFT JOIN EnergyContract ec
on ec.LocationId = el.Id
WHERE el.Code2 IS NULL;
(哎哟.混合右和左让我的头像猫头鹰一样旋转.)
(Ouch. Mixing RIGHT and LEFT makes my head spin like an owl's.)
在ON 中使用OR 听起来效率很低.意图是什么?
Using OR in ON sounds very inefficient. What is the intent?
这篇关于MySQL INSERT INTO table SELECT FROM another_table，在一个 PROCEDURE 里面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
