为什么不应该在sql字段名称中包含逗号？ [英] Why shouldn't commas be included in sql field names?

问题描述

人们不断告诉我，列名中不应包含空格。我只是想知道，为什么呢？我为学校创建的一些数据库表存在这个问题。字段名称包括Preble和Darke。

People keep telling me that spaces shouldn't be included in column names. I was just wondering, why is that? It is an issue I am having with a few database tables I am creating for school. The field names include Preble and Darke.

相反，它们必须是 Preble County（OH）和 Darke County（OH）。如果它们是行名，我只需创建一个ID＃列，然后自然地将其与一个表连接起来，该表将显示我想要它们的名字（ Darke County（OH）而不是 Darke）。

Instead, they need to be "Preble County (OH)" and "Darke County (OH)". If they were row names, I would just create an ID # column, and natural join that with a table displaying names that I want them to be ("Darke County (OH)" instead of "Darke").

但是，我不知道如何更改这些名称，因为它们是我的字段名称。谁能帮我吗？任何帮助将不胜感激。

However, I have no idea how I can go about changing these names since they are my field names. Can anyone help me out? Any help would be greatly appreciated.

推荐答案

tldr; 尝试包含逗号（或其他特殊字符）通常表明列名称为 ² 的根本缺陷。

tldr; Trying to included commas (or other special characters) generally indicates a fundamental flaw with the column name².

这是一个坏数据库设计。这是 bad ，因为它试图将信息编码为列名 ¹ 。这不是列名的重点！列名仅仅是记录/元组元素的友好绰号-仅此而已。在关系代数（RA）以及SQL之下，包含信息的是记录/元组。

This is a bad database design. It is bad because it tries to encode information into a column name¹. This is not the point of a column name! A column name is merely a friendly moniker for an element of a record/tuple - nothing more. Under Relational Algebra (RA), and thus SQL, it is the record/tuple that contains the information.

此外，这还导致了难以处理的模式（需要使用基于变化的信息（列名和多重性）进行硬编码查询的额外引号语法），因此也无法灵活地与多种RA技术一起使用。 RA通常只能处理跨记录的多重性-并且发现时包括连接。

Besides just leading to a schema that is hard to deal with (extra quoting syntax) that requires hard-coding queries based on changing information (column names and multiplicity), it is also impossible to use with a number of RA techniques in a flexible manner. RA can only generally handle multiplicity across records - and, as discovered, this includes joins.

相反，该架构应类似于：

Instead, the schema should look similar to, say:

County    State   Other Columns
=======   =====   =============
Darke     OH      ..
Prebel    OH      ..

其中的键是（州，县）和其他列取决于键。当然，应该相对于捕获的所有其他信息正确地对模型进行归一化。

Where the Key is, say, (State,County) and "Other Columns" are dependent upon the key. Of course the model should be correctly normalized in relation to all the other information that is captured.

请注意，上述列名称中没有存储任何信息：名称仅仅是表示每一列中存储的信息的友好绰号。

Note that there is no information stored in the column names presented above: the names are merely friendly monikers representing the information stored in each column.

¹ 现在a PIVOT转换 需要。这是每组记录值生成的输出表（而不是模式表！）中的一列。但是，这是次要问题，不应影响主模式。

¹ Now a PIVOT transformation, which is primarily for human output/display, can be performed as needed. This is were a column in the output table (not schema table!) is generated per set of record values. However, this is a secondary issue and should not affect the primary schema.

如果使用SQL Server，则可以先取消信息填充的列名，然后执行联接和然后重新设置PIVOT（给定一组已知的列名）。但是，我不知道如何在MySQL中完成此操作-也许是一个混乱的动态查询。无论如何，这是我会避免的方法。

If using SQL Server, one could first UNPIVOT the information-filled column names, perform the join and then re-PIVOT (given a known set of column names). However, I have no idea how this would be done in MySQL - a messy dynamic query, perhaps. In any case, this is an approach I would avoid.

² 不允许在裸标识符中使用，而在引用的标识符中使用时可以使用这样的名称-例如华盛顿州温哥华市，或带有 ANSI引号，华盛顿州温哥华。但是，请继续阅读本响应的其余部分，这些内容都反对使用此类标识符。

² While special characters are not allowed in bare identifiers, it is possible to use such names when used in quoted identifiers - e.g. `Vancouver, WA` or, with ANSI quotes, "Vancouver, WA". However, keep reading the rest of this response which argues against using such identifiers.

这篇关于为什么不应该在sql字段名称中包含逗号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！