| List: | Commits | « Previous MessageNext Message » | |
| From: | paul | Date: | February 8 2007 4:53pm |
| Subject: | svn commit - mysqldoc@docsrva: r4860 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | ||
| View as plain text | |||
Author: paul Date: 2007-02-08 16:53:43 +0100 (Thu, 08 Feb 2007) New Revision: 4860 Log: r16016@frost: paul | 2007-02-08 09:47:42 -0600 People often miss the fact that ALTER TABLE allows table_option clauses, just like CREATE TABLE. Attempt to address this: - Put table_option first in the syntax description - Give it expanded discussion early in the page - Provide examples (changing the storage engine, resetting the AUTO_INCREMENT counter, which seem to be the most commonly asked about changes). Modified: 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:19514 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:15987 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:13520 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:19514 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:16016 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:13520 Modified: trunk/refman-4.1/sql-syntax.xml =================================================================== --- trunk/refman-4.1/sql-syntax.xml 2007-02-08 15:39:16 UTC (rev 4859) +++ trunk/refman-4.1/sql-syntax.xml 2007-02-08 15:53:43 UTC (rev 4860) Changed blocks: 5, Lines Added: 52, Lines Deleted: 21; 5126 bytes @@ -143,7 +143,8 @@ <replaceable>alter_specification</replaceable> [, <replaceable>alter_specification</replaceable>] ... <replaceable>alter_specification</replaceable>: - ADD [COLUMN] <replaceable>column_definition</replaceable> [FIRST | AFTER <replaceable>col_name</replaceable> ] + <replaceable>table_option</replaceable> ... + | ADD [COLUMN] <replaceable>column_definition</replaceable> [FIRST | AFTER <replaceable>col_name</replaceable> ] | ADD [COLUMN] (<replaceable>column_definition</replaceable>,...) | ADD {INDEX|KEY} [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...) | ADD [CONSTRAINT [<replaceable>symbol</replaceable>]] @@ -170,7 +171,6 @@ | [DEFAULT] CHARACTER SET <replaceable>charset_name</replaceable> [COLLATE <replaceable>collation_name</replaceable>] | DISCARD TABLESPACE | IMPORT TABLESPACE - | <replaceable>table_option</replaceable> ... <replaceable>index_col_name</replaceable>: <replaceable>col_name</replaceable> [(<replaceable>length</replaceable>)] [ASC | DESC] @@ -193,14 +193,8 @@ <para> The syntax for many of the allowable alterations is similar to - clauses of the <literal>CREATE TABLE</literal> statement. This - includes <replaceable>table_option</replaceable> modifications, - for options such as <literal>ENGINE</literal>, - <literal>AUTO_INCREMENT</literal>, and - <literal>AVG_ROW_LENGTH</literal>. (However, <literal>ALTER - TABLE</literal> ignores the <literal>DATA DIRECTORY</literal> - and <literal>INDEX DIRECTORY</literal> table options.) - <xref linkend="create-table"/>, lists all table options. + clauses of the <literal>CREATE TABLE</literal> statement. See + <xref linkend="create-table"/>, for more information. </para> <para> @@ -285,6 +279,54 @@ <listitem> <para> + <literal>table_option</literal> signifies a table option of + the kind that can be used in the <literal>CREATE + TABLE</literal> statement. (<xref linkend="create-table"/>, + lists all table options.) This includes options such as + <literal>ENGINE</literal>, + <literal>AUTO_INCREMENT</literal>, and + <literal>AVG_ROW_LENGTH</literal>. However, <literal>ALTER + TABLE</literal> ignores the <literal>DATA + DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal> + table options. + </para> + + <para> + For example, to convert a table to be an + <literal>InnoDB</literal> table, use this statement: + </para> + +<programlisting> +ALTER TABLE t1 ENGINE = InnoDB; +</programlisting> + + <para> + To change the value of the <literal>AUTO_INCREMENT</literal> + counter to be used for new rows, do this: + </para> + +<programlisting> +ALTER TABLE t2 AUTO_INCREMENT = <replaceable>value</replaceable>; +</programlisting> + + <para> + You cannot reset the counter to a value less than or equal + to any that have already been used. For + <literal>MyISAM</literal>, if the value is less than or + equal to the maximum value currently in the + <literal>AUTO_INCREMENT</literal> column, the value is reset + to the current maximum plus one. For + <literal>InnoDB</literal>, you can use <literal>ALTER TABLE + ... AUTO_INCREMENT = + <replaceable>value</replaceable></literal> as of MySQL + 4.1.12, but <emphasis>if the value is less than the current + maximum value in the column, no error message is given and + the current sequence value is not changed.</emphasis> + </para> + </listitem> + + <listitem> + <para> You can issue multiple <literal>ADD</literal>, <literal>ALTER</literal>, <literal>DROP</literal>, and <literal>CHANGE</literal> clauses in a single <literal>ALTER @@ -897,17 +939,6 @@ </para> <para> - From MySQL 4.1.2, you can use the <literal>ALTER TABLE ... - AUTO_INCREMENT=<replaceable>value</replaceable></literal> table - option for <literal>InnoDB</literal> tables to set the sequence - number for new rows if the value is greater than the maximum - value in the <literal>AUTO_INCREMENT</literal> column. - <emphasis>If the value is less than the current maximum value in - the column, no error message is given and the current sequence - value is not changed.</emphasis> - </para> - - <para> With <literal>MyISAM</literal> tables, if you do not change the <literal>AUTO_INCREMENT</literal> column, the sequence number is not affected. If you drop an <literal>AUTO_INCREMENT</literal> Modified: trunk/refman-5.0/sql-syntax.xml =================================================================== --- trunk/refman-5.0/sql-syntax.xml 2007-02-08 15:39:16 UTC (rev 4859) +++ trunk/refman-5.0/sql-syntax.xml 2007-02-08 15:53:43 UTC (rev 4860) Changed blocks: 5, Lines Added: 59, Lines Deleted: 25; 5649 bytes @@ -185,7 +185,8 @@ <replaceable>alter_specification</replaceable> [, <replaceable>alter_specification</replaceable>] ... <replaceable>alter_specification</replaceable>: - ADD [COLUMN] <replaceable>column_definition</replaceable> [FIRST | AFTER <replaceable>col_name</replaceable> ] + <replaceable>table_option</replaceable> ... + | ADD [COLUMN] <replaceable>column_definition</replaceable> [FIRST | AFTER <replaceable>col_name</replaceable> ] | ADD [COLUMN] (<replaceable>column_definition</replaceable>,...) | ADD {INDEX|KEY} [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...) | ADD [CONSTRAINT [<replaceable>symbol</replaceable>]] @@ -212,7 +213,6 @@ | [DEFAULT] CHARACTER SET <replaceable>charset_name</replaceable> [COLLATE <replaceable>collation_name</replaceable>] | DISCARD TABLESPACE | IMPORT TABLESPACE - | <replaceable>table_option</replaceable> ... <replaceable>index_col_name</replaceable>: <replaceable>col_name</replaceable> [(<replaceable>length</replaceable>)] [ASC | DESC] @@ -235,18 +235,8 @@ <para> The syntax for many of the allowable alterations is similar to - clauses of the <literal>CREATE TABLE</literal> statement. This - includes <replaceable>table_option</replaceable> modifications, - for options such as <literal>ENGINE</literal>, - <literal>AUTO_INCREMENT</literal>, and - <literal>AVG_ROW_LENGTH</literal>. (However, <literal>ALTER - TABLE</literal> ignores the <literal>DATA DIRECTORY</literal> - and <literal>INDEX DIRECTORY</literal> table options.) - <xref linkend="create-table"/>, lists all table options. As of - MySQL 5.0.23, to prevent inadvertent loss of data, - <literal>ALTER TABLE</literal> cannot be used to change the - storage engine of a table to <literal>MERGE</literal> or - <literal>BLACKHOLE</literal>. + clauses of the <literal>CREATE TABLE</literal> statement. See + <xref linkend="create-table"/>, for more information. </para> <para> @@ -326,6 +316,61 @@ <listitem> <para> + <literal>table_option</literal> signifies a table option of + the kind that can be used in the <literal>CREATE + TABLE</literal> statement. (<xref linkend="create-table"/>, + lists all table options.) This includes options such as + <literal>ENGINE</literal>, + <literal>AUTO_INCREMENT</literal>, and + <literal>AVG_ROW_LENGTH</literal>. However, <literal>ALTER + TABLE</literal> ignores the <literal>DATA + DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal> + table options. + </para> + + <para> + For example, to convert a table to be an + <literal>InnoDB</literal> table, use this statement: + </para> + +<programlisting> +ALTER TABLE t1 ENGINE = InnoDB; +</programlisting> + + <para> + As of MySQL 5.0.23, to prevent inadvertent loss of data, + <literal>ALTER TABLE</literal> cannot be used to change the + storage engine of a table to <literal>MERGE</literal> or + <literal>BLACKHOLE</literal>. + </para> + + <para> + To change the value of the <literal>AUTO_INCREMENT</literal> + counter to be used for new rows, do this: + </para> + +<programlisting> +ALTER TABLE t2 AUTO_INCREMENT = <replaceable>value</replaceable>; +</programlisting> + + <para> + You cannot reset the counter to a value less than or equal + to any that have already been used. For + <literal>MyISAM</literal>, if the value is less than or + equal to the maximum value currently in the + <literal>AUTO_INCREMENT</literal> column, the value is reset + to the current maximum plus one. For + <literal>InnoDB</literal>, you can use <literal>ALTER TABLE + ... AUTO_INCREMENT = + <replaceable>value</replaceable></literal> as of MySQL + 5.0.3, but <emphasis>if the value is less than the current + maximum value in the column, no error message is given and + the current sequence value is not changed.</emphasis> + </para> + </listitem> + + <listitem> + <para> You can issue multiple <literal>ADD</literal>, <literal>ALTER</literal>, <literal>DROP</literal>, and <literal>CHANGE</literal> clauses in a single <literal>ALTER @@ -914,17 +959,6 @@ </para> <para> - From MySQL 5.0.3, you can use the <literal>ALTER TABLE ... - AUTO_INCREMENT=<replaceable>value</replaceable></literal> table - option for <literal>InnoDB</literal> tables to set the sequence - number for new rows if the value is greater than the maximum - value in the <literal>AUTO_INCREMENT</literal> column. - <emphasis>If the value is less than the current maximum value in - the column, no error message is given and the current sequence - value is not changed.</emphasis> - </para> - - <para> With <literal>MyISAM</literal> tables, if you do not change the <literal>AUTO_INCREMENT</literal> column, the sequence number is not affected. If you drop an <literal>AUTO_INCREMENT</literal> Modified: trunk/refman-5.1/sql-syntax.xml =================================================================== --- trunk/refman-5.1/sql-syntax.xml 2007-02-08 15:39:16 UTC (rev 4859) +++ trunk/refman-5.1/sql-syntax.xml 2007-02-08 15:53:43 UTC (rev 4860) Changed blocks: 5, Lines Added: 57, Lines Deleted: 25; 5546 bytes @@ -192,7 +192,8 @@ <replaceable>alter_specification</replaceable> [, <replaceable>alter_specification</replaceable>] ... <replaceable>alter_specification</replaceable>: - ADD [COLUMN] <replaceable>column_definition</replaceable> [FIRST | AFTER <replaceable>col_name</replaceable> ] + <replaceable>table_option</replaceable> ... + | ADD [COLUMN] <replaceable>column_definition</replaceable> [FIRST | AFTER <replaceable>col_name</replaceable> ] | ADD [COLUMN] (<replaceable>column_definition</replaceable>,...) | ADD {INDEX|KEY} [<replaceable>index_name</replaceable>] [<replaceable>index_type</replaceable>] (<replaceable>index_col_name</replaceable>,...) | ADD [CONSTRAINT [<replaceable>symbol</replaceable>]] @@ -221,7 +222,6 @@ | [DEFAULT] CHARACTER SET <replaceable>charset_name</replaceable> [COLLATE <replaceable>collation_name</replaceable>] | DISCARD TABLESPACE | IMPORT TABLESPACE - | <replaceable>table_option</replaceable> ... | PARTITION BY <replaceable>partition_options</replaceable> | ADD PARTITION (<replaceable>partition_definition</replaceable>) | DROP PARTITION <replaceable>partition_names</replaceable> @@ -255,18 +255,8 @@ <para> The syntax for many of the allowable alterations is similar to - clauses of the <literal>CREATE TABLE</literal> statement. This - includes <replaceable>table_option</replaceable> modifications, - for options such as <literal>ENGINE</literal>, - <literal>AUTO_INCREMENT</literal>, and - <literal>AVG_ROW_LENGTH</literal>. (However, <literal>ALTER - TABLE</literal> ignores the <literal>DATA DIRECTORY</literal> - and <literal>INDEX DIRECTORY</literal> table options.) - <xref linkend="create-table"/>, lists all table options. As of - MySQL 5.1.11, to prevent inadvertent loss of data, - <literal>ALTER TABLE</literal> cannot be used to change the - storage engine of a table to <literal>MERGE</literal> or - <literal>BLACKHOLE</literal>. + clauses of the <literal>CREATE TABLE</literal> statement. See + <xref linkend="create-table"/>, for more information. </para> <para> @@ -363,6 +353,59 @@ <listitem> <para> + <literal>table_option</literal> signifies a table option of + the kind that can be used in the <literal>CREATE + TABLE</literal> statement. (<xref linkend="create-table"/>, + lists all table options.) This includes options such as + <literal>ENGINE</literal>, + <literal>AUTO_INCREMENT</literal>, and + <literal>AVG_ROW_LENGTH</literal>. However, <literal>ALTER + TABLE</literal> ignores the <literal>DATA + DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal> + table options. + </para> + + <para> + For example, to convert a table to be an + <literal>InnoDB</literal> table, use this statement: + </para> + +<programlisting> +ALTER TABLE t1 ENGINE = InnoDB; +</programlisting> + + <para> + As of MySQL 5.1.11, to prevent inadvertent loss of data, + <literal>ALTER TABLE</literal> cannot be used to change the + storage engine of a table to <literal>MERGE</literal> or + <literal>BLACKHOLE</literal>. + </para> + + <para> + To change the value of the <literal>AUTO_INCREMENT</literal> + counter to be used for new rows, do this: + </para> + +<programlisting> +ALTER TABLE t2 AUTO_INCREMENT = <replaceable>value</replaceable>; +</programlisting> + + <para> + You cannot reset the counter to a value less than or equal + to any that have already been used. For + <literal>MyISAM</literal>, if the value is less than or + equal to the maximum value currently in the + <literal>AUTO_INCREMENT</literal> column, the value is reset + to the current maximum plus one. For + <literal>InnoDB</literal>, <emphasis>if the value is less + than the current maximum value in the column, no error + message is given and the current sequence value is not + changed.</emphasis> + </para> + </listitem> + + <listitem> + <para> You can issue multiple <literal>ADD</literal>, <literal>ALTER</literal>, <literal>DROP</literal>, and <literal>CHANGE</literal> clauses in a single <literal>ALTER @@ -1226,17 +1269,6 @@ </para> <para> - You can use the <literal>ALTER TABLE ... - AUTO_INCREMENT=<replaceable>value</replaceable></literal> table - option for <literal>InnoDB</literal> tables to set the sequence - number for new rows if the value is greater than the maximum - value in the <literal>AUTO_INCREMENT</literal> column. - <emphasis>If the value is less than the current maximum value in - the column, no error message is given and the current sequence - value is not changed.</emphasis> - </para> - - <para> With <literal>MyISAM</literal> tables, if you do not change the <literal>AUTO_INCREMENT</literal> column, the sequence number is not affected. If you drop an <literal>AUTO_INCREMENT</literal>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r4860 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 8 Feb |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.510 sec. using MySQL 5.1.14-beta-log