How to Add Default Time to Fields in MySQL

Date types, differences and uses

There are five date types of MySQL, which are date, time, year, datetime and timestamp.

类型	字节	格式	用途	是否支持设置系统默认值
date	3	YYYY-MM-DD	日期值	不支持
time	3	HH:MM:SS	时间值或持续时间	不支持
year	1	YYYY	年份	不支持
datetime	8	YYYY-MM-DD HH:MM:SS	日期和时间混合值	不支持
timestamp	4	YYYYMMDD HHMMSS	混合日期和时间，可作时间戳	支持

Application scenario:

In the data table, to record when each piece of data was created, the application program does not need to record it specially, but the data database obtains the current time and automatically records the creation time; In the database, to record when each piece of data is modified, the application program does not need to record it specially, but the data database obtains the current time and automatically records the modification time;

Implementation method:

Set the field type to TIMESTAMP Set the default value to CURRENT_TIMESTAMP

Example application:

MySQL script implementation use case

`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT ' Creation time ',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' Update time ',
`datalevel` tinyint(1) DEFAULT '1' COMMENT ' Whether it has been deleted (0 Delete /1 Normal )',

ALTER TABLE table_name
ADD COLUMN create_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT ' Creation time ';
ALTER TABLE table_name
ADD COLUMN update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' Update time ';
ALTER TABLE table_name
ADD COLUMN datalevel tinyint(1) DEFAULT '1' COMMENT ' Whether it has been deleted (0 Delete /1 Normal )';

MySQL Create Common Indexes

ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid);

Summarize