For MySQL version 5.7, you can use the sys.session view to view the last SQL executed by the session:
SELECT * FROM sys.session WHERE CONN_ID = 20036 \G
The sys.session view is associated with sys.processlist, and sys.processlist uses `performance_schema'. `events_statements_current'.
If you want to query the SQL situation that a session has been executed since it was opened, you need to use the following tables:
performance_schema.events_statements_current
performance_schema.events_statements_history
performance_schema.events_transactions_history_long
The three tables above record the recent execution of SQL by MySQL server, but the fields representing time in the three tables above are expressed in picoseconds and can not be converted directly using FROM_UNIXTIME (instead of starting from 1970-01-00:00 or 0000-01-01:00:00, the starting baseline of each MySQL instance is different).
Using the following script, you can see the execution of SQL on a particular session:
SELECT @dt_ts:=UNIX_TIMESTAMP(NOW()); SELECT @dt_timer:=MAX(SH.TIMER_START) FROM performance_schema.threads AS T1 INNER JOIN performance_schema.events_statements_history AS SH ON T1.`THREAD_ID`=SH.`THREAD_ID` WHERE T1.PROCESSLIST_ID=CONNECTION_ID(); SELECT SH.`SQL_TEXT`, FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_START)/1000000000000 AS SIGNED)) AS start_time, FROM_UNIXTIME(@dt_ts-CAST((@dt_timer+SH.TIMER_END)/1000000000000 AS SIGNED)) AS end_time, (SH.TIMER_END-SH.TIMER_START)/1000000000000 AS used_seconds, SH.ROWS_AFFECTED AS affected_rows, SH.ROWS_SENT AS send_rows FROM performance_schema.threads AS T1 INNER JOIN performance_schema.events_statements_history AS SH ON T1.`THREAD_ID`=SH.`THREAD_ID` WHERE T1.PROCESSLIST_ID=946364 AND SH.TIMER_START<@dt_timer ORDER BY SH.TIMER_START ASC;
The output effect is as follows:
+------------------------------------+---------------------+---------------------+--------------+---------------+-----------+ | SQL_TEXT | start_time | end_time | used_seconds | affected_rows | send_rows | +------------------------------------+---------------------+---------------------+--------------+---------------+-----------+ | select sleep(5) | 2019-07-29 19:59:55 | 2019-07-29 20:00:00 | 5.0003 | 0 | 1 | | insert into tb001(c1,c2)select 2,4 | 2019-07-29 20:01:26 | 2019-07-29 20:01:26 | 0.0002 | 1 | 0 | | insert into tb001(c1,c2)select 2,4 | 2019-07-29 20:04:34 | 2019-07-29 20:04:34 | 0.0001 | 1 | 0 | +------------------------------------+---------------------+---------------------+--------------+---------------+-----------+
Expansion: MySQL Transaction -- View the SQL for uncommitted transaction execution