ps_thread_account()

• 在threads表中查询并返回给定内部线程ID号相关联的account名称(user_name@host_name)

  • 该函数在MySQL 5.7.9中新增

• 参数：

  • in_thread_id BIGINT UNSIGNED：指定一个内部线程ID，返回该内部线程ID相关联的account名称，该值与performance_schema.threads表中的thread_id列值对应

• 返回值：一个TEXT文本值

• 定义语句

DROP FUNCTION IF EXISTS ps_thread_account;

DELIMITER $$

CREATE DEFINER='root'@'localhost' FUNCTION ps_thread_account (
        in_thread_id BIGINT UNSIGNED
    ) RETURNS TEXT
    COMMENT '
            Description
            -----------

            Return the user@host account for the given Performance Schema thread id.

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

            in_thread_id (BIGINT UNSIGNED):
              The id of the thread to return the account for.

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

            mysql> select thread_id, processlist_user, processlist_host from performance_schema.threads where type = ''foreground'';
              +-----------+------------------+------------------+
              | thread_id | processlist_user | processlist_host |
              +-----------+------------------+------------------+
              |        23 | NULL            | NULL            |
              |        30 | root            | localhost        |
              |        31 | msandbox        | localhost        |
              |        32 | msandbox        | localhost        |
              +-----------+------------------+------------------+
              4 rows in set (0.00 sec)

              mysql> select sys.ps_thread_account(31);
              +---------------------------+
              | sys.ps_thread_account(31) |
              +---------------------------+
              | msandbox@localhost        |
              +---------------------------+
              1 row in set (0.00 sec)
            '

    SQL SECURITY INVOKER
    NOT DETERMINISTIC
    READS SQL DATA
BEGIN
    RETURN (SELECT IF(
                      type = 'FOREGROUND',
                      CONCAT(processlist_user, '@', processlist_host),
                      type
                    ) AS account
              FROM `performance_schema`.`threads`
            WHERE thread_id = in_thread_id);
END$$

DELIMITER ;

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

---

上一篇： ps_is_thread_instrumented()函数 | 下一篇： ps_thread_id()函数