php - 枚举 mysql 查询中使用的表？

Question

有没有办法枚举mysql查询中使用的表？

假设我有查询:

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

Answer 1

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

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";

您必须自己构建查询中使用的不同表的数组，方法是遍历字段。