在连接中，如何将所有列名加上它来自的表的前缀

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作业，它通常调用超过100万个单独查询)。

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?

是否有办法做到这一点?

6 个解决方案

#1

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.)}

(编辑为删除不必要的显式别名，请参阅注释。)

#2

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.

…一个好的文本编辑器和复制粘贴。

#3

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. -

如果需要，此命令将提高组concat限制。- - - - - -

#4

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()来创建要在查询中使用的列名?。我认为这是一个解决方案。

#5

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提供了一种方法，可以自动在列名前面加上表名……

#6

@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 (....)

#1