wordpress学做网站,网站建设技术支持牛商网,广告公司广告语,58网站怎么做才有客户问performance_schema中有很多的表(语句信息表#xff0c;事务信息表等)记录执行的SQL的具体信息#xff0c;执行事务的具体信息#xff0c;其中都会有一个叫做TIMER_START的字段#xff0c;这个字段的值易读性很差#xff0c;官方文档说是皮秒#xff0c;但也没说怎么转成…performance_schema中有很多的表(语句信息表事务信息表等)记录执行的SQL的具体信息执行事务的具体信息其中都会有一个叫做TIMER_START的字段这个字段的值易读性很差官方文档说是皮秒但也没说怎么转成人类易读的形式通过以下的方法可以将其转换为人易读的格式。举例以performance_schema.events_statements_history_long表为例摆上对应的SQL语句可以考虑将其写成函数。SELECT*,DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAMEUPTIME) - TIMER_START*10e-13 second) AS start_time,ROUND(timer_wait*10E-10, 3) AS wait in (ms)FROMperformance_schema.events_statements_history_longwhere thread_id194277limit 1\G;*************************** 1. row ***************************THREAD_ID: 194277EVENT_ID: 323END_EVENT_ID: 354EVENT_NAME: statement/sql/selectSOURCE:TIMER_START: 1122588644870331000TIMER_END: 1122588645297519000TIMER_WAIT: 427188000LOCK_TIME: 327000000SQL_TEXT: select count(*) from mysql.userDIGEST: a41461c07eca51bcda21d91cf128cfa6DIGEST_TEXT: SELECT COUNT ( * ) FROM mysql . userCURRENT_SCHEMA: NULLOBJECT_TYPE: NULLOBJECT_SCHEMA: NULLOBJECT_NAME: NULLOBJECT_INSTANCE_BEGIN: NULLMYSQL_ERRNO: 0RETURNED_SQLSTATE: NULLMESSAGE_TEXT: NULLERRORS: 0WARNINGS: 0ROWS_AFFECTED: 0ROWS_SENT: 1ROWS_EXAMINED: 0CREATED_TMP_DISK_TABLES: 0CREATED_TMP_TABLES: 0SELECT_FULL_JOIN: 0SELECT_FULL_RANGE_JOIN: 0SELECT_RANGE: 0SELECT_RANGE_CHECK: 0SELECT_SCAN: 0SORT_MERGE_PASSES: 0SORT_RANGE: 0SORT_ROWS: 0SORT_SCAN: 0NO_INDEX_USED: 0NO_GOOD_INDEX_USED: 0NESTING_EVENT_ID: NULLNESTING_EVENT_TYPE: NULLNESTING_EVENT_LEVEL: 0start_time: 2020-10-22 10:07:39.644871 wait in (ms): 0.4271 row in set (0.10 sec)关键字段DATE_SUB(NOW(),INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAMEUPTIME) - TIMER_START*10e-13 second) AS start_timeDATE_SUB(指定日期A, 需要减去的时间间隔B)如现在是2020-10-22 10:15:00.000(参数A)我指定需要减去的天数为2即INTERVAL 2 DAY(参数B)就能返回得到2020-10-20 10:15:00.000结论timer_*即从数据库正常启动以来经过的时间但这个时间不一定准确官方文档内说这个会基于处理器的啥啥啥进行计算可能会有波动。笔者有一台数据库的timer_*字段信息用以上方法计算之后相差特别大(大的离谱那种时间多了1年)不知道原因这台经历过升级有知道的大佬欢迎留言。创建时间转换函数方便调用begin;set global log_bin_trust_function_creatorson;DELIMITER $$DROP FUNCTION IF EXISTS timer_to_date$$CREATE FUNCTION timer_to_date(timer bigint(20) unsigned) RETURNS varchar(50)BEGINDECLARE res_date varchar(50);DECLARE uptime int;SET uptime(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAMEUPTIME);SET res_dateDATE_SUB(NOW(),INTERVAL uptime - timer*10e-13 second);return res_date;END $$delimiter ;set global log_bin_trust_function_creatorsoff;commit;
