ALTER TABLE changes the structure
of a 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 characteristics
such as the storage engine used for the table or the table
comment.

Following the table name, specify the alterations to be made. If
none are given, ALTER TABLE does
nothing.

table_options signifies table options
of the kind that can be used in the CREATE
TABLE statement, such as ENGINE,
AUTO_INCREMENT,
AVG_ROW_LENGTH, MAX_ROWS, or
ROW_FORMAT. For a list of all table options and
a description of each, see Section 13.1.17, “CREATE TABLE Syntax”.
However, ALTER TABLE ignores the
DATA DIRECTORY and INDEX
DIRECTORY table options.

partition_options signifies options
that can be used with partitioned tables for repartitioning, for
adding, dropping, merging, and splitting partitions, and for
performing partitioning maintenance. It is possible for an
ALTER TABLE statement to contain a
PARTITION BY or REMOVE
PARTITIONING clause in an addition to other alter
specifications, but the PARTITION BY or
REMOVE PARTITIONING clause must be specified
last after any other specifications. The ADD
PARTITION, DROP PARTITION,
COALESCE PARTITION, REORGANIZE
PARTITION, ANALYZE PARTITION,
CHECK PARTITION, and REPAIR
PARTITION options cannot be combined with other alter
specifications in a single ALTER TABLE, since
the options just listed act on individual partitions. For a list
of partition options and a description of each, see
Section 13.1.17, “CREATE TABLE Syntax”. For additional information, see
Section 13.1.7.1, “ALTER TABLE Partition Operations”.

Storage, Performance, and Concurrency Considerations

In most cases, ALTER TABLE makes a
temporary copy of the original table. MySQL waits for other
operations that are modifying the table, then proceeds. It
incorporates the alteration into the copy, deletes the original
table, and renames the new one. While ALTER
TABLE is executing, the original table is readable by
other sessions (with the exception noted shortly). Updates and
writes to the table that begin after the
ALTER TABLE operation begins are
stalled until the new table is ready, then are automatically
redirected to the new table without any failed updates. The
temporary copy of the original table is created in the database
directory of the new table. This can differ from the database
directory of the original table for ALTER
TABLE operations that rename the table to a different
database.

The exception referred to earlier is that
ALTER TABLE blocks reads (not just
writes) at the point where it is ready to install a new version of
the table .frm file, discard the old file,
and clear outdated table structures from the table and table
definition caches. At this point, it must acquire an exclusive
lock. To do so, it waits for current readers to finish, and blocks
new reads (and writes).

For MyISAM tables, you can speed up index
re-creation (the slowest part of the alteration process) by
setting the
myisam_sort_buffer_size system
variable to a high value.

For some operations, an in-place ALTER
TABLE is possible that does not require a temporary
table:

For ALTER TABLE tbl_name
RENAME TO new_tbl_name
without any other options, MySQL simply renames any files that
correspond to the table tbl_name
without making a copy. (You can also use the
RENAME TABLE statement to
rename tables. See Section 13.1.32, “RENAME TABLE Syntax”.) Any
privileges granted specifically for the renamed table are not
migrated to the new name. They must be changed manually.

Alterations that modify only table metadata and not table data
are immediate because the server only needs to alter the table
.frm file, not touch table contents. The
following changes are fast alterations that can be made this
way:

Changing the definition of an
ENUM or
SET column by adding new
enumeration or set members to the end
of the list of valid member values, as long as the storage
size of the data type does not change. For example, adding
a member to a SET column
that has 8 members changes the required storage per value
from 1 byte to 2 bytes; this will require a table copy.
Adding members in the middle of the list causes
renumbering of existing members, which requires a table
copy.

ALTER TABLE with ADD
PARTITION, DROP PARTITION,
COALESCE PARTITION, REBUILD
PARTITION, or REORGANIZE
PARTITION does not create any temporary tables
(except when used with NDB
tables); however, these operations can and do create temporary
partition files.

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 all
partitions, unless LINEAR HASH or
LINEAR KEY was used; this is effectively
the same as creating a new table, although the
ADD or COALESCE
operation is performed partition by partition.
REORGANIZE operations copy only changed
partitions and do not touch unchanged ones.

Usage Notes

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 one 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.

Note

