SQL 表别名 - 好还是坏？

SQL Table Aliases - Good or Bad?

提问by tbreffni

What are the pros and cons of using table aliases in SQL? I personally try to avoid them, as I think they make the code less readable (especially when reading through large where/and statements), but I'd be interested in hearing any counter-points to this. When is it generally a good idea to use table aliases, and do you have any preferred formats?

在 SQL 中使用表别名的优缺点是什么？我个人尽量避免使用它们，因为我认为它们会使代码的可读性降低（尤其是在阅读大型 where/and 语句时），但我有兴趣听到对此的任何反对意见。什么时候使用表别名通常是一个好主意，您是否有任何首选格式？

采纳答案by Rob Allen

Table aliases are a necessary evil when dealing with highly normalized schemas. For example, and I'm not the architect on this DB so bear with me, it can take 7 joins in order to get a clean and complete record back which includes a person's name, address, phone number and company affiliation.

在处理高度规范化的模式时，表别名是必不可少的。例如，我不是这个数据库的架构师，所以请耐心等待，它可能需要 7 个连接才能获得干净完整的记录，其中包括一个人的姓名、地址、电话号码和公司隶属关系。

Rather than the somewhat standard single character aliases, I tend to favor short word aliases so the above example's SQL ends up looking like:

我倾向于使用短单词别名，而不是有些标准的单字符别名，因此上面示例的 SQL 最终看起来像：

select person.FirstName
      ,person.LastName
      ,addr.StreetAddress
      ,addr.City
      ,addr.State
      ,addr.Zip
      ,phone.PhoneNumber
      ,company.CompanyName
from tblPeople person
left outer join tblAffiliations affl on affl.personID = person.personID
left outer join tblCompany company on company.companyID = affl.companyID

... etc

... 等等

回答by Lasse V. Karlsen

Well, there are some cases you mustuse them, like when you need to join to the same table twice in one query.

好吧，在某些情况下您必须使用它们，例如当您需要在一个查询中两次连接到同一个表时。

It also depends on wether you have unique column names across tables. In our legacy database we have 3-letter prefixes for all columns, stemming from an abbreviated form from the table, simply because one ancient database system we were once compatible with didn't support table aliases all that well.

它还取决于您是否在表中具有唯一的列名。在我们的遗留数据库中，所有列都有 3 个字母的前缀，源于表的缩写形式，仅仅是因为我们曾经兼容的一个古老的数据库系统并不能很好地支持表别名。

If you have column names that occur in more than one table, specifying the table name as part of the column reference is a must, and thus a table alias will allow for a shorter syntax.

如果列名出现在多个表中，则必须将表名指定为列引用的一部分，因此表别名将允许使用更短的语法。

回答by jedd.ahyoung

Am I the only person here who really hates them?

我是这里唯一一个真正讨厌他们的人吗？

Generally, I don't use them unless I have to. I just really hate having to read something like

一般来说，除非万不得已，我不会使用它们。我真的很讨厌必须阅读类似的东西

select a.id, a.region, a.firstname, a.blah, b.yadda, b.huminahumina, c.crap
from table toys as a
inner join prices as b on a.blah = b.yadda
inner join customers as c on c.crap = something else
etc

When I read SQL, I like to know exactly what I'm selecting when I read it; aliases actually confuse me more because I've got to slog through lines of columns before I actually get to the table name, which generally represents information about the data that the alias doesn't. Perhaps it's okay if you made the aliases, but I commonly read questions on StackOverflow with code that seems to use aliases for no good reason. (Additionally, sometimes, someone will create an alias in a statement and just not use it. Why?)

当我阅读 SQL 时，我喜欢确切地知道我在阅读它时选择了什么；别名实际上让我更加困惑，因为在我真正到达表名之前我必须遍历列的行，表名通常表示有关别名没有的数据的信息。如果您使用别名，也许没问题，但我经常阅读有关 StackOverflow 的问题，其中的代码似乎无缘无故地使用别名。（此外，有时，有人会在语句中创建一个别名而不使用它。为什么？）

