MySQL 是否有命名约定? [英] Is there a naming convention for MySQL?

问题描述

这是我的做法:

1. 表名是小写的，使用下划线分隔单词，并且是单数(例如foo、foo_bar等
2. 我通常(并非总是)有一个自动递增的 PK.我使用以下约定:tablename_id(例如 foo_id、foo_bar_id 等).
3. 当一个表包含一个作为外键的列时，我只需从它来自的任何表中复制该键的列名.例如，假设表 foo_bar 具有 FK foo_id(其中 foo_id 是 foo 的 PK).
4. 在定义 FK 以强制执行参照完整性时，我使用以下内容:tablename_fk_columnname(例如进一步示例 3，它将是 foo_bar_foo_id).由于这是一个表名/列名组合，所以它保证在数据库中是唯一的.
5. 我这样对列进行排序:PK、FK，然后按字母顺序排列其余列

1. Table names are lower case, uses underscores to separate words, and are singular (e.g. foo, foo_bar, etc.
2. I generally (not always) have a auto increment PK. I use the following convention: tablename_id (e.g. foo_id, foo_bar_id, etc.).
3. When a table contains a column that is a foreign key, I just copy the column name of that key from whatever table it came from. For example, say table foo_bar has the FK foo_id (where foo_id is the PK of foo).
4. When defining FKs to enforce referential integrity, I use the following: tablename_fk_columnname (e.g. furthering example 3, it would be foo_bar_foo_id). Since this is a table name/column name combination, it is guaranteed to be unique within the database.
5. I order the columns like this: PKs, FKs, then the rest of columns alphabetically

有没有更好、更标准的方法来做到这一点?

Is there a better, more standard way to do this?

推荐答案

我要说的第一点是:保持一致.

I would say that first and foremost: be consistent.

我认为您已经了解了您在问题中概述的约定.不过有几点意见:

I reckon you are almost there with the conventions that you have outlined in your question. A couple of comments though:

我认为第 1 点和第 2 点很好.

Points 1 and 2 are good I reckon.

第 3 点 - 遗憾的是这并不总是可能的.想一想您将如何处理具有列 foo_id 和 another_foo_id 的单个表 foo_bar，它们都引用了 foo> 表 foo_id 列.您可能需要考虑如何处理此问题.不过，这有点极端！

Point 3 - sadly this is not always possible. Think about how you would cope with a single table foo_bar that has columns foo_id and another_foo_id both of which reference the foo table foo_id column. You might want to consider how to deal with this. This is a bit of a corner case though!

第 4 点 - 与第 3 点类似.您可能需要在外键名称的末尾引入一个数字，以适应有多个引用列.

Point 4 - Similar to Point 3. You may want to introduce a number at the end of the foreign key name to cater for having more than one referencing column.

第 5 点 - 我会避免这种情况.当您想在以后从表中添加或删除列时，它为您提供的帮助很少，并且会变得很头疼.

Point 5 - I would avoid this. It provides you with little and will become a headache when you want to add or remove columns from a table at a later date.

其他一些要点是:

索引命名约定

您可能希望为索引引入命名约定——这对您可能想要执行的任何数据库元数据工作都有很大帮助.例如，您可能只想调用索引 foo_bar_idx1 或 foo_idx1 - 完全取决于您，但值得考虑.

You may wish to introduce a naming convention for indexes - this will be a great help for any database metadata work that you might want to carry out. For example you might just want to call an index foo_bar_idx1 or foo_idx1 - totally up to you but worth considering.

单列名与复数列名

解决列名和表名中复数与单数的棘手问题可能是个好主意.这个主题经常在数据库社区中引起大辩论.对于表名和列，我会坚持使用单数形式.那里.我说过了

It might be a good idea to address the thorny issue of plural vs single in your column names as well as your table name(s). This subject often causes big debates in the DB community. I would stick with singular forms for both table names and columns. There. I've said it.

这里最重要的当然是一致性！

The main thing here is of course consistency!

这篇关于MySQL 是否有命名约定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！