MySQL数据库TIMESTAMP设置默认值新手技术人员对他感到很陌生，不知道该怎么设置。下面学步园小编来讲解下MySQL数据库TIMESTAMP怎么设置默认值?
MySQL数据库TIMESTAMP怎么设置默认值
#1查看表定义，看到的是b列有个属性ONUPDATECURRENT_TIMESTAMP，导致更新数据时，即便未涉及到该列，该列数据也被自动更新。另一方面，c列默认值是'0000-00-0000:00:00'，实际插入已经被自动赋值为current_timestamp。
chinastor.com-root@localhost:test>showcreatetabledj1\G***************************1.row***************************Table:dj1CreateTable:CREATETABLE`dj1`(`a`char(1)COLLATEutf8_binDEFAULTNULL,`b`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`c`timestampNOTNULLDEFAULT'0000-00-0000:00:00',UNIQUEKEY`dj1_idx_u1`(`b`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin1rowinset(0.00sec)
#2创建表dj2，列b不带自动更新属性。
chinastor.com-root@localhost:test>CREATETABLE`dj2`(->`a`char(1)COLLATEutf8_binDEFAULTNULL,->`b`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,->`c`timestampNOTNULLDEFAULT'0000-00-0000:00:00',->UNIQUEKEY`dj1_idx_u1`(`b`)->)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;QueryOK,0rowsaffected(0.01sec)
#3插入dj2测试数据
chinastor.com-root@localhost:test>insertintodj2values(1,null,null);QueryOK,1rowaffected(0.00sec)chinastor.com-root@localhost:test>insertintodj2values(2,null,null);QueryOK,1rowaffected(0.00sec)
MySQL数据库TIMESTAMP怎么设置默认值
#4查看dj2数据
chinastor.com-root@localhost:test>select*fromdj2;+------+---------------------+---------------------+|a|b|c|+------+---------------------+---------------------+|1|2009-09-0914:02:55|2009-09-0914:02:55||2|2009-09-0914:03:00|2009-09-0914:03:00|+------+---------------------+---------------------+2rowsinset(0.00sec)
#5dj2上创建唯一索引
chinastor.com-root@localhost:test>createuniqueindexdj2_idx_u1ondj2(b);QueryOK,2rowsaffected(0.02sec)Records:2Duplicates:0Warnings:0#更新数据成功chinastor.com-root@localhost:test>updatedj2seta=9;QueryOK,2rowsaffected(0.00sec)Rowsmatched:2Changed:2Warnings:0chinastor.com-root@localhost:test>select*fromdj2;+------+---------------------+---------------------+|a|b|c|+------+---------------------+---------------------+|9|2009-09-0914:02:55|2009-09-0914:02:55||9|2009-09-0914:03:00|2009-09-0914:03:00|+------+---------------------+---------------------+2rowsinset(0.00sec)
#6创建表dj3，b列默认值为CURRENT_TIMESTAMP，c列默认值为CURRENT_TIMESTAMP带自动更新属性，报错，不允许行为。
chinastor.com-root@localhost:test>CREATETABLE`dj3`(->`a`char(1)COLLATEutf8_binDEFAULTNULL,->`b`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,->`c`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,->UNIQUEKEY`dj1_idx_u1`(`b`)->)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;ERROR1293(HY000):Incorrecttabledefinition;therecanbeonlyoneTIMESTAMPcolumnwithCURRENT_TIMESTAMPinDEFAULTorONUPDATEclause
#7创建表dj4，b列默认值为CURRENT_TIMESTAMP，c列默认值为'0000-00-0000:00:00'带自动更新属性，报错，不允许行为。
chinastor.com-root@localhost:test>CREATETABLE`dj4`(->`a`char(1)COLLATEutf8_binDEFAULTNULL,->`b`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,->`c`timestampNOTNULLDEFAULT'0000-00-0000:00:00'ONUPDATECURRENT_TIMESTAMP,->UNIQUEKEY`dj1_idx_u1`(`b`)->)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;ERROR1293(HY000):Incorrecttabledefinition;therecanbeonlyoneTIMESTAMPcolumnwithCURRENT_TIMESTAMPinDEFAULTorONUPDATEclause
#8创建表dj5，b列默认值为CURRENT_TIMESTAMP带自动更新属性，c列默认值为CURRENT_TIMESTAMP，报错，不允许行为。
chinastor.com-root@localhost:test>CREATETABLE`dj5`(->`a`char(1)COLLATEutf8_binDEFAULTNULL,->`b`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,->`c`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,->UNIQUEKEY`dj1_idx_u1`(`b`)->)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;ERROR1293(HY000):Incorrecttabledefinition;therecanbeonlyoneTIMESTAMPcolum
以上就是关于“MySQL数据库TIMESTAMP怎么设置默认值”的内容，希望对大家有用。更多资讯请关注学步园。学步园，您学习IT技术的优质平台!