如果关心模式变化，这可能对你有用: 1. 在所有涉及的表上运行'DESCRIBE table'查询。 2. 使用返回的字段名动态构造以所选别名为前缀的列名字符串。

PHP 7.2 + MySQL/Mariadb

MySQL会给你发送多个相同名称的字段。甚至在终端客户端。但如果你想要一个关联数组，你必须自己创建键。

感谢@axelbrz的原创。我已经将它移植到更新的php，并对它进行了一些清理:

function mysqli_rows_with_columns($link, $query) {
    $result = mysqli_query($link, $query);
    if (!$result) {
        return mysqli_error($link);
    }
    $field_count = mysqli_num_fields($result);
    $fields = array();
    for ($i = 0; $i < $field_count; $i++) {
        $field = mysqli_fetch_field_direct($result, $i);
        $fields[] = $field->table . '.' . $field->name; # changed by AS
        #$fields[] = $field->orgtable . '.' . $field->orgname; # actual table/field names
    }
    $rows = array();
    while ($row = mysqli_fetch_row($result)) {
        $new_row = array();
        for ($i = 0; $i < $field_count; $i++) {
            $new_row[$fields[$i]] = $row[$i];
        }
        $rows[] = $new_row;
    }
    mysqli_free_result($result);
    return $rows;
}

$link = mysqli_connect('localhost', 'fixme', 'fixme', 'fixme');
print_r(mysqli_rows_with_columns($link, 'select foo.*, bar.* from foo, bar'));

Select *通常会导致糟糕的代码，因为往往会添加新列，或者表中列的顺序经常改变，这通常会以非常微妙的方式破坏Select *。所以列出列是正确的解决方案。

至于如何进行查询，mysql不确定，但在sqlserver中，您可以从syscolumns中选择列名，并动态构建select子句。

对此没有SQL标准。

然而，通过代码生成(在表创建或修改或运行时按需生成)，你可以很容易地做到这一点:

CREATE TABLE [dbo].[stackoverflow_329931_a](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL,
    [col4] [nchar](10) NULL,
 CONSTRAINT [PK_stackoverflow_329931_a] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[stackoverflow_329931_b](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL,
    [col4] [nchar](10) NULL,
 CONSTRAINT [PK_stackoverflow_329931_b] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

DECLARE @table1_name AS varchar(255)
DECLARE @table1_prefix AS varchar(255)
DECLARE @table2_name AS varchar(255)
DECLARE @table2_prefix AS varchar(255)
DECLARE @join_condition AS varchar(255)
SET @table1_name = 'stackoverflow_329931_a'
SET @table1_prefix = 'a_'
SET @table2_name = 'stackoverflow_329931_b'
SET @table2_prefix = 'b_'
SET @join_condition = 'a.[id] = b.[id]'

DECLARE @CRLF AS varchar(2)
SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @a_columnlist AS varchar(MAX)
DECLARE @b_columnlist AS varchar(MAX)
DECLARE @sql AS varchar(MAX)

SELECT @a_columnlist = COALESCE(@a_columnlist + @CRLF + ',', '') + 'a.[' + COLUMN_NAME + '] AS [' + @table1_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1_name
ORDER BY ORDINAL_POSITION

SELECT @b_columnlist = COALESCE(@b_columnlist + @CRLF + ',', '') + 'b.[' + COLUMN_NAME + '] AS [' + @table2_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2_name
ORDER BY ORDINAL_POSITION

SET @sql = 'SELECT ' + @a_columnlist + '
,' + @b_columnlist + '
FROM [' + @table1_name + '] AS a
INNER JOIN [' + @table2_name + '] AS b
ON (' + @join_condition + ')'

PRINT @sql
-- EXEC (@sql)

这个问题在实践中很有用。在软件编程中，只需要列出所有显式列，在这些列中，您需要特别小心地处理所有条件。

想象一下，当调试或尝试使用DBMS作为日常办公工具，而不是特定程序员的抽象底层基础设施的可变实现时，我们需要编写大量的sql。这种场景随处可见，比如数据库转换、迁移、管理等。这些sql大多只执行一次，不会再使用，给每个列名只是浪费时间。不要忘记SQL的发明不仅仅是为程序员使用的。

通常我会创建一个带列名前缀的实用程序视图，这里是pl/pgsql中的函数，这并不容易，但你可以将它转换为其他过程语言。

-- Create alias-view for specific table.

create or replace function mkaview(schema varchar, tab varchar, prefix varchar)
    returns table(orig varchar, alias varchar) as $$
declare
    qtab varchar;
    qview varchar;
    qcol varchar;
    qacol varchar;
    v record;
    sql varchar;
    len int;
begin
    qtab := '"' || schema || '"."' || tab || '"';
    qview := '"' || schema || '"."av' || prefix || tab || '"';
    sql := 'create view ' || qview || ' as select';

    for v in select * from information_schema.columns
            where table_schema = schema and table_name = tab
    loop
        qcol := '"' || v.column_name || '"';
        qacol := '"' || prefix || v.column_name || '"';

        sql := sql || ' ' || qcol || ' as ' || qacol;
        sql := sql || ', ';

        return query select qcol::varchar, qacol::varchar;
    end loop;

    len := length(sql);
    sql := left(sql, len - 2); -- trim the trailing ', '.
    sql := sql || ' from ' || qtab;

    raise info 'Execute SQL: %', sql;
    execute sql;
end
$$ language plpgsql;

例子:

-- This will create a view "avp_person" with "p_" prefix to all column names.
select * from mkaview('public', 'person', 'p_');

select * from avp_person;

I see two possible situations here. First, you want to know if there is a SQL standard for this, that you can use in general regardless of the database. No, there is not. Second, you want to know with regard to a specific dbms product. Then you need to identify it. But I imagine the most likely answer is that you'll get back something like "a.id, b.id" since that's how you'd need to identify the columns in your SQL expression. And the easiest way to find out what the default is, is just to submit such a query and see what you get back. If you want to specify what prefix comes before the dot, you can use "SELECT * FROM a AS my_alias", for instance.