ALTER TABLE
Syntax
1015
is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting
rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
• Pending
INSERT DELAYED
statements are lost if a table is write locked and
ALTER TABLE
is used
to modify the table structure.
•
table_option
signifies a table option of the kind that can be used in the
CREATE TABLE
statement, such as
ENGINE
,
AUTO_INCREMENT
, or
AVG_ROW_LENGTH
. (
Section 13.1.10, “
CREATE
TABLE
Syntax”
, lists all table options.) However,
ALTER TABLE
ignores the
DATA DIRECTORY
and
INDEX DIRECTORY
table options.
For example, to convert a table to be an
InnoDB
table, use this statement:
ALTER TABLE t1 ENGINE = InnoDB;
When you specify an
ENGINE
clause,
ALTER TABLE
rebuilds the table. This is true even if the table
already has the specified storage engine.
The outcome of attempting to change a table's storage engine is affected by whether the desired
storage engine is available and the setting of the
NO_ENGINE_SUBSTITUTION
[537]
SQL mode, as
described in
Section 5.1.7, “Server SQL Modes”
.
As of MySQL 5.0.23, to prevent inadvertent loss of data,
ALTER TABLE
cannot be used to change
the storage engine of a table to
MERGE
or
BLACKHOLE
.
To change the value of the
AUTO_INCREMENT
counter to be used for new rows, do this:
ALTER TABLE t2 AUTO_INCREMENT =
value
;
You cannot reset the counter to a value less than or equal to any that have already been used. For
MyISAM
, if the value is less than or equal to the maximum value currently in the
AUTO_INCREMENT
column, the value is reset to the current maximum plus one. For
InnoDB
, you can use
ALTER
TABLE ... AUTO_INCREMENT = value
as of MySQL 5.0.3, but if the value is less than the
current maximum value in the column, no error occurs and the current sequence value is not
changed.
• You can issue multiple
ADD
,
ALTER
,
DROP
, and
CHANGE
clauses in a single
ALTER TABLE
statement, separated by commas. This is a MySQL extension to standard SQL, which permits only
one of each clause per
ALTER TABLE
statement. For example, to drop multiple columns in a single
statement, do this:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
•
CHANGE col_name
,
DROP col_name
, and
DROP INDEX
are MySQL extensions to standard SQL.
• The word
COLUMN
is optional and can be omitted.
•
column_definition
clauses use the same syntax for
ADD
and
CHANGE
as for
CREATE TABLE
.
See
Section 13.1.10, “
CREATE TABLE
Syntax”
.
• You can rename a column using a
CHANGE old_col_name new_col_name
column_definition
clause. To do so, specify the old and new column names and the definition
that the column currently has. For example, to rename an
INTEGER
column from
a
to
b
, you can do
this:
ALTER TABLE t1 CHANGE a b INTEGER;
To change a column's type but not the name,
CHANGE
syntax still requires an old and new column
name, even if they are the same. For example:
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
You can also use
MODIFY
to change a column's type without renaming it:
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
