Learning Mysql

The value stored always matches the template
YYYY-MM-DD HH:MM:SS
, but the value can be provided in a wide range of formats:
YYYY-MM-DD HH:MM:SS
or
YY-MM-DD HH:MM:SS
The date and time components follow the same relaxed restrictions as the
DATE
and
TIME
components described previously (however, as of MySQL zero values aren’t permitted. This includes allowance for any punctuation characters, including (unlike TIME) flexibility in the punctuation used in the time component. For example,
2005/02/15 is valid.
YYYYMMDDHHMMSS
or
YYMMDDHHMMSS
Punctuation can be omitted, but the string should be either 12 or 14 digits in length. We recommend only the unambiguous digit version, for the reasons discussed for the
DATE
type. You can specify values with other lengths without providing separators, but we don’t recommend doing so.
Let’s discuss the automatic-update feature in detail. Only one
TIMESTAMP
column per table can be automatically set to the current date and time on insert or update.
You control this by following these steps when creating a table. Choose the column you want to be automatically updated. If you have other
TIMESTAMP
columns in the table, set the ones that precede the selected column in the CREATE TABLE
statement to have a constant default (such as DEFAULT 0
).
3. For the automatically updating column, decide which behavior you want:
a. If you want the timestamp to beset only when anew row is inserted into the table, add DEFAULT CURRENT_TIMESTAMP
to the end of the column dec- laration.
b. If you don’t want a default timestamp but want the current time to be used whenever the data in a row is updated, add ON UPDATE CURRENT_TIME
STAMP
to the end of the column declaration.
c. If you want both of the above—that is, you want the timestamp to beset to the current time in each new row or whenever an existing row is modified add DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
to the end of the column declaration.
If you specify DEFAULT NULL
for a
TIMESTAMP
column, it will be interpreted differently depending on whether there are any other
TIMESTAMP
columns before it in the table.
DEFAULT NULL
is handled as DEFAULT CURRENT_TIMESTAMP
for the first timestamp column, but as DEFAULT for any subsequent ones.
Consider this example:
mysql> CREATE TABLE mytime(id INT NOT NULL,

Download 4.24 Mb.

Share with your friends: