在MySQL中重新创建显示概要文件变量的代码

Question

嗨，我在用MySQL，

SHOW PROFILES;

返回一个长列表，其中的行如下所示：

 |Query_ID|Duration     |Query
>| 9    |    0.00028550 |   INSERT INTO Duration(Status,DURATION,Query_ID)|

我试图复制这个结果，这样我就可以将结果插入到表中并添加一个WHERE子句。

所以我试了一下

SELECT Query_ID, STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 9;

但这将返回一个较低级别的视图(我不希望这样)：

先开0.000053张桌子0.000088张

这与以下相同：

SHOW PROFILE;

我将如何重新创建SHOW PROFILES？所以我可以这样表演：

INSERT INTO Duration(Query_ID,Status,DURATION)
SELECT * FROM SHOW PROFILE
WHERE PROFILE.QUERY_ID = 9;

谢谢

Answer 1

QUERY_ID组。要获得状态，您需要记录的STATE与群最大 SEQ；要获得持续时间，只需将组中每个记录的DURATION之和：

INSERT INTO Duration
  (Query_ID, Status, DURATION)
SELECT QUERY_ID, STATE, t.DURATION
FROM   INFORMATION_SCHEMA.PROFILING JOIN (
  SELECT   QUERY_ID, MAX(SEQ) AS SEQ, SUM(DURATION) AS DURATION
  FROM     INFORMATION_SCHEMA.PROFILING
  WHERE    QUERY_ID = 9
  GROUP BY QUERY_ID  -- superfluous in the presence of the above filter
) t USING (QUERY_ID, SEQ)