Search code examples
mysqlnulldefaultcreate-table

MySQL: COLUMN_DEFAULT is always NULL for non null default values

CREATE TABLE `test` (
`id` INT(10) UNSIGNED NOT NULL,
`c1` VARCHAR(60) NULL DEFAULT NULL,
`c2` VARCHAR(60) NOT NULL,
PRIMARY KEY (`id`)
 )

In the information_schema.columns.COLUMN_DEFAULT contains NULL for both c1,c2 columns but c2 doesn't have default value.

How to get the real COLUMN_DEFAULT value?

Solution

  • In information_schema.columns, if COLUMN_DEFAULT is NULL and IS_NULLABLE is NO then it doesn't have a default.

    But basically you can see it as: the default, if not specified, is always NULL. So, c2 isn't allowed to be NULL, but it will default to NULL, thus will always need to be specified on insert statements.

    Note: a field specified as NOT NULL doesn't need to be able to represent NULL, thus it can theoretically take up (slightly) less space.