MySQL currently does not support the form of default as a function,
If you reach your default value for the current update date and time,
You can use the TimeSTAMP column type
The TimeStamp column type is described in detail below.
TimeSTAMP column type
The timestamp value can be from 1970 to 2037, with a second, its value is a digital display.
The TimeStamp value shows the size of the size as shown in the table:
:
------------- --------------
| Column Type | Display Format |
| TIMESTAMP (14) | YYYYMMDDHHMMSS |
| TIMESTAMP (12) | YYMMDDHHMMSS |
| TIMESTAMP (10) | YYMMDDHHMM |
| TIMESTAMP (8) | YYYYMMDD |
| TIMESTAMP (6) | YYMMDD |
| TIMESTAMP (4) | YYMM |
| TIMESTAMP (2) | YY |
------------- --------------
"Complete" TimeStamp format is 14 bits, but the TimeStAMP column can also create shorter display sizes
The most common display size is 6, 8, 12, and 14.
You can specify an arbitrary display size when you create a table, but define the collar length of 0 or more will be mandatory as the column length 14.
The odd-numbered dimensions of the columns from 1 to 13 are forced to be the next larger even.
Columns such as:
Define field length forced field length
TimeStamp (0) -> TimeStamp (14)
TimeStamp (15) -> TimeStamp (14)
TimeStamp (1) -> TimeStamp (2)
TimeStamp (5) -> TimeStamp (6)
All TimeStamp columns have the same storage size,
The display size is displayed using the complete accuracy (14-bit) storage legal value using the specified period time value.
If the date is not legal, it will be mandatory for 0 storage.
There are several integration:
1. Although you define the column TimeStamp (8) when you build a table, the timestamp column when you perform data insertion and update.
Actually saved 14 digits (including the year-on-day-time date),
Just when you check, MySQL is returned to you is an 8-bit annual date data.
If you use ALTER TABLE to broaden a narrow TIMESTAMP column, the information previously "hidden" will be displayed.
2. Similarly, narrowing a TimeStAMP column does not cause information to be lost, in addition to the value of the value, less information is displayed.
3, although the timestamp value is stored as complete precision, the unique function of the direct operation of the stored value is unix_timestamp ();
Since MySQL returns the column value of the TimeStAMP column is the value of the retrieval after the formatting,
This means you may not use some functions to manipulate the TimeStAMP column (such as Hour () or second ()),
Unless the relevant part of the TimeStAmp value is included in the formatted value.
For example, a TimeStAMP column is only displayed when it is defined as timestamp (10), and the TimeStAMP column will be displayed, so it is unpredictable to generate an unpredictable result on a shorter TimeStamp value.
4, the illegal TimeStamp value is transformed to the appropriate type of "zero" value (00000000000000). (DateTime, Date)
You can use the following statements to verify:
Create Table Test ('id' int (3) unsigned auto_increment, 'Date1' TimeStamp (8) Primary Key ('ID'));
INSERT INTO TEST SET Id = 1;
SELECT *.
-- ----------------
| ID | DATE1 |
-- ----------------
| 1 | 20021114 |
-- ----------------
Alter Table Test Change 'Date1' Date1 'TimeStamp (14);
SELECT *.
-- ----------------
| ID | DATE1 |
-- ----------------
| 1 | 20021114093723 |
-- ----------------
You can automatically use the TimeStAMP column type to automatically use the current date and time to mark the operation of INSERT or UPDATE.
If you have multiple TimeStAMP columns, only the first automatic update.
Automatic Update First TimeStAMP column happens under any of the following:
1. The column value is not explicitly specified in an INSERT or LOAD DATA INFILE statement.
2, the column value is not explicitly specified in a UPDATE statement and other columns change the value.
(Note that a Update settings a value listed as it already,
This will not cause timestamp columns to be updated.
Because if you set a list of current values, MySQL ignore changes for efficiency. )
3, you explicitly set TimeStamp as NULL.
4. In addition to the first TimeStAMP column can also be set to the current date and time, as long as it is null, or now ().
Create Table Test
'id' int (3) unsigned auto_increment,
'Date1' TimeStamp (14),
'Date2' TimeStamp (14),
Primary Key ('ID')
);
Insert Into Test (ID, Date1, Date2) Values (1, null, null);
INSERT INTO TEST SET Id = 2;
---- ---------------- --------------
| ID | DATE1 | DATE2 |
-- ---------------- ---------------- | 1 | 20021114093723 | 20021114093723 |
| 2 | 20021114093724 | 00000000000000 |
---- ---------------- --------------
-> The first command is Date1, Date2 is NULL, so Date1, Date2 value is the current time.
Article 2 The command is not a Date1, Date2 column value, and the first timestamp column Date1 is updated to the current time.
And the two TimeStAMP columns Date2 becomes "0000000000000000" due to the date of date
Update test set id = 3 where id = 1;
---- ---------------- --------------
| ID | DATE1 | DATE2 |
---- ---------------- --------------
| 3 | 20021114094009 | 20021114093723 |
| 2 | 20021114093724 | 00000000000000 |
---- ---------------- --------------
-> This instruction did not explicitly set the value of Date2, so the first TimeStAMP column Date1 will be updated to the current time.
Update test set id = 1, date1 = DATE1, date2 = now () where id = 3;
---- ---------------- --------------
| ID | DATE1 | DATE2 |
---- ---------------- --------------
| 1 | 20021114094009 | 20021114094320 |
| 2 | 20021114093724 | 00000000000000 |
---- ---------------- --------------
-> This directive is set by setting Date1 = Date1, so the Date1 column value does not change when updating data.
And because Date2 = now (), the Date2 column value will be updated to the current time when updating data.
This instruction is equivalent to update test set id = 1, date1 = DATE1, DATE2 = null where id = 3;
Because MySQL returned, the TimeSTAMP is listed as a digital display,
