带有 DEFAULT NULL 的 MySQL 列 - 风格选择，还是它？

MySQL columns with DEFAULT NULL - stylistic choice, or is it?

提问by Kevin Jin

In many flavors of SQL, there are three ways you can implicitly set a column to NULL on every row insertion. These are:

在许多 SQL 风格中，您可以通过三种方式在每次插入行时将列隐式设置为 NULL。这些是：

columnname type NULL
columnname type DEFAULT NULL
columnname type NULL DEFAULT NULL

I.e. the first one sets the NULL flag (as opposed to NOT NULL), second one leaves the NULL flag to the default value, and the third one sets the NULL flag and sets the implicit value as NULL.

即第一个设置 NULL 标志（与 NOT NULL 相对），第二个将 NULL 标志保留为默认值，第三个设置 NULL 标志并将隐式值设置为 NULL。

I've heard about how in Microsoft SQL, the second variation is not exactly the same since you can also customize the default value of the NULL flag for tables or schemas.

我听说在 Microsoft SQL 中，第二个变体并不完全相同，因为您还可以为表或模式自定义 NULL 标志的默认值。

But for MySQL, I don't believe there is such a feature. In addition, in MySQL, columns with the NULL flag, unlike NOT NULL columns, always are implicitly set to NULL on insert if there is no explicit value, even if strict mode is turned on. So that leaves all these column declarations identical.

但是对于 MySQL，我不相信有这样的功能。此外，在 MySQL 中，与 NOT NULL 列不同，带有 NULL 标志的列在插入时总是隐式设置为 NULL，如果没有显式值，即使打开了严格模式。因此，所有这些列声明都相同。

In my MySQL script, for every NOT NULL column, I specify a DEFAULT value for columns I don't expect to set in some insertions in my application, to avoid any issues if I were to enable strict mode. Thus, I feel it would be more symmetrical if I were to choose the third variation, even though it is the most verbose and explicit. Are declarations like the third variation common, or does the first or second variation occur more frequently in others' scripts?

在我的 MySQL 脚本中，对于每个 NOT NULL 列，我为我不希望在我的应用程序的某些插入中设置的列指定一个 DEFAULT 值，以避免在启用严格模式时出现任何问题。因此，我觉得如果我选择第三个变体会更对称，即使它是最冗长和明确的。像第三个变体这样的声明是否常见，或者第一个或第二个变体在其他人的脚本中是否更频繁地出现？

I was considering leaning to the second approach because that's the alias that MySQL dump uses, but I'm not sure that's such a good idea since it also dumps integer literals as strings (single quotes) in default clauses of column declarations.

我正在考虑倾向于第二种方法，因为这是 MySQL 转储使用的别名，但我不确定这是一个好主意，因为它还将整数文字转储为列声明的默认子句中的字符串（单引号）。

This question may appear to be more opinion than one that has a solution, but I also want to be aware of any potential gotchas that I am not aware of. I may choose to migrate from MySQL to PostgreSQL in the future, and I also could use some advice from those experts, e.g. if PostgreSQL distinguishes these cases like MS-SQL does. Correct me if I am wrong if I made any incorrect assumptions.

这个问题似乎比一个有解决方案的问题更具意见，但我也想知道我不知道的任何潜在问题。将来我可能会选择从 MySQL 迁移到 PostgreSQL，我也可以参考那些专家的一些建议，例如 PostgreSQL 是否像 MS-SQL 那样区分这些情况。如果我做出了任何不正确的假设，请纠正我。

采纳答案by Gordon Linoff

Every database that I've encountered treats NULLs the way that you describe them. When a column accepts NULL values, then not providing a value on INSERT defaults the value to NULL. I believe this is part of the ANSI standard behavior.

我遇到的每个数据库都按照您描述它们的方式处理 NULL。当列接受 NULL 值时，在 INSERT 上不提供值会将值默认为 NULL。我相信这是 ANSI 标准行为的一部分。

As for specifying "NULL" itself. That is a matter of preference. The standard does say that a column allows NULLs unlessNOT NULL is specified (at the level of the data definition language). So, the NULL itself is unnecesary, and you have a fourth, equivalent option:

至于指定“NULL”本身。这是一个偏好问题。标准确实说，除非指定了 NOT NULL（在数据定义语言级别），否则列允许 NULL 。因此，NULL 本身是不必要的，您还有第四个等效选项：

columnname type

NULLs are quite embedded in the SQL language through the ANSI standard. Your third option is the most explicit, but it also looks a bit unusual.

NULL 通过 ANSI 标准完全嵌入到 SQL 语言中。你的第三个选项是最明确的，但它看起来也有点不寻常。

If you are planning on being super-consistent throughout your system, then I would take the path you are on. For every column in every table have NULL or NOT NULL. For all columns that take default values, have the DEFAULT statement.

如果您打算在整个系统中保持超级一致，那么我会选择您所走的道路。对于每个表中的每一列都有 NULL 或 NOT NULL。对于所有采用默认值的列，都有 DEFAULT 语句。

However, don't expect other people you work with (now or in the future) to readily follow this example. Many would prefer the fourth option in this case simply because it requires less typing and is how all (or almost all) SQL dialects behave.

但是，不要指望与您一起工作的其他人（现在或将来）会很容易地遵循此示例。在这种情况下，许多人更喜欢第四个选项，因为它需要较少的输入并且是所有（或几乎所有）SQL 方言的行为方式。

回答by eggyal

As documented under Data Type Default Values:

如数据类型默认值下所述：

If the column can take NULLas a value, the column is defined with an explicit DEFAULT NULLclause.

如果该列可以NULL作为一个值，则该列是用显式DEFAULT NULL子句定义的。

(I think they meant implicit, not explicit).

（我认为他们的意思是隐含的，而不是明确的）。

Moreover, as documented under CREATE TABLESyntax:

此外，如CREATE TABLESyntax下所述：

If neither NULLnor NOT NULLis specified, the column is treated as though NULLhad been specified.

如果既未指定也NULL未NOT NULL指定，则将该列视为NULL已指定。

Therefore, in MySQL the following column definitions are all identical:

因此，在 MySQL 中，以下列定义都是相同的：

columnname type
columnname type NULL
columnname type DEFAULT NULL
columnname type NULL DEFAULT NULL

The choice of which to use is a balance between being explicit, and being concise. Depending on the circumstances, I might use any of the above.

选择使用哪个是明确和简洁之间的平衡。根据情况，我可能会使用上述任何一种。