枚举mysql查询中使用的表? [英] Enumerating tables used in mysql query?

问题描述

有什么方法可以枚举mysql查询中使用的表吗?

Is there any way to enumerate tables used in mysql 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

我想要返回数组:

$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.

最好的解决方案是找到一个好的解析器，另一种解决方案是使用REGEX和EXPLAIN查询(下一个链接中的更多信息):

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

在查询中获取mysql表

但是我认为另一个好的解决方案是 列出所有表并在查询中搜索它们 ，您可以缓存表列表.

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

如果没有，它将返回假阳性，例如"table_name2"，"table_name3" ... table_name将返回FOUND两次.

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

这篇关于枚举mysql查询中使用的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！