php SQL 从多个表中选择 *
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2523631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Select * from multiple tables
提问by zaf
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?
使用 PHP/PDO/MySQL 当在多个表上完成选择并且返回的数组键是完全限定的以避免列名冲突时，是否可以对列使用通配符？
example:
例子：
SELECT * from table1, table2;
SELECT * from table1, table2;
gives:
给出：
Array keys are 'table1.id', 'table2.id', 'table1.name' etc.
数组键是“table1.id”、“table2.id”、“table1.name”等。
I tried "SELECT table1.*,table2.* ..." but the returned array keys were not fully qualified so columns with the same name clashed and were overwritten.
我尝试了“SELECT table1.*,table2.* ...”，但返回的数组键不是完全限定的，因此具有相同名称的列发生冲突并被覆盖。
采纳答案by goat
Yes, you can. The easiest way is with pdo, although there's at least a few other extensions which are capable of it.
是的你可以。最简单的方法是使用 pdo，尽管至少有一些其他扩展能够使用它。
pdo
pdo
Set the attribute on the PDOobject, not the PDOStatment.
在PDO对象上设置属性，而不是PDOStatment。
$PDO->setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);
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'}
就是这样。然后你会得到像$row['myTable.myColumn']. 如果您也获取对象（例如 via PDO::FETCH_OBJECT），它也会起作用，所以要小心，因为您需要访问像$obj->{'myTable.myColumn'}
*The manual saysthe PDO::ATTR_FETCH_TABLE_NAMESattribute is only supported by certain drivers. If the above doesn't work, this mightwork instead.
*手册说该PDO::ATTR_FETCH_TABLE_NAMES属性仅受某些驱动程序支持。如果上述方法不起作用，这可能会起作用。
$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
相同的基本模式用于其他数据库扩展
mysql
mysql
$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
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);
}
This should also work with table aliases (tested in php 7.1) - the qualified column name will use the table alias.
这也应该适用于表别名（在 php 7.1 中测试） - 限定列名将使用表别名。
回答by KM.
you can do this:
你可以这样做：
SELECT Table1.*,Table2.xyz, Table2.abc,... From...
where you get all columns from one table using "*" and then just the columns from the other table you need, so there is no clash.
您可以使用“*”从一个表中获取所有列，然后只从您需要的另一个表中获取列，因此没有冲突。
You could also use column aliases, where you "rename" a column:
您还可以使用列别名，在其中“重命名”列：
SELECT Table1.A AS T1_A,Table2.A AS T2_A,... From...
your result set would be of columns T1_A and T2_A
您的结果集将是列 T1_A 和 T2_A
回答by Adam Robinson
Unfortunately, no; there is no SQL syntax for ensuring that column names are unique.
抱歉不行; 没有用于确保列名唯一的 SQL 语法。
If you truly don't know the names of the columns and must use SELECT *, your only real option would be to revert to some very ugly looking dynamic SQL that could inspect the structure of the tables and generate a query that would select them all explicitly with a table-name prefix.
如果您真的不知道列的名称并且必须使用SELECT *，那么您唯一真正的选择是恢复到一些非常难看的动态 SQL，它可以检查表的结构并生成一个查询，该查询将使用表名前缀。
I don't know which RDBMS you're using, but something like this should work on SQL Server:
我不知道您使用的是哪个 RDBMS，但这样的事情应该适用于 SQL Server：
declare @columns table (idx int identity(1,1), tablename varchar(100), columnname varchar(100))
insert into @columns (tablename, columnname)
select tablename, columnname
from INFORMATION_SCHEMA.COLUMNS
where tablename in ('table_1', 'table_2')
declare @sql nvarchar(4000)
declare @i int
declare @cnt in
declare @col varchar(100)
declare @table varchar(100)
select @i = 0, @cnt = max(idx), @sql = '' from @columns
while @i < @cnt
begin
select @i = @i + 1
select @col = columnname, @table = tablename from @columns where idx = @i
if len(@sql) > 0
select @sql = @sql + ', '
select @sql = @sql + '[' + @table + '].[' + @col + '] as [' + @table + '_' + @col + ']'
end
select @sql = 'select ' + @sql + ' from table_1, table_2'
exec sp_executesql @sql
回答by William Entriken
Shamelessly repackaged from @goat:
无耻地从@goat 重新打包：
// Workaround for setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);
function pdoStatementExecuteAndFetchObjWithTableNames(PDOStatement $statement)
{
$statement->setFetchMode(PDO::FETCH_NUM);
$statement->execute();
//build our associative array keys
$qualifiedColumnNames = array();
for ($i = 0; $i < $statement->columnCount(); $i++) {
$columnMeta = $statement->getColumnMeta($i);
$qualifiedColumnNames[] = "$columnMeta[table].$columnMeta[name]";
}
//fetch results and combine with keys
while ($row = $statement->fetch()) {
$qualifiedRow = array_combine($qualifiedColumnNames, $row);
yield (object) $qualifiedRow;
}
}
NOTE: if you use:
注意：如果您使用：
SELECT 1 FROM my_table AS my_table_alias
SELECT 1 FROM my_table AS my_table_alias
then you will get my_table. I would have hoped for my_table_alias. I got this result with PHP 5.6 and sqlite driver.
那么你会得到my_table。我本来希望的my_table_alias。我用 PHP 5.6 和 sqlite 驱动程序得到了这个结果。
回答by Dominic Barnes
Unfortunately, PHP (particularly the MySQL, PgSQL, MSSQL extensions) will always have your columns overwrite in the case of overlap.
不幸的是，在重叠的情况下，PHP（尤其是 MySQL、PgSQL、MSSQL 扩展）总是会覆盖您的列。
I would recommend creating a Viewin your database, and Alias your columns so that they are "fully-qualified".
我建议View在您的数据库中创建一个，并为您的列添加别名，以便它们是“完全限定的”。
For example: (MySQL)
例如：（MySQL）
CREATE VIEW viewTable1Table2 AS
SELECT
t1.field1 AS Table1Field1
t2.field1 AS Table2Field1
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.id = t2.id;
The syntax may not be perfect, but you can get a general idea of what I am talking about.
语法可能并不完美，但您可以大致了解我在说什么。