Due to a bug related to
Fast Index
Creation (Bug #40344), the statement ALTER
IGNORE TABLE ... ADD UNIQUE INDEX does not delete
duplicate rows. The IGNORE keyword is
ignored. If any duplicate rows exist, the operation fails
with the following error message:

ERROR 23000: Duplicate entry '347' for key 'pl'

A workaround is to set
old_alter_table=1 prior to
running an ALTER IGNORE TABLE ... ADD UNIQUE
INDEX statement.

table_options signifies table
options of the kind that can be used in the
CREATE TABLE statement, such as
ENGINE, AUTO_INCREMENT,
AVG_ROW_LENGTH,
MAX_ROWS, or ROW_FORMAT.
For a list of all table options and a description of each, see
Section 13.1.17, “CREATE TABLE Syntax”. 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:

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 permits only one of each clause per
ALTER TABLE statement. For
example, to drop multiple columns in a single statement, do
this:

You can rename a column using a CHANGE
old_col_namenew_col_namecolumn_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:

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:

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.

Warning

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.7, “Server SQL Modes”).

To add a column at a specific position within a table row, use
FIRST or AFTER
col_name. The default is
to add the column last. You can also use
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 11.6, “Data Type Default Values”.

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.

If you add a UNIQUE INDEX or
PRIMARY KEY to a table, MySQL stores it
before any nonunique index to permit detection of duplicate
keys as early as possible.

Some storage engines permit you to specify an index type when
creating an index. The syntax for the
index_type specifier is
USING type_name.
For details about USING, see
Section 13.1.13, “CREATE INDEX Syntax”. The preferred position is
after the column list. Support for use of the option before
the column list will be removed in a future MySQL release.

ORDER BY enables you to create the new
table with the rows in a specific order. 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.

Note

The table does not remain in the specified order after
inserts and deletes.

ORDER BY syntax permits 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 permitted as sort criteria; arbitrary
expressions are not permitted. This clause should be given
last after any other clauses.

ORDER BY does not make sense for
InnoDB tables because
InnoDB always orders table rows according
to the clustered
index.

Note

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.

For MyISAM tables, key updating can be
controlled explicitly. Use ALTER TABLE ... DISABLE
KEYS to tell MySQL to stop updating nonunique
indexes. Then use ALTER TABLE ... ENABLE
KEYS to re-create missing indexes.
MyISAM 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.

For ALTER TABLE, unlike
CREATE TABLE, ADD
FOREIGN KEY ignores
index_name if given and uses an
automatically generated foreign key name. As a workaround,
include the CONSTRAINT clause to specify
the foreign key name:

ADD CONSTRAINT name FOREIGN KEY (....) ...

Important

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.

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 TABLE tbl_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

The statement also changes the collation of all character
columns. If you specify no COLLATE clause
to indicate which collation to use, the statement uses default
collation for the character set. If this collation is
inappropriate for the intended table use (for example, if it
would change from a case-sensitive collation to a
case-insensitive collation), specify a collation explicitly.

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 latin1TEXT 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:

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.

Warning

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:

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 TABLE tbl_name DEFAULT CHARACTER SET charset_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).

User Comments

IF you want to change a SET or ENUM column you maynot want to use the ALTER TABLE ... MODIFYsyntax.It tries to keep the actual string values and notthe integer representation of the values, eventhough they are stored as integers. For example, if you just want to make a change inspelling of the values in your enum column or yourset 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.

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.

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.

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.

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'

If you use "ALTER TABLE mytable AUTO_INCREMENT = N" the MySQL server will create a new table, copy all the data from the old table to the new, delete the old and then rename the new one even though there is no structural change to the table. The server response will show that all the rows have been "affected", like this:mysql> alter table mytable auto_increment=1000000;Query OK, 512691 rows affected (1 min 4.55 sec)Records: 512691 Duplicates: 0 Warnings: 0

There are potential issues that may arise from the table copy, especially if you didn't expect it! I.e. is there a sufficient amount of free disk space for the second copy of the data, etc., etc..

The bottom line for me is to go back to the "old fashioned way" - just insert a dummy row and explicitly set the AUTO_INCREMENT column to N - 1, then immediately delete the dummy row. The next row that is inserted will start at N and go from there.

It is not possible to change a column name on an InnoDB table if the column participates in a foreign key without first dropping the foreign key. It does not matter if the column is used to reference another table or if it is referenced by another table in the foreign key.

Note that if you include 'UNIQUE' as part of the column definition in an ALTER TABLE MODIFY COLUMN ... statement for a column which was original defined as UNIQUE, MySQL will create a second UNIQUE index. What you need to do if you don't want that second index (any why would you? as it does nothing but slow things down), and you're dealing with scripted changes which you have to give to a separate DBA team who won't accept any instructions which involve manual work (so they won't examine the results of the ALTER TABLE statement to find out the name of the extra index which needs to be dropped), on a server over which you don't have administrative privileges, is to leave out the UNIQUE keyword from the column definition, knowing that MySQL will leave the UNIQUE constraint in place. It's as if MySQL doesn't consider 'UNIQUE' as part of the column definition (even though the syntax rules include it as part of column_definition).

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 my website http://techleaks.us/ . I wasted my 4 days looking and searching all around this..):

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"