ps_truncate_all_tables()

• 清空performance_schema下的%summary%和%history%表，有一个传参，表示是否在每一张表执行清空前打印表名，如果为FALSE，则不打印，只在执行操作完成最后打印总操作表数量，否则会打印每一个被执行truncate表的相关信息（类似：Running: TRUNCATE TABLE performance_schema.tb_name）

• 参数：

  • in_verbose BOOLEAN：是否在执行清空每一个表的内容之前显示每个TRUNCATE TABLE语句

• 定义语句

DROP PROCEDURE IF EXISTS ps_truncate_all_tables;

DELIMITER $$

CREATE DEFINER='root'@'localhost' PROCEDURE ps_truncate_all_tables (
        IN in_verbose BOOLEAN
    )
    COMMENT '
            Description
            -----------

            Truncates all summary tables within Performance Schema, 
            resetting all aggregated instrumentation as a snapshot.

            Parameters
            -----------

            in_verbose (BOOLEAN):
              Whether to print each TRUNCATE statement before running

            Example
            -----------

            mysql> CALL sys.ps_truncate_all_tables(false);
            +---------------------+
            | summary            |
            +---------------------+
            | Truncated 44 tables |
            +---------------------+
            1 row in set (0.10 sec)

            Query OK, 0 rows affected (0.10 sec)
            '
    SQL SECURITY INVOKER
    DETERMINISTIC
    MODIFIES SQL DATA
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_total_tables INT DEFAULT 0;
    DECLARE v_ps_table VARCHAR(64);
    DECLARE ps_tables CURSOR FOR
        SELECT table_name
          FROM INFORMATION_SCHEMA.TABLES
        WHERE table_schema = 'performance_schema'
          AND (table_name LIKE '%summary%'
            OR table_name LIKE '%history%');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    OPEN ps_tables;

    ps_tables_loop: LOOP
        FETCH ps_tables INTO v_ps_table;
        IF v_done THEN
          LEAVE ps_tables_loop;
        END IF;

        SET @truncate_stmt := CONCAT('TRUNCATE TABLE performance_schema.', v_ps_table);
        IF in_verbose THEN
            SELECT CONCAT('Running: ', @truncate_stmt) AS status;
        END IF;

        PREPARE truncate_stmt FROM @truncate_stmt;
        EXECUTE truncate_stmt;
        DEALLOCATE PREPARE truncate_stmt;

        SET v_total_tables = v_total_tables + 1;
    END LOOP;

    CLOSE ps_tables;

    SELECT CONCAT('Truncated ', v_total_tables, ' tables') AS summary;

END$$

DELIMITER ;

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-ps-truncate-all-tables.html

---

上一篇： ps_trace_thread()存储过程 | 下一篇： statement_performance_analyzer()存储过程