SQL select join:是否可以将所有列的前缀设置为“prefix.*”?

我在这里看到两种可能的情况。首先,您想知道是否有一个针对这个的SQL标准,不管数据库是什么,您都可以使用它。不,没有。第二,你想知道一个特定的DBMS产品。然后你需要识别它。但我认为最有可能的答案是,您将得到类似“a.i d,b.i d”的返回,因为这就是您需要在SQL表达式中标识列的方式。找出默认值的最简单方法就是提交这样的查询,然后看看能得到什么。例如,如果要指定点前面的前缀,可以使用“select*from a”作为我的\别名。

您的问题的答案似乎是否定的,但是您可以使用的一种方法是分配一个虚拟列来分隔每个新表。如果在脚本语言(如python或php)中循环遍历列列表的结果集,那么这一点尤其有效。

SELECT '' as table1_dummy, table1.*, '' as table2_dummy, table2.*, '' as table3_dummy, table3.* FROM table1
JOIN table2 ON table2.table1id = table1.id
JOIN table3 ON table3.table1id = table1.id

我知道这并不能准确回答你的问题,但是如果你是一个编码员,这是一个很好的方法来分离具有重复列名的表。希望这能帮助别人。

我完全理解为什么这是必要的——至少对我来说,当有很多表需要连接时(包括许多内部连接),它在快速原型制作过程中很方便。只要第二个“joinetable.*”字段通配符中的列名相同,主表的字段值就会被joinetable值覆盖。当不得不手动指定带有别名的表字段时,容易出错、令人沮丧并违反dry…

下面是一个php(wordpress)函数,通过代码生成以及如何使用它的示例来实现这一点。在本例中,它用于快速生成一个自定义查询,该查询将提供通过 高级自定义字段 字段。

function prefixed_table_fields_wildcard($table, $alias)
{
    global $wpdb;
    $columns = $wpdb->get_results("SHOW COLUMNS FROM $table", ARRAY_A);

    $field_names = array();
    foreach ($columns as $column)
    {
        $field_names[] = $column["Field"];
    }
    $prefixed = array();
    foreach ($field_names as $field_name)
    {
        $prefixed[] = "`{$alias}`.`{$field_name}` AS `{$alias}.{$field_name}`";
    }

    return implode(", ", $prefixed);
}

function test_prefixed_table_fields_wildcard()
{
    global $wpdb;

    $query = "
    SELECT
        " . prefixed_table_fields_wildcard($wpdb->posts, 'campaigns') . ",
        " . prefixed_table_fields_wildcard($wpdb->posts, 'venues') . "
        FROM $wpdb->posts AS campaigns
    LEFT JOIN $wpdb->postmeta meta1 ON (meta1.meta_key = 'venue' AND campaigns.ID = meta1.post_id)
    LEFT JOIN $wpdb->posts venues ON (venues.post_status = 'publish' AND venues.post_type = 'venue' AND venues.ID = meta1.meta_value)
    WHERE 1
    AND campaigns.post_status = 'publish'
    AND campaigns.post_type = 'campaign'
    LIMIT 1
    ";

    echo "<pre>$query</pre>";

    $posts = $wpdb->get_results($query, OBJECT);

    echo "<pre>";
    print_r($posts);
    echo "</pre>";
}

输出:

SELECT
    `campaigns`.`ID` AS `campaigns.ID`, `campaigns`.`post_author` AS `campaigns.post_author`, `campaigns`.`post_date` AS `campaigns.post_date`, `campaigns`.`post_date_gmt` AS `campaigns.post_date_gmt`, `campaigns`.`post_content` AS `campaigns.post_content`, `campaigns`.`post_title` AS `campaigns.post_title`, `campaigns`.`post_excerpt` AS `campaigns.post_excerpt`, `campaigns`.`post_status` AS `campaigns.post_status`, `campaigns`.`comment_status` AS `campaigns.comment_status`, `campaigns`.`ping_status` AS `campaigns.ping_status`, `campaigns`.`post_password` AS `campaigns.post_password`, `campaigns`.`post_name` AS `campaigns.post_name`, `campaigns`.`to_ping` AS `campaigns.to_ping`, `campaigns`.`pinged` AS `campaigns.pinged`, `campaigns`.`post_modified` AS `campaigns.post_modified`, `campaigns`.`post_modified_gmt` AS `campaigns.post_modified_gmt`, `campaigns`.`post_content_filtered` AS `campaigns.post_content_filtered`, `campaigns`.`post_parent` AS `campaigns.post_parent`, `campaigns`.`guid` AS `campaigns.guid`, `campaigns`.`menu_order` AS `campaigns.menu_order`, `campaigns`.`post_type` AS `campaigns.post_type`, `campaigns`.`post_mime_type` AS `campaigns.post_mime_type`, `campaigns`.`comment_count` AS `campaigns.comment_count`,
    `venues`.`ID` AS `venues.ID`, `venues`.`post_author` AS `venues.post_author`, `venues`.`post_date` AS `venues.post_date`, `venues`.`post_date_gmt` AS `venues.post_date_gmt`, `venues`.`post_content` AS `venues.post_content`, `venues`.`post_title` AS `venues.post_title`, `venues`.`post_excerpt` AS `venues.post_excerpt`, `venues`.`post_status` AS `venues.post_status`, `venues`.`comment_status` AS `venues.comment_status`, `venues`.`ping_status` AS `venues.ping_status`, `venues`.`post_password` AS `venues.post_password`, `venues`.`post_name` AS `venues.post_name`, `venues`.`to_ping` AS `venues.to_ping`, `venues`.`pinged` AS `venues.pinged`, `venues`.`post_modified` AS `venues.post_modified`, `venues`.`post_modified_gmt` AS `venues.post_modified_gmt`, `venues`.`post_content_filtered` AS `venues.post_content_filtered`, `venues`.`post_parent` AS `venues.post_parent`, `venues`.`guid` AS `venues.guid`, `venues`.`menu_order` AS `venues.menu_order`, `venues`.`post_type` AS `venues.post_type`, `venues`.`post_mime_type` AS `venues.post_mime_type`, `venues`.`comment_count` AS `venues.comment_count`
    FROM wp_posts AS campaigns
LEFT JOIN wp_postmeta meta1 ON (meta1.meta_key = 'venue' AND campaigns.ID = meta1.post_id)
LEFT JOIN wp_posts venues ON (venues.post_status = 'publish' AND venues.post_type = 'venue' AND venues.ID = meta1.meta_value)
WHERE 1
AND campaigns.post_status = 'publish'
AND campaigns.post_type = 'campaign'
LIMIT 1

Array
(
    [0] => stdClass Object
        (
            [campaigns.ID] => 33
            [campaigns.post_author] => 2
            [campaigns.post_date] => 2012-01-16 19:19:10
            [campaigns.post_date_gmt] => 2012-01-16 19:19:10
            [campaigns.post_content] => Lorem ipsum
            [campaigns.post_title] => Lorem ipsum
            [campaigns.post_excerpt] => 
            [campaigns.post_status] => publish
            [campaigns.comment_status] => closed
            [campaigns.ping_status] => closed
            [campaigns.post_password] => 
            [campaigns.post_name] => lorem-ipsum
            [campaigns.to_ping] => 
            [campaigns.pinged] => 
            [campaigns.post_modified] => 2012-01-16 21:01:55
            [campaigns.post_modified_gmt] => 2012-01-16 21:01:55
            [campaigns.post_content_filtered] => 
            [campaigns.post_parent] => 0
            [campaigns.guid] => http://example.com/?p=33
            [campaigns.menu_order] => 0
            [campaigns.post_type] => campaign
            [campaigns.post_mime_type] => 
            [campaigns.comment_count] => 0
            [venues.ID] => 84
            [venues.post_author] => 2
            [venues.post_date] => 2012-01-16 20:12:05
            [venues.post_date_gmt] => 2012-01-16 20:12:05
            [venues.post_content] => Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
            [venues.post_title] => Lorem ipsum venue
            [venues.post_excerpt] => 
            [venues.post_status] => publish
            [venues.comment_status] => closed
            [venues.ping_status] => closed
            [venues.post_password] => 
            [venues.post_name] => lorem-ipsum-venue
            [venues.to_ping] => 
            [venues.pinged] => 
            [venues.post_modified] => 2012-01-16 20:53:37
            [venues.post_modified_gmt] => 2012-01-16 20:53:37
            [venues.post_content_filtered] => 
            [venues.post_parent] => 0
            [venues.guid] => http://example.com/?p=84
            [venues.menu_order] => 0
            [venues.post_type] => venue
            [venues.post_mime_type] => 
            [venues.comment_count] => 0
        )
)