I think that table aliases are used so much because a lot of people are averse to typing. I don't think that's a good excuse, though. That excuse is the reason we end up with terrible variable naming, terrible function acronyms, bad code...I would take the time to type out the full name. I'm a quick typer, though, so maybe that has something to do with it. (Maybe in the future, when I've got carpal tunnel, I'll reconsider my opinion on aliases. :P ) I especially hate running across table aliases in PHP code, where I believe there's absolutely no reason to have to do that - you've only got to type it once!

我认为表格别名使用得如此之多，因为很多人都反对打字。不过，我认为这不是一个很好的借口。这个借口是我们最终得到糟糕的变量命名、糟糕的函数首字母缩略词、糟糕的代码的原因……我会花时间输入全名。不过，我打字快​​，所以也许这与它有关。（也许在未来，当我有腕管时，我会重新考虑我对别名的看法。:P）我特别讨厌在 PHP 代码中运行表别名，我认为绝对没有理由必须这样做 -你只需要输入一次！

I always use column qualifiers in my statements, but I'm not averse to typing a lot, so I will gladly type the full name multiple times. (Granted, I do abuse MySQL's tab completion.) Unless it's a situation where I have to use an alias (like some described in other answers), I find the extra layer of abstraction cumbersome and unnecessary.

我总是在我的语句中使用列限定符，但我并不反对大量输入，所以我很乐意多次输入全名。（当然，我确实滥用了 MySQL 的 tab 补全。）除非是我必须使用别名的情况（就像其他答案中描述的那样），否则我发现额外的抽象层既麻烦又不必要。

Edit:(Over a year later) I'm dealing with some stored procedures that use aliases (I did not write them and I'm new to this project), and they're kind of painful. I realize that the reason I don't like aliases is because of how they're defined. You know how it's generally good practice to declare variables at the top of your scope? (And usually at the beginning of a line?) Aliases in SQL don't follow this convention, which makes me grind my teeth. Thus, I have to search the entire code for a single alias to find out where it is (and what's frustrating is, I have to read through the logic before I find the alias declaration). If it weren't for that, I honestly might like the system better.

编辑：（一年多后）我正在处理一些使用别名的存储过程（我没有编写它们，而且我是这个项目的新手），它们有点痛苦。我意识到我不喜欢别名的原因是因为它们的定义方式。您知道在作用域顶部声明变量通常是一种好习惯吗？（通常在一行的开头？）SQL 中的别名不遵循这个约定，这让我感到很不舒服。因此，我必须在整个代码中搜索单个别名以找出它的位置（令人沮丧的是，在找到别名声明之前，我必须通读逻辑）。如果不是因为这个，老实说，我可能会更喜欢这个系统。

If I ever write a stored procedure that someone else will have to deal with, I'm putting my alias definitions in a comment block at the beginning of the file, as a reference. I honestly can't understand how you guys don't go crazy without it.

如果我编写了其他人必须处理的存储过程，我会将我的别名定义放在文件开头的注释块中，作为参考。老实说，我无法理解没有它你们怎么会发疯。

回答by Krzysztof Sikorski

Good

好的

As it has been mentioned multiple times before, it is a good practice to prefix allcolumn names to easily see which column belongs to which table - and aliases are shorter than full table names so the query is easier to read and thus understand. If you use a good aliasing scheme of course.

正如之前多次提到的那样，为所有列名添加前缀是一个很好的做法，以便轻松查看哪个列属于哪个表 - 别名比完整表名更短，因此查询更易于阅读和理解。当然，如果您使用良好的别名方案。

And if you create or read the code of an application, which uses externally stored or dynamically generated table names, then without aliases it is really hard to tell at the first glance what all those "%s"es or other placeholders stand for. It is not an extreme case, for example many web apps allow to customize the table name prefix at installation time.

