MySQL 在连接中，如何使用它来自的表作为所有列名的前缀
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13153344/
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
In a join, how to prefix all column names with the table it came from
提问by Jarrod Smith
I'm analysing a rather horrible legacy database/codebase, trying to reduce server load by combining queries into joins (including an email alert cron job that typically invokes well over a million separate queries).
我正在分析一个相当糟糕的遗留数据库/代码库，试图通过将查询组合到连接中来减少服务器负载（包括通常调用超过一百万个单独查询的电子邮件警报 cron 作业）。
SELECT * FROM
class_alerts_holding ah
INNER JOIN class_listings l ON l.id = ah.lid
INNER JOIN class_users u ON u.id = ah.uid
LEFT JOIN class_prodimages pi ON pi.pid = ah.lid
This spits out 120 columns...
这会吐出 120 列...
aid | id | lid | uid | oid | catName | searchtext | alertfreq | listType | id | owner | title | section | shortDescription | description | featured | price | display | hitcount | dateadded | expiration | url | notified | searchcount | repliedcount | pBold | pHighlighted | notes | ...
To assist my analysis of how to construct the new queries it would be awesome if I could prefix the columns in the result with the table they came from in the JOIN e.g.
为了帮助我分析如何构建新查询，如果我可以在结果中的列前面加上它们在 JOIN 中来自的表的前缀，那将会很棒
class_alerts_holding.aid | class_alerts_holding.id | class_listings.lid | ...
Is there a way to achieve this?
有没有办法实现这一目标？
采纳答案by koljaTM
You could
你可以
select ah.*, l.*, u.*, pi.* from ...
then the columns will be returned ordered by table at least.
那么列将至少按表排序返回。
For better distinction between every two sets of columns, you could also add "delimiter" columns like this:
为了更好地区分每两组列，您还可以添加“分隔符”列，如下所示：
select ah.*, ':', l.*, ':', u.*, ':', pi.* from ...
(Edited to remove explicit aliases as unnecessary, see comments.)
（编辑以删除不必要的显式别名，请参阅注释。）
回答by Bjoern
You could name the fields in your query and give them aliases:
您可以命名查询中的字段并为其指定别名：
SELECT ah.whateverfield1 AS 'ah_field1',
ah.whateverfield2 AS 'ah_field2',
l.whateverfield3 AS 'l.field3',
[....]
FROM class_alerts_holding ah
INNER JOIN class_listings l ON l.id = ah.lid
INNER JOIN class_users u ON u.id = ah.uid
LEFT JOIN class_prodimages pi ON pi.pid = ah.lid
Its a bit of work to manually set up if you have that many fields, but you can simplify this with this query...
如果您有这么多字段，手动设置需要一些工作，但是您可以使用此查询简化此操作...
SHOW FULL FIELDS FROM your_table_name;
...and a good text editor and copy & paste.
...和一个很好的文本编辑器和复制和粘贴。
回答by Alden W.
The way to dynamically name columns is to generate a prepared statement that references the information_schema. This would give you the results you were looking for.
动态命名列的方法是生成一个引用 information_schema 的准备好的语句。这会给你你正在寻找的结果。
SET @sql = NULL;
SELECT CONCAT(
'SELECT ',GROUP_CONCAT(c.TABLE_NAME,'.',c.COLUMN_NAME,' AS `',c.TABLE_NAME,'.',c.COLUMN_NAME,'`'),'
FROM class_alerts_holding
INNER JOIN class_listings ON class_listings.id = class_alerts_holding.lid
INNER JOIN class_users ON class_users.id = class_alerts_holding.uid
LEFT JOIN class_prodimages ON class_prodimages.pid = class_alerts_holding.lid'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME IN ('class_alerts_holding','class_listings',
'class_users','class_prodimages');
PREPARE sql_statement FROM @sql;
EXECUTE sql_statement;
The GROUP_CONCAT() function has a default limit of 1024 characters, so depending on the number of columns in your tables, you may need to raise this limit in order to generate the prepared statement.
GROUP_CONCAT() 函数的默认限制为 1024 个字符，因此根据表中的列数，您可能需要提高此限制以生成准备好的语句。
SET SESSION group_concat_max_len = 1000000;
This command will raise the group concat limit if needed. -
如果需要，此命令将提高组连接限制。——
回答by Sorin Postelnicu
Based on the solution proposed by koljaTM and AndriyM, maybe an even better solution is to write your query like this:
根据 koljaTM 和 AndriyM 提出的解决方案，也许更好的解决方案是这样编写查询：
select
'--TABLE_AAA:--', TABLE_AAA.*,
'--TABLE_BBB:--', TABLE_BBB.*,
'--TABLE_CCC:--', TABLE_CCC.*,
'--TABLE_DDD:--', TABLE_DDD.*
from ...
Unfortunately this is still not good enough in cases when one (or more) of the tables contains more column names than can fit on the screen width. (So you might see on your screen 20 columns but still not be visible on the screen the name of the table from which they come.)
不幸的是，当一个（或多个）表包含的列名超过屏幕宽度时，这仍然不够好。（因此，您可能会在屏幕上看到 20 列，但在屏幕上仍然看不到它们来自的表的名称。）
It would still have been better if SQL provided a way to automatically prefix the column names with the table names...
如果 SQL 提供了一种自动为列名加上表名前缀的方法，那就更好了……
回答by Mihai Matei
I've found something usefull in this question MySQL concat() to create column names to be used in a query?. I think that this can be one of the solutions.
我在这个问题中发现了一些有用的东西MySQL concat() to create column names to be used in a query? . 我认为这可能是解决方案之一。
回答by Anatoly Alekseev
I am convinced that such feature to prefix and/or postfix fields names with a table name in a join SHOULD BE INCLUDED INTO ANSI SQL STANDARD. Currently, in year 2019, there is still no elegant cross-platform way to do it, and all what's left is ugly-looking and error-prone manual hacking with aliases, or platform-specific solutions involving dynamic sql. Everyone would really benefit from having ability to specify custom prefix or/and postfix to fields denoted by 'dot-star' (.*). Sample select after adding such feature would be:
我相信这种在连接中使用表名前缀和/或后缀字段名称的功能应该包含在ANSI SQL STANDARD 中。目前，在 2019 年，仍然没有优雅的跨平台方式来做到这一点，剩下的就是使用别名的丑陋且容易出错的手动黑客攻击，或者涉及动态 sql 的特定平台解决方案。每个人都将从能够为由“点星”(.*) 表示的字段指定自定义前缀或/和后缀的能力中受益。添加此类功能后的示例选择将是：
select a.* use prefix,b.* use postfix '_b' from table_a a inner join table_b b on a.id=b.id
As you can see, by default prefix or postfix would equal table name (or alias name), and can be overridden with any desired string literal.
如您所见，默认情况下前缀或后缀等于表名（或别名），并且可以用任何所需的字符串文字覆盖。
Also what's aching to be added to standard, is ability to exclude certain fields from 'starred' (*) output, which is a shortcut to select all fields. I would add exceptkeyword to list fieds which I do not want to be included for reasons of reducing network data transfer or/and brevity, e.g. :
此外，渴望添加到标准中的是能够从“带星号”(*) 输出中排除某些字段，这是选择所有字段的快捷方式。由于减少网络数据传输或/和简洁的原因，我将添加except关键字以列出我不想包含的字段，例如：
select * except large_binary_data_field,another_notneeded_field,etc from my_table
Such feature would allow to avoid necessity of explicitly specifying full (and potentially large) list of fields which are neededas opposed to only specifying star and a few fields which are not needed.
这样的特性将允许避免显式指定所需字段的完整（并且可能很大）列表的必要性，而不是仅指定星号和一些不需要的字段。
So please, whoever reading this post and being able to reach out to ANSI SQL standard influencers, you know what to do )
因此，请阅读这篇文章并能够联系 ANSI SQL 标准影响者的人，您知道该怎么做）
P.S.yet another ugly, but at least automated & generic dynamic sql wrapper
PS又一个丑陋的，但至少是自动化和通用的动态 sql 包装器
For the Python advocates who work with psycopg, here is the convenient sub I use (strictly internally, as it's prone to possible sql injections)
对于使用 psycopg 的 Python 拥护者，这里是我使用的方便的 sub（严格来说是在内部，因为它很容易被 sql 注入）
def get_table_fields(table,alias,prefix='',suffix='',excluding=''):
if type(excluding)==str: excluding=excluding.split(',')
cur.execute('select * from '+table+' where 0=1');cur.fetchall()
if not (cur.description is None):
return ','.join([alias+'.'+col.name+' '+prefix+col.name+suffix for col in cur.description if not (col.name in excluding)])
And the calling code, where I am joining 3 tables and want to avoid fetching large datafield from the datasetstable:
以及调用代码，我在其中加入 3 个表并希望避免从数据集表中获取大数据字段：
sql="""select %s,%s,%s from tasks t,features_sets f,datasets d
where
t.is_active=true and f.is_active=true
and f.task=t.id and t.train_dataset=d.id
""" % (
get_table_fields('tasks','t',prefix='ts_'),
get_table_fields('features_sets','f',prefix='fs_'),
get_table_fields('datasets','d',prefix='ds_',excluding='data')
)
which gets unrolled for me into mighty
为我展开变得强大
select t.id ts_id,t.project ts_project,t.name ts_name,***,
fs_id,f.task fs_task,f.name fs_name,f.description fs_description,***,
d.id ds_id,d.project ds_project,d.name ds_name,***
from tasks t,features_sets f,datasets d
where
t.is_active=true and f.is_active=true
and f.task=t.id and t.train_dataset=d.id
where *** means tons of other useful fields, some of them are common for more than one table (hence the need for prefixing). curis obviously the psycopg cursor, and 0=1 condition is intended to retrieve only fields names without real data.
其中 *** 表示大量其他有用的字段，其中一些字段对于多个表是常见的（因此需要添加前缀）。cur显然是 psycopg 游标，并且 0=1 条件旨在仅检索没有实际数据的字段名称。
回答by Othyn
I ended up just building the field set for the query, as as of 2020 this still isn't supported.
我最终只是为查询构建了字段集，截至 2020 年，这仍然不受支持。
But, being a lazy programmer, I obviously didn't want to manually type this all out for all of the tables in my query. So I wrote a query to build the select statement:
但是，作为一个懒惰的程序员，我显然不想为查询中的所有表手动键入所有内容。所以我写了一个查询来构建选择语句：
SELECT
CONCAT(table_name, ".", column_name, " AS ", CHAR(34), table_name, ".", column_name, CHAR(34)) field_names
FROM
information_schema.columns
WHERE
table_schema = "my_database"
AND table_name IN(
"table_1",
"table_2"
);
which will output something like:
这将输出如下内容：
| field_names |
|------------------------------------|
| table_1.id AS "table_1.id" |
| table_1.name AS "table_1.name" |
| table_2.id AS "table_2.id" |
| table_2.number AS "table_2.number" |
That can then easily be copied into your SELECTstatement.
然后可以轻松地将其复制到您的SELECT语句中。
回答by iabr
@alden-w, You may add TABLE_SCHEMA condition to where to do not mix up same table names from different schemas
@alden-w，您可以将 TABLE_SCHEMA 条件添加到不要混淆来自不同模式的相同表名的位置
WHERE c.TABLE_SCHEMA='YOUR_SCHEMA_NAME' AND c.TABLE_NAME IN (....)
回答by DarkRob
You may try dynamic sql to create a query on the go as per the table definition.
您可以尝试使用动态 sql 根据表定义随时随地创建查询。
declare @col varchar(max)
set @col = Select stuff(
(select ', ' + column_name + '.' + table_name
from information_schema.columns
where table_name in ( 'table1', 'table2' ...) for xml
path('')),1,1,'')
declare @query nvarchar(max) = '
select ' + @col + '
from table1
inner join table2 on table1.id = table2.id '
exec sp_executesql @query