我知道的唯一一个数据库是sqlite,这取决于您配置的设置。 PRAGMA full_column_names 和 PRAGMA short_column_names . 见 http://www.sqlite.org/pragma.html

否则,如果您在查询中键入列的名称太麻烦,那么我只能推荐按顺序位置而不是按列名称提取结果集中的列。

这是一个很好的例子 it's bad practice to use SELECT * --因为最终你还是需要输入所有的列名。

我了解支持可能更改名称或位置的列的必要性,但使用通配符可以实现这一点 更努力 不容易。

不同的数据库产品会给你不同的答案;但是如果你走得太远,你会让自己受到伤害。你最好不要选择你想要的列,给它们自己的别名,这样每一列的标识都是清晰的,你可以在结果中区分它们。

我和OP处于同一条船上——我加入了三个不同表中的几十个字段,其中一些字段具有相同的名称(如ID、名称等)。我不想列出每个字段,所以我的解决方案是对共享名称的字段进行别名,并对具有唯一名称的字段使用select*。

例如:

表A: 身份证件, 姓名, 第一场, 第二场…

表B: 身份证件, 姓名, 第三场, 第四场…

选择a.id作为a id,a.name作为a name,a.*,b.id作为b id,b.name作为b name,b.*…。

当访问结果时,我们将使用这些字段的别名,并忽略“原始”名称。

也许不是最好的解决方案,但它对我有用……我使用的是MySQL

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

想象一下,在调试时,或者尝试使用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;

没有用于此的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)

我完全理解你关于重复域名的问题。

我也需要它,直到我编写了自己的函数来解决它。如果您使用的是PHP,那么您可以使用它,如果您有以下功能,也可以使用您的语言编写代码。

这里的诀窍是 mysql_field_table() 返回表名和 mysql_field_name() 结果中每行的字段(如果有) mysql_num_fields() 所以你可以把它们混合在一个新的数组中。

这会在所有列前面加上前缀;)

当做,

function mysql_rows_with_columns($query) {
    $result = mysql_query($query);
    if (!$result) return false; // mysql_error() could be used outside
    $fields = mysql_num_fields($result);
    $rows = array();
    while ($row = mysql_fetch_row($result)) { 
        $newRow = array();
        for ($i=0; $i<$fields; $i++) {
            $table = mysql_field_table($result, $i);
            $name = mysql_field_name($result, $i);
            $newRow[$table . "." . $name] = $row[$i];
        }
        $rows[] = $newRow;
    }
    mysql_free_result($result);
    return $rows;
}

或者可以使用红门SQL重构或SQL提示,单击选项卡按钮将select*扩展到列列表中。

所以在您的例子中,如果您输入select*from a join b… 转到*的末尾,选项卡按钮,瞧!你会看到 选择A.column1,A.column2,…。,B.column1,B.column2来自连接B

但这不是免费的

如果在要连接的2个或3个表中存在某个字段,那么如何在WHERE子句中引用该字段,就不能不加别名了。 对于MySQL,您试图引用的是哪一个还不清楚。

