SQL 从多个表中选择 *

2021-12-26 00:00:00 sql database php mysql pdo

使用 PHP/PDO/MySQL 是否可以在对多个表进行选择并且返回的数组键是完全限定的以避免列名冲突时对列使用通配符?

Using PHP/PDO/MySQL is it possible to use a wildcard for the columns when a select is done on multiple tables and the returned array keys are fully qualified to avoid column name clash?

示例:

SELECT * from table1, table2;

给出:

数组键是'table1.id'、'table2.id'、'table1.name'等

Array keys are 'table1.id', 'table2.id', 'table1.name' etc.

我尝试了SELECT table1.*,table2.* ..."，但返回的数组键不是完全限定的，因此具有相同名称的列发生冲突并被覆盖.

I tried "SELECT table1.*,table2.* ..." but the returned array keys were not fully qualified so columns with the same name clashed and were overwritten.

推荐答案

是的，你可以.最简单的方法是使用 pdo，尽管至少有一些其他扩展可以使用它.

Yes, you can. The easiest way is with pdo, although there's at least a few other extensions which are capable of it.

在 PDO 对象上设置属性，而不是 PDOStatment.

Set the attribute on the PDO object, not the PDOStatment.

$PDO->setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);

就是这样.然后你会得到像 $row['myTable.myColumn'] 这样的关联数组键.如果您也获取对象(例如通过 PDO::FETCH_OBJECT)，它也可以工作，所以要小心，因为您需要访问像 $obj->{'myTable.myColumn'}<这样的属性/代码>

That's it. Then you get associative array keys like $row['myTable.myColumn']. It works if you fetch an object too (eg via PDO::FETCH_OBJECT) so beware, because you need to access the properties like $obj->{'myTable.myColumn'}

*手册说 PDO::ATTR_FETCH_TABLE_NAMES 属性仅受某些驱动程序支持.如果上述方法不起作用，则可能可以代替.

*The manual says the PDO::ATTR_FETCH_TABLE_NAMES attribute is only supported by certain drivers. If the above doesn't work, this might work instead.

$pdoStatement->setFetchMode(PDO::FETCH_NUM);
$pdoStatement->execute();
//build our associative array keys
$qualifiedColumnNames = array();
for ($i = 0; $i < $pdoStatement->columnCount(); $i++) {
    $columnMeta = $pdoStatement->getColumnMeta($i);
    $qualifiedColumnNames[] = "$columnMeta[table].$columnMeta[name]";
}

//fetch results and combine with keys
while ($row = $pdoStatement->fetch()) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

<小时>

相同的基本模式用于其他数据库扩展

Same basic pattern is used for other database extensions

$res = mysql_query($sql);
//build our associative array keys
$qualifiedColumnNames = array();
for ($i = 0; $i < mysql_num_fields($res); $i++) {
    $columnMeta = mysql_fetch_field($res, $i);
    $qualifiedColumnNames[] = "$columnMeta[table].$columnMeta[name]";
}

//fetch results and combine with keys
while ($row = mysql_fetch_row($res)) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

<小时>

mysqli

$res = $mysqli->query($sql);
//build our associative array keys
$qualifiedColumnNames = array();
foreach ($res->fetch_fields() as $columnMeta) {
    $qualifiedColumnNames[] = "{$columnMeta->table}.{$columnMeta->name}";
}

//fetch results and combine with keys
while ($row = $res->fetch_row()) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

这也适用于表别名(在 php 7.1 中测试) - 限定列名将使用表别名.

This should also work with table aliases (tested in php 7.1) - the qualified column name will use the table alias.

相关文章