我有两个表

表testlist20230519_20

表testtimestamp_10

|id |seconds              |cnt|cnt1|cnt2|cnt3|counted|counted2 |
|---|---------------------|---|----|----|----|-------|---------| 
|12 | 2023-05-19 00:00:11 | 0 | 0  | 0  |  0 |  0    |   0     |
|13 | 2023-05-19 00:00:12 | 0 | 0  | 0  |  0 |  0    |   0     |
|14 | 2023-05-19 00:00:13 | 0 | 0  | 0  |  0 |  0    |   0     |
|15 | 2023-05-19 00:00:14 | 0 | 0  | 0  |  0 |  0    |   0     |
|16 | 2023-05-19 00:00:15 | 0 | 0  | 0  |  0 |  0    |   0     |
|17 | 2023-05-19 00:00:16 | 0 | 0  | 0  |  0 |  0    |   0     |
|18 | 2023-05-19 00:00:17 | 0 | 0  | 0  |  0 |  0    |   0     |
|19 | 2023-05-19 00:00:18 | 0 | 0  | 0  |  0 |  0    |   0     |
|20 | 2023-05-19 00:00:19 | 0 | 0  | 0  |  0 |  0    |   0     |
|21 | 2023-05-19 00:00:20 | 0 | 0  | 0  |  0 |  0    |   0     |

我想按每秒对列 cnt 进行并发调用统计，像这样：

|id |seconds              |cnt|cnt1|cnt2|cnt3|counted|counted2 |
|---|---------------------|---|----|----|----|-------|---------| 
|12 | 2023-05-19 00:00:11 | 15| 0  | 0  |  0 |  0    |   0     |
|13 | 2023-05-19 00:00:12 | 16| 0  | 0  |  0 |  0    |   0     |
|14 | 2023-05-19 00:00:13 | 15| 0  | 0  |  0 |  0    |   0     |
|15 | 2023-05-19 00:00:14 | 13| 0  | 0  |  0 |  0    |   0     |
|16 | 2023-05-19 00:00:15 | 11| 0  | 0  |  0 |  0    |   0     |
|17 | 2023-05-19 00:00:16 | 8 | 0  | 0  |  0 |  0    |   0     |
|18 | 2023-05-19 00:00:17 | 6 | 0  | 0  |  0 |  0    |   0     |
|19 | 2023-05-19 00:00:18 | 4 | 0  | 0  |  0 |  0    |   0     |
|20 | 2023-05-19 00:00:19 | 2 | 0  | 0  |  0 |  0    |   0     |
|21 | 2023-05-19 00:00:20 | 0 | 0  | 0  |  0 |  0    |   0     |

我本来会创建一个存储过程，但速度非常慢，而一天有86400秒，通常需要大约12小时来计算，是否有更好的办法？

CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure2`()
proc_label:BEGIN
declare sqlstring varchar(5412);
DECLARE a    int;
Declare b   varchar(256);
declare test     varchar(128);
DECLARE errno INT;
DECLARE done INT DEFAULT FALSE;

DECLARE cur1 CURSOR FOR SELECT id,unix_timestamp(seconds) as seconds FROM vms.testtimestamp_10;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION

   -- set test = "OK2";
    select test;

 OPEN cur1;
  read_loop: LOOP
    FETCH cur1 INTO a, b;
    IF done THEN
      LEAVE read_loop;
    END IF;

 -- START TRANSACTION;
  -- set test = "OK3";
  -- select test;

    set sqlstring = concat("UPDATE vms.testtimestamp_10 " );
    set sqlstring = concat(sqlstring, "SET cnt = (SELECT count(*) FROM testlist20230519_20 WHERE ", b ,">= floor(starttime) and ",b," <= floor(endtime) )");
    set sqlstring = concat(sqlstring," where unix_timestamp(seconds) = ",b);
    set @d = sqlstring;
    PREPARE stmt from @d;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

-- COMMIT;

 END LOOP;
   select sqlstring;
  CLOSE cur1;

END

解决方案

我建议用一条查询来更新testtimestamp_10表，而不使用游标和循环。

with src as(
  SELECT id,count(cdrid) cnt
  FROM testtimestamp_10 t
  LEFT JOIN testlist20230519_20 d ON unix_timestamp(t.seconds) between d.starttime AND d.endtime
  GROUP BY id
)
UPDATE testtimestamp_10,src
  SET testtimestamp_10.cnt=src.cnt
WHERE testtimestamp_10.id=src.id
;

带数据的示例

在示例中，数据已被修改，因为你的示例中的表在时间上并不重叠。

对于MariaDb11及更早版本，请使用

UPDATE testtimestamp_10
JOIN (
  SELECT id, count(cdrid) cnt
  FROM testtimestamp_10 t
  LEFT JOIN testlist20230519_20 d ON unix_timestamp(t.seconds) between d.starttime and d.endtime
  GROUP BY id
) src ON testtimestamp_10.id=src.id
  SET testtimestamp_10.cnt=src.cnt
;

演示