Shulou(Shulou.com)06/01 Report--

MySQL currently does not support the Default of a column as a function. If you achieve the function that the default value of a column is the current update date and time, you can use the TIMESTAMP column type to specify the TIMESTAMP column type below.

TIMESTAMP column type

The TIMESTAMP value can run from sometime in 1970 to 2037 with a precision of one second, and its value is displayed as a number.

The format of the TIMESTAMP value display size is shown in the following 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 | |

+-+ +

The "full" TIMESTAMP format is 14 bits, but TIMESTAMP columns can also be created with shorter display sizes.

The most common display sizes are 6, 8, 12, and 14.

You can specify any display size when creating a table, but defining a column length of 0 or greater than 14 will be forced to be defined as a column length of 14.

Column lengths in the range from 1 to 13 are forced to the next larger even number.

Such as: define the field length to force the 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

Use the full precision (14 bits) of the specified time value to store the legal value regardless of the display size.

Illegal date will be forced to be stored as 0

[@ more@]

This has several implications: 1, although you build the table when defining the column TIMESTAMP (8), but in your data insertion and update when the TIMESTAMP column actually saved 14-bit data (including the year, month, day, minute and second), but in your query when the MySQL returned to you is 8-bit data. If you use ALTER TABLE to widen a narrow TIMESTAMP column, previously "hidden" information will be displayed.

2. Similarly, shrinking a TIMESTAMP column does not cause information to be lost, except that less information is displayed when the perceived upper value is displayed.

3, although the timestamp value is stored as a full precision, the only function that directly manipulates the stored value is UNIX_TIMESTAMP (); because MySQL returns the column value of the TIMESTAMP column is the value retrieved after formatting, this means that you may not be able to use some functions to operate on the TIMESTAMP column (such as HOUR () or SECOND ()), unless the relevant part of the timestamp value is included in the formatted value. For example, the HH portion of a TIMESTAMP column is displayed only if it is defined as more than TIMESTAMP (10), so using HOUR () on a shorter timestamp value produces an unpredictable result.

4. The illegal timestamp value is converted to a zero value of the appropriate type (00000000000000). (so does 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 * 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 mark INSERT or UPDATE operations with the current date and time.

If you have more than one TIMESTAMP column, only the first one is automatically updated.

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

1. Column values are not explicitly specified in an INSERT or LOAD DATA INFILE statement.

2. Column values are not explicitly specified in one UPDATE statement and other columns change the value. Note that a UPDATE sets a column to its existing value, which will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the change for the sake of efficiency. )

3. You explicitly set TIMESTAMP as NULL.

4. TIMESTAMP columns other than the first can also be set to the current date and time, as long as the column is set to 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 instruction sets date1 and date2 to NULL, so the values of date1 and date2 are both current time.

Because the second instruction does not set the date1 and date2 column values, the first TIMESTAMP column date1 is updated to the current time

While the two TIMESTAMP columns date2 become "00000000000000" because the date is illegal.

UPDATE test SET id= 3 WHERE id=1

+-- +

| | id | date1 | date2 | |

+-- +

| | 3 | 20021114094009 | 20021114093723 |

| | 2 | 20021114093724 | 00000000000000 |

+-- +

This directive does not explicitly set the column value of date2, so the first TIMESTAMP column date1 will be updated to the current time

UPDATE test SET id= 1, date 1, date 2, now () WHERE id=3

+-- +

| | id | date1 | date2 | |

+-- +

| | 1 | 20021114094009 | 20021114094320 |

| | 2 | 20021114093724 | 00000000000000 |

+-- +

-> this instruction sets date1=date1, so the date1 column value does not change when the data is updated

Because date2=NOW () is set, the date2 column value is updated to the current time when the data is updated.

This directive is equivalent to UPDATE test SET id= 1, date1, date1, date2, null WHERE id=3.

The TIMESTAMP returned by MySQL is listed as a digital display.

You can use the DATE_FROMAT () function to format the TIMESTAMP column

SELECT id,DATE_FORMAT (date1,'%Y-%m-%d% HRV% iRU% s') As date1

DATE_FORMAT (date2,'%Y-%m-%d% HRV% iRU% s') As date2 FROM test

+-- +

| | id | date1 | date2 | |

+-- +

| | 1 | 2002-11-14 09:40:09 | 2002-11-14 09:43:20 |

| | 2 | 2002-11-14 09:37:24 | 0000-0000: 00:00 |

+-- +

SELECT id,DATE_FORMAT (date1,'%Y-%m-%d') As date1

DATE_FORMAT (date2,'%Y-%m-%d') As date2 FROM test

+-- +

| | id | date1 | date2 | |

+-- +

| | 1 | 2002-11-14 | 2002-11-14 | |

| | 2 | 2002-11-14 | 0000-00-00 | |

+-- +

To some extent, you can assign a value of one date type to an object of a different date type.

However, it is particularly important to note that there may be some changes in values or loss of information:

1. If you assign a date value to a DATETIME or TIMESTAMP object, the time portion of the resulting value is set to'00, 00, 00, 00, 00, because the date value does not contain time information.

2. If you assign a DATETIME or timestamp value to a DATE object, the time portion of the resulting value is deleted because the DATE type does not store time information.

3. Although DATETIME, DATE and timestamp values can all be specified in the same format set

But not all types have the same range of values.

For example, the timestamp value cannot be earlier than 1970 or later than 2037

This means that a date, such as' 1968-01-01, is legal as a DATETIME or date value

But it is not a correct timestamp value! And if such an object is assigned to the TIMESTAMP column, it will be transformed to 0.

When specifying a date value, beware of certain defects:

1. A loose format that is allowed to be specified as a string can be deceived. For example, because of the use of the ":" delimiter, the value'10 11 'may look like a time value, but if used in a date, the context will be interpreted as' 2010-11-12'as a year. The value of'10 45 15 'will be converted to' 0000-00-00 month, because'45'is not a legal month.

2. The year value specified in two digits is vague because the century is unknown. MySQL uses the following rules to interpret 2-digit annual values: annual values in the range 00-69 are converted to 2000-2069. The annual value in the range 70-99 is converted to 1970-1999.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.