表字段命名规范和SQL语句

Question

我有一个关于数据库表字段命名的实际问题。例如，我有两个表：

student (id int; name varchar(30))
teacher (id int, s_id int; name varchar(30))

两个表中都有'id'和"name"。在SQL语句中，如果没有表名前缀，则会对这两个字段产生歧义。有两种选择：

1. 在SQL 'where'子句中使用表名作为字段的前缀
2. 在表中使用带前缀的字段名称，以便在'where'子句中不使用前缀。

哪种方法更好？

- marlon

Answer 1

毫无疑问，选择选项1。这是任何类型数据库中有效的SQL语句，并被认为是正确且最易读的格式。在列名前加上表名是一个好习惯，在进行连接时非常必要。我经常见到的唯一例外是在id列前加上表名，但我仍然不会这样做。
如果选择选项2，经验丰富的数据库管理员可能会指责你。
进一步证明，请参见此处的#2：https://www.periscopedata.com/blog/better-sql-schema.html
以及此处。规则1b-http://www.isbe.net/ILDS/pdf/SQL_server_standards.pdf
正如TT所提到的，如果您学会使用别名来表示表名，将会使您的生活变得更容易。只需在查询中使用SomeTableNameThatsWayTooLong as long_table即可。

SELECT LT.Id FROM SomeTableNameThatsWayTooLong AS LT

- Ageonix

---

那么，在这种情况下，我不应该在字段名中使用“student_ID”、“teach_ID”、“student_name”和“teacher_name”，而是在两个表中都只使用“id”和“name”。对吗？ - marlon

一个名为“student_id”的列会导致哪个数据库出现无效的SQL？ - Bampfer

我并没有说这会导致无效的SQL。我说只要你在列名前加上表名，使用'id'或'name'总是有效的。给每个列都命名为student_id、student_name、student_dorm、student_major等会让你的开发人员疯掉，而且是不必要的。 - Ageonix

这真的是一个偏好问题，@martin，但我可以告诉你，在专业环境中，我见过大约25个以上的数据库，我认为只有一次看到表名前缀每个列。就像我之前提到的，唯一的例外是id列，但那只是为了在连接时更易读。我个人从未觉得阅读student.id或professor.id很困难。 - Ageonix

+1. 我遵循在查询中限定所有列引用的规则。（我的个人偏好是使用短表别名，而不是完整的表名。）我也更喜欢将列名id用于表中的唯一键。当它是另一个表中引用student表中的id列的外键列的名称时，将使用列名student_id。 - spencer7593

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Bampfer

Answer 2

对于非临时查询，您应该始终在每个字段前加上表名或表别名的前缀，即使字段名不含歧义。这可以防止如果有人向其中一个表添加新列导致歧义后查询出现问题。

这样，“id”和“name”就不会产生歧义了。但我仍然建议使用比“id”更具体的名称来命名主键。在您的示例中，我将使用student_id和teacher_id。这有助于防止连接时出现错误。当您遇到具有多个唯一键或多部分键的表时，您将需要更具体的名称。

值得思考这些问题，但最终一致性可能是更重要的因素。我可以处理围绕id而不是student_id构建的表，但我目前正在使用不一致的架构，其中使用了以下所有内容：id、sid、systemid以及像taskid这样的特定名称。那是最糟糕的情况。

- Bampfer

---

是的，在查询中始终要限定所有列引用。这可以防止在表中添加列时（引入模糊的列引用错误）导致查询在未来出现问题。而且，这使得后来的读者更容易解密SQL...那个未经限定的引用foobar列...是对fee表、fi表还是fo表中的列的引用？（你可别让我去查找表定义以找出它所指的表。）但我的个人偏好是在主键（或唯一键）处将列命名为id。 - spencer7593

Answer 3

我会使用别名而不是表名。

在查询中，您可以为表分配一个别名，它比表名要短。这使得查询更易读。例如：

SELECT 
    t.name AS teacher_name,
    s.name AS student_name 
FROM 
    teacher AS t 
    INNER JOIN student AS s ON 
        s.id=t.s_id;

当然，如果您不使用别名，您可以使用表名称，这比您的第二个选项更好。

- TT.

---

个人而言，我更喜欢按照 referenced_table_id 的模式来命名外键列。例如，使用 student_id 而不是 s_id。除此之外，这就是我遵循的模式。 - spencer7593

提到别名是个好主意。我们的数据库管理员使用非常长的名称，每次在每个列之前键入StudentClassListDetails变得很繁琐。语法取决于数据库类型，但我认为大多数都像“StudentClassListDetails as scld”一样简单。 - Ageonix

@spencer7593 很好的观点，清晰的名称对于避免大型数据库中存在许多相关表时的混淆非常重要。 - TT.

每个实体表中都有一个id列，作为主键或唯一键的一致性，这对开发人员来说是一个好处...它允许按照“约定”编码，而不是使该列名称成为必须具有特殊代码的“异常”。当没有这种模式时，我看到的SQL语句是 ON fee.student_id = fo.student_id ... 这是外键到主键的连接吗？哪个表是主键？还是这是外键到外键的快捷连接？或者，SQL是错误的吗？我发现将id列作为PK使得SQL更容易理解，并且使错误的SQL“看起来”就是错误的。 - spencer7593

Answer 4

如果不太长的话，我更喜欢在表格本身中加上前缀，例如teacher.teacher_id，student.student_name。这样，即使您忘记为表格名称添加前缀，您也始终可以确定正在讨论哪个名称或ID。

- grambo25

---

在现代的代码优先应用程序中，Id（作为GUID）是您数据库中每个表的首选主键。它具有通用的好处、可读性的好处，而且没有真正的现实世界的缺点。即使这个平台的近亲也使用它：https://data.stackexchange.com/stackoverflow/query/new - Ryan Naccarato