代码之家 › 专栏 › 技术社区 › Beiru

枚举mysql查询中使用的表?

enumerate tags mysql php

2

Beiru · 技术社区 · 17 年前

假设我有疑问:

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',
);

3 回复 | 直到 10 年前

1

4

Bill Karwin 17 年前

是的,您可以获得有关作为查询结果一部分的表和列的信息。这叫做 结果集元数据

MySQL结果集元数据的唯一PHP解决方案是使用MySQLi扩展和 mysqli_stmt::result_metadata() 作用

$stmt = $mysqli->prepare("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");

$meta = $stmt->result_metadata();

$field1 = $meta->fetch_field();

echo "Table for field " . $field1->name . " is " . $field1->table . "\n";

2

1

typeoneerror 17 年前

http://dev.mysql.com/doc/refman/5.0/en/explain.html

3

0

Community Mohan Dere 9 年前

标记为good的解决方案将只返回结果表。但如果您执行下一个查询,它将失败:

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

将而不是猫和狗的桌子。

Get mysql tables in a query

但我认为另一个好的解决办法是

编辑 :搜索表时,最好使用preg,如:

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

如果不是,它可以返回误报,例如“table_name2”、“table_name3”。。。表_name将返回find两次。