ALTER [IGNORE] TABLEtbl_name
{table_options
|partitioning_specification
] ...table_options
:table_option
[,table_option
] ...table_option
: ADD [COLUMN]col_name
column_definition
[FIRST | AFTERcol_name
] | ADD [COLUMN] (col_name
column_definition
,...) | ADD {INDEX|KEY} [index_name
] [index_type
] (index_col_name
,...) [index_option
] ... | ADD [CONSTRAINT [symbol
]] PRIMARY KEY [index_type
] (index_col_name
,...) [index_option
] ... | ADD [CONSTRAINT [symbol
]] UNIQUE [INDEX|KEY] [index_name
] [index_type
] (index_col_name
,...) [index_option
] ... | ADD FULLTEXT [INDEX|KEY] [index_name
] (index_col_name
,...) [index_option
] ... | ADD SPATIAL [INDEX|KEY] [index_name
] (index_col_name
,...) [index_option
] ... | ADD [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
,...)reference_definition
| ALTER [COLUMN]col_name
{SET DEFAULTliteral
| DROP DEFAULT} | CHANGE [COLUMN]old_col_name
new_col_name
column_definition
[FIRST|AFTERcol_name
] | MODIFY [COLUMN]col_name
column_definition
[FIRST | AFTERcol_name
] | DROP [COLUMN]col_name
| DROP PRIMARY KEY | DROP {INDEX|KEY}index_name
| DROP FOREIGN KEYfk_symbol
| DISABLE KEYS | ENABLE KEYS | RENAME [TO]new_tbl_name
| ORDER BYcol_name
[,col_name
] ... | CONVERT TO CHARACTER SETcharset_name
[COLLATEcollation_name
] | [DEFAULT] CHARACTER SET [=]charset_name
[COLLATE [=]collation_name
] | DISCARD TABLESPACE | IMPORT TABLESPACEpartitioning_specification
: ADD PARTITION (partition_definition
) | DROP PARTITIONpartition_names
| COALESCE PARTITIONnumber
| REORGANIZE PARTITION [partition_names
INTO (partition_definitions
)] | ANALYZE PARTITION {partition_names
| ALL } | CHECK PARTITION {partition_names
| ALL } | OPTIMIZE PARTITION {partition_names
| ALL } | REBUILD PARTITION {partition_names
| ALL } | REPAIR PARTITION {partition_names
| ALL } | PARTITION BYpartitioning_expression
| REMOVE PARTITIONINGindex_col_name
:col_name
[(length
)] [ASC | DESC]index_type
: USING {BTREE | HASH | RTREE}index_option
: KEY_BLOCK_SIZE [=]value
|index_type
| WITH PARSERparser_name
| COMMENT 'string
'table_options
:table_option
[[,]table_option
] ... (seeCREATE TABLE
options)
ALTER TABLE
enables you to change
the structure of an existing table. For example, you can add or
delete columns, create or destroy indexes, change the type of
existing columns, or rename columns or the table itself. You can
also change the comment for the table and type of the table.
The syntax for many of the allowable alterations is similar to
clauses of the CREATE TABLE
statement. See Section 12.1.14, “CREATE TABLE
Syntax”, for more
information.
Some operations may result in warnings if attempted on a table for
which the storage engine does not support the operation. These
warnings can be displayed with SHOW
WARNINGS
. See Section 12.4.5.41, “SHOW WARNINGS
Syntax”.
In most cases, ALTER TABLE
works by
making a temporary copy of the original table. The alteration is
performed on the copy, and then the original table is deleted and
the new one is renamed. While ALTER
TABLE
is executing, the original table is readable by
other sessions. Updates and writes to the table are stalled until
the new table is ready, and then are automatically redirected to
the new table without any failed updates. The temporary table is
created in the database directory of the new table. This can be
different from the database directory of the original table if
ALTER TABLE
is renaming the table
to a different database.
In some cases, no temporary table is necessary:
Alterations that modify only table metadata and not table data
can be made immediately by altering the table's
.frm
file and not touching table
contents. The following changes are fast alterations that can
be made this way:
If you use ALTER TABLE
without any
other options, MySQL simply renames any files that correspond
to the table tbl_name
RENAME TO
new_tbl_name
tbl_name
. (You can
also use the RENAME TABLE
statement to rename tables. See
Section 12.1.26, “RENAME TABLE
Syntax”.) Any privileges granted
specifically for the renamed table are not migrated to the new
name. They must be changed manually.
ALTER TABLE ... ADD PARTITION
creates no
temporary table except for MySQL Cluster.
ADD
or DROP
operations
for RANGE
or LIST
partitions are immediate operations or nearly so.
ADD
or COALESCE
operations for HASH
or
KEY
partitions copy data between changed
partitions; unless LINEAR HASH
or
LINEAR KEY
was used, this is much the same
as creating a new table (although the operation is done
partition by partition). REORGANIZE
operations copy only changed partitions and do not touch
unchanged ones.
If other cases, MySQL creates a temporary table, even if the data
wouldn't strictly need to be copied. For MyISAM
tables, you can speed up the index re-creation operation (which is
the slowest part of the alteration process) by setting the
myisam_sort_buffer_size
system
variable to a high value.
You can force an ALTER TABLE
operation to use
the temporary table method (as supported in MySQL 5.0) by setting
old-alter-table
to ON
.
For information on troubleshooting ALTER
TABLE
, see Section B.5.7.1, “Problems with ALTER TABLE
”.
To use ALTER TABLE
, you need
ALTER
,
INSERT
, and
CREATE
privileges for the
table.
IGNORE
is a MySQL extension to standard
SQL. It controls how ALTER
TABLE
works if there are duplicates on unique keys
in the new table or if warnings occur when strict mode is
enabled. If IGNORE
is not specified, the
copy is aborted and rolled back if duplicate-key errors occur.
If IGNORE
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.
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 12.1.14, “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;
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
SQL
mode, as described in Section 5.1.8, “Server SQL Modes”.
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
, 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 allows 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
,
and col_name
DROP INDEX
are MySQL
extensions to standard SQL.
MODIFY
is an Oracle extension to
ALTER TABLE
.
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 12.1.14, “CREATE TABLE
Syntax”.
You can rename a column using a CHANGE
clause.
To do so, specify the old and new column names and the
definition that the column currently has. For example, to
rename an old_col_name
new_col_name
column_definition
INTEGER
column from
a
to b
, you can do this:
ALTER TABLE t1 CHANGE a b INTEGER;
If you want 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:
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
When you use CHANGE
or
MODIFY
,
column_definition
must include the
data type and all attributes that should apply to the new
column, other than index attributes such as PRIMARY
KEY
or UNIQUE
. Attributes present
in the original definition but not specified for the new
definition are not carried forward. Suppose that a column
col1
is defined as INT UNSIGNED
DEFAULT 1 COMMENT 'my column'
and you modify the
column as follows:
ALTER TABLE t1 MODIFY col1 BIGINT;
The resulting column will be defined as
BIGINT
, but will not include the attributes
UNSIGNED DEFAULT 1 COMMENT 'my column'
. To
retain them, the statement should be:
ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
When you change a data type using CHANGE
or
MODIFY
, MySQL tries to convert existing
column values to the new type as well as possible.
This conversion may result in alteration of data. For
example, if you shorten a string column, values may be
truncated. To prevent the operation from succeeding if
conversions to the new data type would result in loss of
data, enable strict SQL mode before using
ALTER TABLE
(see
Section 5.1.8, “Server SQL Modes”).
To add a column at a specific position within a table row, use
FIRST
or AFTER
. The default is
to add the column last. You can also use
col_name
FIRST
and AFTER
in
CHANGE
or MODIFY
operations to reorder columns within a table.
ALTER ... SET DEFAULT
or ALTER ...
DROP DEFAULT
specify a new default value for a
column or remove the old default value, respectively. If the
old default is removed and the column can be
NULL
, the new default is
NULL
. If the column cannot be
NULL
, MySQL assigns a default value as
described in Section 10.1.4, “Data Type Default Values”.
DROP INDEX
removes an index.
This is a MySQL extension to standard SQL. See
Section 12.1.20, “DROP INDEX
Syntax”. If you are unsure of the index
name, use SHOW INDEX FROM
.
tbl_name
If columns are dropped from a table, the columns are also
removed from any index of which they are a part. If all
columns that make up an index are dropped, the index is
dropped as well. If you use CHANGE
or
MODIFY
to shorten a column for which an
index exists on the column, and the resulting column length is
less than the index length, MySQL shortens the index
automatically.
If a table contains only one column, the column cannot be
dropped. If what you intend is to remove the table, use
DROP TABLE
instead.
DROP PRIMARY KEY
drops the primary key. If
there is no primary key, an error occurs.
If you add a UNIQUE INDEX
or
PRIMARY KEY
to a table, it is stored before
any nonunique index so that MySQL can detect duplicate keys as
early as possible.
Some storage engines allow you to specify an index type when
creating an index. The syntax for the
index_type
specifier is
USING
.
For details about type_name
USING
, see
Section 12.1.11, “CREATE INDEX
Syntax”. The preferred position is
after the column list. Use of the option before the column
list will no longer be recognized in a future MySQL release.
index_option
values specify
additional options for an index. USING
is
one such option. For details about allowable
index_option
values, see
Section 12.1.11, “CREATE INDEX
Syntax”.
After an ALTER TABLE
statement,
it may be necessary to run ANALYZE
TABLE
to update index cardinality information. See
Section 12.4.5.23, “SHOW INDEX
Syntax”.
ORDER BY
enables you to create the new
table with the rows in a specific order. Note that the table
does not remain in this order after inserts and deletes. This
option is useful primarily when you know that you are mostly
to query the rows in a certain order most of the time. By
using this option after major changes to the table, you might
be able to get higher performance. In some cases, it might
make sorting easier for MySQL if the table is in order by the
column that you want to order it by later.
ORDER BY
syntax allows for one or more
column names to be specified for sorting, each of which
optionally can be followed by ASC
or
DESC
to indicate ascending or descending
sort order, respectively. The default is ascending order. Only
column names are allowed as sort criteria; arbitrary
expressions are not allowed.
ORDER BY
does not make sense for
InnoDB
tables that contain a user-defined
clustered index (PRIMARY KEY
or
NOT NULL UNIQUE
index).
InnoDB
always orders table rows according
to such an index if one is present.
When used on a partitioned table, ALTER TABLE ...
ORDER BY
orders rows within each partition only.
If you use ALTER TABLE
on a
MyISAM
table, all nonunique indexes are
created in a separate batch (as for
REPAIR TABLE
). This should make
ALTER TABLE
much faster when
you have many indexes.
This feature can be activated explicitly for a
MyISAM
table. ALTER TABLE ...
DISABLE KEYS
tells MySQL to stop updating nonunique
indexes. ALTER TABLE ... ENABLE KEYS
then
should be used to re-create missing indexes. MySQL does this
with a special algorithm that is much faster than inserting
keys one by one, so disabling keys before performing bulk
insert operations should give a considerable speedup. Using
ALTER TABLE ... DISABLE KEYS
requires the
INDEX
privilege in addition to
the privileges mentioned earlier.
While the nonunique indexes are disabled, they are ignored for
statements such as SELECT
and
EXPLAIN
that otherwise would
use them.
If ALTER TABLE
for an
InnoDB
table results in changes to column
values (for example, because a column is truncated),
InnoDB
's FOREIGN KEY
constraint checks do not notice possible violations caused by
changing the values.
The FOREIGN KEY
and
REFERENCES
clauses are supported by the
InnoDB
storage engine, which implements
ADD [CONSTRAINT [
. See
Section 13.6.4.4, “symbol
]]
FOREIGN KEY (...) REFERENCES ... (...)FOREIGN KEY
Constraints”. For other
storage engines, the clauses are parsed but ignored. The
CHECK
clause is parsed but ignored by all
storage engines. See Section 12.1.14, “CREATE TABLE
Syntax”. The
reason for accepting but ignoring syntax clauses is for
compatibility, to make it easier to port code from other SQL
servers, and to run applications that create tables with
references. See Section 1.8.5, “MySQL Differences from Standard SQL”.
The inline REFERENCES
specifications
where the references are defined as part of the column
specification are silently ignored by
InnoDB
. InnoDB only accepts
REFERENCES
clauses defined as part of a
separate FOREIGN KEY
specification.
Partitioned tables do not support foreign keys. See Section 17.5, “Restrictions and Limitations on Partitioning”, for more information.
InnoDB
supports the use of
ALTER TABLE
to drop foreign
keys:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_symbol
;
For more information, see
Section 13.6.4.4, “FOREIGN KEY
Constraints”.
You cannot add a foreign key and drop a foreign key in
separate clauses of a single ALTER
TABLE
statement. You must use separate statements.
For an InnoDB
table that is created with
its own tablespace in an .ibd
file, that
file can be discarded and imported. To discard the
.ibd
file, use this statement:
ALTER TABLE tbl_name
DISCARD TABLESPACE;
This deletes the current .ibd
file, so be
sure that you have a backup first. Attempting to access the
table while the tablespace file is discarded results in an
error.
To import the backup .ibd
file back into
the table, copy it into the database directory, and then issue
this statement:
ALTER TABLE tbl_name
IMPORT TABLESPACE;
Pending INSERT DELAYED
statements are lost if a table is write locked and
ALTER TABLE
is used to modify
the table structure.
If you want to change the table default character set and all
character columns (CHAR
,
VARCHAR
,
TEXT
) to a new character set,
use a statement like this:
ALTER TABLEtbl_name
CONVERT TO CHARACTER SETcharset_name
;
For a column that has a data type of
VARCHAR
or one of the
TEXT
types, CONVERT TO
CHARACTER SET
will change the data type as necessary
to ensure that the new column is long enough to store as many
characters as the original column. For example, a
TEXT
column has two length
bytes, which store the byte-length of values in the column, up
to a maximum of 65,535. For a latin1
TEXT
column, each character
requires a single byte, so the column can store up to 65,535
characters. If the column is converted to
utf8
, each character might require up to
three bytes, for a maximum possible length of 3 × 65,535
= 196,605 bytes. That length will not fit in a
TEXT
column's length bytes, so
MySQL will convert the data type to
MEDIUMTEXT
, which is the
smallest string type for which the length bytes can record a
value of 196,605. Similarly, a
VARCHAR
column might be
converted to MEDIUMTEXT
.
To avoid data type changes of the type just described, do not
use CONVERT TO CHARACTER SET
. Instead, use
MODIFY
to change individual columns. For
example:
ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M
) CHARACTER SET utf8;
If you specify CONVERT TO CHARACTER SET
binary
, the CHAR
,
VARCHAR
, and
TEXT
columns are converted to
their corresponding binary string types
(BINARY
,
VARBINARY
,
BLOB
). This means that the
columns no longer will have a character set and a subsequent
CONVERT TO
operation will not apply to
them.
If charset_name
is
DEFAULT
, the database character set is
used.
The CONVERT TO
operation converts column
values between the character sets. This is
not what you want if you have a column
in one character set (like latin1
) but
the stored values actually use some other, incompatible
character set (like utf8
). In this case,
you have to do the following for each such column:
ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
The reason this works is that there is no conversion when
you convert to or from BLOB
columns.
To change only the default character set for a table, use this statement:
ALTER TABLEtbl_name
DEFAULT CHARACTER SETcharset_name
;
The word DEFAULT
is optional. The default
character set is the character set that is used if you do not
specify the character set for columns that you add to a table
later (for example, with ALTER TABLE ... ADD
column
).
Partitioning-related clauses for ALTER
TABLE
can be used with partitioned tables for
repartitioning, for adding, dropping, merging, and splitting
partitions, and for performing partitioning maintenance.
Simply using a partition_options
clause with ALTER TABLE
on a
partitioned table repartitions the table according to the
partitioning scheme defined by the
partition_options
. This clause
always begins with PARTITION BY
, and
follows the same syntax and other rules as apply to the
partition_options
clause for
CREATE TABLE
(see
Section 12.1.14, “CREATE TABLE
Syntax”, for more detailed
information), and can also be used to partition an existing
table that is not already partitioned. For example, consider a
(nonpartitioned) table defined as shown here:
CREATE TABLE t1 ( id INT, year_col INT );
This table can be partitioned by HASH
,
using the id
column as the partitioning
key, into 8 partitions by means of this statement:
ALTER TABLE t1 PARTITION BY HASH(id) PARTITIONS 8;
The table that results from using an ALTER TABLE ...
PARTITION BY
statement must follow the same rules as
one created using CREATE TABLE ... PARTITION
BY
. This includes the rules governing the
relationship between any unique keys (including any primary
key) that the table might have, and the column or columns used
in the partitioning expression, as discussed in
Section 17.5.1, “Partitioning Keys, Primary Keys, and Unique Keys”.
The CREATE TABLE ... PARTITION BY
rules for
specifying the number of partitions also apply to
ALTER TABLE ... PARTITION BY
.
The partition_definition
clause for
ALTER TABLE ADD PARTITION
supports the same
options as the clause of the same name for the
CREATE TABLE
statement. (See
Section 12.1.14, “CREATE TABLE
Syntax”, for the syntax and
description.) Suppose that you have the partitioned table
created as shown here:
CREATE TABLE t1 ( id INT, year_col INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999) );
You can add a new partition p3
to this
table for storing values less than 2002
as
follows:
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
DROP PARTITION
can be used to drop one or
more RANGE
or LIST
partitions. This statement cannot be used with
HASH
or KEY
partitions;
instead, use COALESCE PARTITION
(see
below). Any data that was stored in the dropped partitions
named in the partition_names
list
is discarded. For example, given the table
t1
defined previously, you can drop the
partitions named p0
and
p1
as shown here:
ALTER TABLE t1 DROP PARTITION p0, p1;
ADD PARTITION
and DROP
PARTITION
do not currently support IF [NOT]
EXISTS
. It is also not possible to rename a
partition or a partitioned table. Instead, if you wish to
rename a partition, you must drop and re-create the partition;
if you wish to rename a partitioned table, you must instead
drop all partitions, rename the table, and then add back the
partitions that were dropped.
COALESCE PARTITION
can be used with a table
that is partitioned by HASH
or
KEY
to reduce the number of partitions by
number
. Suppose that you have
created table t2
using the following
definition:
CREATE TABLE t2 ( name VARCHAR (30), started DATE ) PARTITION BY HASH( YEAR(started) ) PARTITIONS 6;
You can reduce the number of partitions used by
t2
from 6 to 4 using the following
statement:
ALTER TABLE t2 COALESCE PARTITION 2;
The data contained in the last
number
partitions will be merged
into the remaining partitions. In this case, partitions 4 and
5 will be merged into the first 4 partitions (the partitions
numbered 0, 1, 2, and 3).
To change some but not all the partitions used by a
partitioned table, you can use REORGANIZE
PARTITION
. This statement can be used in several
ways:
To merge a set of partitions into a single partition. This
can be done by naming several partitions in the
partition_names
list and
supplying a single definition for
partition_definition
.
To split an existing partition into several partitions.
You can accomplish this by naming a single partition for
partition_names
and providing
multiple partition_definitions
.
To change the ranges for a subset of partitions defined
using VALUES LESS THAN
or the value
lists for a subset of partitions defined using
VALUES IN
.
For partitions that have not been explicitly named, MySQL
automatically provides the default names
p0
, p1
,
p2
, and so on. The same is true with
regard to subpartitions.
For more detailed information about and examples of
ALTER TABLE ... REORGANIZE PARTITION
statements, see
Section 17.3.1, “Management of RANGE
and LIST
Partitions”.
Several additional options provide partition maintenance and
repair functionality analogous to that implemented for
nonpartitioned tables by statements such as
CHECK TABLE
and
REPAIR TABLE
(which are also
supported for partitioned tables; see
Section 12.4.2, “Table Maintenance Statements” for more information).
These include ANALYZE PARTITION
,
CHECK PARTITION
, OPTIMIZE
PARTITION
, REBUILD PARTITION
, and
REPAIR PARTITION
. Each of these options
takes a partition_names
clause
consisting of one or more names of partitions, separated by
commas. The partitions must already exist in the table to be
altered. You can also use the ALL
keyword
in place of partition_names
, in
which case the statement acts on all partitions in the table.
For more information and examples, see
Section 17.3.3, “Maintenance of Partitions”.
The ANALYZE PARTITION
, CHECK
PARTITION
, OPTIMIZE PARTITION
,
and REPAIR PARTITION
options are disallowed
for tables which are not partitioned.
REMOVE PARTITIONING
enables you to remove a
table's partitioning without otherwise affecting the table or
its data. This option can be combined with other
ALTER TABLE
options such as
those used to add, drop, or rename drop columns or indexes.
Using the ENGINE
option with
ALTER TABLE
changes the storage
engine used by the table without affecting the partitioning.
Only a single instance of any
one of the following options can be used in
a given ALTER TABLE
statement:
PARTITION BY
, ADD
PARTITION
, DROP PARTITION
,
REORGANIZE PARTITION
, or COALESCE
PARTITION
, ANALYZE PARTITION
,
CHECK PARTITION
, OPTIMIZE
PARTITION
, REBUILD PARTITION
,
REMOVE PARTITIONING
.
For example, the following two statements are invalid:
ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2; ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;
In the first case, you can analyze partitions
p1
and p2
of table
t1
concurrently using a single statement with
a single ANALYZE PARTITION
option that lists
both of the partitions to be analyzed, like this:
ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;
In the second case, it is not possible to perform
ANALYZE
and CHECK
operations on different partitions of the same table
concurrently. Instead, you must issue two separate statements,
like this:
ALTER TABLE t1 ANALYZE PARTITION p1; ALTER TABLE t1 CHECK PARTITION p2;
With the mysql_info()
C API
function, you can find out how many rows were copied, and (when
IGNORE
is used) how many rows were deleted due
to duplication of unique key values. See
Section 20.9.3.35, “mysql_info()
”.
Here are some examples that show uses of
ALTER TABLE
. Begin with a table
t1
that is created as shown here:
CREATE TABLE t1 (a INTEGER,b CHAR(10));
To rename the table from t1
to
t2
:
ALTER TABLE t1 RENAME t2;
To change column a
from
INTEGER
to TINYINT NOT
NULL
(leaving the name the same), and to change column
b
from CHAR(10)
to
CHAR(20)
as well as renaming it from
b
to c
:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new TIMESTAMP
column named
d
:
ALTER TABLE t2 ADD d TIMESTAMP;
To add an index on column d
and a
UNIQUE
index on column a
:
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
To remove column c
:
ALTER TABLE t2 DROP COLUMN c;
To add a new AUTO_INCREMENT
integer column
named c
:
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c);
We indexed c
(as a PRIMARY
KEY
) because AUTO_INCREMENT
columns
must be indexed, and we declare c
as
NOT NULL
because primary key columns cannot be
NULL
.
When you add an AUTO_INCREMENT
column, column
values are filled in with sequence numbers automatically. For
MyISAM
tables, you can set the first sequence
number by executing SET
INSERT_ID=
before
value
ALTER TABLE
or by using the
AUTO_INCREMENT=
table option. See Section 5.1.5, “Session System Variables”.
value
With MyISAM
tables, if you do not change the
AUTO_INCREMENT
column, the sequence number is
not affected. If you drop an AUTO_INCREMENT
column and then add another AUTO_INCREMENT
column, the numbers are resequenced beginning with 1.
When replication is used, adding an
AUTO_INCREMENT
column to a table might not
produce the same ordering of the rows on the slave and the master.
This occurs because the order in which the rows are numbered
depends on the specific storage engine used for the table and the
order in which the rows were inserted. If it is important to have
the same order on the master and slave, the rows must be ordered
before assigning an AUTO_INCREMENT
number.
Assuming that you want to add an AUTO_INCREMENT
column to the table t1
, the following
statements produce a new table t2
identical to
t1
but with an
AUTO_INCREMENT
column:
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY) SELECT * FROM t1 ORDER BY col1, col2;
This assumes that the table t1
has columns
col1
and col2
.
This set of statements will also produce a new table
t2
identical to t1
, with the
addition of an AUTO_INCREMENT
column:
CREATE TABLE t2 LIKE t1; ALTER TABLE T2 ADD id INT AUTO_INCREMENT PRIMARY KEY; INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
To guarantee the same ordering on both master and slave,
all columns of t1
must
be referenced in the ORDER BY
clause.
Regardless of the method used to create and populate the copy
having the AUTO_INCREMENT
column, the final
step is to drop the original table and then rename the copy:
DROP t1; ALTER TABLE t2 RENAME t1;
User Comments
IF you want to change a SET or ENUM column you may
not want to use the ALTER TABLE ... MODIFY
syntax.
It tries to keep the actual string values and not
the integer representation of the values, even
though they are stored as integers.
For example, if you just want to make a change in
spelling of the values in your enum column or your
set column, consider doing it like this:
ALTER TABLE table ADD new_column ...;
UPDATE table SET new_column = old_column + 0;
ALTER TABLE table DROP old_column;
You can use Alter Table to optimise a table without locking out selects (only writes), by altering a column to be the same as it's current definition. This is better than using repair table which obtains a read/write lock.
1 row in set (0.00 sec)E.g.
mysql> describe Temp_Table;
mysql> alter table Temp_Table change column ID ID int unsigned;
This will cause mysql to re-create the table and thus remove any deleted space.
This is useful for 24/7 databases where you don't want to completely lock a table.
If you are just changing a column name on a MyISAM table and want to avoid duplicating the entire table, try the following (no warranty provided but worked for me):
For peace-of-mind -- try this with some dummy data first!
1. Backup the <original_table>.frm file from your master table (and the data if you can, but you're probably reading this because you can't).
2. create table with the identical schema to the one you want to alter (type "show create table <tablename> and just change the name to something). Lets say you called the table "rename_temp1"
3. execute the "alter table <rename_temp1> change <old_column_name> <new_column_name> char(128) not null" [substituting your the old definition -- ensuring you keep column type the same]
3. Ensuring you a have made a copy of your original .frm file -- copy the <rename_temp1>.frm file to <original_table>.frm.
4. voila -- all going well your column should be renamed without a full copy in/out (very useful for 140G tables...)
5. probably best to run a myisamchck on the table before making live again
When you want to drop a UNIQUE KEY in an InnoDb table, have to pay attention not to occure this situation:
Please check that columns used in the UNIQUE KEY are not used as FOREIGN KEY (each of them).
If so, must to drop that Forign keys first.
See Example below please.
UNIQUE KEY `unique` (`id1`, `id2`),
CONSTRAINT `fk_1` FOREIGN KEY (`id1`) REFERENCES `tbl1` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_2` FOREIGN KEY (`id2`) REFERENCES `tbl2` (`id`) ON DELETE CASCADE
In this situation, you have to drop both FOREIGN KEYs first, in order to can drop the UNIQUE KEY.
If you're trying to convert a whole database to a different character set, and you thought you might have to change the fields one by one, this kind of command is really handy:
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
However, after using it on a lot of tables I made the grim discovery that for older myisam tables that didn't have any character set, it mangled the length of most varchar fields. Specifically, it divided their length with 3. Bizarrely, it didn't lose the existing data, even though it was longer than the field lengths, but it was a problem with new records and with indexes.
So, if you're going to do a character set converstion, make sure the table already has a character set. That it doesn't might not be immediately obvious, as tools like phpMyAdmin will show the default character set, if the table or the field doesn't have one set.
TO ADD A FOREIGN KEY TO AN EXISTING TABLE (I couldn't see a good example) you can do this:
alter table users add foreign key(favGenre) references products_genre(gid);
Where favgenre is the column of the table that has the foreign key and products_genre(gid) is the table and primary key you are referencing.
Attempting to "ALTER TABLE ... DROP PRIMARY KEY" on a table when an AUTO_INCREMENT column exists in the key generates an error:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.
To make this work without erroring, drop and re-add the new primary key in a single statement, e.g.:
ALTER TABLE mytable DROP PRIMARY KEY, ADD PRIMARY KEY(col1,col2);
You can't drop a NOT NULL constraint on a column the way you can a foreign key or an index, or a default. Instead, just use the 'change' or 'modify' syntax and leave off the NOT NULL bit:
alter table table_name modify col_name bigint default null;
Any pre-existing indexes or foreign keys on the column are not affected.
If you are trying to change the case of a table name using the ALTER TABLE syntax and it appears to fail silently,
or if you try to RENAME TABLE something TO soMeThiNg and get a 'table already exists' error,
or if you try to CREATE TABLE MixedCaseTableName and get a table named mixedcasetablename, these are not bugs:
See: Identifier Case Sensitivity
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
If your development environment has MySQL5 and you're hosting on MySQL4 you can get 'table not found' errors based on the case of the table names.
As mentioned above, ALTER TABLE is going to make a temporary copy of the whole table data (.MYD) in the same directory as the original table and not in the temporary directories given to MySQL.
In some cases a third copy of the table (.TMD) is made. This means you must have up to three copies of free space in that directory. Unfortunately MySQL does not break the files into pieces if it runs out of space.
As a table grows larger this process becomes more expensive. Therefore, keys and indices must be added as early as possible to large tables in spite of the update cost that comes with each insert.
For moving a table from one database to another just do:
use db_old;
alter table tab_name rename db_new.tab_name;
There seem to any number of convoluted methods (not to mention some finger wagging by purists questioning the practice, even here) for altering the sequence of fields in a MySQL table but ALTER does the job as prescribed. It isn't completely self-evident from the description above so here's what worked for me:
ALTER TABLE tablex CHANGE colx colx colxtype AFTER coly.
That is, you're not changing the name of the column but still need to specify 'oldname newname' as 'oldname oldname'
Converting_Database_Character_Sets
http://codex.wordpress.org/Converting_Database_Character_Sets
If you want to change the table's engine for all tables, you can use this code to generate your sql script.
From MyISAM engine to InnoDB engine: set db_name and db_username then copy and paste the follow lines on a Linux/MacOSX shell.
DB_NAME="db_name";
mysql --user=db_username -p --execute="USE information_schema; SELECT CONCAT(\"ALTER TABLE \`\", TABLE_SCHEMA,\"\`.\`\", TABLE_NAME, \"\` TYPE = InnoDB;\") as MySQLCMD from TABLES where TABLE_SCHEMA = \""${DB_NAME}"\";" > ${DB_NAME}-temp.sql;
#delete first line
sed '/MySQLCMD/d' ${DB_NAME}-temp.sql > ${DB_NAME}-innodb.sql;
mysql --user=db_username -p < ${DB_NAME}-innodb.sql;
rm ${DB_NAME}-temp.sql;
rm ${DB_NAME}-innodb.sql;
You can customize the code above for your OS.
I used code from here:
http://forums.mysql.com/read.php?20,244395,244421#msg-244421
Found some good alter table here:
http://www.examplenow.com/mysql/alter
--John
Add your own comment.