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屋！