NULL vs DEFAULT NULL vs NULL DEFAULT NULL in MYSQL column creation?

Question

Following SQL table definition is illustrated one of create table statement from my MYSQL database which is developed by a former developer of my company.

DROP TABLE IF EXISTS `classifieds`.`category_vehicles`;
CREATE TABLE `classifieds`.`category_vehicles`(
`adv_id_ref` BIGINT UNSIGNED NOT NULL,
`category_id_ref` TINYINT UNSIGNED NOT NULL,
`forsale_status` TINYINT (1) NOT NULL,
`vehicle_type_id_ref` TINYINT UNSIGNED NOT NULL,
`price` DOUBLE NULL DEFAULT NULL,
 PRIMARY KEY (`adv_id_ref`)
) ENGINE = INNODB CHARSET = latin1 COLLATE = latin1_swedish_ci ;

In there look at the statement price DOUBLE NULL DEFAULT NULL,

Normally I'm using:

price DOUBLE NULL;

if I want to enable that column to accept NULL values.

So what are the differences between these 3 statements?

1. price DOUBLE NULL;

2. price DOUBLE DEFAULT NULL;

3. price DOUBLE NULL DEFAULT NULL;

Answer 1

There is no difference. NULL DEFAULT NULL is the implicit default.

From the CREATE TABLE documentation:

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

From the "Data Type Default Values" chapter:

• If a column definition 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.

Answer 2

In all three of the following cases:

price DOUBLE NULL;

price DOUBLE DEFAULT NULL;

price DOUBLE NULL DEFAULT NULL;

price is a double and can be null and its default value is null.