我通过重命名相关表中的字段解决了我的一个类似问题。是的,我有幸做到这一点,我明白每个人都可能没有。我为表中表示表名的每个字段添加了前缀。因此,OP发布的SQL将保持不变-

SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);

并且仍然给出了预期的结果——便于识别输出字段所属的表。

有两种方法可以让我想到以可重用的方式实现这一点。一种方法是用列的前缀重命名所有列。我见过很多次,但我真的不喜欢。我发现它是多余的,会导致大量的输入,并且当需要覆盖一个来源不清楚的列名的情况时,您可以总是使用别名。

另一种方法,如果您致力于完成这一点,我建议您在这种情况下这样做,就是为每个表创建别名为表名的视图。然后针对这些视图而不是表进行联接。这样,您可以自由使用*如果愿意,可以自由使用具有原始列名称的原始表,并且还可以方便地编写任何后续查询,因为您已经在视图中完成了重命名工作。

最后,我不清楚为什么您需要知道每个列来自哪个表。这有关系吗?最重要的是他们所包含的数据。用户ID是来自用户表还是来自用户问题表并不重要。当然,当您需要更新它时,它是很重要的,但是在这一点上,您应该已经足够了解您的模式来确定它。

如果担心模式更改,这可能对您有用: 1。对所有相关表运行“描述表”查询。 2。使用返回的字段名动态构造以所选别名为前缀的列名字符串。

对于那些使用mysql c-api的人,您的问题有一个直接的答案。

给定SQL:

  SELECT a.*, b.*, c.* FROM table_a a JOIN table_b b USING (x) JOIN table_c c USING (y)

“mysql_stmt_result_metadata()”的结果给出了从准备好的SQL查询到结构mysql_field[]的字段定义。每个字段包含以下数据:

  char *name;                 /* Name of column (may be the alias) */
  char *org_name;             /* Original column name, if an alias */
  char *table;                /* Table of column if column was a field */
  char *org_table;            /* Org table name, if table was an alias */
  char *db;                   /* Database for table */
  char *catalog;              /* Catalog for table */
  char *def;                  /* Default value (set by mysql_list_fields) */
  unsigned long length;       /* Width of column (create length) */
  unsigned long max_length;   /* Max width for selected set */
  unsigned int name_length;
  unsigned int org_name_length;
  unsigned int table_length;
  unsigned int org_table_length;
  unsigned int db_length;
  unsigned int catalog_length;
  unsigned int def_length;
  unsigned int flags;         /* Div flags */
  unsigned int decimals;      /* Number of decimals in field */
  unsigned int charsetnr;     /* Character set */
  enum enum_field_types type; /* Type of field. See mysql_com.h for types */

注意字段:目录、表、组织名称

现在您知道SQL中的哪些字段属于哪个模式(即目录)和表。 这足以通用地从多表SQL查询中标识每个字段,而无需对任何字段进行别名。

一个实际的产品sqlyog显示在这样一个庄园中使用这个精确的数据,当存在pk字段时,它们能够独立地更新多表联接的每个表。

从发展 this solution ,这是我处理问题的方法:

首先创建所有 AS 声明:

DECLARE @asStatements varchar(8000)

SELECT @asStatements = ISNULL(@asStatements + ', ','') + QUOTENAME(table_name) + '.' + QUOTENAME(column_name) + ' AS ' + '[' + table_name + '.' + column_name + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE_A' OR TABLE_NAME = 'TABLE_B'
ORDER BY ORDINAL_POSITION

然后在查询中使用它:

EXEC('SELECT ' + @asStatements + ' FROM TABLE_A a JOIN TABLE_B b USING (some_id)');

但是,这可能需要修改,因为类似的东西只在SQL Server中测试。但此代码在SQL Server中不完全有效,因为不支持使用。

如果您可以测试/更正此代码(例如mysql),请发表评论。

最近在Nodejs和Postgres中遇到了这个问题。

ES6方法

我知道没有任何RDBMS功能提供此功能,因此我创建了一个包含所有字段的对象,例如:

