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

Question

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

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.

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.

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?

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.

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.

I'm not sure if you're aware, but in MySQL you can set a default value (that isn't null) for a nullable column. If you fail to specify a value for that column when inserting, the column has its default value. You can also explicitly specify that null is to be inserted, in which case the column is null. For example, you can specify a column as `SMALLINT DEFAULT 3`. Then if you insert a non-null integer value explicitly, it stores that value; if you insert null explicitly, it stores null; and if you do not specify a value when inserting, it stores 3, the default value. — Hammerite, Sep 03 '12 at 20:15
yep! I'm only worrying about columns that have a default value of null though. — Kevin Jin, Sep 03 '12 at 20:23
DataGrip, a SQL IDE, provides auto-completion of SQL statements as you type. It offers an "all columns" version, and a "minimum columns" version for auto-complete. In the version I am using, a column that can contain null but does not have an explicit default IS included in the "minimal columns" version, even though the column can be omitted. For this reason, I prefer to be explicit; "NULL DEFAULT NULL". — allenru, Apr 27 '20 at 00:31

score 40 · Answer 1 · answered Sep 03 '12 at 20:21

As documented under Data Type Default Values:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

(I think they meant implicit, not explicit).

Moreover, as documented under CREATE TABLE Syntax:

If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.

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

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.

Note that there is an edge case with the `TIMESTAMP` type. If your column is declared as `stamp TIMESTAMP`, and you do an INSERT w/o specifying a value for `stamp`, it will default to INSERTing with the value of the current UTC datetime. If you declare the column as `stamp TIMESTAMP NULL`, then INSERTs will default to NULL. — sam-6174, Jun 01 '17 at 20:35

score 18 · Accepted Answer · answered Sep 03 '12 at 20:21

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.

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

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.

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.

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.

Yeah. Almost nobody writes the explicit `SELECT ALL` but use the implicit (and equivalent) `SELECT`. — ypercubeᵀᴹ, Sep 03 '12 at 21:16
although eggyal had great points (and a reference to the MySQL documentation), your info made it clear that it was a SQL standard rather than just a MySQL one. As I am confident that I will not have to revisit this particular part of the column definitions if I were to change databases systems, and most should know about the implicit modifiers, I will stick with the most concise alias that you have given. Thank you! — Kevin Jin, Sep 04 '12 at 18:02

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

2 Answers2

Linked