Author: paul
Date: 2006-01-19 05:38:55 +0100 (Thu, 19 Jan 2006)
New Revision: 923
Log:
r6397@frost: paul | 2006-01-18 22:11:37 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/data-types.xml
trunk/refman-5.0/data-types.xml
trunk/refman-5.1/data-types.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6396
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2335
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6397
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2335
Modified: trunk/refman-4.1/data-types.xml
===================================================================
--- trunk/refman-4.1/data-types.xml 2006-01-19 04:38:42 UTC (rev 922)
+++ trunk/refman-4.1/data-types.xml 2006-01-19 04:38:55 UTC (rev 923)
@@ -2653,9 +2653,8 @@
<literal>2037</literal>. This means that a date such as
<literal>'1968-01-01'</literal>, while legal as a
<literal>DATETIME</literal> or <literal>DATE</literal>
- value, is not a valid <literal>TIMESTAMP</literal> value and
- is converted to <literal>0</literal> if assigned to such an
- object.
+ value, is not valid as a <literal>TIMESTAMP</literal> value
+ and is converted to <literal>0</literal>.
</para>
</listitem>
@@ -2792,6 +2791,14 @@
</para>
<para>
+ Note that the information in the following discussion applies
+ to <literal>TIMESTAMP</literal> columns only for tables not
+ created with <literal>MAXDB</literal> mode enabled, because
+ such columns are created as <literal>DATETIME</literal>
+ columns.
+ </para>
+
+ <para>
You can set any <literal>TIMESTAMP</literal> column to a value
different from the current date and time by setting it
explicitly to the desired value. This is true even for the
@@ -3092,12 +3099,13 @@
</itemizedlist>
<para>
- Beginning with MySQL 4.1.1, the MySQL server can be run in
- <literal>MAXDB</literal> mode. When the server runs in this
- mode, <literal>TIMESTAMP</literal> is identical with
- <literal>DATETIME</literal>. That is, if the server is running
- in <literal>MAXDB</literal> mode at the time that a table is
- created, <literal>TIMESTAMP</literal> columns are created as
+ Beginning with MySQL 4.1.1, the MySQL server can be also be
+ run with the <literal>MAXDB</literal> SQL mode enabled. When
+ the server runs with this mode enabled,
+ <literal>TIMESTAMP</literal> is identical with
+ <literal>DATETIME</literal>. That is, if this mode is enabled
+ at the time that a table is created,
+ <literal>TIMESTAMP</literal> columns are created as
<literal>DATETIME</literal> columns. As a result, such columns
use <literal>DATETIME</literal> display format, have the same
range of values, and there is no automatic initialization or
@@ -3136,11 +3144,14 @@
<listitem>
<para>
- You can assign the current timestamp as the default value
- and the auto-update value, as before. But it is possible
- to have just one automatic behavior or the other, or
- neither of them. (It is not possible to have one behavior
- for one column and the other for another column.)
+ For one <literal>TIMESTAMP</literal> column in a table,
+ you can assign the current timestamp as the default value
+ and the auto-update value. It is possible to have the
+ current timestamp be the default value for initializing
+ the column, for the auto-update value, or both. It is not
+ possible to have the current timestamp be the default
+ value for one column and the auto-update value for another
+ column.
</para>
</listitem>
@@ -3148,7 +3159,7 @@
<para>
You can specify which <literal>TIMESTAMP</literal> column
to automatically initialize or update to the current date
- and time. This no longer need be the first
+ and time. This need not be the first
<literal>TIMESTAMP</literal> column.
</para>
</listitem>
@@ -3223,7 +3234,7 @@
<para>
Any single <literal>TIMESTAMP</literal> column in a table can
- be set to be the one that is initialized to the current
+ be used as the one that is initialized to the current
timestamp or updated automatically.
</para>
@@ -3294,20 +3305,18 @@
<literal>CURRENT_TIMESTAMP()</literal>, or
<literal>NOW()</literal> can be used in the
<literal>DEFAULT</literal> and <literal>ON UPDATE</literal>
- clauses. They all have the same effect.
+ clauses. They all mean <quote>the current timestamp.</quote>
</para>
<para>
- The order of the two attributes does not matter. If both
+ The order of the <literal>DEFAULT</literal> and <literal>ON
+ UPDATE</literal> attributes does not matter. If both
<literal>DEFAULT</literal> and <literal>ON UPDATE</literal>
are specified for a <literal>TIMESTAMP</literal> column,
- either can precede the other.
+ either can precede the other. For example, these statements
+ are equivalent:
</para>
- <para>
- Example. These statements are equivalent:
- </para>
-
<programlisting>
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
@@ -3326,12 +3335,15 @@
<literal>DEFAULT '2003-01-01 00:00:00'</literal>). Then for
the other <literal>TIMESTAMP</literal> column, the rules are
the same as for the first <literal>TIMESTAMP</literal> column,
- except that you cannot omit both of the
- <literal>DEFAULT</literal> and <literal>ON UPDATE</literal>
- clauses. If you do so, no automatic initialization or updating
- occurs.
+ except that if you omit both of the <literal>DEFAULT</literal>
+ and <literal>ON UPDATE</literal> clauses, no automatic
+ initialization or updating occurs.
</para>
+ <remark role="todo">
+ What is this supposed to be an example OF? [pd]
+ </remark>
+
<para>
Example. These statements are equivalent:
</para>
@@ -3354,14 +3366,14 @@
<literal>TIMESTAMP</literal> values still are stored in UTC,
but are converted from the current time zone for storage, and
converted back to the current time zone for retrieval. As long
- as the time zone setting remains the same, you get back the
+ as the time zone setting remains constant, you get back the
same value you store. If you store a
<literal>TIMESTAMP</literal> value, and then change the time
- zone and retrieve the value, it is different than the value
- you stored. This occurs because the same time zone is not used
- for conversion in both directions. The current time zone is
- available as the value of the <literal>time_zone</literal>
- system variable.
+ zone and retrieve the value, the retrieved value is different
+ than the value you stored. This occurs because the same time
+ zone was not used for conversion in both directions. The
+ current time zone is available as the value of the
+ <literal>time_zone</literal> system variable.
</para>
<para>
@@ -3384,25 +3396,45 @@
<literal>NULL</literal> attribute is not specified), setting
the column to <literal>NULL</literal> sets it to the current
timestamp. Note that a <literal>TIMESTAMP</literal> column
- which allows <literal>NULL</literal> values does
- <emphasis>not</emphasis> take on the current timestamp unless
- either its default value is defined as
- <literal>CURRENT_TIMESTAMP</literal>, <emphasis>or</emphasis>
- either <literal>NOW()</literal> or
- <literal>CURRENT_TIMESTAMP</literal> is inserted into the
- column. In other words, a <literal>TIMESTAMP</literal> column
- defined as <literal>NULL</literal> auto-updates only if it
- defined using a definition such as the following:
+ which allows <literal>NULL</literal> values
+ <emphasis>not</emphasis> take on the current timestamp except
+ under one of the following conditions:
</para>
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Its default value is defined as
+ <literal>CURRENT_TIMESTAMP</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>NOW()</literal> or
+ <literal>CURRENT_TIMESTAMP</literal> is inserted into the
+ column
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, a <literal>TIMESTAMP</literal> column defined
+ as <literal>NULL</literal> will auto-update only if it is
+ created using a definition such as the following:
+ </para>
+
<programlisting>
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
</programlisting>
<para>
Otherwise — that is, if the <literal>TIMESTAMP</literal>
- column is defined using <literal>NULL</literal> but not using
- <literal>DEFAULT TIMESTAMP</literal>, as shown here…
+ column is defined to allow <literal>NULL</literal> values but
+ not using <literal>DEFAULT TIMESTAMP</literal>, as shown
+ here…
</para>
<programlisting>
@@ -3443,12 +3475,11 @@
<literal>'HHH:MM:SS'</literal> format for large hours values).
<literal>TIME</literal> values may range from
<literal>'-838:59:59'</literal> to
- <literal>'838:59:59'</literal>. The reason for which the hours
- part may be so large is that the <literal>TIME</literal> type
- may be used not only to represent a time of day (which must be
- less than 24 hours), but elapsed time or a time interval between
- two events as well. (Note that this interval may be much greater
- than 24 hours, or even negative.)
+ <literal>'838:59:59'</literal>. The hours part may be so large
+ because the <literal>TIME</literal> type can be used not only to
+ represent a time of day (which must be less than 24 hours), but
+ also elapsed time or a time interval between two events (which
+ may be much greater than 24 hours, or even negative).
</para>
<para>
@@ -3535,24 +3566,27 @@
</para>
<para>
- Values that lie outside the <literal>TIME</literal> range but
- are otherwise legal are clipped to the closest endpoint of the
- range. For example, <literal>'-850:00:00'</literal> and
+ By default, values that lie outside the <literal>TIME</literal>
+ range but are otherwise legal are clipped to the closest
+ endpoint of the range. For example,
+ <literal>'-850:00:00'</literal> and
<literal>'850:00:00'</literal> are converted to
<literal>'-838:59:59'</literal> and
- <literal>'838:59:59'</literal>.
- </para>
-
- <para>
- Illegal <literal>TIME</literal> values are converted to
- <literal>'00:00:00'</literal>. Note that because
- <literal>'00:00:00'</literal> is itself a legal
+ <literal>'838:59:59'</literal>. Illegal <literal>TIME</literal>
+ values are converted to <literal>'00:00:00'</literal>. Note that
+ because <literal>'00:00:00'</literal> is itself a legal
<literal>TIME</literal> value, there is no way to tell, from a
value of <literal>'00:00:00'</literal> stored in a table,
whether the original value was specified as
<literal>'00:00:00'</literal> or whether it was illegal.
</para>
+ <para>
+ For more restrictive treatment of invalid
+ <literal>TIME</literal> values, enable strict SQL mode to cause
+ errors to occur. See <xref linkend="server-sql-mode"/>.
+ </para>
+
</section>
<section id="year">
@@ -3670,12 +3704,12 @@
</indexterm>
<para>
- MySQL itself is year 2000 (Y2K) safe (see
- <xref linkend="year-2000-compliance"/>), but input values
- presented to MySQL may not be. Any input containing two-digit
- year values is ambiguous, because the century is unknown. Such
- values must be interpreted into four-digit form because MySQL
- stores years internally using four digits.
+ As discussed in <xref linkend="year-2000-compliance"/>, MySQL
+ itself is year 2000 (Y2K) safe. However, particular input values
+ presented to MySQL may not be Y2K safe. Any value containing a
+ two-digit year is ambiguous, because the century is unknown.
+ Such values must be interpreted into four-digit form because
+ MySQL stores years internally using four digits.
</para>
<para>
@@ -3704,10 +3738,11 @@
</itemizedlist>
<para>
- Remember that these rules provide only reasonable guesses as to
- what your data values mean. If the heuristics used by MySQL do
- not produce the correct values, you should provide unambiguous
- input containing four-digit year values.
+ Remember that these rules are only heuristics that provide
+ reasonable guesses as to what your data values mean. If the
+ rules used by MySQL do not produce the correct values, you
+ should provide unambiguous input containing four-digit year
+ values.
</para>
<para>
Modified: trunk/refman-5.0/data-types.xml
===================================================================
--- trunk/refman-5.0/data-types.xml 2006-01-19 04:38:42 UTC (rev 922)
+++ trunk/refman-5.0/data-types.xml 2006-01-19 04:38:55 UTC (rev 923)
@@ -2696,9 +2696,8 @@
<literal>2037</literal>. This means that a date such as
<literal>'1968-01-01'</literal>, while legal as a
<literal>DATETIME</literal> or <literal>DATE</literal>
- value, is not a valid <literal>TIMESTAMP</literal> value and
- is converted to <literal>0</literal> if assigned to such an
- object.
+ value, is not valid as a <literal>TIMESTAMP</literal> value
+ and is converted to <literal>0</literal>.
</para>
</listitem>
@@ -2810,12 +2809,12 @@
</para>
<para>
- The MySQL server can be also be run in
- <literal>MAXDB</literal> mode. When the server runs in this
- mode, <literal>TIMESTAMP</literal> is identical with
- <literal>DATETIME</literal>. That is, if the server is running
- in <literal>MAXDB</literal> mode at the time that a table is
- created, <literal>TIMESTAMP</literal> columns are created as
+ The MySQL server can be also be run with the
+ <literal>MAXDB</literal> SQL mode enabled. When the server
+ runs with this mode enabled, <literal>TIMESTAMP</literal> is
+ identical with <literal>DATETIME</literal>. That is, if this
+ mode is enabled at the time that a table is created,
+ <literal>TIMESTAMP</literal> columns are created as
<literal>DATETIME</literal> columns. As a result, such columns
use <literal>DATETIME</literal> display format, have the same
range of values, and there is no automatic initialization or
@@ -2844,6 +2843,14 @@
</programlisting>
<para>
+ Note that the information in the following discussion applies
+ to <literal>TIMESTAMP</literal> columns only for tables not
+ created with <literal>MAXDB</literal> mode enabled, because
+ such columns are created as <literal>DATETIME</literal>
+ columns.
+ </para>
+
+ <para>
As of MySQL 5.0.2, MySQL does not accept timestamp values that
include a zero in the day or month column or values that are
not a valid date. The sole exception to this rule is the
@@ -2860,11 +2867,14 @@
<listitem>
<para>
- You can assign the current timestamp as the default value
- and the auto-update value, as before. But it is possible
- to have just one automatic behavior or the other, or
- neither of them. (It is not possible to have one behavior
- for one column and the other for another column.)
+ For one <literal>TIMESTAMP</literal> column in a table,
+ you can assign the current timestamp as the default value
+ and the auto-update value. It is possible to have the
+ current timestamp be the default value for initializing
+ the column, for the auto-update value, or both. It is not
+ possible to have the current timestamp be the default
+ value for one column and the auto-update value for another
+ column.
</para>
</listitem>
@@ -2872,7 +2882,7 @@
<para>
You can specify which <literal>TIMESTAMP</literal> column
to automatically initialize or update to the current date
- and time. This no longer need be the first
+ and time. This need not be the first
<literal>TIMESTAMP</literal> column.
</para>
</listitem>
@@ -2880,13 +2890,8 @@
</itemizedlist>
<para>
- Note that the information in the following discussion applies
- to <literal>TIMESTAMP</literal> columns only for tables not
- created with <literal>MAXDB</literal> mode enabled. (As noted
- above, <literal>MAXDB</literal> mode causes columns to be
- created as <literal>DATETIME</literal> columns.) The rules
- governing initialization and updating of
- <literal>TIMESTAMP</literal> columns are as follows:
+ The following rules govern initialization and updating of
+ <literal>TIMESTAMP</literal> columns:
</para>
<itemizedlist>
@@ -2915,8 +2920,8 @@
<listitem>
<para>
Any single <literal>TIMESTAMP</literal> column in a table
- can be set to be the one that is initialized to the
- current timestamp or updated automatically.
+ can be used as the one that is initialized to the current
+ timestamp or updated automatically.
</para>
</listitem>
@@ -2991,21 +2996,19 @@
<literal>CURRENT_TIMESTAMP()</literal>, or
<literal>NOW()</literal> can be used in the
<literal>DEFAULT</literal> and <literal>ON
- UPDATE</literal> clauses. They all have the same effect.
+ UPDATE</literal> clauses. They all mean <quote>the current
+ timestamp.</quote>
</para>
<para>
- The order of the two attributes does not matter. If both
- <literal>DEFAULT</literal> and <literal>ON
+ The order of the <literal>DEFAULT</literal> and
+ <literal>ON UPDATE</literal> attributes does not matter.
+ If both <literal>DEFAULT</literal> and <literal>ON
UPDATE</literal> are specified for a
<literal>TIMESTAMP</literal> column, either can precede
- the other.
+ the other. For example, these statements are equivalent:
</para>
- <para>
- Example. These statements are equivalent:
- </para>
-
<programlisting>
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
@@ -3027,12 +3030,16 @@
<literal>DEFAULT '2003-01-01 00:00:00'</literal>). Then,
for the other <literal>TIMESTAMP</literal> column, the
rules are the same as for the first
- <literal>TIMESTAMP</literal> column, except that you
- cannot omit both of the <literal>DEFAULT</literal> and
- <literal>ON UPDATE</literal> clauses. If you do so, no
- automatic initialization or updating occurs.
+ <literal>TIMESTAMP</literal> column, except that if you
+ omit both of the <literal>DEFAULT</literal> and
+ <literal>ON UPDATE</literal> clauses, no automatic
+ initialization or updating occurs.
</para>
+ <remark role="todo">
+ What is this supposed to be an example OF? [pd]
+ </remark>
+
<para>
Example. These statements are equivalent:
</para>
@@ -3059,12 +3066,12 @@
converted back to the current time zone upon retrieval. As
long as the time zone setting remains constant, you get back
the same value you store. If you store a
- <literal>TIMESTAMP</literal> value, change the time zone and
- then retrieve the value, it is different than the value you
- stored. This occurs because the same time zone was not used
- for conversion in both directions. The current time zone is
- available as the value of the <literal>time_zone</literal>
- system variable.
+ <literal>TIMESTAMP</literal> value, and then change the time
+ zone and retrieve the value, the retrieved value is different
+ than the value you stored. This occurs because the same time
+ zone was not used for conversion in both directions. The
+ current time zone is available as the value of the
+ <literal>time_zone</literal> system variable.
</para>
<para>
@@ -3088,25 +3095,44 @@
setting the column to <literal>NULL</literal> sets it to the
current timestamp. Note that a <literal>TIMESTAMP</literal>
column which allows <literal>NULL</literal> values will
- <emphasis role="bold">not</emphasis> take on the current
- timestamp unless either its default value is defined as
- <literal>CURRENT_TIMESTAMP</literal>,
- <emphasis role="bold">or</emphasis> either
- <literal>NOW()</literal> or
- <literal>CURRENT_TIMESTAMP</literal> is inserted into the
- column. In other words, a <literal>TIMESTAMP</literal> column
- defined as <literal>NULL</literal> will auto-update only if it
- is created using a definition such as the following:
+ <emphasis>not</emphasis> take on the current timestamp except
+ under one of the following conditions:
</para>
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Its default value is defined as
+ <literal>CURRENT_TIMESTAMP</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>NOW()</literal> or
+ <literal>CURRENT_TIMESTAMP</literal> is inserted into the
+ column
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, a <literal>TIMESTAMP</literal> column defined
+ as <literal>NULL</literal> will auto-update only if it is
+ created using a definition such as the following:
+ </para>
+
<programlisting>
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
</programlisting>
<para>
Otherwise — that is, if the <literal>TIMESTAMP</literal>
- column is defined using <literal>NULL</literal> but not using
- <literal>DEFAULT TIMESTAMP</literal>, as shown here…
+ column is defined to allow <literal>NULL</literal> values but
+ not using <literal>DEFAULT TIMESTAMP</literal>, as shown
+ here…
</para>
<programlisting>
@@ -3147,12 +3173,11 @@
<literal>'HHH:MM:SS'</literal> format for large hours values).
<literal>TIME</literal> values may range from
<literal>'-838:59:59'</literal> to
- <literal>'838:59:59'</literal>. The reason for which the hours
- part may be so large is that the <literal>TIME</literal> type
- may be used not only to represent a time of day (which must be
- less than 24 hours), but also elapsed time or a time interval
- between two events (which may be much greater than 24 hours, or
- even negative).
+ <literal>'838:59:59'</literal>. The hours part may be so large
+ because the <literal>TIME</literal> type can be used not only to
+ represent a time of day (which must be less than 24 hours), but
+ also elapsed time or a time interval between two events (which
+ may be much greater than 24 hours, or even negative).
</para>
<para>
@@ -3238,24 +3263,27 @@
</para>
<para>
- Values that lie outside the <literal>TIME</literal> range but
- are otherwise legal are clipped to the closest endpoint of the
- range. For example, <literal>'-850:00:00'</literal> and
+ By default, values that lie outside the <literal>TIME</literal>
+ range but are otherwise legal are clipped to the closest
+ endpoint of the range. For example,
+ <literal>'-850:00:00'</literal> and
<literal>'850:00:00'</literal> are converted to
<literal>'-838:59:59'</literal> and
- <literal>'838:59:59'</literal>.
- </para>
-
- <para>
- Illegal <literal>TIME</literal> values are converted to
- <literal>'00:00:00'</literal>. Note that because
- <literal>'00:00:00'</literal> is itself a legal
+ <literal>'838:59:59'</literal>. Illegal <literal>TIME</literal>
+ values are converted to <literal>'00:00:00'</literal>. Note that
+ because <literal>'00:00:00'</literal> is itself a legal
<literal>TIME</literal> value, there is no way to tell, from a
value of <literal>'00:00:00'</literal> stored in a table,
whether the original value was specified as
<literal>'00:00:00'</literal> or whether it was illegal.
</para>
+ <para>
+ For more restrictive treatment of invalid
+ <literal>TIME</literal> values, enable strict SQL mode to cause
+ errors to occur. See <xref linkend="server-sql-mode"/>.
+ </para>
+
</section>
<section id="year">
@@ -3373,12 +3401,12 @@
</indexterm>
<para>
- MySQL itself is year 2000 (Y2K) safe (see
- <xref linkend="year-2000-compliance"/>), but input values
- presented to MySQL may not be. Any input containing two-digit
- year values is ambiguous, because the century is unknown. Such
- values must be interpreted into four-digit form because MySQL
- stores years internally using four digits.
+ As discussed in <xref linkend="year-2000-compliance"/>, MySQL
+ itself is year 2000 (Y2K) safe. However, particular input values
+ presented to MySQL may not be Y2K safe. Any value containing a
+ two-digit year is ambiguous, because the century is unknown.
+ Such values must be interpreted into four-digit form because
+ MySQL stores years internally using four digits.
</para>
<para>
@@ -3407,10 +3435,11 @@
</itemizedlist>
<para>
- Remember that these rules provide only reasonable guesses as to
- what your data values mean. If the heuristics used by MySQL do
- not produce the correct values, you should provide unambiguous
- input containing four-digit year values.
+ Remember that these rules are only heuristics that provide
+ reasonable guesses as to what your data values mean. If the
+ rules used by MySQL do not produce the correct values, you
+ should provide unambiguous input containing four-digit year
+ values.
</para>
<para>
Modified: trunk/refman-5.1/data-types.xml
===================================================================
--- trunk/refman-5.1/data-types.xml 2006-01-19 04:38:42 UTC (rev 922)
+++ trunk/refman-5.1/data-types.xml 2006-01-19 04:38:55 UTC (rev 923)
@@ -2599,9 +2599,8 @@
<literal>2037</literal>. This means that a date such as
<literal>'1968-01-01'</literal>, while legal as a
<literal>DATETIME</literal> or <literal>DATE</literal>
- value, is not a valid <literal>TIMESTAMP</literal> value and
- is converted to <literal>0</literal> if assigned to such an
- object.
+ value, is not valid as a <literal>TIMESTAMP</literal> value
+ and is converted to <literal>0</literal>.
</para>
</listitem>
@@ -2701,12 +2700,12 @@
</para>
<para>
- The MySQL server can be also be run in
- <literal>MAXDB</literal> mode. When the server runs in this
- mode, <literal>TIMESTAMP</literal> is identical with
- <literal>DATETIME</literal>. That is, if the server is running
- in <literal>MAXDB</literal> mode at the time that a table is
- created, <literal>TIMESTAMP</literal> columns are created as
+ The MySQL server can be also be run with the
+ <literal>MAXDB</literal> SQL mode enabled. When the server
+ runs with this mode enabled, <literal>TIMESTAMP</literal> is
+ identical with <literal>DATETIME</literal>. That is, if this
+ mode is enabled at the time that a table is created,
+ <literal>TIMESTAMP</literal> columns are created as
<literal>DATETIME</literal> columns. As a result, such columns
use <literal>DATETIME</literal> display format, have the same
range of values, and there is no automatic initialization or
@@ -2735,6 +2734,14 @@
</programlisting>
<para>
+ Note that the information in the following discussion applies
+ to <literal>TIMESTAMP</literal> columns only for tables not
+ created with <literal>MAXDB</literal> mode enabled, because
+ such columns are created as <literal>DATETIME</literal>
+ columns.
+ </para>
+
+ <para>
MySQL does not accept timestamp values that include a zero in
the day or month column or values that are not a valid date.
The sole exception to this rule is the special value
@@ -2751,11 +2758,14 @@
<listitem>
<para>
- You can assign the current timestamp as the default value
- and the auto-update value, as before. But it is possible
- to have just one automatic behavior or the other, or
- neither of them. (It is not possible to have one behavior
- for one column and the other for another column.)
+ For one <literal>TIMESTAMP</literal> column in a table,
+ you can assign the current timestamp as the default value
+ and the auto-update value. It is possible to have the
+ current timestamp be the default value for initializing
+ the column, for the auto-update value, or both. It is not
+ possible to have the current timestamp be the default
+ value for one column and the auto-update value for another
+ column.
</para>
</listitem>
@@ -2763,7 +2773,7 @@
<para>
You can specify which <literal>TIMESTAMP</literal> column
to automatically initialize or update to the current date
- and time. This no longer need be the first
+ and time. This need not be the first
<literal>TIMESTAMP</literal> column.
</para>
</listitem>
@@ -2771,13 +2781,8 @@
</itemizedlist>
<para>
- Note that the information in the following discussion applies
- to <literal>TIMESTAMP</literal> columns only for tables not
- created with <literal>MAXDB</literal> mode enabled. (As noted
- above, <literal>MAXDB</literal> mode causes columns to be
- created as <literal>DATETIME</literal> columns.) The rules
- governing initialization and updating of
- <literal>TIMESTAMP</literal> columns are as follows:
+ The following rules govern initialization and updating of
+ <literal>TIMESTAMP</literal> columns:
</para>
<itemizedlist>
@@ -2806,8 +2811,8 @@
<listitem>
<para>
Any single <literal>TIMESTAMP</literal> column in a table
- can be set to be the one that is initialized to the
- current timestamp or updated automatically.
+ can be used as the one that is initialized to the current
+ timestamp or updated automatically.
</para>
</listitem>
@@ -2882,21 +2887,19 @@
<literal>CURRENT_TIMESTAMP()</literal>, or
<literal>NOW()</literal> can be used in the
<literal>DEFAULT</literal> and <literal>ON
- UPDATE</literal> clauses. They all have the same effect.
+ UPDATE</literal> clauses. They all mean <quote>the current
+ timestamp.</quote>
</para>
<para>
- The order of the two attributes does not matter. If both
- <literal>DEFAULT</literal> and <literal>ON
+ The order of the <literal>DEFAULT</literal> and
+ <literal>ON UPDATE</literal> attributes does not matter.
+ If both <literal>DEFAULT</literal> and <literal>ON
UPDATE</literal> are specified for a
<literal>TIMESTAMP</literal> column, either can precede
- the other.
+ the other. For example, these statements are equivalent:
</para>
- <para>
- Example. These statements are equivalent:
- </para>
-
<programlisting>
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
@@ -2918,12 +2921,16 @@
<literal>DEFAULT '2003-01-01 00:00:00'</literal>). Then,
for the other <literal>TIMESTAMP</literal> column, the
rules are the same as for the first
- <literal>TIMESTAMP</literal> column, except that you
- cannot omit both of the <literal>DEFAULT</literal> and
- <literal>ON UPDATE</literal> clauses. If you do so, no
- automatic initialization or updating occurs.
+ <literal>TIMESTAMP</literal> column, except that if you
+ omit both of the <literal>DEFAULT</literal> and
+ <literal>ON UPDATE</literal> clauses, no automatic
+ initialization or updating occurs.
</para>
+ <remark role="todo">
+ What is this supposed to be an example OF? [pd]
+ </remark>
+
<para>
Example. These statements are equivalent:
</para>
@@ -2950,12 +2957,12 @@
converted back to the current time zone upon retrieval. As
long as the time zone setting remains constant, you get back
the same value you store. If you store a
- <literal>TIMESTAMP</literal> value, change the time zone and
- then retrieve the value, it is different than the value you
- stored. This occurs because the same time zone was not used
- for conversion in both directions. The current time zone is
- available as the value of the <literal>time_zone</literal>
- system variable.
+ <literal>TIMESTAMP</literal> value, and then change the time
+ zone and retrieve the value, the retrieved value is different
+ than the value you stored. This occurs because the same time
+ zone was not used for conversion in both directions. The
+ current time zone is available as the value of the
+ <literal>time_zone</literal> system variable.
</para>
<para>
@@ -2979,25 +2986,44 @@
setting the column to <literal>NULL</literal> sets it to the
current timestamp. Note that a <literal>TIMESTAMP</literal>
column which allows <literal>NULL</literal> values will
- <emphasis role="bold">not</emphasis> take on the current
- timestamp unless either its default value is defined as
- <literal>CURRENT_TIMESTAMP</literal>,
- <emphasis role="bold">or</emphasis> either
- <literal>NOW()</literal> or
- <literal>CURRENT_TIMESTAMP</literal> is inserted into the
- column. In other words, a <literal>TIMESTAMP</literal> column
- defined as <literal>NULL</literal> will auto-update only if it
- is created using a definition such as the following:
+ <emphasis>not</emphasis> take on the current timestamp except
+ under one of the following conditions:
</para>
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Its default value is defined as
+ <literal>CURRENT_TIMESTAMP</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>NOW()</literal> or
+ <literal>CURRENT_TIMESTAMP</literal> is inserted into the
+ column
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, a <literal>TIMESTAMP</literal> column defined
+ as <literal>NULL</literal> will auto-update only if it is
+ created using a definition such as the following:
+ </para>
+
<programlisting>
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
</programlisting>
<para>
Otherwise — that is, if the <literal>TIMESTAMP</literal>
- column is defined using <literal>NULL</literal> but not using
- <literal>DEFAULT TIMESTAMP</literal>, as shown here…
+ column is defined to allow <literal>NULL</literal> values but
+ not using <literal>DEFAULT TIMESTAMP</literal>, as shown
+ here…
</para>
<programlisting>
@@ -3038,12 +3064,11 @@
<literal>'HHH:MM:SS'</literal> format for large hours values).
<literal>TIME</literal> values may range from
<literal>'-838:59:59'</literal> to
- <literal>'838:59:59'</literal>. The reason for which the hours
- part may be so large is that the <literal>TIME</literal> type
- may be used not only to represent a time of day (which must be
- less than 24 hours), but also elapsed time or a time interval
- between two events (which may be much greater than 24 hours, or
- even negative).
+ <literal>'838:59:59'</literal>. The hours part may be so large
+ because the <literal>TIME</literal> type can be used not only to
+ represent a time of day (which must be less than 24 hours), but
+ also elapsed time or a time interval between two events (which
+ may be much greater than 24 hours, or even negative).
</para>
<para>
@@ -3129,24 +3154,27 @@
</para>
<para>
- Values that lie outside the <literal>TIME</literal> range but
- are otherwise legal are clipped to the closest endpoint of the
- range. For example, <literal>'-850:00:00'</literal> and
+ By default, values that lie outside the <literal>TIME</literal>
+ range but are otherwise legal are clipped to the closest
+ endpoint of the range. For example,
+ <literal>'-850:00:00'</literal> and
<literal>'850:00:00'</literal> are converted to
<literal>'-838:59:59'</literal> and
- <literal>'838:59:59'</literal>.
- </para>
-
- <para>
- Illegal <literal>TIME</literal> values are converted to
- <literal>'00:00:00'</literal>. Note that because
- <literal>'00:00:00'</literal> is itself a legal
+ <literal>'838:59:59'</literal>. Illegal <literal>TIME</literal>
+ values are converted to <literal>'00:00:00'</literal>. Note that
+ because <literal>'00:00:00'</literal> is itself a legal
<literal>TIME</literal> value, there is no way to tell, from a
value of <literal>'00:00:00'</literal> stored in a table,
whether the original value was specified as
<literal>'00:00:00'</literal> or whether it was illegal.
</para>
+ <para>
+ For more restrictive treatment of invalid
+ <literal>TIME</literal> values, enable strict SQL mode to cause
+ errors to occur. See <xref linkend="server-sql-mode"/>.
+ </para>
+
</section>
<section id="year">
@@ -3264,12 +3292,12 @@
</indexterm>
<para>
- MySQL itself is year 2000 (Y2K) safe (see
- <xref linkend="year-2000-compliance"/>), but input values
- presented to MySQL may not be. Any input containing two-digit
- year values is ambiguous, because the century is unknown. Such
- values must be interpreted into four-digit form because MySQL
- stores years internally using four digits.
+ As discussed in <xref linkend="year-2000-compliance"/>, MySQL
+ itself is year 2000 (Y2K) safe. However, particular input values
+ presented to MySQL may not be Y2K safe. Any value containing a
+ two-digit year is ambiguous, because the century is unknown.
+ Such values must be interpreted into four-digit form because
+ MySQL stores years internally using four digits.
</para>
<para>
@@ -3298,10 +3326,11 @@
</itemizedlist>
<para>
- Remember that these rules provide only reasonable guesses as to
- what your data values mean. If the heuristics used by MySQL do
- not produce the correct values, you should provide unambiguous
- input containing four-digit year values.
+ Remember that these rules are only heuristics that provide
+ reasonable guesses as to what your data values mean. If the
+ rules used by MySQL do not produce the correct values, you
+ should provide unambiguous input containing four-digit year
+ values.
</para>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r923 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 19 Jan |
