Author: paul
Date: 2006-05-19 21:56:01 +0200 (Fri, 19 May 2006)
New Revision: 2134
Log:
r10531@frost: paul | 2006-05-19 14:55:01 -0500
General CREATE INDEX/CREATE TABLE revisions.
Modified:
trunk/
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.1/sql-syntax.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:7638
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:10525
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:4886
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:7638
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:10531
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:4886
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2006-05-19 19:40:06 UTC (rev 2133)
+++ trunk/refman-4.1/sql-syntax.xml 2006-05-19 19:56:01 UTC (rev 2134)
@@ -1056,7 +1056,7 @@
<programlisting>
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <replaceable>index_name</replaceable>
- [USING <replaceable>index_type</replaceable>]
+ [USING <replaceable>type_name</replaceable>]
ON <replaceable>tbl_name</replaceable> (<replaceable>index_col_name</replaceable>,...)
<replaceable>index_col_name</replaceable>:
@@ -1070,7 +1070,7 @@
mapped to an <literal>ALTER TABLE</literal> statement to create
indexes. See <xref linkend="alter-table"/>. The <literal>CREATE
INDEX</literal> statement does not do anything prior to MySQL
- 3.22. For more information about how MySQL uses indexes, see
+ 3.22. For more information about indexes, see
<xref linkend="mysql-indexes"/>.
</para>
@@ -1092,18 +1092,21 @@
<para>
For <literal>CHAR</literal>, <literal>VARCHAR</literal>,
<literal>BINARY</literal>, and <literal>VARBINARY</literal>
- columns, indexes can be created that use only part of a column,
- using
+ columns, indexes can be created that use only the leading part
+ of column values, using
<literal><replaceable>col_name</replaceable>(<replaceable>length</replaceable>)</literal>
- syntax to specify an index prefix length. Index entries consist
- of the first <replaceable>length</replaceable> characters of
- each column value for <literal>CHAR</literal> and
- <literal>VARCHAR</literal> columns, and the first
+ syntax to specify an index prefix length.
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns also
+ can be indexed, but a prefix length <emphasis>must</emphasis> be
+ given. Prefix lengths are given in characters for non-binary
+ string types and in bytes for binary string types. That is,
+ index entries consist of the first
+ <replaceable>length</replaceable> characters of each column
+ value for <literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ and <literal>TEXT</literal> columns, and the first
<replaceable>length</replaceable> bytes of each column value for
- <literal>BINARY</literal> and <literal>VARBINARY</literal>
- columns. <literal>BLOB</literal> and <literal>TEXT</literal>
- columns also can be indexed, but a prefix length
- <emphasis>must</emphasis> be given.
+ <literal>BINARY</literal>, <literal>VARBINARY</literal>, and
+ <literal>BLOB</literal> columns.
</para>
<para>
@@ -1137,12 +1140,26 @@
</para>
<para>
+ A <literal>UNIQUE</literal> index creates a constraint such that
+ all values in the index must be distinct. An error occurs if you
+ try to add a new row with a key value that matches an existing
+ row. This constraint does not apply to <literal>NULL</literal>
+ values except for the <literal>BDB</literal> storage engine. For
+ other engines, a <literal>UNIQUE</literal> index allows multiple
+ <literal>NULL</literal> values for columns that can contain
+ <literal>NULL</literal>.
+ </para>
+
+ <para>
<literal>FULLTEXT</literal> indexes are supported only for
<literal>MyISAM</literal> tables and can include only
<literal>CHAR</literal>, <literal>VARCHAR</literal>, and
- <literal>TEXT</literal> columns. <literal>FULLTEXT</literal>
- indexes are available in MySQL 3.23.23 or later. See
- <xref linkend="fulltext-search"/>.
+ <literal>TEXT</literal> columns. Indexing always happens over
+ the entire column; partial indexing is not supported and any
+ prefix length is ignored if specified. See
+ <xref linkend="fulltext-search"/>, for details of operation.
+ <literal>FULLTEXT</literal> indexes are available in MySQL
+ 3.23.23 or later.
</para>
<para>
@@ -1179,14 +1196,11 @@
<para>
From MySQL 4.1.0 on, some storage engines allow you to specify
- an index type when creating an index. The syntax for the
- <replaceable>index_type</replaceable> specifier is
- <literal>USING <replaceable>type_name</replaceable></literal>.
- The allowable <replaceable>type_name</replaceable> values
- supported by different storage engines are shown in the
- following table. Where multiple index types are listed, the
- first one is the default when no
- <replaceable>index_type</replaceable> specifier is given.
+ an index type when creating an index. The allowable
+ <replaceable>type_name</replaceable> values supported by
+ different storage engines are shown in the following table.
+ Where multiple index types are listed, the first one is the
+ default when no index type specifier is given.
</para>
<informaltable>
@@ -1215,6 +1229,13 @@
</informaltable>
<para>
+ If you specify an index type that is not legal for a given
+ storage engine, but there is another index type available that
+ the engine can use without affecting query results, the engine
+ uses the available type.
+ </para>
+
+ <para>
Examples:
</para>
@@ -1224,20 +1245,12 @@
</programlisting>
<para>
- <literal>TYPE <replaceable>type_name</replaceable></literal> can
- be used as a synonym for <literal>USING
- <replaceable>type_name</replaceable></literal> to specify an
- index type. However, <literal>USING</literal> is the preferred
- form.
+ <literal>TYPE <replaceable>type_name</replaceable></literal> is
+ recognized as a synonym for <literal>USING
+ <replaceable>type_name</replaceable></literal>. However,
+ <literal>USING</literal> is the preferred form.
</para>
- <para>
- If you specify an index type that is not legal for a given
- storage engine, but there is another index type available that
- the engine can use without affecting query results, the engine
- uses the available type.
- </para>
-
</section>
<section id="create-table">
@@ -1291,21 +1304,20 @@
<replaceable>create_definition</replaceable>:
<replaceable>column_definition</replaceable>
| [CONSTRAINT [<replaceable>symbol</replaceable>]] PRIMARY KEY [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- | KEY [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- | INDEX [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- | [CONSTRAINT [<replaceable>symbol</replaceable>]] UNIQUE [INDEX]
+ | {INDEX|KEY} [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
+ | [CONSTRAINT [<replaceable>symbol</replaceable>]] UNIQUE [INDEX|KEY]
[<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- | {FULLTEXT|SPATIAL} [INDEX] [<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...)
+ | {FULLTEXT|SPATIAL} [INDEX|KEY] [<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...)
| [CONSTRAINT [<replaceable>symbol</replaceable>]] FOREIGN KEY
[<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...) [<replaceable>reference_definition</replaceable>]
| CHECK (<replaceable>expr</replaceable>)
<replaceable>column_definition</replaceable>:
- <replaceable>col_name</replaceable> <replaceable>type</replaceable> [NOT NULL | NULL] [DEFAULT <replaceable>default_value</replaceable>]
+ <replaceable>col_name</replaceable> <replaceable>data_type</replaceable> [NOT NULL | NULL] [DEFAULT <replaceable>default_value</replaceable>]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT '<replaceable>string</replaceable>'] [<replaceable>reference_definition</replaceable>]
-<replaceable>type</replaceable>:
+<replaceable>data_type</replaceable>:
TINYINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
@@ -1473,8 +1485,8 @@
</para>
<para>
- <replaceable>type</replaceable> represents the data type is a
- column definition. <replaceable>spatial_type</replaceable>
+ <replaceable>data_type</replaceable> represents the data type is
+ a column definition. <replaceable>spatial_type</replaceable>
represents a spatial data type. For general information on the
properties of data types other than the spatial types, see
<xref linkend="data-types"/>. For information about spatial data
@@ -1563,6 +1575,12 @@
<programlisting>
SELECT * FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>auto_col</replaceable> IS NULL
</programlisting>
+
+ <para>
+ For information about <literal>InnoDB</literal> and
+ <literal>AUTO_INCREMENT</literal>, see
+ <xref linkend="innodb-auto-increment-column"/>.
+ </para>
</listitem>
<listitem>
@@ -1592,7 +1610,9 @@
<para>
Also as of 4.1, MySQL interprets length specifications in
character column definitions in characters. (Earlier
- versions interpret them in bytes.)
+ versions interpret them in bytes.) Lengths for
+ <literal>BINARY</literal> and <literal>VARBINARY</literal>
+ are in bytes.
</para>
</listitem>
@@ -1691,13 +1711,12 @@
<para>
A <literal>UNIQUE</literal> index creates a constraint such
that all values in the index must be distinct. An error
- occurs if you try to add a new row with a key that matches
- an existing row. The exception to this is that if a column
- in the index is allowed to contain <literal>NULL</literal>
- values, it can contain multiple <literal>NULL</literal>
- values. This exception does not apply to
- <literal>BDB</literal> tables, for which a column with a
- <literal>UNIQUE</literal> index allows only a single
+ occurs if you try to add a new row with a key value that
+ matches an existing row. This constraint does not apply to
+ <literal>NULL</literal> values except for the
+ <literal>BDB</literal> storage engine. For other engines, a
+ <literal>UNIQUE</literal> index allows multiple
+ <literal>NULL</literal> values for columns that can contain
<literal>NULL</literal>.
</para>
</listitem>
@@ -1804,7 +1823,7 @@
</para>
<para>
- For more information about how MySQL uses indexes, see
+ For more information about indexes, see
<xref linkend="mysql-indexes"/>.
</para>
</listitem>
@@ -1833,18 +1852,26 @@
<listitem>
<para>
- With
+ For <literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ <literal>BINARY</literal>, and <literal>VARBINARY</literal>
+ columns, indexes can be created that use only the leading
+ part of column values, using
<literal><replaceable>col_name</replaceable>(<replaceable>length</replaceable>)</literal>
- syntax in an index specification, you can create an index
- that uses only part of a column. Index entries consist of
- the first <replaceable>length</replaceable> characters of
- each column value for <literal>CHAR</literal> and
- <literal>VARCHAR</literal> columns, and the first
- <replaceable>length</replaceable> bytes of each column value
- for <literal>BINARY</literal> and
- <literal>VARBINARY</literal> columns. Indexing only a prefix
- of column values like this can make the index file much
- smaller. See <xref linkend="indexes"/>.
+ syntax to specify an index prefix length.
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns
+ also can be indexed, but a prefix length
+ <emphasis>must</emphasis> be given. Prefix lengths are given
+ in characters for non-binary string types and in bytes for
+ binary string types. That is, index entries consist of the
+ first <replaceable>length</replaceable> characters of each
+ column value for <literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, and <literal>TEXT</literal>
+ columns, and the first <replaceable>length</replaceable>
+ bytes of each column value for <literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, and <literal>BLOB</literal>
+ columns. Indexing only a prefix of column values like this
+ can make the index file much smaller. See
+ <xref linkend="indexes"/>.
</para>
<indexterm>
@@ -1868,13 +1895,10 @@
</indexterm>
<para>
- The <literal>MyISAM</literal> and (as of MySQL 4.0.14)
+ Only the <literal>MyISAM</literal> and (as of MySQL 4.0.14)
<literal>InnoDB</literal> storage engines support indexing
on <literal>BLOB</literal> and <literal>TEXT</literal>
- columns. When indexing a <literal>BLOB</literal> or
- <literal>TEXT</literal> column, you
- <emphasis>must</emphasis> specify a prefix length for the
- index. For example:
+ columns. For example:
</para>
<programlisting>
@@ -2299,8 +2323,9 @@
<para>
The maximum number of rows you plan to store in the table.
- This is not a hard limit, but rather an indicator that the
- table must be able to store at least this many rows.
+ This is not a hard limit, but rather a hint to the storage
+ engine that the table must be able to store at least this
+ many rows.
</para>
</listitem>
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-05-19 19:40:06 UTC (rev 2133)
+++ trunk/refman-5.0/sql-syntax.xml 2006-05-19 19:56:01 UTC (rev 2134)
@@ -1087,7 +1087,7 @@
<programlisting>
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <replaceable>index_name</replaceable>
- [USING <replaceable>index_type</replaceable>]
+ [USING <replaceable>type_name</replaceable>]
ON <replaceable>tbl_name</replaceable> (<replaceable>index_col_name</replaceable>,...)
<replaceable>index_col_name</replaceable>:
@@ -1099,8 +1099,8 @@
<para>
<literal>CREATE INDEX</literal> is mapped to an <literal>ALTER
TABLE</literal> statement to create indexes. See
- <xref linkend="alter-table"/>. For more information about how
- MySQL uses indexes, see <xref linkend="mysql-indexes"/>.
+ <xref linkend="alter-table"/>. For more information about
+ indexes, see <xref linkend="mysql-indexes"/>.
</para>
<remark role="help-description-end"/>
@@ -1121,18 +1121,21 @@
<para>
For <literal>CHAR</literal>, <literal>VARCHAR</literal>,
<literal>BINARY</literal>, and <literal>VARBINARY</literal>
- columns, indexes can be created that use only part of a column,
- using
+ columns, indexes can be created that use only the leading part
+ of column values, using
<literal><replaceable>col_name</replaceable>(<replaceable>length</replaceable>)</literal>
- syntax to specify an index prefix length. Index entries consist
- of the first <replaceable>length</replaceable> characters of
- each column value for <literal>CHAR</literal> and
- <literal>VARCHAR</literal> columns, and the first
+ syntax to specify an index prefix length.
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns also
+ can be indexed, but a prefix length <emphasis>must</emphasis> be
+ given. Prefix lengths are given in characters for non-binary
+ string types and in bytes for binary string types. That is,
+ index entries consist of the first
+ <replaceable>length</replaceable> characters of each column
+ value for <literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ and <literal>TEXT</literal> columns, and the first
<replaceable>length</replaceable> bytes of each column value for
- <literal>BINARY</literal> and <literal>VARBINARY</literal>
- columns. <literal>BLOB</literal> and <literal>TEXT</literal>
- columns also can be indexed, but a prefix length
- <emphasis>must</emphasis> be given.
+ <literal>BINARY</literal>, <literal>VARBINARY</literal>, and
+ <literal>BLOB</literal> columns.
</para>
<para>
@@ -1165,11 +1168,24 @@
</para>
<para>
+ A <literal>UNIQUE</literal> index creates a constraint such that
+ all values in the index must be distinct. An error occurs if you
+ try to add a new row with a key value that matches an existing
+ row. This constraint does not apply to <literal>NULL</literal>
+ values except for the <literal>BDB</literal> storage engine. For
+ other engines, a <literal>UNIQUE</literal> index allows multiple
+ <literal>NULL</literal> values for columns that can contain
+ <literal>NULL</literal>.
+ </para>
+
+ <para>
<literal>FULLTEXT</literal> indexes are supported only for
<literal>MyISAM</literal> tables and can include only
<literal>CHAR</literal>, <literal>VARCHAR</literal>, and
- <literal>TEXT</literal> columns. See
- <xref linkend="fulltext-search"/>.
+ <literal>TEXT</literal> columns. Indexing always happens over
+ the entire column; partial indexing is not supported and any
+ prefix length is ignored if specified. See
+ <xref linkend="fulltext-search"/>, for details of operation.
</para>
<para>
@@ -1218,14 +1234,11 @@
<para>
Some storage engines allow you to specify an index type when
- creating an index. The syntax for the
- <replaceable>index_type</replaceable> specifier is
- <literal>USING <replaceable>type_name</replaceable></literal>.
- The allowable <replaceable>type_name</replaceable> values
- supported by different storage engines are shown in the
- following table. Where multiple index types are listed, the
- first one is the default when no
- <replaceable>index_type</replaceable> specifier is given.
+ creating an index. The allowable
+ <replaceable>type_name</replaceable> values supported by
+ different storage engines are shown in the following table.
+ Where multiple index types are listed, the first one is the
+ default when no index type specifier is given.
</para>
<informaltable>
@@ -1254,6 +1267,13 @@
</informaltable>
<para>
+ If you specify an index type that is not legal for a given
+ storage engine, but there is another index type available that
+ the engine can use without affecting query results, the engine
+ uses the available type.
+ </para>
+
+ <para>
Examples:
</para>
@@ -1263,20 +1283,12 @@
</programlisting>
<para>
- <literal>TYPE <replaceable>type_name</replaceable></literal> can
- be used as a synonym for <literal>USING
- <replaceable>type_name</replaceable></literal> to specify an
- index type. However, <literal>USING</literal> is the preferred
- form.
+ <literal>TYPE <replaceable>type_name</replaceable></literal> is
+ recognized as a synonym for <literal>USING
+ <replaceable>type_name</replaceable></literal>. However,
+ <literal>USING</literal> is the preferred form.
</para>
- <para>
- If you specify an index type that is not legal for a given
- storage engine, but there is another index type available that
- the engine can use without affecting query results, the engine
- uses the available type.
- </para>
-
</section>
<section id="create-table">
@@ -1329,21 +1341,20 @@
<replaceable>create_definition</replaceable>:
<replaceable>column_definition</replaceable>
| [CONSTRAINT [<replaceable>symbol</replaceable>]] PRIMARY KEY [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- | KEY [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- | INDEX [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- | [CONSTRAINT [<replaceable>symbol</replaceable>]] UNIQUE [INDEX]
+ | {INDEX|KEY} [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
+ | [CONSTRAINT [<replaceable>symbol</replaceable>]] UNIQUE [INDEX|KEY]
[<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- | {FULLTEXT|SPATIAL} [INDEX] [<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...)
+ | {FULLTEXT|SPATIAL} [INDEX|KEY] [<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...)
| [CONSTRAINT [<replaceable>symbol</replaceable>]] FOREIGN KEY
[<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...) [<replaceable>reference_definition</replaceable>]
| CHECK (<replaceable>expr</replaceable>)
<replaceable>column_definition</replaceable>:
- <replaceable>col_name</replaceable> <replaceable>type</replaceable> [NOT NULL | NULL] [DEFAULT <replaceable>default_value</replaceable>]
+ <replaceable>col_name</replaceable> <replaceable>data_type</replaceable> [NOT NULL | NULL] [DEFAULT <replaceable>default_value</replaceable>]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT '<replaceable>string</replaceable>'] [<replaceable>reference_definition</replaceable>]
-<replaceable>type</replaceable>:
+<replaceable>data_type</replaceable>:
TINYINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
@@ -1508,8 +1519,8 @@
</para>
<para>
- <replaceable>type</replaceable> represents the data type is a
- column definition. <replaceable>spatial_type</replaceable>
+ <replaceable>data_type</replaceable> represents the data type is
+ a column definition. <replaceable>spatial_type</replaceable>
represents a spatial data type. For general information on the
properties of data types other than the spatial types, see
<xref linkend="data-types"/>. For information about spatial data
@@ -1596,6 +1607,12 @@
<programlisting>
SELECT * FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>auto_col</replaceable> IS NULL
</programlisting>
+
+ <para>
+ For information about <literal>InnoDB</literal> and
+ <literal>AUTO_INCREMENT</literal>, see
+ <xref linkend="innodb-auto-increment-column"/>.
+ </para>
</listitem>
<listitem>
@@ -1624,7 +1641,9 @@
<para>
MySQL ¤t-series; interprets length specifications in
character column definitions in characters. (Versions before
- MySQL 4.1 interpreted them in bytes.)
+ MySQL 4.1 interpreted them in bytes.) Lengths for
+ <literal>BINARY</literal> and <literal>VARBINARY</literal>
+ are in bytes.
</para>
</listitem>
@@ -1727,13 +1746,12 @@
<para>
A <literal>UNIQUE</literal> index creates a constraint such
that all values in the index must be distinct. An error
- occurs if you try to add a new row with a key that matches
- an existing row. The exception to this is that if a column
- in the index is allowed to contain <literal>NULL</literal>
- values, it can contain multiple <literal>NULL</literal>
- values. This exception does not apply to
- <literal>BDB</literal> tables, for which a column with a
- <literal>UNIQUE</literal> index allows only a single
+ occurs if you try to add a new row with a key value that
+ matches an existing row. This constraint does not apply to
+ <literal>NULL</literal> values except for the
+ <literal>BDB</literal> storage engine. For other engines, a
+ <literal>UNIQUE</literal> index allows multiple
+ <literal>NULL</literal> values for columns that can contain
<literal>NULL</literal>.
</para>
</listitem>
@@ -1840,7 +1858,7 @@
</para>
<para>
- For more information about how MySQL uses indexes, see
+ For more information about indexes, see
<xref linkend="mysql-indexes"/>.
</para>
</listitem>
@@ -1869,18 +1887,26 @@
<listitem>
<para>
- With
+ For <literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ <literal>BINARY</literal>, and <literal>VARBINARY</literal>
+ columns, indexes can be created that use only the leading
+ part of column values, using
<literal><replaceable>col_name</replaceable>(<replaceable>length</replaceable>)</literal>
- syntax in an index specification, you can create an index
- that uses only part of a column. Index entries consist of
- the first <replaceable>length</replaceable> characters of
- each column value for <literal>CHAR</literal> and
- <literal>VARCHAR</literal> columns, and the first
- <replaceable>length</replaceable> bytes of each column value
- for <literal>BINARY</literal> and
- <literal>VARBINARY</literal> columns. Indexing only a prefix
- of column values like this can make the index file much
- smaller. See <xref linkend="indexes"/>.
+ syntax to specify an index prefix length.
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns
+ also can be indexed, but a prefix length
+ <emphasis>must</emphasis> be given. Prefix lengths are given
+ in characters for non-binary string types and in bytes for
+ binary string types. That is, index entries consist of the
+ first <replaceable>length</replaceable> characters of each
+ column value for <literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, and <literal>TEXT</literal>
+ columns, and the first <replaceable>length</replaceable>
+ bytes of each column value for <literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, and <literal>BLOB</literal>
+ columns. Indexing only a prefix of column values like this
+ can make the index file much smaller. See
+ <xref linkend="indexes"/>.
</para>
<indexterm>
@@ -1904,13 +1930,10 @@
</indexterm>
<para>
- The <literal>MyISAM</literal>, <literal>BDB</literal>, and
- <literal>InnoDB</literal> storage engines support indexing
- on <literal>BLOB</literal> and <literal>TEXT</literal>
- columns. When indexing a <literal>BLOB</literal> or
- <literal>TEXT</literal> column, you
- <emphasis>must</emphasis> specify a prefix length for the
- index. For example:
+ Only the <literal>MyISAM</literal>, <literal>BDB</literal>,
+ and <literal>InnoDB</literal> storage engines support
+ indexing on <literal>BLOB</literal> and
+ <literal>TEXT</literal> columns. For example:
</para>
<programlisting>
@@ -2339,8 +2362,9 @@
<para>
The maximum number of rows you plan to store in the table.
- This is not a hard limit, but rather an indicator that the
- table must be able to store at least this many rows.
+ This is not a hard limit, but rather a hint to the storage
+ engine that the table must be able to store at least this
+ many rows.
</para>
</listitem>
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-05-19 19:40:06 UTC (rev 2133)
+++ trunk/refman-5.1/sql-syntax.xml 2006-05-19 19:56:01 UTC (rev 2134)
@@ -1551,7 +1551,7 @@
<programlisting>
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <replaceable>index_name</replaceable>
- [USING <replaceable>index_type</replaceable>]
+ [USING <replaceable>type_name</replaceable>]
ON <replaceable>tbl_name</replaceable> (<replaceable>index_col_name</replaceable>,...)
[<replaceable>index_option</replaceable> ...]
@@ -1560,7 +1560,7 @@
<replaceable>index_option</replaceable>:
KEY_BLOCK_SIZE <replaceable>value</replaceable>
- | USING <replaceable>index_type</replaceable>
+ | USING <replaceable>type_name</replaceable>
| WITH PARSER <replaceable>parser_name</replaceable>
</programlisting>
@@ -1569,8 +1569,8 @@
<para>
<literal>CREATE INDEX</literal> is mapped to an <literal>ALTER
TABLE</literal> statement to create indexes. See
- <xref linkend="alter-table"/>. For more information about how
- MySQL uses indexes, see <xref linkend="mysql-indexes"/>.
+ <xref linkend="alter-table"/>. For more information about
+ indexes, see <xref linkend="mysql-indexes"/>.
</para>
<remark role="help-description-end"/>
@@ -1591,18 +1591,21 @@
<para>
For <literal>CHAR</literal>, <literal>VARCHAR</literal>,
<literal>BINARY</literal>, and <literal>VARBINARY</literal>
- columns, indexes can be created that use only part of a column,
- using
+ columns, indexes can be created that use only the leading part
+ of column values, using
<literal><replaceable>col_name</replaceable>(<replaceable>length</replaceable>)</literal>
- syntax to specify an index prefix length. Index entries consist
- of the first <replaceable>length</replaceable> characters of
- each column value for <literal>CHAR</literal> and
- <literal>VARCHAR</literal> columns, and the first
+ syntax to specify an index prefix length.
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns also
+ can be indexed, but a prefix length <emphasis>must</emphasis> be
+ given. Prefix lengths are given in characters for non-binary
+ string types and in bytes for binary string types. That is,
+ index entries consist of the first
+ <replaceable>length</replaceable> characters of each column
+ value for <literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ and <literal>TEXT</literal> columns, and the first
<replaceable>length</replaceable> bytes of each column value for
- <literal>BINARY</literal> and <literal>VARBINARY</literal>
- columns. <literal>BLOB</literal> and <literal>TEXT</literal>
- columns also can be indexed, but a prefix length
- <emphasis>must</emphasis> be given.
+ <literal>BINARY</literal>, <literal>VARBINARY</literal>, and
+ <literal>BLOB</literal> columns.
</para>
<para>
@@ -1635,11 +1638,24 @@
</para>
<para>
+ A <literal>UNIQUE</literal> index creates a constraint such that
+ all values in the index must be distinct. An error occurs if you
+ try to add a new row with a key value that matches an existing
+ row. This constraint does not apply to <literal>NULL</literal>
+ values except for the <literal>BDB</literal> storage engine. For
+ other engines, a <literal>UNIQUE</literal> index allows multiple
+ <literal>NULL</literal> values for columns that can contain
+ <literal>NULL</literal>.
+ </para>
+
+ <para>
<literal>FULLTEXT</literal> indexes are supported only for
<literal>MyISAM</literal> tables and can include only
<literal>CHAR</literal>, <literal>VARCHAR</literal>, and
- <literal>TEXT</literal> columns. See
- <xref linkend="fulltext-search"/>.
+ <literal>TEXT</literal> columns. Indexing always happens over
+ the entire column; partial indexing is not supported and any
+ prefix length is ignored if specified. See
+ <xref linkend="fulltext-search"/>, for details of operation.
</para>
<para>
@@ -1712,20 +1728,16 @@
<listitem>
<para>
<literal>USING
- <replaceable>index_type</replaceable></literal>
+ <replaceable>type_name</replaceable></literal>
</para>
<para>
Some storage engines allow you to specify an index type when
- creating an index. The syntax for the
- <replaceable>index_type</replaceable> specifier is
- <literal>USING
- <replaceable>type_name</replaceable></literal>. The
- allowable <replaceable>type_name</replaceable> values
- supported by different storage engines are shown in the
- following table. Where multiple index types are listed, the
- first one is the default when no
- <replaceable>index_type</replaceable> specifier is given.
+ creating an index. The allowable
+ <replaceable>type_name</replaceable> values supported by
+ different storage engines are shown in the following table.
+ Where multiple index types are listed, the first one is the
+ default when no index type specifier is given.
</para>
<informaltable>
@@ -1754,6 +1766,13 @@
</informaltable>
<para>
+ If you specify an index type that is not legal for a given
+ storage engine, but there is another index type available
+ that the engine can use without affecting query results, the
+ engine uses the available type.
+ </para>
+
+ <para>
Examples:
</para>
@@ -1764,20 +1783,12 @@
<para>
<literal>TYPE <replaceable>type_name</replaceable></literal>
- can be used as a synonym for <literal>USING
- <replaceable>type_name</replaceable></literal> to specify an
- index type. However, <literal>USING</literal> is the
- preferred form.
+ is recognized as a synonym for <literal>USING
+ <replaceable>type_name</replaceable></literal>. However,
+ <literal>USING</literal> is the preferred form.
</para>
<para>
- If you specify an index type that is not legal for a given
- storage engine, but there is another index type available
- that the engine can use without affecting query results, the
- engine uses the available type.
- </para>
-
- <para>
Note: Before MySQL 5.1.10, this option can be given only
before the <literal>ON
<replaceable>tbl_name</replaceable></literal> clause. Use of
@@ -1860,25 +1871,23 @@
<replaceable>column_definition</replaceable>
| [CONSTRAINT [<replaceable>symbol</replaceable>]] PRIMARY KEY [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
[<replaceable>index_option</replaceable> ...]
- | KEY [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
+ | {INDEX|KEY} [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
[<replaceable>index_option</replaceable> ...]
- | INDEX [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
- [<replaceable>index_option</replaceable> ...]
- | [CONSTRAINT [<replaceable>symbol</replaceable>]] UNIQUE [INDEX]
+ | [CONSTRAINT [<replaceable>symbol</replaceable>]] UNIQUE [INDEX|KEY]
[<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...)
[<replaceable>index_option</replaceable> ...]
- | {FULLTEXT|SPATIAL} [INDEX] [<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...)
+ | {FULLTEXT|SPATIAL} [INDEX|KEY] [<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...)
[<replaceable>index_option</replaceable> ...]
| [CONSTRAINT [<replaceable>symbol</replaceable>]] FOREIGN KEY
[<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...) [<replaceable>reference_definition</replaceable>]
| CHECK (<replaceable>expr</replaceable>)
<replaceable>column_definition</replaceable>:
- <replaceable>col_name</replaceable> <replaceable>type</replaceable> [NOT NULL | NULL] [DEFAULT <replaceable>default_value</replaceable>]
+ <replaceable>col_name</replaceable> <replaceable>data_type</replaceable> [NOT NULL | NULL] [DEFAULT <replaceable>default_value</replaceable>]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT '<replaceable>string</replaceable>'] [<replaceable>reference_definition</replaceable>]
-<replaceable>type</replaceable>:
+<replaceable>data_type</replaceable>:
TINYINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(<replaceable>length</replaceable>)] [UNSIGNED] [ZEROFILL]
@@ -2086,8 +2095,8 @@
</para>
<para>
- <replaceable>type</replaceable> represents the data type is a
- column definition. <replaceable>spatial_type</replaceable>
+ <replaceable>data_type</replaceable> represents the data type is
+ a column definition. <replaceable>spatial_type</replaceable>
represents a spatial data type. For general information on the
properties of data types other than the spatial types, see
<xref linkend="data-types"/>. For information about spatial data
@@ -2174,6 +2183,12 @@
<programlisting>
SELECT * FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>auto_col</replaceable> IS NULL
</programlisting>
+
+ <para>
+ For information about <literal>InnoDB</literal> and
+ <literal>AUTO_INCREMENT</literal>, see
+ <xref linkend="innodb-auto-increment-column"/>.
+ </para>
</listitem>
<listitem>
@@ -2202,7 +2217,9 @@
<para>
MySQL ¤t-series; interprets length specifications in
character column definitions in characters. (Versions before
- MySQL 4.1 interpreted them in bytes.)
+ MySQL 4.1 interpreted them in bytes.) Lengths for
+ <literal>BINARY</literal> and <literal>VARBINARY</literal>
+ are in bytes.
</para>
</listitem>
@@ -2305,13 +2322,12 @@
<para>
A <literal>UNIQUE</literal> index creates a constraint such
that all values in the index must be distinct. An error
- occurs if you try to add a new row with a key that matches
- an existing row. The exception to this is that if a column
- in the index is allowed to contain <literal>NULL</literal>
- values, it can contain multiple <literal>NULL</literal>
- values. This exception does not apply to
- <literal>BDB</literal> tables, for which a column with a
- <literal>UNIQUE</literal> index allows only a single
+ occurs if you try to add a new row with a key value that
+ matches an existing row. This constraint does not apply to
+ <literal>NULL</literal> values except for the
+ <literal>BDB</literal> storage engine. For other engines, a
+ <literal>UNIQUE</literal> index allows multiple
+ <literal>NULL</literal> values for columns that can contain
<literal>NULL</literal>.
</para>
</listitem>
@@ -2413,18 +2429,23 @@
</programlisting>
<para>
- <replaceable>index_option</replaceable> values specify
- additional options for an index.
+ Before MySQL 5.1.10, <literal>USING</literal> can be given
+ only before the index column list. As of 5.1.10, the
+ preferred position is after the column list. Use of the
+ option before the column list will no longer be recognized
+ as of MySQL 5.3.
</para>
<para>
- For details about <literal>USING</literal> and
+ <replaceable>index_option</replaceable> values specify
+ additional options for an index. <literal>USING</literal> is
+ one such option. For details about allowable
<replaceable>index_option</replaceable> values, see
<xref linkend="create-index"/>.
</para>
<para>
- For more information about how MySQL uses indexes, see
+ For more information about indexes, see
<xref linkend="mysql-indexes"/>.
</para>
</listitem>
@@ -2453,18 +2474,26 @@
<listitem>
<para>
- With
+ For <literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ <literal>BINARY</literal>, and <literal>VARBINARY</literal>
+ columns, indexes can be created that use only the leading
+ part of column values, using
<literal><replaceable>col_name</replaceable>(<replaceable>length</replaceable>)</literal>
- syntax in an index specification, you can create an index
- that uses only part of a column. Index entries consist of
- the first <replaceable>length</replaceable> characters of
- each column value for <literal>CHAR</literal> and
- <literal>VARCHAR</literal> columns, and the first
- <replaceable>length</replaceable> bytes of each column value
- for <literal>BINARY</literal> and
- <literal>VARBINARY</literal> columns. Indexing only a prefix
- of column values like this can make the index file much
- smaller. See <xref linkend="indexes"/>.
+ syntax to specify an index prefix length.
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns
+ also can be indexed, but a prefix length
+ <emphasis>must</emphasis> be given. Prefix lengths are given
+ in characters for non-binary string types and in bytes for
+ binary string types. That is, index entries consist of the
+ first <replaceable>length</replaceable> characters of each
+ column value for <literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, and <literal>TEXT</literal>
+ columns, and the first <replaceable>length</replaceable>
+ bytes of each column value for <literal>BINARY</literal>,
+ <literal>VARBINARY</literal>, and <literal>BLOB</literal>
+ columns. Indexing only a prefix of column values like this
+ can make the index file much smaller. See
+ <xref linkend="indexes"/>.
</para>
<indexterm>
@@ -2488,13 +2517,10 @@
</indexterm>
<para>
- The <literal>MyISAM</literal>, <literal>BDB</literal>, and
- <literal>InnoDB</literal> storage engines support indexing
- on <literal>BLOB</literal> and <literal>TEXT</literal>
- columns. When indexing a <literal>BLOB</literal> or
- <literal>TEXT</literal> column, you
- <emphasis>must</emphasis> specify a prefix length for the
- index. For example:
+ Only the <literal>MyISAM</literal>, <literal>BDB</literal>,
+ and <literal>InnoDB</literal> storage engines support
+ indexing on <literal>BLOB</literal> and
+ <literal>TEXT</literal> columns. For example:
</para>
<programlisting>
@@ -2549,10 +2575,11 @@
partial indexing is not supported and any prefix length is
ignored if specified. See <xref linkend="fulltext-search"/>,
for details of operation. A <literal>WITH PARSER</literal>
- clause can be specified to associate a parser plugin with
- the index if full-text indexing and searching operations
- need special handling. This clause is legal only for
- <literal>FULLTEXT</literal> indexes. See
+ clause can be specified as an
+ <replaceable>index_option</replaceable> value to associate a
+ parser plugin with the index if full-text indexing and
+ searching operations need special handling. This clause is
+ legal only for <literal>FULLTEXT</literal> indexes. See
<xref linkend="plugin-api"/>, for details on creating
plugins.
</para>
@@ -2966,8 +2993,9 @@
<para>
The maximum number of rows you plan to store in the table.
- This is not a hard limit, but rather an indicator that the
- table must be able to store at least this many rows.
+ This is not a hard limit, but rather a hint to the storage
+ engine that the table must be able to store at least this
+ many rows.
</para>
</listitem>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r2134 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 19 May |
