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?

price DOUBLE NULL;
price DOUBLE DEFAULT NULL;
price DOUBLE NULL DEFAULT NULL;

You forgot one possibility... what about price DOUBLE DEFAULT NULL NOT NULL... i shit you not, i actually have a database defined like that :-) — John Henckel
– John Henckel, Commented Apr 4, 2019 at 19:06

user330315 · Accepted Answer · 2014-09-22 06:59:18Z

44

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.

answered Sep 22, 2014 at 6:59

user330315

Sign up to request clarification or add additional context in comments.

Comments

user207421 · Accepted Answer · 2024-01-30 02:06:09Z

21

price DOUBLE NULL;

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

price DOUBLE DEFAULT NULL;

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

price DOUBLE NULL DEFAULT NULL;

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

edited Jan 30, 2024 at 2:06

answered Sep 22, 2014 at 6:59

user207421

312k45 gold badges324 silver badges493 bronze badges

5 Comments

user330315 Over a year ago

"price is a double and cannot be null" for the second expression is wrong. If NULL is omitted, NULL is assumed (see my quote from the manual)

Prageeth Liyanage Over a year ago

@EJP to me seems like every statements are equal. I still cant extract this. :)

user207421 Over a year ago

@Sylar They are equal.

user207421 Over a year ago

@JanacMeena 'Looking for differences when there are none' is the whole point of the answer. That's why I wrote it that way. Edit reverted.

Janac Meena Over a year ago

I edited since other users also were confused by this. Up to you if you want to keep this less readable

Abdulla Nilam · Accepted Answer · 2022-12-19 05:29:52Z

0

price DOUBLE NULL;

Allows the price column to accept NULL.

price DOUBLE DEFAULT NULL;

Allows the price column to accept NULL and sets the default value to NULL.

price DOUBLE NULL DEFAULT NULL;

Act the same as the second statement, as it accepts NULL values and sets the default.

answered Dec 19, 2022 at 5:29

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related