Author: jstephens
Date: 2008-09-16 13:11:04 +0200 (Tue, 16 Sep 2008)
New Revision: 11836
Log:
Fixes Docs Bug #39315 (TABLESPACE/STORAGE clauses underdocumented)
Modified:
trunk/refman-5.1/sql-syntax-data-definition.xml
trunk/refman-6.0/sql-syntax-data-definition.xml
Modified: trunk/refman-5.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.1/sql-syntax-data-definition.xml 2008-09-16 05:35:08 UTC (rev 11835)
+++ trunk/refman-5.1/sql-syntax-data-definition.xml 2008-09-16 11:11:04 UTC (rev 11836)
Changed blocks: 6, Lines Added: 154, Lines Deleted: 15; 9175 bytes
@@ -2191,14 +2191,120 @@
</programlisting>
<para>
- Note that we indexed <literal>c</literal> (as a <literal>PRIMARY
+ We indexed <literal>c</literal> (as a <literal>PRIMARY
KEY</literal>) because <literal>AUTO_INCREMENT</literal> columns
- must be indexed, and also that we declare <literal>c</literal> as
+ must be indexed, and we declare <literal>c</literal> as
<literal>NOT NULL</literal> because primary key columns cannot be
<literal>NULL</literal>.
</para>
<para>
+ For <literal>NDB</literal> tables, it is also possible to change
+ the storage type used for a table or column. For example, consider
+ an <literal>NDB</literal> table created as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;</userinput>
+Query OK, 0 rows affected (1.27 sec)
+</programlisting>
+
+ <para>
+ To convert this table to disk-based storage, you can use the
+ following <literal>ALTER TABLE</literal> statement:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK;</userinput>
+Query OK, 0 rows affected (2.99 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SHOW CREATE TABLE t1\G</userinput>
+*************************** 1. row ***************************
+ Table: t1
+Create Table: CREATE TABLE `t1` (
+ `c1` int(11) DEFAULT NULL
+) /*!50100 TABLESPACE ts_1 STORAGE DISK */
+ENGINE=ndbcluster DEFAULT CHARSET=latin1
+1 row in set (0.01 sec)
+</programlisting>
+
+ <para>
+ It is not necessary that the tablespace was referenced when the
+ table was originally created; however, the tablespace must be
+ referenced by the <literal>ALTER TABLE</literal>:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE t2 (c1 INT) ts_1 ENGINE NDB;</userinput>
+Query OK, 0 rows affected (1.00 sec)
+
+mysql> <userinput>ALTER TABLE t2 STORAGE DISK;</userinput>
+<errortext>ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)</errortext>
+mysql> <userinput>ALTER TABLE t2 TABLESPACE ts_1 STORAGE DISK;</userinput>
+Query OK, 0 rows affected (3.42 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+mysql> <userinput>SHOW CREATE TABLE t2\G</userinput>
+*************************** 1. row ***************************
+ Table: t1
+Create Table: CREATE TABLE `t2` (
+ `c1` int(11) DEFAULT NULL
+) /*!50100 TABLESPACE ts_1 STORAGE DISK */
+ENGINE=ndbcluster DEFAULT CHARSET=latin1
+1 row in set (0.01 sec)
+</programlisting>
+
+ <para>
+ To change the storage type of an individual column, you can use
+ <literal>ALTER TABLE ... MODIFY [COLUMN]</literal>. For example,
+ suppose you create a MySQL Cluster Disk Data table with two
+ columns, using this <literal>CREATE TABLE</literal> statement:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE t3 (c1 INT, c2 INT)</userinput>
+ -> <userinput>TABLESPACE ts_1 STORAGE DISK ENGINE NDB;</userinput>
+Query OK, 0 rows affected (1.34 sec)
+</programlisting>
+
+ <para>
+ To change column <literal>c2</literal> from disk-based to
+ in-memory storage, include a STORAGE MEMORY clause in the column
+ definition used by the ALTER TABLE statement, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE t3 MODIFY c2 INT STORAGE MEMORY;</userinput>
+Query OK, 0 rows affected (3.14 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ <para>
+ You can make an in-memory column into a disk-based column by using
+ <literal>STORAGE DISK</literal> in a similar fashion.
+ </para>
+
+ <para>
+ Column <literal>c1</literal> uses disk-based storage, since this
+ is the default for the table (determined by the table-level
+ <literal>STORAGE DISK</literal> clause in the <literal>CREATE
+ TABLE</literal> statement). However, column <literal>c2</literal>
+ uses in-memory storage, as can be seen here in the output of SHOW
+ <literal>CREATE TABLE</literal>:
+ </para>
+
+<programlisting>
+mysql> <userinput>SHOW CREATE TABLE t3\G</userinput>
+*************************** 1. row ***************************
+ Table: t3
+Create Table: CREATE TABLE `t3` (
+ `c1` int(11) DEFAULT NULL,
+ `c2` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL
+) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+1 row in set (0.02 sec)
+</programlisting>
+
+ <para>
When you add an <literal>AUTO_INCREMENT</literal> column, column
values are filled in with sequence numbers automatically. For
<literal>MyISAM</literal> tables, you can set the first sequence
@@ -4464,7 +4570,7 @@
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT '<replaceable>string</replaceable>'] [<replaceable>reference_definition</replaceable>]
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
- [STORAGE {DISK|MEMORY}]
+ [STORAGE {DISK|MEMORY|DEFAULT}]
<replaceable>data_type</replaceable>:
BIT[(<replaceable>length</replaceable>)]
@@ -4528,8 +4634,7 @@
<replaceable>reference_option</replaceable>:
RESTRICT | CASCADE | SET NULL | NO ACTION
-<replaceable>table_option</replaceable>:
- TABLESPACE <replaceable>tablespace_name</replaceable> STORAGE DISK
+<replaceable>table_option</replaceable>:
ENGINE [=] <replaceable>engine_name</replaceable>
| AUTO_INCREMENT [=] <replaceable>value</replaceable>
| AVG_ROW_LENGTH [=] <replaceable>value</replaceable>
@@ -4548,6 +4653,7 @@
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] '<replaceable>string</replaceable>'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
+ | TABLESPACE <replaceable>tablespace_name</replaceable> [STORAGE {DISK|MEMORY|DEFAULT}]
| UNION [=] (<replaceable>tbl_name</replaceable>[,<replaceable>tbl_name</replaceable>]...)
<replaceable>partition_options</replaceable>:
@@ -4937,12 +5043,29 @@
memory by using a <literal>STORAGE</literal> clause.
<literal>STORAGE DISK</literal> causes the column to be stored
on disk, and <literal>STORAGE MEMORY</literal> causes
- in-memory storage to be used.
+ in-memory storage to be used. The <literal>CREATE
+ TABLE</literal> statement used must still include a
+ <literal>TABLESPACE</literal> clause:
</para>
+<programlisting>
+mysql> <userinput>CREATE TABLE t1 (</userinput>
+ -> <userinput>c1 INT STORAGE DISK,</userinput>
+ -> <userinput>c2 INT STORAGE MEMORY</userinput>
+ -> <userinput>) ENGINE NDB;</userinput>
+<errortext>ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140)</errortext>
+
+mysql> <userinput>CREATE TABLE t1 (</userinput>
+ -> <userinput>c1 INT STORAGE DISK,</userinput>
+ -> <userinput>c2 INT STORAGE MEMORY</userinput>
+ -> <userinput>) TABLESPACE ts_1 ENGINE NDB;</userinput>
+Query OK, 0 rows affected (1.06 sec)
+</programlisting>
+
<para>
- For <literal>NDB</literal> tables, the default is
- <literal>MEMORY</literal>.
+ For <literal>NDB</literal> tables, <literal>STORAGE
+ DEFAULT</literal> is equivalent to <literal>STORAGE
+ MEMORY</literal>.
</para>
<para>
@@ -5327,16 +5450,32 @@
</itemizedlist>
<para>
- The <literal>TABLESPACE ... STORAGE DISK</literal> table option is
- used only with <literal>NDBCLUSTER</literal> tables. It assigns
- the table to a Cluster Disk Data tablespace. The tablespace named
- <replaceable>tablespace_name</replaceable> must already have been
- created using <literal>CREATE TABLESPACE</literal>. This table
- option was introduced in MySQL 5.1.6. See
- <xref linkend="mysql-cluster-disk-data"/>.
+ The <literal>TABLESPACE</literal> and <literal>STORAGE</literal>
+ table options were both introduced in MySQL 5.1.6. In MySQL 5.1,
+ they are employed only with <literal>NDBCLUSTER</literal> tables.
+ The tablespace named <replaceable>tablespace_name</replaceable>
+ must already have been created using <literal>CREATE
+ TABLESPACE</literal>. <literal>STORAGE</literal> determines the
+ type of storage used (disk or memory), and can be one of
+ <literal>DISK</literal>, <literal>MEMORY</literal>, or
+ <literal>DEFAULT</literal>.
</para>
<para>
+ <literal>TABLESPACE ... STORAGE DISK</literal> assigns a table to
+ a MySQL Cluster Disk Data tablespace. See
+ <xref linkend="mysql-cluster-disk-data"/>, for more information.
+ </para>
+
+ <important>
+ <para>
+ A <literal>STORAGE</literal> clause cannot be used in a
+ <literal>CREATE TABLE</literal> statement without a
+ <literal>TABLESPACE</literal> clause.
+ </para>
+ </important>
+
+ <para>
The <literal>ENGINE</literal> table option specifies the storage
engine for the table.
</para>
Modified: trunk/refman-6.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-data-definition.xml 2008-09-16 05:35:08 UTC (rev 11835)
+++ trunk/refman-6.0/sql-syntax-data-definition.xml 2008-09-16 11:11:04 UTC (rev 11836)
Changed blocks: 2, Lines Added: 1, Lines Deleted: 1; 1011 bytes
@@ -3884,7 +3884,6 @@
RESTRICT | CASCADE | SET NULL | NO ACTION
<replaceable>table_option</replaceable>:
- TABLESPACE <replaceable>tablespace_name</replaceable>
ENGINE [=] <replaceable>engine_name</replaceable>
| AUTO_INCREMENT [=] <replaceable>value</replaceable>
| AVG_ROW_LENGTH [=] <replaceable>value</replaceable>
@@ -3903,6 +3902,7 @@
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] '<replaceable>string</replaceable>'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
+ | TABLESPACE <replaceable>tablespace_name</replaceable>
| UNION [=] (<replaceable>tbl_name</replaceable>[,<replaceable>tbl_name</replaceable>]...)
<replaceable>partition_options</replaceable>:
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r11836 - in trunk: refman-5.1 refman-6.0 | jon | 16 Sep |
