我想知道这在SQL中是否可行。假设你有两个表A和B，你在表A上做一个选择，在表B上做一个连接:
SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);
如果表A有“a_id”、“name”、“some_id”列，表B有“b_id”、“name”、“some_id”列，查询将返回“a_id”、“name”、“some_id”、“b_id”、“name”、“some_id”列。有什么方法可以为表B的列名加上前缀而不单独列出每一列吗?等价于这个:
SELECT a.*, b.b_id as 'b.b_id', b.name as 'b.name', b.some_id as 'b.some_id'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);
但是，如前所述，没有列出每一列，所以像这样:
SELECT a.*, b.* as 'b.*'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);
基本上就是说，“用‘something’为b.*返回的每一列添加前缀”。这可能吗，还是我运气不好?
编辑
关于不使用SELECT *等的建议是有效的建议，但与我的上下文无关，因此请关注眼前的问题——是否可以在连接中为表的所有列名添加前缀(SQL查询中指定的常量)?
我的最终目标是能够通过连接对两个表执行SELECT *操作，并且能够从结果集中获得的列的名称中分辨出哪些列来自表a，哪些列来自表b。同样，我不想单独列出列，我需要能够执行SELECT *操作。
对于使用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 */
请注意以下字段:catalog、table、org_name
现在您知道SQL中的哪个字段属于哪个模式(即目录)和表。
这足以从多表sql查询中通用地识别每个字段，而不需要别名。
一个实际的产品SqlYOG在这样一个庄园中使用这个确切的数据，当PK字段存在时，它们能够独立地更新多表连接的每个表。
从这个解决方案出发，我将如何处理这个问题:
首先创建一个所有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中并不完全有效，因为不支持USING。
请评论，如果你可以测试/纠正这段代码，例如MySQL。
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'));
对此没有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)
我根据答案实现了一个解决方案，建议在节点中使用虚拟或哨兵列。你可以通过生成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)。
实现(基于我的驱动程序返回的字段/行，但应该很容易为其他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)
})
})
这个问题在实践中很有用。在软件编程中，只需要列出所有显式列，在这些列中，您需要特别小心地处理所有条件。
想象一下，当调试或尝试使用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;
