The timestamp data type is a special data type that can automatically update the time if you update the record without using program updates timestamp
I. Variants of TIMESTAMP
Timestamp timestamps can have multiple different features when created, such as:
1. Refresh the data column when creating new records and modifying existing records:
?
1 |
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
2. Set this field to the current time when creating a new record, but do not refresh it when you modify it later:
?
1 |
TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
3. Set this field to 0 when creating a new record, and refresh it later when it is modified:
?
1 |
TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
4. When creating a new record, set this field to the given value and refresh it later when it is modified:
?
1 |
TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss‘ ON UPDATE CURRENT_TIMESTAMP |
MySQL currently does not support column default as a function, such as the default value of your column to the current update date and time function, you can use the timestamp column type, the following detailed description timestamp column type
Second, timestamp column type
The timestamp value can be from the beginning of one 1970 to 2037, with a precision of one second and its value displayed as a number.
The format of the timestamp value display dimension is shown in the following table:
:
?
12345678910 |
+
---------------+----------------+
| 列类型 | 显示格式 |
|
TIMESTAMP
(14) | YYYYMMDDHHMMSS |
|
TIMESTAMP
(12) | YYMMDDHHMMSS |
|
TIMESTAMP
(10) | YYMMDDHHMM |
|
TIMESTAMP
(8) | YYYYMMDD |
|
TIMESTAMP
(6) | YYMMDD |
|
TIMESTAMP
(4) | YYMM |
|
TIMESTAMP
(2) | YY |
+
---------------+----------------+
|
The "full" timestamp format is 14-bit, but the timestamp column can also be used for shorter display sizes, creating the most common display sizes of 6, 8, 12, and 14.
You can specify an arbitrary display size when creating a table, but defining a column length of 0 or greater than 14 is mandatory to define column length 14.
The odd numeric dimensions of the column length in the range from 1~13 are coerced to the next larger even number.
Gca
?
12345 |
Define field length force field length timestamp (0)- > timestamp timestamp timestamp timestamp (1) timestamp (2) timestamp (5), timestamp (6) |
1. Although you define column timestamp (8) When you create a table, the timestamp column actually holds 14 bits of data when you insert and update the data (including date and time of month and minute), except that MySQL returns you with 8-digit date data when you query. If you use ALTER TABLE to widen a narrow timestamp column, information that was previously "hidden" will be displayed.
2. Similarly, narrowing a timestamp column does not result in information loss, except when the value is displayed, less information is displayed.
MySQL timestamp (timestamp) usage