Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 509
Kevin Jin Avatar asked Sep 03 '12 20:09

Kevin Jin


People also ask

Are MySQL columns nullable by default?

If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows: If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.

Are columns NULL by default?

By default, a column holds NULL values. In the example, we use the NOT NULL constraint that enforces a column not to accept NULL values.

Should all columns be not null?

You must therefore use NOT NULL for all columns that cannot legitimately contain nulls. If you specify that a column is NOT NULL , you are defining a constraint that ensures that that the column can never hold or accept NULL , so you can't accidentally leave the value out.

How do you find which columns have NULL values in MySQL?

To search for column values that are NULL , you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true for any expression: mysql> SELECT * FROM my_table WHERE phone = NULL; To look for NULL values, you must use the IS NULL test.


1 Answers

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.

like image 197
eggyal Avatar answered Sep 28 '22 22:09

eggyal