如果您创建或阅读使用外部存储或动态生成的表名的应用程序的代码，那么如果没有别名，乍一看真的很难分辨所有这些“%s”或其他占位符代表什么。这不是极端情况，例如许多网络应用程序允许在安装时自定义表名前缀。

回答by Keith

Microsoft SQL's query optimiser benefits from using either fully qualified names or aliases.

Microsoft SQL 的查询优化器受益于使用完全限定名称或别名。

Personally I prefer aliases, and unless I have a lot of tables they tend to be single letter ones.

我个人更喜欢别名，除非我有很多表，否则它们往往是单字母的。

--seems pretty readable to me ;-)
select a.Text
from Question q
    inner join Answer a
        on a.QuestionId = q.QuestionId

There's also a practical limit on how long a Sql string can be executed - aliases make this limit easier to avoid.

对于 Sql 字符串可以执行多长时间，还有一个实际限制 - 别名使这个限制更容易避免。

回答by BTB

If I write a query myself (by typing into the editor and not using a designer) I always use aliases for the table name just so I only have to type the full table name once.

如果我自己编写查询（通过在编辑器中输入而不是使用设计器），我总是使用表名的别名，这样我只需要输入一次完整的表名。

我真的很讨厌阅读设计者生成的查询，并将完整的表名作为每个列名的前缀。

回答by S?ren Kuklau

I suppose the only thing that really speaks against them is excessive abstraction. If you will have a good idea what the alias refers to (good naming helps; 'a', 'b', 'c' can be quite problematic especially when you're reading the statement months or years later), I see nothing wrong with aliasing.

我想唯一真正反对他们的是过度抽象。如果您对别名所指的内容有一个很好的了解（良好的命名有帮助；'a'、'b'、'c' 可能会有很大的问题，尤其是当您在几个月或几年后阅读该声明时），我认为没有任何问题带混叠。

As others have said, joins requirethem if you're using the same table (or view) multiple times, but even outside that situation, an alias can serve to clarify a data source's purpose in a particular context. In the alias's name, try to answer whyyou are accessing particular data, not whatthe data is.

正如其他人所说，如果您多次使用同一个表（或视图），则连接需要它们，但即使在这种情况之外，别名也可以用于在特定上下文中阐明数据源的用途。在别名的名字，试图回答为什么要访问特定的数据，没有什么数据是。

回答by Kirk Harrington

I LOVE aliases!!!! I have done some tests using them vs. not and have seen some processing gains. My guess is the processing gains would be higher when you're dealing with larger datasets and complex nested queries than without. If I'm able to test this, I'll let you know.

我喜欢别名！！！！我已经使用它们做了一些测试，而不是使用它们，并且看到了一些处理增益。我的猜测是，当您处理更大的数据集和复杂的嵌套查询时，处理增益会比不处理时更高。如果我能测试这个，我会告诉你的。

回答by Shawn

You need them if you're going to join a table to itself, or if you use the column again in a subquery...

如果要将表与自身连接，或者在子查询中再次使用该列，则需要它们...

回答by Pulsehead

Aliases are great if you consider that my organization has table names like: SchemaName.DataPointName_SubPoint_Sub-SubPoint_Sub-Sub-SubPoint... My team uses a pretty standard set of abbreviations, so the guesswork is minimized. We'll have say ProgramInformationDataPoint shortened to pidp, and submissions to just sub.

如果您认为我的组织具有以下表名，则别名非常有用： SchemaName.DataPointName_SubPoint_Sub-SubPoint_Sub-Sub-SubPoint... 我的团队使用一组非常标准的缩写，因此可以最大限度地减少猜测。我们会说 ProgramInformationDataPoint 缩短为 pidp，而提交只是 sub。

The good thing is that once you get going in this manner and people agree with it, it makes those HAYUGE files just a little smaller and easier to manage. At least for me, fewer characters to convey the same info seems to go a little easier on my brain.

好消息是，一旦你以这种方式开始并且人们同意它，它就会使那些 HAYUGE 文件更小，更易于管理。至少对我来说，传达相同信息的字符越少，我的大脑似乎就越容易。