Below is the list of changes that have just been committed into a local
mysqldoc repository of jon. When jon does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.2984 05/07/12 04:08:57 jon@stripped +3 -0
RefMan 5.0 edits for column-types chapter; cut section timestamp-pre-4-1.
Correction to tutorial chapter for 5.0.
Noted ID of deleted section.
refman-5.0/tutorial.xml
1.5 05/07/12 04:08:55 jon@stripped +7 -5
Corrected max size of VARCHAR type.
refman-5.0/deleted-sections.txt
1.8 05/07/12 04:08:55 jon@stripped +3 -0
Added ID of section deleted from column-types.xml.
refman-5.0/column-types.xml
1.7 05/07/12 04:08:55 jon@stripped +522 -987
RefMan 5.0 edits; cut section timestamp-pre-4-1.
(Operation Bifurcation continues...)
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: jon
# Host: gigan.
# Root: /home/jon/bk/mysqldoc
--- 1.7/refman-5.0/deleted-sections.txt 2005-07-03 18:47:57 +10:00
+++ 1.8/refman-5.0/deleted-sections.txt 2005-07-12 04:08:55 +10:00
@@ -318,3 +318,6 @@
password-hashing-4-1-0
update-log
+# from column-types.xml:
+
+timestamp-pre-4-1
\ No newline at end of file
--- 1.6/refman-5.0/column-types.xml 2005-06-26 11:27:18 +10:00
+++ 1.7/refman-5.0/column-types.xml 2005-07-12 04:08:55 +10:00
@@ -11,11 +11,11 @@
<title id='title-column-types'>&title-column-types;</title>
-<!-- TODO: rename to Data types; values of these types can be used independently -->
-
-<!-- of being stored in table columns. -->
-
-<!-- help_category Column Types -->
+<!--
+ TODO: rename to Data types; values of these types can be used
+ independently of being stored in table columns. help_category
+ Column Types
+-->
<para>
MySQL supports a number of column types in several categories: numeric
@@ -29,8 +29,8 @@
</para>
<para>
- MySQL versions 4.1 and up support extensions for handing spatial data.
- Information about spatial types is provided in
+ MySQL 5.0 supports extensions for handing spatial data. Information
+ about spatial types is provided in
<xref linkend="spatial-extensions-in-mysql"/>.
</para>
@@ -77,6 +77,8 @@
<secondary>display</secondary>
</indexterm>
+<!-- TODO: Convert to an informaltable? -->
+
<listitem><para>
<literal><replaceable>M</replaceable></literal>
</para>
@@ -270,10 +272,8 @@
</para>
<para>
- These are synonyms for <literal>TINYINT(1)</literal>. The
- <literal>BOOLEAN</literal> synonym was added in MySQL 4.1.0. A
- value of zero is considered false. Non-zero values are considered
- true.
+ These are synonyms for <literal>TINYINT(1)</literal>. A value of
+ zero is considered false. Non-zero values are considered true.
</para>
<para>
@@ -441,7 +441,7 @@
</para>
<para>
- MySQL 4.0 can handle <literal>BIGINT</literal> in the following
+ MySQL 5.0 can handle <literal>BIGINT</literal> in the following
cases:
</para>
@@ -523,10 +523,7 @@
</para>
<para>
- As of MySQL 3.23, this is a true floating-point value. In earlier
- MySQL versions,
- <literal>FLOAT(<replaceable>p</replaceable>)</literal> always has
- two decimals.
+ In MySQL 5.0, this is a true floating-point value.
</para>
<para>
@@ -694,7 +691,7 @@
</para>
<para>
- A packed ``exact'' fixed-point number. <replaceable>M</replaceable>
+ A packed, exact fixed-point number. <replaceable>M</replaceable>
is the total number of digits and <replaceable>D</replaceable> is
the number of decimals. The decimal point and (for negative
numbers) the '<literal>-</literal>' sign are not counted in
@@ -724,20 +721,20 @@
<para>
An unpacked fixed-point number. Behaves like a
- <literal>CHAR</literal> column; ``unpacked'' means the number is
- stored as a string, using one character for each digit of the
- value. <replaceable>M</replaceable> is the total number of digits
- and <replaceable>D</replaceable> is the number of decimals. The
- decimal point and (for negative numbers) the '<literal>-</literal>'
- sign are not counted in <replaceable>M</replaceable>, although
- space for them is reserved. If <replaceable>D</replaceable> is 0,
- values have no decimal point or fractional part. The maximum range
- of <literal>DECIMAL</literal> values is the same as for
- <literal>DOUBLE</literal>, but the actual range for a given
- <literal>DECIMAL</literal> column may be constrained by the choice
- of <replaceable>M</replaceable> and <replaceable>D</replaceable>.
- If <literal>UNSIGNED</literal> is specified, negative values are
- disallowed.
+ <literal>CHAR</literal> column; "unpacked" means that the
+ number is stored as a string, using one character for each digit of
+ the value. <replaceable>M</replaceable> is the total number of
+ digits and <replaceable>D</replaceable> is the number of decimals.
+ The decimal point and (for negative numbers) the
+ '<literal>-</literal>' sign are not counted in
+ <replaceable>M</replaceable>, although space for them is reserved.
+ If <replaceable>D</replaceable> is 0, values have no decimal point
+ or fractional part. The maximum range of <literal>DECIMAL</literal>
+ values is the same as for <literal>DOUBLE</literal>, but the actual
+ range for a given <literal>DECIMAL</literal> column may be
+ constrained by the choice of <replaceable>M</replaceable> and
+ <replaceable>D</replaceable>. If <literal>UNSIGNED</literal> is
+ specified, negative values are disallowed.
</para>
<para>
@@ -746,16 +743,6 @@
</para>
<para>
- Before MySQL 3.23:
- </para>
-
- <para>
- As just described, with the exception that the
- <replaceable>M</replaceable> value must be large enough to include
- the space needed for the sign and the decimal point characters.
- </para>
-
- <para>
<indexterm type="type">
<primary>DEC data type</primary>
</indexterm>
@@ -795,8 +782,8 @@
<para>
These are synonyms for <literal>DECIMAL</literal>. The
- <literal>FIXED</literal> synonym was added in MySQL 4.1.0 for
- compatibility with other servers.
+ <literal>FIXED</literal> synonym is available for compatibility
+ with other servers.
<!-- end_description_for_help_topic -->
</para></listitem>
@@ -911,48 +898,15 @@
</para>
<para>
- From MySQL 4.1 on, <literal>TIMESTAMP</literal> is returned as a
- string with the format <literal>'YYYY-MM-DD HH:MM:SS'</literal>.
- Display widths (used as described in the following paragraphs) are no
- longer supported; the display width is fixed at 19 characters.
- If you want to obtain the value as a number, you should add
- <literal>+0</literal> to the timestamp column.
- </para>
-
- <para>
- In MySQL 4.0 and earlier, <literal>TIMESTAMP</literal> values are
- displayed in <literal>YYYYMMDDHHMMSS</literal>,
- <literal>YYMMDDHHMMSS</literal>, <literal>YYYYMMDD</literal>, or
- <literal>YYMMDD</literal> format, depending on whether
- <replaceable>M</replaceable> is 14 (or missing), 12, 8, or 6, but
- allows you to assign values to <literal>TIMESTAMP</literal> columns
- using either strings or numbers. The <replaceable>M</replaceable>
- argument affects only how a <literal>TIMESTAMP</literal> column is
- displayed, not storage. Its values always are stored using four
- bytes each. From MySQL 4.0.12, the <literal>--new</literal> option
- can be used to make the server behave as in MySQL 4.1.
- </para>
-
- <para>
- Note that
- <literal>TIMESTAMP(<replaceable>M</replaceable>)</literal> columns
- where <replaceable>M</replaceable> is 8 or 14 are reported to be
- numbers, whereas other
- <literal>TIMESTAMP(<replaceable>M</replaceable>)</literal> columns
- are reported to be strings. This is just to ensure that you can
- reliably dump and restore the table with these types.
+ In MySQL 5.0, <literal>TIMESTAMP</literal> is returned as a string
+ in the format <literal>'YYYY-MM-DD HH:MM:SS'</literal>whose display
+ width is fixed at 19 characters. If you want to obtain the value as
+ a number, you should add <literal>+0</literal> to the timestamp
+ column.
<!-- end_description_for_help_topic -->
</para>
- <para><emphasis role="bold">Note</emphasis>: The behaviour of
- <literal>TIMESTAMP</literal> columns changed considerably in MySQL
- 4.1. For complete information on the differences with regard to this
- column type in MySQL 4.1 and later versions (as opposed to MySQL 4.0
- and earlier versions), be sure to see
- <xref linkend="timestamp-pre-4-1"/> and
- <xref linkend="timestamp-4-1"/>.</para>
-
<para>
<!-- description_for_help_topic TIME TIME -->
</para>
@@ -1010,8 +964,7 @@
years from 1970 to 2069. MySQL displays <literal>YEAR</literal>
values in <literal>YYYY</literal> format, but allows you to assign
values to <literal>YEAR</literal> columns using either strings or
- numbers. The <literal>YEAR</literal> type is unavailable prior to
- MySQL 3.22.
+ numbers.
<!-- end_description_for_help_topic -->
</para></listitem>
@@ -1038,7 +991,8 @@
</para>
<para>
- As of MySQL 4.1, several changes affect string data types:
+ MySQL 5.0 string data types include some features that you may not
+ have encountered in working with versions of MySQL prior to 4.1:
</para>
<itemizedlist>
@@ -1071,19 +1025,16 @@
</para></listitem>
<listitem><para>
- MySQL 4.1, MySQL interprets length specifications in character
- column definitions in character units. Earlier versions interpret
- lengths in byte units.
+ MySQL 5.0 interprets length specifications in character
+ column definitions in character units. (Some earlier MySQL versions
+ interpreted lengths in bytes.)
</para></listitem>
<listitem><para>
For <literal>CHAR</literal>, <literal>VARCHAR</literal>, and the
<literal>TEXT</literal> types, the <literal>BINARY</literal>
attribute causes the column to be assigned the binary collation of
- the column character set. For earlier versions,
- <literal>BINARY</literal> causes <literal>CHAR</literal> and
- <literal>VARCHAR</literal> to be treated as binary strings and is
- disallowed for the <literal>TEXT</literal> types.
+ the column character set.
</para></listitem>
<listitem><para>
@@ -1099,8 +1050,8 @@
</itemizedlist>
<para>
- For more details about character set support in MySQL 4.1 and up,
- see <xref linkend="charset"/>.
+ For more about character set support in MySQL 5.0, see
+ <xref linkend="charset"/>.
</para>
<itemizedlist>
@@ -1152,8 +1103,7 @@
A fixed-length string that is always right-padded with spaces to
the specified length when stored. <replaceable>M</replaceable>
represents the column length. The range of
- <replaceable>M</replaceable> is 0 to 255 characters (1 to 255 prior
- to MySQL 3.23).
+ <replaceable>M</replaceable> in MySQL 5.0 is 0 to 255 characters.
</para>
<para>
@@ -1162,8 +1112,8 @@
</para>
<para>
- From MySQL 4.1.0 to 5.0.2, a <literal>CHAR</literal> column with a
- length specification greater than 255 is converted to the smallest
+ Before MySQL 5.0.3, a <literal>CHAR</literal> column with a length
+ specification greater than 255 is converted to the smallest
<literal>TEXT</literal> type that can hold values of the given
length. For example, <literal>CHAR(500)</literal> is converted to
<literal>TEXT</literal>, and <literal>CHAR(200000)</literal> is
@@ -1181,29 +1131,25 @@
</para>
<para>
- As of MySQL 4.1.2, the <literal>BINARY</literal> attribute is
- shorthand for specifying the binary collation of the column
- character set. Sorting and comparison is based on numeric character
- values. Before 4.1.2, <literal>BINARY</literal> attribute causes
- the column to be treated as a binary string. Sorting and comparison
- is based on numeric byte values.
+ The <literal>BINARY</literal> attribute is shorthand for specifying
+ the binary collation of the column character set. Sorting and
+ comparison is based on numeric character values.
</para>
<para>
- From MySQL 4.1.0 on, column type <literal>CHAR BYTE</literal> is an
- alias for <literal>CHAR BINARY</literal>. This is a compatibility
- feature.
+ The column type <literal>CHAR BYTE</literal> is an alias for
+ <literal>CHAR BINARY</literal>. This is a compatibility feature.
</para>
<para>
- From MySQL 4.1.0 on, the <literal>ASCII</literal> attribute can be
- specified for <literal>CHAR</literal>. It assigns the
- <literal>latin1</literal> character set.
+ The <literal>ASCII</literal> attribute can be specified for
+ <literal>CHAR</literal>. It assigns the <literal>latin1</literal>
+ character set.
</para>
<para>
- From MySQL 4.1.1 on, the <literal>UNICODE</literal> attribute can
- be specified for <literal>CHAR</literal>. It assigns the
+ The <literal>UNICODE</literal> attribute can be specified in MySQL
+ 5.0 for <literal>CHAR</literal>. It assigns the
<literal>ucs2</literal> character set.
</para>
@@ -1278,12 +1224,13 @@
<para>
A variable-length string. <replaceable>M</replaceable> represents
- the maximum column length. The range of
- <replaceable>M</replaceable> is 1 to 255 before MySQL 4.0.2, 0 to
- 255 as of MySQL 4.0.2, and 0 to 65,535 as of MySQL 5.0.3. (The
- maximum actual length of a <literal>VARCHAR</literal> in MySQL 5.0
- is determined by the maximum row size and the character set you
- use. The maximum effective length is 65,532 bytes.)
+ the maximum column length. In MySQL 5.0, the range of
+ <replaceable>M</replaceable> is 0 to 255 before MySQL 5.0.3, and 0
+ to 65,535 in MySQL 5.0.3 and later. (The maximum actual length of a
+ <literal>VARCHAR</literal> in MySQL 5.0 is determined by the
+ maximum row size and the character set you use. The maximum
+ <emphasis>effective</emphasis> length starting with MySQL 5.0.3 is
+ 65,532 bytes.)
</para>
<para>
@@ -1293,14 +1240,14 @@
</para>
<para>
- From MySQL 4.1.0 to 5.0.2, a <literal>VARCHAR</literal> column with
- a length specification greater than 255 is converted to the
- smallest <literal>TEXT</literal> type that can hold values of the
- given length. For example, <literal>VARCHAR(500)</literal> is
+ Previus to MySQL 5.0.3, a <literal>VARCHAR</literal> column with
+ a length specification greater than 255 was converted to the
+ smallest <literal>TEXT</literal> type that could hold values of the
+ given length. For example, <literal>VARCHAR(500)</literal> was
converted to <literal>TEXT</literal>, and
- <literal>VARCHAR(200000)</literal> is converted to
- <literal>MEDIUMTEXT</literal>. This is a compatibility feature.
- However, this conversion affects trailing-space removal.
+ <literal>VARCHAR(200000)</literal> was converted to
+ <literal>MEDIUMTEXT</literal>. This was a compatibility feature.
+ However, this conversion affected trailing-space removal.
</para>
<para>
@@ -1309,12 +1256,9 @@
</para>
<para>
- As of MySQL 4.1.2, the <literal>BINARY</literal> attribute is
- shorthand for specifying the binary collation of the column
- character set. Sorting and comparison is based on numeric character
- values. Before 4.1.2, <literal>BINARY</literal> attribute causes
- the column to be treated as a binary string. Sorting and comparison
- is based on numeric byte values.
+ In MySQL 5.0, the <literal>BINARY</literal> attribute is shorthand
+ for specifying the binary collation of the column character set.
+ Sorting and comparison is based on numeric character values.
</para>
<para>
@@ -1351,10 +1295,6 @@
The <literal>BINARY</literal> type is similar to the
<literal>CHAR</literal> type, but stores binary byte strings rather
than non-binary character strings.
- </para>
-
- <para>
- This type was added in MySQL 4.1.2.
<!-- end_description_for_help_topic -->
</para>
@@ -1382,10 +1322,6 @@
The <literal>VARBINARY</literal> type is similar to the
<literal>VARCHAR</literal> type, but stores binary byte strings
rather than non-binary character strings.
- </para>
-
- <para>
- This type was added in MySQL 4.1.2.
<!-- end_description_for_help_topic -->
</para>
@@ -1467,10 +1403,10 @@
</para>
<para>
- Beginning with MySQL 4.1, an optional length
- <replaceable>M</replaceable> can be given. MySQL will create the
- column as the smallest <literal>BLOB</literal> type largest enough
- to hold values <replaceable>M</replaceable> bytes long.
+ An optional length <replaceable>M</replaceable> can be given for
+ this type in MySQL 5.0. If this is done, then MySQL will create the
+ column as the smallest <literal>BLOB</literal> type large enough to
+ hold values of <replaceable>M</replaceable> bytes.
<!-- end_description_for_help_topic -->
</para>
@@ -1500,10 +1436,10 @@
</para>
<para>
- Beginning with MySQL 4.1, an optional length
- <replaceable>M</replaceable> can be given. MySQL will create the
- column as the smallest <literal>TEXT</literal> type largest enough
- to hold values <replaceable>M</replaceable> characters long.
+ In MySQL 5.0, an optional length <replaceable>M</replaceable> can
+ be given. Then MySQL will create the column as the smallest
+ <literal>TEXT</literal> type large enough to hold values
+ <replaceable>M</replaceable> characters long.
<!-- end_description_for_help_topic -->
</para>
@@ -1581,12 +1517,11 @@
<para>
A <literal>BLOB</literal> column with a maximum length of
- 4,294,967,295 or 4GB (2^32 - 1) bytes. Up to MySQL 3.23, the
- client/server protocol and <literal>MyISAM</literal> tables had a
- limit of 16MB per communication packet / table row. From MySQL 4.0,
- the maximum allowed length of <literal>LONGBLOB</literal> columns
- depends on the configured maximum packet size in the client/server
- protocol and available memory.
+ 4,294,967,295 or 4GB (2^32 - 1) bytes. The maximum
+ <emphasis>effective</emphasis> (permitted) length of
+ <literal>LONGBLOB</literal> columns depends on the configured
+ maximum packet size in the client/server protocol and available
+ memory.
<!-- end_description_for_help_topic -->
</para>
@@ -1612,12 +1547,11 @@
<para>
A <literal>TEXT</literal> column with a maximum length of
- 4,294,967,295 or 4GB (2^32 - 1) characters. Up to MySQL 3.23, the
- client/server protocol and <literal>MyISAM</literal> tables had a
- limit of 16MB per communication packet / table row. From MySQL 4.0,
- the maximum allowed length of <literal>LONGTEXT</literal> columns
- depends on the configured maximum packet size in the client/server
- protocol and available memory.
+ 4,294,967,295 or 4GB (2^32 - 1) characters. The maximum
+ <emphasis>effective</emphasis> (permitted) length of
+ <literal>LONGTEXT</literal> columns depends on the configured
+ maximum packet size in the client/server protocol and available
+ memory.
<!-- end_description_for_help_topic -->
</para>
@@ -1919,7 +1853,7 @@
</para>
<para>
- As of MySQL 4.0.2, floating-point and fixed-point types also can be
+ Floating-point and fixed-point types also can be
<literal>UNSIGNED</literal>. As with integer types, this attribute
prevents negative values from being stored in the column. However,
unlike the integer types, the upper range of column values remains
@@ -2026,31 +1960,12 @@
case, therefore, the range of values that can be stored in the
<literal>salary</literal> column is from <literal>-999.99</literal>
to <literal>999.99</literal>. MySQL enforces this limit as of MySQL
- 5.0.3. Before 5.0.3, MySQL varies from this limit in two ways due to
- the use of string format for value storage:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- On the positive end of the range, the column actually can store
- numbers up to <literal>9999.99</literal>. For positive numbers,
- MySQL uses the byte reserved for the sign to extend the upper end of
- the range.
- </para></listitem>
-
- <listitem><para>
- <literal>DECIMAL</literal> columns in MySQL before 3.23 are stored
- differently and cannot represent all the values required by standard
- SQL. This is because for a type of
- <literal>DECIMAL(<replaceable>M</replaceable>,<replaceable>D</replaceable>)</literal>,
- the value of <replaceable>M</replaceable> includes the bytes for the
- sign and the decimal point. The range of the
- <literal>salary</literal> column before MySQL 3.23 would be
- <literal>-9.99</literal> to <literal>99.99</literal>.
- </para></listitem>
-
- </itemizedlist>
+ 5.0.3. Before 5.0.3, MySQL 5.0 varied from this limit in that, on the
+ positive end of the range, the column could actually store numbers up
+ to <literal>9999.99</literal>. (For positive numbers, MySQL 5.0.2 and
+ earlier used the byte reserved for the sign to extend the upper end
+ of the range.)
+ </para>
<para>
In standard SQL, the syntax
@@ -2060,12 +1975,10 @@
Similarly, the syntax <literal>DECIMAL</literal> is equivalent to
<literal>DECIMAL(<replaceable>M</replaceable>,0)</literal>, where the
implementation is allowed to decide the value of
- <replaceable>M</replaceable>. As of MySQL 3.23.6, both of these
- variant forms of the <literal>DECIMAL</literal> and
- <literal>NUMERIC</literal> data types are supported. The default
- value of <replaceable>M</replaceable> is 10. Before 3.23.6,
- <replaceable>M</replaceable> and <replaceable>D</replaceable> both
- must be specified explicitly.
+ <replaceable>M</replaceable>. Both of these variant forms of the
+ <literal>DECIMAL</literal> and <literal>NUMERIC</literal> data types
+ are supported in MySQL 5.0. The default value of
+ <replaceable>M</replaceable> is 10.
</para>
<para>
@@ -2093,7 +2006,7 @@
To specify bit values,
<literal>b'<replaceable>value</replaceable>'</literal> notation can
be used. <replaceable>value</replaceable> is a binary value written
- using 0s and 1s. For example, <literal>b'111'</literal> and
+ using zeroes and ones. For example, <literal>b'111'</literal> and
<literal>b'100000000'</literal> represent 7 and 128, respectively.
See <xref linkend="bit-field-values"/>.
</para>
@@ -2102,11 +2015,13 @@
If you assign a value to a
<literal>BIT(<replaceable>M</replaceable>)</literal> column that is
less than <replaceable>M</replaceable> bits long, the value is padded
- on the left with 0-valued bits. For example, assigning a value of
+ on the left with zeroes. For example, assigning a value of
<literal>b'101'</literal> to a <literal>BIT(6)</literal> column is,
in effect, the same as assigning <literal>b'000101'</literal>.
</para>
+<!-- TODO: Isn't this behaviour now dependent upon the SQL mode? -->
+
<para>
When asked to store a value in a numeric column that is outside the
column type's allowable range, MySQL clips the value to the
@@ -2143,7 +2058,7 @@
</para>
<para>
- Conversions that occur due to clipping are reported as ``warnings''
+ Conversions that occur due to clipping are reported as warnings
for <literal>ALTER TABLE</literal>, <literal>LOAD DATA
INFILE</literal>, <literal>UPDATE</literal>, and multiple-row
<literal>INSERT</literal> statements.
@@ -2155,9 +2070,10 @@
<title id='title-date-and-time-types'>&title-date-and-time-types;</title>
-<!-- TODO: this doesn't have any storage size table, unlike the numeric -->
-
-<!-- and string types section. -->
+<!--
+ TODO: this doesn't have any storage size table, unlike the numeric
+ and string types sections.
+-->
<indexterm type="concept">
<primary>types</primary>
@@ -2223,35 +2139,42 @@
<itemizedlist>
- <listitem><para>
- MySQL retrieves values for a given date or time type in a standard
- output format, but it attempts to interpret a variety of formats for
- input values that you supply (for example, when you specify a value
- to be assigned to or compared to a date or time type). Only the
- formats described in the following sections are supported. It is
- expected that you supply legal values, and unpredictable results may
- occur if you use values in other formats.
- </para></listitem>
-
- <listitem><para>
- Dates containing two-digit year values are ambiguous because the
- century is unknown. MySQL interprets two-digit year values using the
- following rules:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- Year values in the range <literal>00-69</literal> are converted to
- <literal>2000-2069</literal>.
- </para></listitem>
-
- <listitem><para>
- Year values in the range <literal>70-99</literal> are converted to
- <literal>1970-1999</literal>.
- </para></listitem>
+ <listitem>
+ <para>
+ MySQL retrieves values for a given date or time type in a
+ standard output format, but it attempts to interpret a variety of
+ formats for input values that you supply (for example, when you
+ specify a value to be assigned to or compared to a date or time
+ type). Only the formats described in the following sections are
+ supported. It is expected that you supply legal values.
+ Unpredictable results may occur if you use values in other
+ formats.
+ </para>
+ </listitem>
- </itemizedlist></listitem>
+ <listitem>
+ <para>
+ Dates containing two-digit year values are ambiguous because the
+ century is unknown. MySQL interprets two-digit year values using the
+ following rules:
+ </para>
+
+ <itemizedlist>
+
+ <listitem><para>
+ Year values in the range <literal>70-99</literal> are
+ converted to
+ <literal>1970-1999</literal>.
+ </para></listitem>
+
+ <listitem><para>
+ Year values in the range <literal>00-69</literal> are
+ converted to <literal>2000-2069</literal>.
+ </para></listitem>
+
+ </itemizedlist>
+
+ </listitem>
<listitem><para>
Although MySQL tries to interpret values in several formats, dates
@@ -2269,14 +2192,14 @@
<listitem><para>
When MySQL encounters a value for a date or time type that is out of
range or otherwise illegal for the type (as described at the
- beginning of this section), it converts the value to the ``zero''
- value for that type. The exception is that out-of-range
+ beginning of this section), it converts the value to the zero value
+ for that type. The exception is that out-of-range
<literal>TIME</literal> values are clipped to the appropriate
endpoint of the <literal>TIME</literal> range.
</para>
<para>
- The following table shows the format of the ``zero'' value for each
+ The following table shows the format of the zero value for each
type. Note that the use of these values produces warnings if the
<literal>NO_ZERO_DATE</literal> SQL mode is enabled.
</para>
@@ -2291,7 +2214,7 @@
<emphasis role="bold">Column Type</emphasis>
</entry>
<entry>
- <emphasis role="bold">``Zero'' Value</emphasis>
+ <emphasis role="bold">Zero Value</emphasis>
</entry>
</row>
<row>
@@ -2339,16 +2262,16 @@
</informaltable></listitem>
<listitem><para>
- The ``zero'' values are special, but you can store or refer to them
+ The zero values are special, but you can store or refer to them
explicitly using the values shown in the table. You can also do this
using the values <literal>'0'</literal> or <literal>0</literal>,
which are easier to write.
</para></listitem>
<listitem><para>
- ``Zero'' date or time values used through MyODBC are converted
+ Zero date or time values used through MyODBC are converted
automatically to <literal>NULL</literal> in MyODBC 2.50.12 and
- above, because ODBC can't handle such values.
+ above, because ODBC cannot handle such values.
</para></listitem>
</itemizedlist>
@@ -2494,9 +2417,9 @@
<para>
Illegal <literal>DATETIME</literal>, <literal>DATE</literal>, or
- <literal>TIMESTAMP</literal> values are converted to the ``zero''
- value of the appropriate type (<literal>'0000-00-00
- 00:00:00'</literal>, <literal>'0000-00-00'</literal>, or
+ <literal>TIMESTAMP</literal> values are converted to the zero value
+ of the appropriate type (<literal>'0000-00-00 00:00:00'</literal>,
+ <literal>'0000-00-00'</literal>, or
<literal>00000000000000</literal>).
</para>
@@ -2543,13 +2466,13 @@
the string. This means you should not use strings that have fewer
than 6 characters. For example, if you specify
<literal>'9903'</literal>, thinking that represents March, 1999,
- MySQL inserts a ``zero'' date into your table. This is because the
+ MySQL inserts a zero date value into your table. This is because the
year and month values are <literal>99</literal> and
<literal>03</literal>, but the day part is completely missing, so
- the value is not a legal date. However, as of MySQL 3.23, you can
- explicitly specify a value of zero to represent missing month or day
- parts. For example, you can use <literal>'990300'</literal> to
- insert the value <literal>'1999-03-00'</literal>.
+ the value is not a legal date. However, you can explicitly specify a
+ value of zero to represent missing month or day parts. For example,
+ you can use <literal>'990300'</literal> to insert the value
+ <literal>'1999-03-00'</literal>.
</para>
<para>
@@ -2615,9 +2538,9 @@
The value <literal>'10:45:15'</literal> is converted to
<literal>'0000-00-00'</literal> because <literal>'45'</literal> is
not a legal month.
- </para>
+ </para></listitem>
-<!-- TODO: change next para when STRICT mode is implemented --></listitem>
+<!-- TODO: change next para when STRICT mode is implemented -->
<listitem><para>
The MySQL server performs only basic checking on the validity of a
@@ -2630,383 +2553,53 @@
application.
</para></listitem>
- <listitem><para>
- Dates containing two-digit year values are ambiguous because the
- century is unknown. MySQL interprets two-digit year values using
- the following rules:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- Year values in the range <literal>00-69</literal> are converted to
- <literal>2000-2069</literal>.
- </para></listitem>
-
- <listitem><para>
- Year values in the range <literal>70-99</literal> are converted to
- <literal>1970-1999</literal>.
- </para></listitem>
-
- </itemizedlist></listitem>
+ <listitem>
+ <para>
+ Dates containing two-digit year values are ambiguous because the
+ century is unknown. MySQL interprets two-digit year values using
+ the following rules:
+ </para>
+
+ <itemizedlist>
+
+ <listitem><para>
+ Year values in the range <literal>00-69</literal> are
+ converted to <literal>2000-2069</literal>.
+ </para></listitem>
+
+ <listitem><para>
+ Year values in the range <literal>70-99</literal> are
+ converted to <literal>1970-1999</literal>.
+ </para></listitem>
+
+ </itemizedlist>
+ </listitem>
</itemizedlist>
- <section id="timestamp-pre-4-1">
-
- <title id='title-timestamp-pre-4-1'>&title-timestamp-pre-4-1;</title>
-
- <para>
- The <literal>TIMESTAMP</literal> column type provides a type that
- you can use to automatically mark <literal>INSERT</literal> or
- <literal>UPDATE</literal> operations with the current date and
- time. If you have multiple <literal>TIMESTAMP</literal> columns in
- a table, only the first one is updated automatically. (From MySQL
- 4.1.2 on, you can specify which <literal>TIMESTAMP</literal> column
- updates; see <xref linkend="timestamp-4-1"/>.)
- </para>
-
- <para>
- Automatic updating of the first <literal>TIMESTAMP</literal> column
- in a table occurs under any of the following conditions:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- You explicitly set the column to <literal>NULL</literal>.
- </para></listitem>
-
- <listitem><para>
- The column is not specified explicitly in an
- <literal>INSERT</literal> or <literal>LOAD DATA INFILE</literal>
- statement.
- </para></listitem>
-
- <listitem><para>
- The column is not specified explicitly in an
- <literal>UPDATE</literal> statement and some other column changes
- value. An <literal>UPDATE</literal> that sets a column to the
- value it does not cause the <literal>TIMESTAMP</literal> column to
- be updated; if you set a column to its current value, MySQL
- ignores the update for efficiency.
- </para></listitem>
-
- </itemizedlist>
-
- <para>
- A <literal>TIMESTAMP</literal> column other than the first also can
- be assigned the current date and time by setting it to
- <literal>NULL</literal> or to any function that produces the
- current date and time (<literal>NOW()</literal>,
- <literal>CURRENT_TIMESTAMP</literal>).
- </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 first
- <literal>TIMESTAMP</literal> column. You can use this property if,
- for example, you want a <literal>TIMESTAMP</literal> to be set to
- the current date and time when you create a row, but not to be
- changed whenever the row is updated later:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- Let MySQL set the column when the row is created. This initializes
- it to the current date and time.
- </para></listitem>
-
- <listitem><para>
- When you perform subsequent updates to other columns in the row,
- set the <literal>TIMESTAMP</literal> column explicitly to its
- current value:
- </para>
-
-<programlisting>
-UPDATE <replaceable>tbl_name</replaceable>
- SET <replaceable>timestamp_col</replaceable> = <replaceable>timestamp_col</replaceable>,
- <replaceable>other_col1</replaceable> = <replaceable>new_value1</replaceable>,
- <replaceable>other_col2</replaceable> = <replaceable>new_value2</replaceable>, ...
-</programlisting></listitem>
-
- </itemizedlist>
-
- <para>
- Another way to maintain a column that records row-creation time is
- to use a <literal>DATETIME</literal> column that you initialize to
- <literal>NOW()</literal> when the row is created and do not modify
- for subsequent updates.
- </para>
-
- <para>
- <literal>TIMESTAMP</literal> values may range from the beginning of
- 1970 to partway through the year 2037, with a resolution of one
- second. Values are displayed as numbers. When you store a value in
- a <literal>TIMESTAMP</literal> column, it is assumed to be
- represented in the current time zone, and is converted to UTC for
- storage. When you retrieve the value, it is converted from UTC back
- to the local time zone for display. Before MySQL 4.1.3, the server
- has a single time zone. As of 4.1.3, clients can set their time
- zone on a per-connection basis, as described in <literal>Time zone
- support</literal>.
- </para>
-
- <para>
- The format in which MySQL retrieves and displays
- <literal>TIMESTAMP</literal> values depends on the display size, as
- illustrated by the following table. The ``full''
- <literal>TIMESTAMP</literal> format is 14 digits, but
- <literal>TIMESTAMP</literal> columns may be created with shorter
- display sizes:
- </para>
-
- <informaltable>
- <tgroup cols="2">
- <colspec colwidth="30*"/>
- <colspec colwidth="30*"/>
- <tbody>
- <row>
- <entry>
- <emphasis role="bold">Column Type</emphasis>
- </entry>
- <entry>
- <emphasis role="bold">Display Format</emphasis>
- </entry>
- </row>
- <row>
- <entry>
- <literal>TIMESTAMP(14)</literal>
- </entry>
- <entry>
- <literal>YYYYMMDDHHMMSS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>TIMESTAMP(12)</literal>
- </entry>
- <entry>
- <literal>YYMMDDHHMMSS</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>TIMESTAMP(10)</literal>
- </entry>
- <entry>
- <literal>YYMMDDHHMM</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>TIMESTAMP(8)</literal>
- </entry>
- <entry>
- <literal>YYYYMMDD</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>TIMESTAMP(6)</literal>
- </entry>
- <entry>
- <literal>YYMMDD</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>TIMESTAMP(4)</literal>
- </entry>
- <entry>
- <literal>YYMM</literal>
- </entry>
- </row>
- <row>
- <entry>
- <literal>TIMESTAMP(2)</literal>
- </entry>
- <entry>
- <literal>YY</literal>
- </entry>
- </row>
- </tbody>
- </tgroup>
- </informaltable>
-
- <para>
- All <literal>TIMESTAMP</literal> columns have the same storage
- size, regardless of display size. The most common display sizes are
- 6, 8, 12, and 14. You can specify an arbitrary display size at
- table creation time, but values of 0 or greater than 14 are coerced
- to 14. Odd-valued sizes in the range from 1 to 13 are coerced to
- the next higher even number.
- </para>
-
- <para>
- <literal>TIMESTAMP</literal> columns store legal values using the
- full precision with which the value was specified, regardless of
- the display size. This has several implications:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- Always specify year, month, and day, even if your column types are
- <literal>TIMESTAMP(4)</literal> or
- <literal>TIMESTAMP(2)</literal>. Otherwise, the value is not a
- legal date and <literal>0</literal> is stored.
- </para></listitem>
-
- <listitem><para>
- If you use <literal>ALTER TABLE</literal> to widen a narrow
- <literal>TIMESTAMP</literal> column, information is displayed that
- previously was ``hidden.''
- </para></listitem>
-
- <listitem><para>
- Similarly, narrowing a <literal>TIMESTAMP</literal> column does
- not cause information to be lost, except in the sense that less
- information is shown when the values are displayed.
- </para></listitem>
-
- <listitem><para>
- If you are planning to use <literal>mysqldump</literal> for the
- database, do not use <literal>TIMESTAMP(4)</literal> or
- <literal>TIMESTAMP(2)</literal>. The display format for these
- column types are not legal dates and <literal>0</literal> will be
- stored instead. This inconsistency is fixed starting with MySQL
- 4.1, where display width is ignored. To prepare for transition to
- versions after 4.0, you should change to use display widths of 6
- or more, which will produce a legal display format. You can change
- the display width of <literal>TIMESTAMP</literal> column types,
- without losing any information, by using <literal>ALTER
- TABLE</literal> as indicated above.
- </para>
-
- <para>
- If you need to print the timestamps for external applications, you
- can use <literal>MID()</literal> to extract the relevant part of
- the timestamp: for example, to imitate the
- <literal>TIMESTAMP(4)</literal> display format.
- </para></listitem>
-
- <listitem><para>
- Although <literal>TIMESTAMP</literal> values are stored to full
- precision, the only function that operates directly on the
- underlying stored value is <literal>UNIX_TIMESTAMP()</literal>.
- Other functions operate on the formatted retrieved value. This
- means you cannot use a function such as <literal>HOUR()</literal>
- or <literal>SECOND()</literal> unless the relevant part of the
- <literal>TIMESTAMP</literal> value is included in the formatted
- value. For example, the <literal>HH</literal> part of a
- <literal>TIMESTAMP</literal> column is not displayed unless the
- display size is at least 10, so trying to use
- <literal>HOUR()</literal> on shorter <literal>TIMESTAMP</literal>
- values produces a meaningless result.
- </para></listitem>
-
- </itemizedlist>
-
- <para>
- In MySQL 4.1, <literal>TIMESTAMP</literal> display format changes
- to be the same as <literal>DATETIME</literal>, that is, as a string
- in <literal>'YYYY-MM-DD HH:MM:SS'</literal> format rather than as a
- number in <literal>YYYYMMDDHHMMSS</literal> format. To test
- applications written for MySQL 4.0 for compatibility with this
- change, you can set the <literal>new</literal> system variable to
- 1. This variable is available beginning with MySQL 4.0.12. It can
- be set at server startup by specifying the <literal>--new</literal>
- option to <command>mysqld</command>. At runtime, a user who has the
- <literal>SUPER</literal> privilege can set the global value with a
- <literal>SET</literal> statement:
- </para>
-
-<programlisting>
-mysql> SET GLOBAL new = 1;
-</programlisting>
-
- <para>
- Any client can set its session value of <literal>new</literal> as
- follows:
- </para>
-
-<programlisting>
-mysql> SET new = 1;
-</programlisting>
-
- <para>
- The general effect of setting <literal>new</literal> to 1 is that
- values for a <literal>TIMESTAMP</literal> column display as strings
- rather than as numbers. Also, <literal>DESCRIBE</literal> displays
- the column definition as <literal>timestamp(19)</literal>, rather
- than as <literal>timestamp(14)</literal>.
- </para>
-
- <para>
- However, the effect differs somewhat for
- <literal>TIMESTAMP</literal> columns that are created while
- <literal>new</literal> is set to 1. In this case, column values
- display as strings and <literal>DESCRIBE</literal> shows the
- definition as <literal>timestamp(19)</literal>, regardless of the
- current value of <literal>new</literal>.
- </para>
-
- <para>
- In other words, with <literal>new=1</literal>, all
- <literal>TIMESTAMP</literal> values display as strings and
- <literal>DESCRIBE</literal> shows a display width of 19. For
- columns created while <literal>new=1</literal>, they continue to
- display as strings and to have a display width of 19 even if
- <literal>new</literal> is set to 0.
- </para>
-
- <para>
- For a <literal>TIMESTAMP</literal> column that displays as a
- string, you can display it as a number by retrieving it as
- <literal><replaceable>col_name</replaceable>+0</literal>.
- </para>
-
- </section>
-
<section id="timestamp-4-1">
<title id='title-timestamp-4-1'>&title-timestamp-4-1;</title>
<para>
- In MySQL 4.1 and up, the properties of the
- <literal>TIMESTAMP</literal> column type change in the ways
- described in this section.
- </para>
-
- <para>
- From MySQL 4.1.0 on, <literal>TIMESTAMP</literal> display format
- differs from that of earlier MySQL releases:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- <literal>TIMESTAMP</literal> columns are displayed in the same
- format as <literal>DATETIME</literal> columns.
- In other words, the display width is fixed at 19 characters, and
+ <emphasis role="bold">Note</emphasis>: In older versions of MySQL
+ (prior to 4.1), the properties of the <literal>TIMESTAMP</literal>
+ column type differed significantly in many ways from what is
+ described in this section. If you need to convert older
+ <literal>TIMESTAMP</literal> data to work with MySQL 5.0, be sure
+ to see the &title-refman-previous; for details.
+ </para>
+
+ <para>
+ In MySQL 5.0, <literal>TIMESTAMP</literal> columns are displayed
+ in the same format as <literal>DATETIME</literal> columns. In
+ other words, the display width is fixed at 19 characters, and
the format is <literal>YYYY-MM-DD HH:MM:SS</literal>.
- </para></listitem>
-
- <listitem><para>
- Display widths (used as described in the preceding section) are no
- longer supported. In other words, for declarations such as
- <literal>TIMESTAMP(2)</literal>, <literal>TIMESTAMP(4)</literal>,
- and so on, the display width is ignored.
- </para></listitem>
-
- </itemizedlist>
-
+ </para>
+
<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,
+ 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,
@@ -3040,12 +2633,12 @@
<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 exception is the special value
- <literal>'0000-00-00 00:00:00'</literal>.)
+ valid date. The sole exception to this rule is the special value
+ <literal>'0000-00-00 00:00:00'</literal>.
</para>
<para>
- Beginning with MySQL 4.1.2, you have more flexible control over
+ In MYSQL 5.0, you have considerable flexibility ind etermining
when automatic <literal>TIMESTAMP</literal> initialization and
updating occur and which column should have those behaviors:
</para>
@@ -3070,167 +2663,138 @@
</itemizedlist>
<para>
- The following discussion describes the revised syntax and behavior.
- Note that this information applies only to
- <literal>TIMESTAMP</literal> columns for tables not created with
- <literal>MAXDB</literal> mode enabled. As noted earlier in this
- section, <literal>MAXDB</literal> mode causes columns to be created
- as <literal>DATETIME</literal> columns.
- </para>
-
- <para>
- The following items summarize the pre-4.1.2 properties for
- <literal>TIMESTAMP</literal> initialization and updating:
- </para>
-
- <para>
- The first <literal>TIMESTAMP</literal> column in table row
- automatically is set to the current timestamp when the record is
- created if the column is set to <literal>NULL</literal> or is not
- specified at all.
- </para>
-
- <para>
- The first <literal>TIMESTAMP</literal> column in table row
- automatically is updated to the current timestamp when the value of
- any other column in the row is changed, unless the
- <literal>TIMESTAMP</literal> column explicitly is assigned a value
- other than <literal>NULL</literal>.
- </para>
-
- <para>
- If a <literal>DEFAULT</literal> value is specified for the first
- <literal>TIMESTAMP</literal> column when the table is created, it
- is silently ignored.
- </para>
-
- <para>
- Other <literal>TIMESTAMP</literal> columns in the table can be set
- to the current <literal>TIMESTAMP</literal> by assigning
- <literal>NULL</literal> to them, but they do not update
- automatically.
- </para>
-
- <para>
- As of 4.1.2, you have more flexibility in deciding which
- <literal>TIMESTAMP</literal> column automatically is initialized
- and updated to the current timestamp. The rules are as follows:
- </para>
-
- <para>
- If a <literal>DEFAULT</literal> value is specified for the first
- <literal>TIMESTAMP</literal> column in a table, it is not ignored.
- The default can be <literal>CURRENT_TIMESTAMP</literal> or a
- constant date and time value.
- </para>
-
- <para>
- <literal>DEFAULT NULL</literal> is the same as <literal>DEFAULT
- CURRENT_TIMESTAMP</literal> for the <emphasis>first</emphasis>
- <literal>TIMESTAMP</literal> column. For any other
- <literal>TIMESTAMP</literal> column, <literal>DEFAULT
- NULL</literal> is treated as <literal>DEFAULT 0</literal>.
- </para>
-
- <para>
- Any single <literal>TIMESTAMP</literal> column in a table can be
- set to be the one that is initialized to the current timestamp
- and/or updated automatically.
- </para>
-
- <para>
- In a <literal>CREATE TABLE</literal> statement, the first
- <literal>TIMESTAMP</literal> column can be declared in any of the
- following ways:
+ 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
+ initialisation and updating of <literal>TIMESTAMP</literal>
+ columns in MySQL 5.0 are as follows:
</para>
<itemizedlist>
-
- <listitem><para>
- With both <literal>DEFAULT CURRENT_TIMESTAMP</literal> and
- <literal>ON UPDATE CURRENT_TIMESTAMP</literal> clauses, the column
- has the current timestamp for its default value, and is
- automatically updated.
- </para></listitem>
-
- <listitem><para>
- With neither <literal>DEFAULT</literal> nor <literal>ON
- UPDATE</literal> clauses, it is the same as <literal>DEFAULT
- CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP</literal>.
- </para></listitem>
-
- <listitem><para>
- With a <literal>DEFAULT CURRENT_TIMESTAMP</literal> clause and no
- <literal>ON UPDATE</literal> clause, the column has the current
- timestamp for its default value but is not automatically updated.
- </para></listitem>
-
- <listitem><para>
- With no <literal>DEFAULT</literal> clause and with an <literal>ON
- UPDATE CURRENT_TIMESTAMP</literal> clause, the column has a
- default of 0 and is automatically updated.
- </para></listitem>
-
- <listitem><para>
- With a constant <literal>DEFAULT</literal> value, the column has
- the given default. If the column has an <literal>ON UPDATE
- CURRENT_TIMESTAMP</literal> clause, it is automatically updated,
- otherwise not.
- </para></listitem>
-
- </itemizedlist>
-
- <para>
- In other words, you can use the current timestamp for both the
- initial value and the auto-update value, or either one, or neither.
- (For example, you can specify <literal>ON UPDATE</literal> to get
- auto-update without also having the column auto-initialized.)
- </para>
-
- <para>
- Any of <literal>CURRENT_TIMESTAMP</literal>,
- <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.
- </para>
-
- <para>
- The order of the two 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.
- </para>
-
- <para>
- Example. These statements are equivalent:
- </para>
-
+
+ <listitem><para>
+ If a <literal>DEFAULT</literal> value is specified for the
+ first <literal>TIMESTAMP</literal> column in a table, it is
+ not ignored. The default can be
+ <literal>CURRENT_TIMESTAMP</literal> or a constant date and
+ time value.</para></listitem>
+
+ <listitem><para>
+ <literal>DEFAULT NULL</literal> is the same as
+ <literal>DEFAULT CURRENT_TIMESTAMP</literal> for the
+ <emphasis>first</emphasis> <literal>TIMESTAMP</literal>
+ column. For any other <literal>TIMESTAMP</literal> column,
+ <literal>DEFAULT NULL</literal> is treated as <literal>DEFAULT
+ 0</literal>.
+ </para></listitem>
+
+ <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 and/or updated automatically.
+ </para></listitem>
+
+ <listitem>
+ <para>
+ In a <literal>CREATE TABLE</literal> statement, the first
+ <literal>TIMESTAMP</literal> column can be declared in any
+ of the following ways:
+ </para>
+
+ <itemizedlist>
+
+ <listitem><para>
+ With both <literal>DEFAULT CURRENT_TIMESTAMP</literal> and
+ <literal>ON UPDATE CURRENT_TIMESTAMP</literal> clauses, the column
+ has the current timestamp for its default value, and is
+ automatically updated.
+ </para></listitem>
+
+ <listitem><para>
+ With neither <literal>DEFAULT</literal> nor <literal>ON
+ UPDATE</literal> clauses, it is the same as <literal>DEFAULT
+ CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP</literal>.
+ </para></listitem>
+
+ <listitem><para>
+ With a <literal>DEFAULT CURRENT_TIMESTAMP</literal> clause and no
+ <literal>ON UPDATE</literal> clause, the column has the current
+ timestamp for its default value but is not automatically updated.
+ </para></listitem>
+
+ <listitem><para>
+ With no <literal>DEFAULT</literal> clause and with an <literal>ON
+ UPDATE CURRENT_TIMESTAMP</literal> clause, the column has a
+ default of 0 and is automatically updated.
+ </para></listitem>
+
+ <listitem><para>
+ With a constant <literal>DEFAULT</literal> value, the column has
+ the given default. If the column has an <literal>ON UPDATE
+ CURRENT_TIMESTAMP</literal> clause, it is automatically updated,
+ otherwise not.
+ </para></listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, you can use the current timestamp for both the
+ initial value and the auto-update value, or either one, or
+ neither. (For example, you can specify <literal>ON
+ UPDATE</literal> to enable auto-update without also having the
+ column auto-initialized.)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Any of <literal>CURRENT_TIMESTAMP</literal>,
+ <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.
+ </para>
+
+ <para>
+ The order of the two 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.
+ </para>
+
+ <para>
+ Example. These statements are equivalent:
+ </para>
+
<programlisting>
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- ON UPDATE CURRENT_TIMESTAMP);
+ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- DEFAULT CURRENT_TIMESTAMP);
+DEFAULT CURRENT_TIMESTAMP);
</programlisting>
-
- <para>
- To specify automatic default or updating for a
- <literal>TIMESTAMP</literal> column other than the first one, you
- must suppress the automatic initialization and update behaviors for
- the first <literal>TIMESTAMP</literal> column by explicitly
- assigning it a constant <literal>DEFAULT</literal> value (for
- example, <literal>DEFAULT 0</literal> or <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 that, no automatic
- initialization or updating occurs.
- </para>
-
- <para>
- Example. These statements are equivalent:
- </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To specify automatic default or updating for a
+ <literal>TIMESTAMP</literal> column other than the first one,
+ you must suppress the automatic initialization and update
+ behaviors for the first <literal>TIMESTAMP</literal> column by
+ explicitly assigning it a constant <literal>DEFAULT</literal>
+ value (for example, <literal>DEFAULT 0</literal> or
+ <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, then no automatic initialization or
+ updating occurs.
+ </para>
+
+ <para>
+ Example. These statements are equivalent:
+ </para>
<programlisting>
CREATE TABLE t (
@@ -3242,27 +2806,29 @@
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
</programlisting>
+ </listitem>
+
+ </itemizedlist>
<para>
- Beginning with MySQL 4.1.3, you can set the current time zone on a
- per-connection basis, as described in <literal>Time zone
- support</literal>. <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
- same value you store. If you store a <literal>TIMESTAMP</literal>
- value, 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.
+ In MySQL 5.0, you can set the current time zone on a per-connection
+ basis, as described in <xref linkend="time-zone-support"/>.
+ <literal>TIMESTAMP</literal> values are stored in UTC, being
+ converted from the current time zone for storage, and 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.
</para>
<para>
- Beginning with MySQL 4.1.6, you can include the
- <literal>NULL</literal> attribute in the definition of a
- <literal>TIMESTAMP</literal> column to allow the column to contain
- <literal>NULL</literal> values. For example:
+ You can include the <literal>NULL</literal> attribute in the
+ definition of a <literal>TIMESTAMP</literal> column to allow the
+ column to contain <literal>NULL</literal> values. For example:
</para>
<programlisting>
@@ -3277,34 +2843,35 @@
<!-- Updated following to fix Bug#10104 -->
<para>
- Before MySQL 4.1.6 (and even as of 4.1.6 if the
- <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 will <emphasis role="bold">not</emphasis>
- take on the current timestamp unless either its default value is
- defined as <literal>CURRENT_TIMESTAMP</literal>,
+ If the <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 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 defined using a
- definition such as the following:</para>
+ 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:</para>
<programlisting>
CREATE TABLE t (ts 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:</para>
+ <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...</para>
<programlisting>
CREATE TABLE t1 (ts NULL DEFAULT NULL);
CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');
</programlisting>
- <para>then you must explicitly insert a
- value corresponding to the current date and time, for example:</para>
+ <para>...then you must explicitly insert a
+ value corresponding to the current date and time. For example:</para>
<programlisting>
INSERT INTO t1 VALUES (NOW());
@@ -3334,7 +2901,7 @@
<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 the hours part may be so large is that the
+ 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
@@ -3356,7 +2923,7 @@
<literal>'D HH:MM:SS'</literal>, <literal>'D HH:MM'</literal>,
<literal>'D HH'</literal>, or <literal>'SS'</literal>. Here
<literal>D</literal> represents days and can have a value from 0 to
- 34. Note that MySQL doesn't yet store the fraction part.
+ 34. Note that MySQL does not (yet) store the fraction.
</para></listitem>
<listitem><para>
@@ -3374,8 +2941,8 @@
understood as <literal>'10:11:12'</literal>. The following
alternative formats are also understood: <literal>SS</literal>,
<literal>MMSS</literal>, <literal>HHMMSS</literal>,
- <literal>HHMMSS.fraction</literal>. Note that MySQL doesn't yet
- store the fraction part.
+ <literal>HHMMSS.fraction</literal>. Note that MySQL doesn't (yet)
+ store the fraction.
</para></listitem>
<listitem><para>
@@ -3395,18 +2962,18 @@
</para>
<para>
- Be careful about assigning ``short'' <literal>TIME</literal> values
- to a <literal>TIME</literal> column. Without colons, MySQL
- interprets values using the assumption that the rightmost digits
- represent seconds. (MySQL interprets <literal>TIME</literal> values
- as elapsed time rather than as time of day.) For example, you might
- think of <literal>'1112'</literal> and <literal>1112</literal> as
- meaning <literal>'11:12:00'</literal> (12 minutes after 11 o'clock),
- but MySQL interprets them as <literal>'00:11:12'</literal> (11
- minutes, 12 seconds). Similarly, <literal>'12'</literal> and
+ Be careful about assigning abbreviated values to a
+ <literal>TIME</literal> column. Without colons, MySQL interprets
+ values using the assumption that the two rightmost digits represent
+ seconds. (MySQL interprets <literal>TIME</literal> valuesas elapsed
+ time rather than as time of day.) For example, you might think of
+ <literal>'1112'</literal> and <literal>1112</literal> as meaning
+ <literal>'11:12:00'</literal> (12 minutes after 11 o'clock), but
+ MySQL interprets them as <literal>'00:11:12'</literal> (11 minutes,
+ 12 seconds). Similarly, <literal>'12'</literal> and
<literal>12</literal> are interpreted as
<literal>'00:00:12'</literal>. <literal>TIME</literal> values with
- colons, by contrast, are always treated as time of the day. That is
+ colons, by contrast, are always treated as time of the day. That is,
<literal>'11:12'</literal> mean <literal>'11:12:00'</literal>, not
<literal>'00:11:12'</literal>.
</para>
@@ -3435,7 +3002,10 @@
<title id='title-year'>&title-year;</title>
-<!-- TODO: This really applies to YEAR(4). It could be more precise for YEAR(2). -->
+<!--
+ TODO: This really applies to YEAR(4). It could be more precise for
+ YEAR(2).
+-->
<indexterm type="type">
<primary>YEAR data type</primary>
@@ -3640,28 +3210,26 @@
<para>
The <literal>CHAR</literal> and <literal>VARCHAR</literal> types are
declared with a length that indicates the maximum number of
- characters you want to store. For example,
- <literal>CHAR(30)</literal> can hold up to 30 characters. (Before
- MySQL 4.1, the length is interpreted as number of bytes.)
+ characters you want to store. For example,
+ <literal>CHAR(30)</literal> can hold up to 30 characters.
</para>
<para>
The length of a <literal>CHAR</literal> column is fixed to the
length that you declare when you create the table. The length can be
- any value from 0 to 255. (Before MySQL 3.23, the length of
- <literal>CHAR</literal> may be from 1 to 255.) When
- <literal>CHAR</literal> values are stored, they are right-padded
- with spaces to the specified length. When <literal>CHAR</literal>
- values are retrieved, trailing spaces are removed.
+ any value from 0 to 255. When <literal>CHAR</literal> values are
+ stored, they are right-padded with spaces to the specified length.
+ When <literal>CHAR</literal> values are retrieved, trailing spaces
+ are removed.
</para>
<para>
Values in <literal>VARCHAR</literal> columns are variable-length
- strings. The length can be specified as 1 to 255 before MySQL 4.0.2,
- 0 to 255 as of MySQL 4.0.2, and 0 to 65,535 as of MySQL 5.0.3. (The
- maximum actual length of a <literal>VARCHAR</literal> in MySQL 5.0
- is determined by the maximum row size and the character set you use.
- The maximum effective length is 65,532 bytes.)
+ strings. In MySQL 5.0, the length can be specified as 0 to 255
+ before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.
+ (The maximum effective length of a <literal>VARCHAR</literal> in
+ MySQL 5.0 is determined by the maximum row size and the character
+ set used. The maximum length overall is 65,532 bytes.)
</para>
<para>
@@ -3690,7 +3258,7 @@
<literal>VARCHAR</literal> column that exceeds the column's maximum
length, the value is truncated to fit. If the truncated characters
are not spaces, a warning is generated. You can cause an error to
- occur rather than an warning by using ``strict'' SQL mode. See
+ occur rather than an warning by using strict SQL mode. See
<xref linkend="server-sql-mode"/>.
</para>
@@ -3712,9 +3280,10 @@
<literal>VARCHAR(4)</literal> columns:
</para>
-<!-- Need to use @(space) to make sure second column values retain spacing -->
-
-<!-- in output for table below. -->
+<!--
+ NOTE: Need to use spaces to make sure that second column values
+ retain spacing in table:
+-->
<informaltable>
<tgroup cols="5">
@@ -3821,39 +3390,20 @@
</para>
<para>
- As of MySQL 4.1, values in <literal>CHAR</literal> and
+ In MySQL 5.0, values in <literal>CHAR</literal> and
<literal>VARCHAR</literal> columns are sorted and compared according
- to the collation of the character set assigned to the column. Before
- MySQL 4.1, sorting and comparison are based on the collation of the
- server character set; you can declare the column with the
- <literal>BINARY</literal> attribute to cause sorting and comparison
- to be based on the numeric values of the bytes in column values.
- <literal>BINARY</literal> doesn't affect how column values are
- stored or retrieved.
- </para>
-
- <para>
- The <literal>BINARY</literal> attribute is sticky. This means that
- if a column marked <literal>BINARY</literal> is used in an
- expression, the whole expression is treated as a
- <literal>BINARY</literal> value.
+ to the collation of the character set assigned to the column.
</para>
<para>
- From MySQL 4.1.0 on, column type <literal>CHAR BYTE</literal> is an
- alias for <literal>CHAR BINARY</literal>. This is a compatibility
- feature.
+ <literal>CHAR BYTE</literal> is an alias for <literal>CHAR
+ BINARY</literal>. This is a compatibility feature.
</para>
<para>
- From MySQL 4.1.0 on, the <literal>ASCII</literal> attribute can be
- specified for <literal>CHAR</literal>. It assigns the
- <literal>latin1</literal> character set.
- </para>
-
- <para>
- From MySQL 4.1.1 on, the <literal>UNICODE</literal> attribute can be
- specified for <literal>CHAR</literal>. It assigns the
+ The <literal>ASCII</literal> attribute assigns the
+ <literal>latin1</literal> character set to a <literal>CHAR</literal>
+ column. The <literal>UNICODE</literal> attribute assigns the
<literal>ucs2</literal> character set.
</para>
@@ -3920,23 +3470,17 @@
</para>
<para>
- Before MySQL 4.1.2,
- <literal>BINARY(<replaceable>M</replaceable>)</literal> and
- <literal>VARBINARY(<replaceable>M</replaceable>)</literal> are
- treated as <literal>CHAR(<replaceable>M</replaceable>)
- BINARY</literal> and <literal>VARCHAR(<replaceable>M</replaceable>)
- BINARY</literal>. As of MySQL 4.1.2, <literal>BINARY</literal> and
- <literal>VARBINARY</literal> are available as distinct data types,
- and for <literal>CHAR(<replaceable>M</replaceable>) BINARY</literal>
+ In MySQL 5.0, <literal>BINARY</literal> and
+ <literal>VARBINARY</literal> are distinct data types. For
+ <literal>CHAR(<replaceable>M</replaceable>) BINARY</literal>
and <literal>VARCHAR(<replaceable>M</replaceable>) BINARY</literal>,
- the <literal>BINARY</literal> attribute does not cause the column to
- be treated as a binary string column. Instead, it causes the binary
- collation for the column character set to be used, but the column
- itself contains non-binary character strings rather than binary byte
- strings. For example, in 4.1 and up, <literal>CHAR(5)
- BINARY</literal> is treated as <literal>CHAR(5) CHARACTER SET latin1
- COLLATE latin1_bin</literal>, assuming that the default character
- set is <literal>latin1</literal>.
+ the <literal>BINARY</literal> attribute causes the binary collation
+ for the column character set to be used, but the column itself
+ contains non-binary character strings rather than binary byte
+ strings. For example <literal>CHAR(5) BINARY</literal> is treated as
+ <literal>CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin</literal>,
+ assuming that the default character set is
+ <literal>latin1</literal>.
</para>
</section>
@@ -3945,9 +3489,10 @@
<title id='title-blob'>&title-blob;</title>
-<!-- TODO: needs some discussion of the change to character semantics -->
-
-<!-- for TEXT in 4.1. -->
+<!--
+ TODO: needs some discussion of the change to character semantics
+ for TEXT since 4.0.
+-->
<indexterm type="type">
<primary>BLOB data type</primary>
@@ -3980,27 +3525,27 @@
<para>
A <literal>BLOB</literal> is a binary large object that can hold a
- variable amount of data. The four <literal>BLOB</literal> types,
+ variable amount of data. The four <literal>BLOB</literal> types are
<literal>TINYBLOB</literal>, <literal>BLOB</literal>,
- <literal>MEDIUMBLOB</literal>, and <literal>LONGBLOB</literal>,
- differ only in the maximum length of the values they can hold.
+ <literal>MEDIUMBLOB</literal>, and <literal>LONGBLOB</literal>.
+ These differ only in the maximum length of the values they can hold.
<!-- end_description_for_help_topic -->
</para>
<para>
+ The four <literal>TEXT</literal> types are
+ <literal>TINYTEXT</literal>, <literal>TEXT</literal>,
+ <literal>MEDIUMTEXT</literal>, and <literal>LONGTEXT</literal>.
+ These correspond to the four <literal>BLOB</literal> types and have
+ the same maximum lengths and storage requirements.
+ </para>
+
+ <para>
See <xref linkend="storage-requirements"/>.
</para>
<para>
- The four <literal>TEXT</literal> types, <literal>TINYTEXT</literal>,
- <literal>TEXT</literal>, <literal>MEDIUMTEXT</literal>, and
- <literal>LONGTEXT</literal>, correspond to the four
- <literal>BLOB</literal> types and have the same maximum lengths and
- storage requirements.
- </para>
-
- <para>
<literal>BLOB</literal> columns are treated as binary strings (byte
strings). <literal>TEXT</literal> columns are treated as non-binary
strings (character strings). <literal>BLOB</literal> columns have no
@@ -4008,12 +3553,13 @@
values of the bytes in column values. <literal>TEXT</literal>
columns have a character set, and values are sorted and compared
based on the collation of the character set assigned to the column
- as of MySQL 4.1. Before 4.1, <literal>TEXT</literal> sorting and
- comparison are based on the collation of the server character set.
+ as of MySQL 4.1.
</para>
<para>
- No lettercase conversion takes place during storage or retrieval.
+ No lettercase conversion for <literal>TEXT</literal> or
+ <literal>BLOB</literal> columns takes place during storage or
+ retrieval.
</para>
<para>
@@ -4021,13 +3567,13 @@
<literal>TEXT</literal> column that exceeds the column type's
maximum length, the value is truncated to fit. If the truncated
characters are not spaces, a warning is generated. You can cause an
- error to occur rather than an warning by using ``strict'' SQL mode.
- See <xref linkend="server-sql-mode"/>.
+ error to occur rather than an warning by using strict SQL mode. See
+ <xref linkend="server-sql-mode"/>.
</para>
<para>
In most respects, you can regard a <literal>BLOB</literal> column as
- a <literal>VARBINARY</literal> column that can be as big as you
+ a <literal>VARBINARY</literal> column that can be as large as you
like. Similarly, you can regard a <literal>TEXT</literal> column as
a <literal>VARCHAR</literal> column. <literal>BLOB</literal> and
<literal>TEXT</literal> differ from <literal>VARBINARY</literal> and
@@ -4051,18 +3597,10 @@
</para></listitem>
<listitem><para>
- You can have indexes on <literal>BLOB</literal> and
- <literal>TEXT</literal> columns only as of MySQL 3.23.2 for
- <literal>MyISAM</literal> tables or MySQL 4.0.14 for
- <literal>InnoDB</literal> tables. Older versions of MySQL did not
- support indexing these column types.
- </para></listitem>
-
- <listitem><para>
For indexes on <literal>BLOB</literal> and <literal>TEXT</literal>
columns, you must specify an index prefix length. For
<literal>CHAR</literal> and <literal>VARCHAR</literal>, a prefix
- length is optional.
+ length is optional. See <xref linkend="indexes"/>.
</para>
<indexterm type="concept">
@@ -4088,8 +3626,8 @@
</itemizedlist>
<para>
- From MySQL 4.1.0 on, <literal>LONG</literal> and <literal>LONG
- VARCHAR</literal> map to the <literal>MEDIUMTEXT</literal> data
+ In MySQL 5.0, <literal>LONG</literal> and <literal>LONG
+ VARCHAR</literal> map to the <literal>MEDIUMTEXT</literal> data
type. This is a compatibility feature. If you use the
<literal>BINARY</literal> attribute with a <literal>TEXT</literal>
column type, the column is assigned the binary collation of the
@@ -4120,11 +3658,10 @@
</para>
<para>
- As of MySQL 4.0.3, you can make more bytes significant in sorting
- or grouping by increasing the value of
- <literal>max_sort_length</literal> at runtime. Any client can
- change the value of its session <literal>max_sort_length</literal>
- variable:
+ You can make more bytes significant in sorting or grouping by
+ increasing the value of <literal>max_sort_length</literal> at
+ runtime. Any client can change the value of its session
+ <literal>max_sort_length</literal> variable:
</para>
<programlisting>
@@ -4150,18 +3687,7 @@
-> ORDER BY SUBSTRING(comment,1,2000);
</programlisting>
- <para>
- Before MySQL 3.23.2, you can group on an expression involving
- <literal>BLOB</literal> or <literal>TEXT</literal> values by using
- a column alias or by specifying the column position:
- </para>
-
-<programlisting>
-mysql> SELECT id, SUBSTRING(comment,1,2000) AS b
- -> FROM <replaceable>tbl_name</replaceable> GROUP BY b;
-mysql> SELECT id, SUBSTRING(comment,1,2000)
- -> FROM <replaceable>tbl_name</replaceable> GROUP BY 2;
-</programlisting></listitem>
+ </listitem>
<listitem><para>
The maximum size of a <literal>BLOB</literal> or
@@ -4332,19 +3858,17 @@
</para>
<para>
- Starting from MySQL 3.23.51, trailing spaces are automatically
- deleted from <literal>ENUM</literal> member values when the table is
- created.
+ Trailing spaces are automatically deleted from
+ <literal>ENUM</literal> member values when the table is created.
</para>
<para>
When retrieved, values stored into an <literal>ENUM</literal> column
are displayed using the lettercase that was used in the column
- definition. Before MySQL 4.1.1, lettercase is irrelevant when you
- assign values to an <literal>ENUM</literal> column. As of 4.1.1,
- <literal>ENUM</literal> columns can be assigned a character set and
- collation. For binary or case-sensitive collations, lettercase does
- matter when you assign values to to the column.
+ definition. In MySQL 4.1.1, <literal>ENUM</literal> columns can be
+ assigned a character set and collation. For binary or case-sensitive
+ collations, lettercase is taken into account when assigning values
+ to the column.
</para>
<para>
@@ -4363,7 +3887,7 @@
number is treated as an index, and the value stored is the
enumeration member with that index. (However, this does not work
with <literal>LOAD DATA</literal>, which treats all input as
- strings.) It's not advisable to define an <literal>ENUM</literal>
+ strings.) It is not advisable to define an <literal>ENUM</literal>
column with enumeration values that look like numbers, because this
can easily become confusing. For example, the following column has
enumeration members with string values of <literal>'0'</literal>,
@@ -4444,19 +3968,17 @@
</para>
<para>
- Starting from MySQL 3.23.51, trailing spaces are automatically
- deleted from <literal>SET</literal> member values when the table is
- created.
+ Trailing spaces are automatically deleted from
+ <literal>SET</literal> member values when the table is created.
</para>
<para>
When retrieved, values stored into a <literal>SET</literal> column
are displayed using the lettercase that was used in the column
- definition. Before MySQL 4.1.1, lettercase is irrelevant when you
- assign values to an <literal>SET</literal> column. As of 4.1.1,
- <literal>SET</literal> columns can be assigned a character set and
- collation. For binary or case-sensitive collations, lettercase does
- matter when you assign values to to the column.
+ definition. In MySQL 5.0, <literal>SET</literal> columns can be
+ assigned a character set and collation. For binary or case-sensitive
+ collations, lettercase is taken into account when assigning values
+ to to the column.
</para>
<para>
@@ -4665,12 +4187,11 @@
</para>
<para>
- If a <literal>MyISAM</literal> or <literal>ISAM</literal> table
- includes any variable-length column types, the record format is also
- variable length. When a table is created, MySQL may, under certain
- conditions, change a column from a variable-length type to a
- fixed-length type or vice versa. See
- <xref linkend="silent-column-changes"/>.
+ If a <literal>MyISAM</literal> table includes any variable-length
+ column types, the record format is also variable length. When a table
+ is created, MySQL may, under certain conditions, change a column from
+ a variable-length type to a fixed-length type or vice versa. See
+ <xref linkend="silent-column-changes"/> for more information.
</para>
<indexterm type="concept">
@@ -5064,7 +4585,7 @@
<literal>TINYBLOB</literal>, <literal>TINYTEXT</literal>
</entry>
<entry>
- <replaceable>L</replaceable>+1 bytes, where
+ <replaceable>L</replaceable>+1 byte, where
<replaceable>L</replaceable> < 2^8
</entry>
</row>
@@ -5146,18 +4667,17 @@
bytes.
</para>
+<!-- TODO: Can be worded better? -->
+
<para>
For the <literal>CHAR</literal>, <literal>VARCHAR</literal>, and
- <literal>TEXT</literal> types, <replaceable>L</replaceable> and
- <replaceable>M</replaceable> in the preceding table should be
- interpreted as number of bytes before MySQL 4.1 and as number of
- characters thereafter. Lengths for these types in columns
- specifications indicate number of characters from MySQL 4.1 on. The
- number of extra bytes for recording lengths for variable-length data
- types is unchanged. For example, <replaceable>L</replaceable>+1 bytes
- to store a <literal>TINYTEXT</literal> value before MySQL 4.1 becomes
- <replaceable>L</replaceable> characters + 1 byte to store the length
- as of MySQL 4.1.
+ <literal>TEXT</literal> types, the values
+ <replaceable>L</replaceable> and <replaceable>M</replaceable> in the
+ preceding table should be interpreted as number of characters in
+ MySQL 5.0, and lengths for these types in column specifications
+ indicate the number of characters. For example, to store a
+ <literal>TINYTEXT</literal> value requires
+ <replaceable>L</replaceable> characters + 1 byte.
</para>
<para>
@@ -5203,7 +4723,8 @@
The size of an <literal>ENUM</literal> object is determined by the
number of different enumeration values. One byte is used for
enumerations with up to 255 possible values. Two bytes are used for
- enumerations with up to 65,535 values. See <xref linkend="enum"/>.
+ enumerations having between 256 and 65,535 possible values. See
+ <xref linkend="enum"/>.
</para>
<indexterm type="concept">
@@ -5236,16 +4757,16 @@
</indexterm>
<para>
- For the most efficient use of storage, try to use the most precise
- type in all cases. For example, if an integer column is used for
- values in the range from <literal>1</literal> to
- <literal>99999</literal>, <literal>MEDIUMINT UNSIGNED</literal> is
- the best type. Of the types that represent all the required values,
- it uses the least amount of storage.
+ For optimum storage, you should try to use the most precise type in
+ all cases. For example, if an integer column is used for values in
+ the range from <literal>1</literal> to <literal>99999</literal>, then
+ <literal>MEDIUMINT UNSIGNED</literal> is the best type. Of the types
+ that represent all the required values, this type uses the least
+ amount of storage.
</para>
<para>
- Tables created with MySQL 5.0.3 and above uses a a new storage format
+ Tables created in MySQL 5.0.3 and above uses a a new storage format
for <literal>DECIMAL</literal> columns. All basic calculation
(<literal>+,-,*,/</literal>) with <literal>DECIMAL</literal> columns
are done with precision of 64 decimal digits. See
@@ -5253,21 +4774,13 @@
</para>
<para>
- For earlier MySQL version, accurate representation of monetary values
- was a common problem. In these MySQL version, you should also use the
- <literal>DECIMAL</literal> type. In this case the value is stored as
- a string, so no loss of accuracy should occur on storage.
- Calculations on these <literal>DECIMAL</literal> values are however
- done using double-precision operations. If accuracy is not too
- important or if speed is important, the <literal>DOUBLE</literal>
- type may also be good enough.
- </para>
-
- <para>
- For high precision, you can always convert to a fixed-point type
- stored in a <literal>BIGINT</literal>. This allows you to do all
- calculations with 64-bit integers and convert results back to
- floating-point values only when necessary.
+ Calculations on <literal>DECIMAL</literal> values are performed using
+ double-precision operations. If accuracy is not too important or if
+ speed is the highest priority, the <literal>DOUBLE</literal>
+ type may be good enough. For high precision, you can always convert
+ to a fixed-point type stored in a <literal>BIGINT</literal>. This
+ allows you to do all calculations with 64-bit integers, then convert
+ results back to floating-point values as necessary.
</para>
</section>
@@ -5302,7 +4815,7 @@
</indexterm>
<para>
- To make it easier to use code written for SQL implementations from
+ To facilitate the use of code written for SQL implementations from
other vendors, MySQL maps column types as shown in the following
table. These mappings make it easier to import table definitions from
other database engines into MySQL:
@@ -5321,6 +4834,10 @@
<emphasis role="bold">MySQL Type</emphasis>
</entry>
</row>
+
+<!-- Doesn't look like we need this one any longer (4.1+)...? -->
+
+<!--
<row>
<entry>
<literal>BINARY(<replaceable>M</replaceable>)</literal>
@@ -5330,9 +4847,11 @@
(before MySQL 4.1.2)
</entry>
</row>
+-->
+
<row>
<entry>
- <literal>BOOL</literal>
+ <literal>BOOL</literal>,
</entry>
<entry>
<literal>TINYINT</literal>
@@ -5367,7 +4886,7 @@
<literal>FIXED</literal>
</entry>
<entry>
- <literal>DECIMAL</literal> (MySQL 4.1.0 on)
+ <literal>DECIMAL</literal>
</entry>
</row>
<row>
@@ -5447,7 +4966,7 @@
<literal>LONG</literal>
</entry>
<entry>
- <literal>MEDIUMTEXT</literal> (MySQL 4.1.0 on)
+ <literal>MEDIUMTEXT</literal>
</entry>
</row>
<row>
@@ -5466,6 +4985,10 @@
<literal>DECIMAL</literal>
</entry>
</row>
+
+<!-- ...or this one...? -->
+
+<!--
<row>
<entry>
<literal>VARBINARY(<replaceable>M</replaceable>)</literal>
@@ -5475,24 +4998,36 @@
BINARY</literal> (before MySQL 4.1.2)
</entry>
</row>
+-->
+
</tbody>
</tgroup>
</informaltable>
<para>
- As of MySQL 4.1.2, <literal>BINARY</literal> and
- <literal>VARBINARY</literal> are distinct data types and are not
- converted to <literal>CHAR BINARY</literal> and <literal>VARCHAR
- BINARY</literal>.
- </para>
-
- <para>
Column type mapping occurs at table creation time, after which the
original type specifications are discarded. If you create a table
with types used by other vendors and then issue a <literal>DESCRIBE
<replaceable>tbl_name</replaceable></literal> statement, MySQL
- reports the table structure using the equivalent MySQL types.
+ reports the table structure using the equivalent MySQL types. For
+ example:
</para>
+
+<programlisting>
+mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG, d NUMERIC);
+Query OK, 0 rows affected (0.08 sec)
+
+mysql> DESCRIBE t;
++-------+---------------+------+-----+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+---------------+------+-----+---------+-------+
+| a | tinyint(1) | YES | | NULL | |
+| b | double | YES | | NULL | |
+| c | mediumtext | YES | | NULL | |
+| d | decimal(10,0) | YES | | NULL | |
++-------+---------------+------+-----+---------+-------+
+4 rows in set (0.00 sec)
+</programlisting>
</section>
--- 1.4/refman-5.0/tutorial.xml 2005-07-11 20:55:10 +10:00
+++ 1.5/refman-5.0/tutorial.xml 2005-07-12 04:08:55 +10:00
@@ -866,11 +866,13 @@
<literal>name</literal>, <literal>owner</literal>, and
<literal>species</literal> columns because the column values vary in
length. The lengths of those columns need not all be the same, and
- need not be <literal>20</literal>. You can pick any length from
- <literal>1</literal> to <literal>1000</literal>, whatever seems most
- reasonable to you. (If you make a poor choice and it turns out later
- that you need a longer field, MySQL provides an <literal>ALTER
- TABLE</literal> statement.)
+ need not be <literal>20</literal>. In MySQL 5.0.3 and later
+ versions, you can normally pick any length from
+ <literal>1</literal> to <literal>65535</literal>, whatever seems
+ most reasonable to you. (<emphasis role="bold">Note</emphasis>:
+ Prior to MySQL 5.0.3, the upper limit was 255.) If you make a poor
+ choice and it turns out later that you need a longer field, MySQL
+ provides an <literal>ALTER TABLE</literal> statement.
</para>
<para>
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (jon:1.2984) | jon | 11 Jul |
