What is the Meaning of "DEFAULT" in Mysql Datetime? -

in following mysql command seeing keyword default

create table user (     id int(11) not null auto_increment,     created_at datetime default null ) 

my question :

1. why there default keyword if allowing datetime null
2. is datetime syntax, should in default format.

please explain me. found documentation page default in mysql. not understanding it.

[n.b.: pardon me, if beginner question, or asked. did not find looking for.]

1. why there default keyword if allowing datetime null

you mistake null after default null allows column contain null values. read below difference.

2. is datetime syntax, should in default format.

the default keyword in create table statement doesn't tell format. specifies default value used column when insert statement doesn't provide value it.

the complete definition of table column in create table statement contain following pieces, in order:

• field name;
• field type;
• null or not null - null values allowed stored in field? if neither specified, null assumed;
• default default value - value used field when insert statement doesn't provide value it; null can used default value if column nullable; if it's not specified, mysql uses rules compute default value based on field type;
• auto_increment - when value not provided column, mysql uses biggest value existing in column plus one; can used integer , float columns;
• one of unique, unique key, key, primary key - first 2 equivalent , specify column unique index of table (it cannot contain same value 2 or more rows); last 2 specify column identifier of row (it unique index , cannot contain null values); these attributes can specified here compatibility other database systems; on mysql 1 specifies table indexes after columns using different syntax;
• comment string - comment column; not used mysql can useful developers (to specify represents column);
• other, less used, options.

only first 2 pieces list above (the name , type) required; others optional. if present, must provided in order of list.

your statement:

create table user (     id int(11) not null auto_increment,     created_at datetime default null ) 

the column id specifies not null, skips default , specifies auto_increment. without auto_increment, mysql uses 0 default value (this default value integer types). because of auto_increment, when value not provided in insert statement, mysql finds largest value stored in column, increases 1 , uses computed value column.

the column created_at doesn't specify if allows null values (it allows them, default) , specifies default value column null.

examples

let's see how works:

insert user(id, created_at) values (5, '2016-06-01 11:22:33') 

inserts new row having values provided in insert statement; no surprise here.

insert user(created_at) values ('2016-06-02 12:34:56') 

inserts new row id = 6, created_at = '2016-06-02 12:34:56'. because value not provided column id, auto_increment option generated 6 (the successor of larger value in column).

the same happens when null provided auto_increment column:

insert user(id, created_at) values (null, '2016-06-03') 

inserts id = 7, created_at = '2016-06-03 00:00:00'. please note that, because time components not specified in value provided created_at, mysql defaulted them 00:00:00.

a new statement:

insert user(id) values (10) 

creates row having id = 10, created_at = null.

the statement

insert user() values() 

looks strange it's legal , inserts row uses default values columns. in case, new row have values id = 11, created_at = null.

finally, statement

insert user(id) values(5) 

fails because there exists row having id = 5 in table (it inserted first statement @ start of examples.