mysql 枚举表_枚举mysql查询中使用的表？

Is there any way to enumerate tables used in mysql query?

Lets say I have query :

SELECT * FROM db_people.people_facts pf

INNER JOIN db_system.connections sm ON sm.source_id = pf.object_id

INNER JOIN db_people.people p ON sm.target_id = p.object_id

ORDER BY pf.object_id DESC

And I want in return array:

$tables = array(

[0] => 'db_people.people_facts',

[1] => 'db_system.connections',

[2] => 'db_people.people',

);

解决方案

The solution marked as good will return only the result tables. But if you do the next query it will fail:

SELECT users.* FROM users, cats, dogs WHERE users.id = cats.user_id

Will return only users and not cats and dogs tables.

The best solution is find a good parser, another solution is using REGEX and EXPLAIN query (more info in the next link):

But I think that another good solution is list all tables and search them inside the query, you can cache the list of tables.

EDIT: When searching for tables, better use a preg like:

// (`|'|"| )table_name(\1|$)

if(preg_match('/(`|\'|"| )table_name(\1|$)/i', $query))

// found

If not, it can return false positives with for example "table_name2", "table_name3"... table_name will return FOUND two times.