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.
例如，第一个设置空标志(与非空相反)，第二个设置空标志为默认值，第三个设置空标志并将隐式值设置为空。
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中，与非空列不同，具有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脚本中，对于每个非空列，我都为应用程序中的一些插入指定默认值，以避免在启用严格模式时出现任何问题。因此，我觉得如果我选择第三种变异，它会更对称，即使它是最冗长、最明显的。像第三个变体这样的声明是常见的，还是第一个或第二个变体在其他人的脚本中出现得更频繁?
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 dump所使用的别名，但我不确定这是不是一个好主意，因为它还在列声明的默认子句中以字符串(单引号)的形式转储整数文本。
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那样区分这些情况的话。如果我做了错误的假设，请纠正我。
2 个解决方案
#1
9
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.
我所遇到的每个数据库都以你描述它们的方式为零。当列接受空值时，则在插入时不提供值默认为空。我相信这是ANSI标准行为的一部分。
As for specifying "NULL" itself. That is a matter of preference. The standard does say that a column allows NULLs unless NOT 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。零本身是不必要的，你有第四个，等价的选项:
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。对于所有采用默认值的列，都使用默认语句。
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方言的行为方式。
#2
17
As documented under Data Type Default Values:
根据数据类型默认值记录:
If the column can take
NULLas a value, the column is defined with an explicitDEFAULT NULLclause.如果列可以将NULL作为值，则使用显式的默认NULL子句定义列。
(I think they meant implicit, not explicit).
(我认为他们指的是含蓄的，而不是明确的)。
Moreover, as documented under CREATE TABLE Syntax:
此外，如CREATE TABLE语法所示:
If neither
NULLnorNOT NULLis specified, the column is treated as thoughNULLhad 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.
选择使用哪一个是显式和简洁之间的平衡。根据具体情况，我可以使用上述任何一种。
#1
9
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.
我所遇到的每个数据库都以你描述它们的方式为零。当列接受空值时，则在插入时不提供值默认为空。我相信这是ANSI标准行为的一部分。
As for specifying "NULL" itself. That is a matter of preference. The standard does say that a column allows NULLs unless NOT 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。零本身是不必要的，你有第四个，等价的选项:
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。对于所有采用默认值的列，都使用默认语句。
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方言的行为方式。
#2
17
As documented under Data Type Default Values:
根据数据类型默认值记录:
If the column can take
NULLas a value, the column is defined with an explicitDEFAULT NULLclause.如果列可以将NULL作为值，则使用显式的默认NULL子句定义列。
(I think they meant implicit, not explicit).
(我认为他们指的是含蓄的，而不是明确的)。
Moreover, as documented under CREATE TABLE Syntax:
此外，如CREATE TABLE语法所示:
If neither
NULLnorNOT NULLis specified, the column is treated as thoughNULLhad 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.
选择使用哪一个是显式和简洁之间的平衡。根据具体情况，我可以使用上述任何一种。