带有DEFAULT NULL的MySQL列-风格选择，还是它? [英] MySQL columns with DEFAULT NULL - stylistic choice, or is it?

问题描述

在许多类型的SQL中，您可以通过三种方式在每次插入行时将列隐式设置为NULL.这些是:

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

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

即第一个设置NULL标志(而不是NOT NULL)，第二个将NULL标志保留为默认值，第三个设置NULL标志并将隐式值设置为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.

我听说过在Microsoft SQL中，第二种变化并不完全相同，因为您还可以为表或模式自定义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.

但是对于MySQL，我不相信有这样的功能.另外，在MySQL中，即使没有启用严格模式，在没有显式值的情况下，带有NULL标志的列也不同于NOT NULL列，在插入时始终将其隐式设置为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脚本中，对于每个NOT NOT NULL列，我为我不希望在应用程序的某些插入中设置的列指定DEFAULT值，以避免在启用严格模式时出现任何问题.因此，我觉得如果选择第三个变体会更对称，即使它是最冗长和明确的.是像第三个变体这样的声明是通用的，还是第一个或第二个变体在其他人的脚本中出现的频率更高?

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迁移到PostgreSQL，也可以使用这些专家的一些建议，例如如果PostgreSQL可以像MS-SQL一样区分这些情况.如果我做错了任何假设，请改正我.

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.

推荐答案

我遇到的每个数据库都以描述它们的方式对待NULL.当列接受NULL值时，如果不在INSERT上提供值，则该值默认为NULL.我相信这是ANSI标准行为的一部分.

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，除非指定了 NOT NULL(在数据定义语言级别).因此，NULL本身是不必要的，并且您有第四个等效的选项:

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:

columnname type

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

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或NOT NULL.对于所有采用默认值的列，请使用DEFAULT语句.

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.

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

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.

这篇关于带有DEFAULT NULL的MySQL列-风格选择，还是它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！