const schema = { columns: ['id','another_column','yet_another_column'] }

定义了一个reducer,将字符串与表名连接在一起:

const prefix = (table, columns) => columns.reduce((previous, column) => {
  previous.push(table + '.' + column + ' AS ' + table + '_' + column);
  return previous;
}, []);

这将返回字符串数组。为每个表调用它并组合结果:

const columns_joined = [...prefix('tab1',schema.columns), ...prefix('tab2',schema.columns)];

输出最终的SQL语句:

console.log('SELECT ' + columns_joined.join(',') + ' FROM tab1, tab2 WHERE tab1.id = tab2.id');

我实现了一个基于 the answer suggesting using dummy or sentinel columns 在节点中。您可以通过生成如下SQL来使用它:

select 
    s.*
  , '' as _prefix__creator_
  , u.*
  , '' as _prefix__speaker_
  , p.*
from statements s 
  left join users u on s.creator_user_id = u.user_id
  left join persons p on s.speaker_person_id = p.person_id

然后对从数据库驱动程序中得到的行进行后处理,就像 addPrefixes(row) .

实施(基于 fields / rows 由我的驱动程序返回,但对于其他DB驱动程序应易于更改):

const PREFIX_INDICATOR = '_prefix__'
const STOP_PREFIX_INDICATOR = '_stop_prefix'

/** Adds a <prefix> to all properties that follow a property with the name: PREFIX_INDICATOR<prefix> */
function addPrefixes(fields, row) {
  let prefix = null
  for (const field of fields) {
    const key = field.name
    if (key.startsWith(PREFIX_INDICATOR)) {
      if (row[key] !== '') {
        throw new Error(`PREFIX_INDICATOR ${PREFIX_INDICATOR} must not appear with a value, but had value: ${row[key]}`)
      }
      prefix = key.substr(PREFIX_INDICATOR.length)
      delete row[key]
    } else if (key === STOP_PREFIX_INDICATOR) {
      if (row[key] !== '') {
        throw new Error(`STOP_PREFIX_INDICATOR ${STOP_PREFIX_INDICATOR} must not appear with a value, but had value: ${row[key]}`)
      }
      prefix = null
      delete row[key]
    } else if (prefix) {
      const prefixedKey = prefix + key
      row[prefixedKey] = row[key]
      delete row[key]
    }
  }
  return row
}

测试:

const {
  addPrefixes,
  PREFIX_INDICATOR,
  STOP_PREFIX_INDICATOR,
} = require('./BaseDao')

describe('addPrefixes', () => {
  test('adds prefixes', () => {
    const fields = [
      {name: 'id'},
      {name: PREFIX_INDICATOR + 'my_prefix_'},
      {name: 'foo'},
      {name: STOP_PREFIX_INDICATOR},
      {name: 'baz'},
    ]
    const row = {
      id: 1,
      [PREFIX_INDICATOR + 'my_prefix_']: '',
      foo: 'bar',
      [STOP_PREFIX_INDICATOR]: '',
      baz: 'spaz'
    }
    const expected = {
      id: 1,
      my_prefix_foo: 'bar',
      baz: 'spaz',
    }
    expect(addPrefixes(fields, row)).toEqual(expected)
  })
})

select*通常会导致错误的代码,因为新列往往会被添加,或者表中的列顺序经常发生变化,这通常会以非常微妙的方式中断select*的操作。所以列出列是正确的解决方案。

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

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

我要做的是使用Excel连接过程。例如,首先我选择*并获取所有列,将它们粘贴到Excel中。然后写出我需要包围这个列的代码。假设我需要将prev添加到一组列中。在A列中有我的字段,在B列中有“as prev_uu”,在C列中有我的字段。在D列中有一列。

然后在E列中使用concatate并将它们合并在一起,确保包含空格。然后剪切并粘贴到SQL代码中。我还使用这个方法为同一个字段和其他更长的代码编写了case语句,这些代码是我在一个几百字段表中为每个字段所需要做的。