创建表时在 SQL 中对列进行排序的最佳实践
我有一个包含以下列的表 foo
create table foo (
id integer not null auto_increment unique,
name varchar(255),
desc varchar(255),
modified_time datetime not null,
type tinyint(1)
)
是否有遵循所有列的排序约定的最佳实践（例如，按字母顺序排列，或者顶部不为空，底部的其余列）
I have a table foo which contains the following columns
create table foo (
id integer not null auto_increment unique,
name varchar(255),
desc varchar(255),
modified_time datetime not null,
type tinyint(1)
)
Are there any best practices in following a ordering convention for all the columns (e.g. in a alphabetical fashion or not nulls at the top and rest of the columns at the bottom)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
当然是主键首先。
该列的名称通常是tablename_id（传统）或只是“id”（rails 等框架首选）。
如果使用的话，通常接下来是名称/描述字段，正如您所拥有的那样。
我倾向于将外键放在后面（先是父母，然后是孩子），因为它们在开发过程中往往更加关键。
然后我将其他数据分组，例如地址行、城市、州、邮政编码。
当没有其他规则适合时，我倾向于选择较高的必填字段以提高可见性。
时间戳（部分/全部created_on、updated_on、removed_on等）通常是last
所以在你的例子中我实际上会这样做：
注意 - 正如Kolink所指出的，使用description而不是desc因为 desc 是一个保留字，意思是降序，例如
order desc如果您的表随着时间的推移（即现实世界）而变化并且您拥有现有的生产数据，则您将不会按照最初“计划”的方式对字段进行排序。这可以通过导出和重新导入来避免，但在许多情况下，最好接受排序只是为了程序员方便而进行的初始创建的约定，仅此而已。
这里另一个流行的主题是列名命名约定。这是一个完全不同的话题，但我会提醒大家，除非被迫，否则不要缩写！
Certainly the primary key first.
The name of that column is usually tablename_id (traditional) or just 'id' (preferred by frameworks such as rails).
If used, usually a name/description field next, as you have it.
I tend to put foreign keys after that (parents first, children after that) as they tend to be more critical during development.
Then I group other data, e.g. address line, city, state, zip together.
When no other rule fits, I tend to prefer required fields higher up for increased visibility.
timestamps (some/all of created_on, updated_on, removed_on, etc.) usually last
So in your example I would actually do:
Note - As Kolink noted, use description over desc because desc is a reserved word meaning descending, e.g.
order descif your table changes over time (i.e. real world) and you have existing production data, you will not have the fields ordered as initially 'planned'. This can be avoided by export and re-import but in many cases its best to accept that ordering is just a convention for initial creation for programmer convenience and that alone.
Another popular topic here is column name naming conventions. That's a whole 'nother topic but I'll tip my toe in by saying don't abbreviate unless forced!
不仅没有真正的约定，而且最好不要去考虑。例如，有时，当假设您要更新每个字段时，插入查询不会指定字段名称。但随后您必须按照假定的顺序指定值。这很容易出错。因此，最好的思考方式是，“这些列出现在这个表中，没有特定的顺序。”
Not only is there no real convention, but it's best not to think about one. Sometimes insert queries, for example, will not specify field names when it's assumed that you're going to update every field. But then you have to specify values in an assumed order. This is error-prone. So the best way to think about it is, "these columns are present in this table, in no particular order."
不，不是真的。它很灵活，因此您可以按照您的意愿对列进行排序。
我认为主要约定是确保将
primary_key作为表中的第一列。No not really. It's flexible so that order your columns however you wish.
I suppose the main convention is to ensure that you have your
primary_keyas the first column in your table.我个人从未听说过或见过这样的惯例。用于数据库列的唯一约定通常是命名约定（每个组织之间的命名约定通常不同）和数据类型映射。
然而约定就是它们的本质。因此，如果您觉得按字母顺序对列进行排序是正确的，请这样做。
I have personaly never heard or seen such conventions. The only conventions used for database columns are usually naming convention (which is often different from one organisation to another) and data type mapping.
However conventions are simply what they are. So if it feels right to you to sort your columns alphabetically do so.
我想说，这并不重要，而是品味问题。
您希望确保索引中使用的列顺序与频繁查询中使用的列顺序相同，但仅此而已。
It does not really matter and is a question of taste, I'd say.
You want to make sure that you are using the same order of columns in your indexes as you use in frequent queries, but that's all.
据我所知没有。我个人更喜欢将主键（通常是代理键）放在前面，我认为每个表都应该在前面放置主键，然后是名称/描述（用户的用户名，产品的产品名称），如果有任何相关的、唯一的描述性名称字段。然后是逻辑父级的外键（订单行的 orderid）（如果有）。之后，我以最适合我的方式对它们进行排序，通常将相关字段分组（lastEditDate 和 lastEditedBy 彼此相邻）。我认为没有充分的理由按字母顺序对列进行排序。
To my knowledge there is not. I personally prefer putting the primary (often surrogate) key, which I think every table should have, in front, followed by a name/description (username for users, productname for products), if there is any relevant, unique descriptive name field. Then a foreign key to a logical parent (orderid for orderlines) if there is any. After that, I order them in whichever way suits me best, often grouping related fields (lastEditDate and lastEditedBy next to each other). I see no good reason for alphabetically ordering columns.