Author: shuixin13
MySQL is currently not supported in the form of a function, http://www.faq-it.org/design/">faq-it.org/design/ If the default value of your column is the current update date and time The function, you can use the TimeStAMP column Type The TimeStAMP Column Type TimeStAMP Column TimeStamp value can be used from the beginning of the 1970 to 2037, the accuracy is one second, its value is a digital display. TimeStAmp value Displays the size The format is as follows:: ------------ ---------------- | Column Type | Display Format | | TIMESTAMP (14) | YYYYMMDDHHMMS | | TIMESTAMP (12) | YYMMDDHMMS | | TimeStamp (10) | YYMMDDHMM | | TimeStamp (8) | YYYYMMDD | | TIMESTAMP (6) | YYMMDD | | TimeStamp (4) | YYMM | | TIMESTAMP (2 ) | YY | ------------------------------ "Complete" TimeStamp format is 14, but TimeStamp columns You can also create the most common display size with shorter display sizes is 6, 8, 12, and 14. You can specify an arbitrary display size when you create a table, but define the column length of 0 or 14 average Mandatory is defined as a column length 14. The column length is forced to be a next larger number of even numbers from 1 to 13. Columns, such as: Define field length forced field length timestamp (0) -> TimeStamp (14) TimeStamp (15) -> TimeStamp (14) TimeStamp (1) -> Timest AMP (2) TimeStamp (5) -> TimeSTAMP (6) All TimeStAMP columns have the same storage size, using the full precision of the scheduled time value (14-bit) storage legal values regardless of the display size. If you are not legal, it will be mandatory to store this there are several meaning: 1. Although you define column TimeStamp (8) when you build a table, the timestamp column is actually saved 14 digits when you perform data insertion and update. Data (including the year-time daytime or second), but mysql returns to you is 8-bit annual 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 a complete precision, the unique function of the direct operation stored value is unix_timestamp (); because the value of MySQL returns the TimeStAMP column is the value of the retrieved retrieval, this means you may not be able to use some Some functions to operate the TimeStAMP column (for example, Hour () or second ()), unless the relevant portion 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 ('Rel = "NOFOLLOW" ID' INT (3) unsigned auto_increment, 'Date1' TimeStamp (8) Primary Key ('id')); INSERT INTO TEST set ID = 1; select * from test; ---- -------------- | ID | DATE1 | ---- - ------------- | 1 | 20021114 | ---- ---------------- Alter Table Test Change 'Date1' Date1 'TimeStamp (14); Select * from test; ---- -------------- | ID | DATE1 | ---- ---- ------------ | 1 | 20021114093723 | ---- ---------------- You can use the TIMESTAMP column type to automatically use Current date and time mark INSERT or UPDATE operation. If you have multiple TimeStAMP columns, only the first automatic update. Automatic Update The first timestamp column occurs under any of the following: 1. The column value is not 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 sets a value listed as it already, this will not cause the TimeStAMP column to be updated because if you set a list of current values, MySQL ignore the changes for efficiency.
3, you explicitly set the TimeStamp listed 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 | | 2 | 20021114093724 | 0000000000000000000000 | ---- ---------------------------- ------ -> The first instruction is designed for Date1, Date2 is NULL, so DATE1, DATE2 value is the current time second instruction because there is no Date1, Date2 column value, the first timestamp column DATE1 is Updated to the current time, and two timestamp columns DATE2 becomes "00000000000000" Update test set id = 3 where id = 1; - ------------------------------ ---- ---------------- | ID | DATE1 | DATE2 | ---- ------------- - ---------------- | 3 | 20021114094009 | 20021114093723 | | 2 | 20021114093724 | 00000000000000 | ---- -------------------------------- - > This instruction does 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 | | 2 | 20021114093724 | 00000000000000 | ----
