6.5.4 Синтаксис оператора ALTER TABLE

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  или   ADD [COLUMN] (create_definition, create_definition,...)
  или   ADD INDEX [index_name] (index_col_name,...)
  или   ADD PRIMARY KEY (index_col_name,...)
  или   ADD UNIQUE [index_name] (index_col_name,...)
  или   ADD FULLTEXT [index_name] (index_col_name,...)
  или   ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  или   ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  или   CHANGE [COLUMN] old_col_name create_definition
               [FIRST | AFTER column_name]
  или   MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  или   DROP [COLUMN] col_name
  или   DROP PRIMARY KEY
  или   DROP INDEX index_name
  или   DISABLE KEYS
  или   ENABLE KEYS
  или   RENAME [TO] new_tbl_name
  или   ORDER BY col
  или   table_options

Оператор ALTER TABLE обеспечивает возможность изменять структуру существующей таблицы. Например, можно добавлять или удалять столбцы, создавать или уничтожать индексы или переименовывать столбцы либо саму таблицу. Можно также изменять комментарий для таблицы и ее тип. See section 6.5.3 Синтаксис оператора CREATE TABLE.

Если оператор ALTER TABLE используется для изменения определения типа столбца, но DESCRIBE tbl_name показывает, что столбец не изменился, то, возможно, MySQL игнорирует данную модификацию по одной из причин, описанных в разделе section 6.5.3.1 Молчаливые изменения определений столбцов. Например, при попытке изменить столбец VARCHAR на CHAR MySQL будет продолжать использовать VARCHAR, если данная таблица содержит другие столбцы с переменной длиной.

Оператор ALTER TABLE во время работы создает временную копию исходной таблицы. Требуемое изменение выполняется на копии, затем исходная таблица удаляется, а новая переименовывается. Так делается для того, чтобы в новую таблицу автоматически попадали все обновления кроме неудавшихся. Во время выполнения ALTER TABLE исходная таблица доступна для чтения другими клиентами. Операции обновления и записи в этой таблице приостанавливаются, пока не будет готова новая таблица.

Следует отметить, что при использовании любой другой опции для ALTER TABLE кроме RENAME, MySQL всегда будет создавать временную таблицу, даже если данные, строго говоря, и не нуждаются в копировании (например, при изменении имени столбца). Мы планируем исправить это в будущем, однако, поскольку ALTER TABLE выполняется не так часто, мы (разработчики MySQL) не считаем эту задачу первоочередной. Для таблиц MyISAM можно увеличить скорость воссоздания индексной части (что является наиболее медленной частью в процессе восстановления таблицы) путем установки переменной myisam_sort_buffer_size достаточно большого значения.

• Для использования оператора ALTER TABLE необходимы привилегии ALTER, INSERT и CREATE для данной таблицы.
• Опция IGNORE является расширением MySQL по отношению к ANSI SQL92. Она управляет работой ALTER TABLE при наличии дубликатов уникальных ключей в новой таблице. Если опция IGNORE не задана, то для данной копии процесс прерывается и происходит откат назад. Если IGNORE указывается, тогда для строк с дубликатами уникальных ключей только первая строка используется, а остальные удаляются.
• Можно запустить несколько выражений ADD, ALTER, DROP и CHANGE в одной команде ALTER TABLE. Это является расширением MySQL по отношению к ANSI SQL92, где допускается только одно выражение из упомянутых в одной команде ALTER TABLE.
• Опции CHANGE col_name, DROP col_name и DROP INDEX также являются расширениями MySQL по отношению к ANSI SQL92.
• Опция MODIFY представляет собой расширение Oracle для команды ALTER TABLE.
• Необязательное слово COLUMN представляет собой ``белый шум'' и может быть опущено.
• При использовании ALTER TABLE имя_таблицы RENAME TO новое_имя без каких-либо других опций MySQL просто переименовывает файлы, соответствующие заданной таблице. В этом случае нет необходимости создавать временную таблицу. See section 6.5.5 Синтаксис оператора RENAME TABLE.
• В выражении create_definition для ADD и CHANGE используется тот же синтаксис, что и для CREATE TABLE. Следует учитывать, что этот синтаксис включает имя столбца, а не просто его тип. See section 6.5.3 Синтаксис оператора CREATE TABLE.
• Столбец можно переименовывать, используя выражение CHANGE имя_столбца create_definition. Чтобы сделать это, необходимо указать старое и новое имена столбца и его тип в настоящее время. Например, чтобы переименовать столбец INTEGER из a в b, можно сделать следующее:

  mysql> ALTER TABLE t1 CHANGE a b INTEGER;
  

  При изменении типа столбца, но не его имени синтаксис выражения CHANGE все равно требует указания обоих имен столбца, даже если они одинаковы. Например:

  mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
  

  Однако начиная с версии MySQL 3.22.16a можно также использовать выражение MODIFY для изменения типа столбца без переименовывания его:

  mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
  

• При использовании CHANGE или MODIFY для того, чтобы уменьшить длину столбца, по части которого построен индекс (например, индекс по первым 10 символам столбца VARCHAR), нельзя сделать столбец короче, чем число проиндексированных символов.
• При изменении типа столбца с использованием CHANGE или MODIFY MySQL пытается преобразовать данные в новый тип как можно корректнее.
• В версии MySQL 3.22 и более поздних можно использовать FIRST или ADD ... AFTER имя_столбца для добавления столбца на заданную позицию внутри табличной строки. По умолчанию столбец добавляется в конце. Начиная с версии MySQL 4.0.1, можно также использовать ключевые слова FIRST и AFTER в опциях CHANGE или MODIFY.
• Опция ALTER COLUMN задает для столбца новое значение по умолчанию или удаляет старое. Если старое значение по умолчанию удаляется и данный столбец может принимать значение NULL, то новое значение по умолчанию будет NULL. Если столбец не может быть NULL, то MySQL назначает значение по умолчанию так, как описано в разделе section 6.5.3 Синтаксис оператора CREATE TABLE.
• Опция DROP INDEX удаляет индекс. Это является расширением MySQL по отношению к ANSI SQL92. See section 6.5.8 Синтаксис оператора DROP INDEX.
• Если столбцы удаляются из таблицы, то эти столбцы удаляются также и из любого индекса, в который они входят как часть. Если все столбцы, составляющие индекс, удаляются, то данный индекс также удаляется.
• Если таблица содержит только один столбец, то этот столбец не может быть удален. Вместо этого можно удалить данную таблицу, используя команду DROP TABLE.
• Опция DROP PRIMARY KEY удаляет первичный индекс. Если такого индекса в данной таблице не существует, то удаляется первый индекс UNIQUE в этой таблице. (MySQL отмечает первый уникальный ключ UNIQUE как первичный ключ PRIMARY KEY, если никакой другой первичный ключ PRIMARY KEY не был явно указан). При добавлении UNIQUE INDEX или PRIMARY KEY в таблицу они хранятся перед остальными неуникальными ключами, чтобы можно было определить дублирующиеся ключи как можно раньше.
• Опция ORDER BY позволяет создавать новую таблицу со строками, размещенными в заданном порядке. Следует учитывать, что созданная таблица не будет сохранять этот порядок строк после операций вставки и удаления. В некоторых случаях такая возможность может облегчить операцию сортировки в MySQL, если таблица имеет такое расположение столбцов, которое вы хотели бы иметь в дальнейшем. Эта опция в основном полезна, если заранее известен определенный порядок, в котором преимущественно будут запрашиваться строки. Использование данной опции после значительных преобразований таблицы дает возможность получить более высокую производительность.
• При использовании команды ALTER TABLE для таблиц MyISAM все неуникальные индексы создаются в отдельном пакете (подобно REPAIR). Благодаря этому команда ALTER TABLE при наличии нескольких индексов будет работать быстрее.
• Начиная с MySQL 4.0, вышеуказанная возможность может быть активизирована явным образом. Команда ALTER TABLE ... DISABLE KEYS блокирует в MySQL обновление неуникальных индексов для таблиц MyISAM. После этого можно применить команду ALTER TABLE ... ENABLE KEYS для воссоздания недостающих индексов. Так как MySQL делает это с помощью специального алгоритма, который намного быстрее в сравнении со вставкой ключей один за другим, блокировка ключей может дать существенное ускорение на больших массивах вставок.
• Применяя функцию C API mysql_info(), можно определить, сколько записей было скопировано, а также (при использовании IGNORE) - сколько записей было удалено из-за дублирования значений уникальных ключей.
• Выражения FOREIGN KEY, CHECK и REFERENCES фактически ничего не делают. Они введены только из соображений совместимости, чтобы облегчить перенос кода с других серверов SQL и запуск приложений, создающих таблицы со ссылками. See section 1.9.4 Отличия MySQL от ANSI SQL92.

Ниже приводятся примеры, показывающие некоторые случаи употребления команды ALTER TABLE. Пример начинается с таблицы t1, которая создается следующим образом:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

Для того чтобы переименовать таблицу из t1 в t2:

mysql> ALTER TABLE t1 RENAME t2;

Для того чтобы изменить тип столбца с INTEGER на TINYINT NOT NULL (оставляя имя прежним) и изменить тип столбца b с CHAR(10) на CHAR(20) с переименованием его с b на c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

Для того чтобы добавить новый столбец TIMESTAMP с именем d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Для того чтобы добавить индекс к столбцу d и сделать столбец a первичным ключом:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Для того чтобы удалить столбец c:

mysql> ALTER TABLE t2 DROP COLUMN c;

Для того чтобы добавить новый числовой столбец AUTO_INCREMENT с именем c:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
        ADD INDEX (c);

Заметьте, что столбец c индексируется, так как столбцы AUTO_INCREMENT должны быть индексированы, кроме того, столбец c объявляется как NOT NULL, поскольку индексированные столбцы не могут быть NULL.

При добавлении столбца AUTO_INCREMENT значения этого столбца автоматически заполняются последовательными номерами (при добавлении записей). Первый номер последовательности можно установить путем выполнения команды SET INSERT_ID=# перед ALTER TABLE или использования табличной опции AUTO_INCREMENT = #. See section 5.5.6 Синтаксис команды SET.

Если столбец AUTO_INCREMENT для таблиц MyISAM, не изменяется, то номер последовательности остается прежним. При удалении столбца AUTO_INCREMENT и последующем добавлении другого столбца AUTO_INCREMENT номера будут начинаться снова с 1.

See section A.6.1 Проблемы с ALTER TABLE.

User Comments

i want to change the mySQL table "user" on the
mysql database, with this query:
"ALTER TABLE user MODIFY User VARCHAR(40) NOT
NULL"

but i've got problems because, mySQL says query ok
, but nothing happened?? why can't i change the
length of the column user?

Not only an alter, but also a drop index can take
forever!

Suggestion: I've found that you need to refresh
(meaning quit and restart) the MySQLManager
v1.0.2 in order to see ALTER changes.

I've found that on Windows somtimes Alter doesn't
work like it should... it gives errors...

I made it work, restarting the MySQL.
(Everything works fine with selects, uptes and
inserts... just this alter gives errors sometimes)

I made an ALTER TABLE __ MODIFY a column that
was part
of an INDEX and after the command was completed
my server daemon was stuck on 99% CPU used

The IGNORE option doesn't ignore addition of already existing columns.

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;

The DISABLE KEYS and ENABLE KEYS statements
require the user to have the INDEX permission in
addition to the stated ALTER, INSERT, and CREATE.

Suggestion: The following would be useful...
ALTER TABLE ADD COLUMN IF NOT EXISTS

or is it already available?

I am working on the school server and I have an error
when I do the following:
ALTER TABLE TBLCharacteristics RENAME Characteristics;
ERROR 1044: Access denied for user: 'jbouth1@localhost'
to database 'jbouth1'

Any suggestions?
Thanks

Is it possible to create a unique key with a
combination of two or more columns? I can't find
that information anywhere.

In response to Jean-Francois Bouthillier:
To alter a table, you need the alter table
privilege for your mysql user. As root, go
GRANT ALTER ON your_db.* TO your_user@'%'

In response to Jorge Andrade:
You can create a unique key on more than one field
by doing...
CREATE TABLE table_name (
Field1 int(11) default 0 not null,
Field2 int(11),
Field3 text,
PRIMARY KEY (Field1),
UNIQUE KEY my_unique_key (Field2, Field3(255));

A unique key allows nulls,
ylringwood.

In response to Jorge Andrade:
Adding more than one unique column simply means
adding a comma, and then listing the other columns,
all within () brackets. You will be thrown an error
and the rule will not be applied if there are duplicates
to the new rule - in this case, do a self join on the
table using aliases, where T1 and T2 aliases both
share common information, but a different ID or
primary key element... then just delete them one at
a time until you perform this select statement and
get an empty set, which means you are right to
perform the add unique operation.

Making a unique index on multiple columns is an
easy way to insure that the table is unique over
that combination. I have a table which is unique
by e-mail and another that is unique by ip. I
have a third table that records the relationship
between the first two - essentially which ones
have been seen and when and how often, and I
want that to be unique. So making a unique key
over the two fields automatically means that any
inserts will fail if the combination is duplicated.
The create table for this particular table is:

CREATE TABLE trap_to_addr (
ipkey varchar(64) NOT NULL default '',
spamtrapnum mediumint(9) NOT NULL
default '0',
created timestamp(14) NOT NULL,
PRIMARY KEY (ipkey,spamtrapnum)
) TYPE=MyISAM;

I hope 'Column Comment' function(sql) like a

"CREATE TABLE tablename ... COMMENT='Your Comment'"

for Column Infomation.

^^

Perhaps I overlooked it, but you can define a constraint to instruct SQL to set a field's default by invoking a function. Take this example for instance, which will default the "date" field by invoking function "getdate()":

CREATE TABLE [orddtl] (
[date] [smalldatetime] NULL ,
[remindercode] [int] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [orddtl] WITH NOCHECK ADD
CONSTRAINT [DF_orddtl_date] DEFAULT (getdate()) FOR [date],
CONSTRAINT [DF_orddtl_remindercode] DEFAULT (0) FOR [remindercode]
GO

Unclear documentation about UNIQUE KEYS.

UNIQUE KEYs allow to create an index on many fields, making the combination unique.
I just noticed that if one of these fields is NULL, then no check on the uniqueness is made.
Imagine you have a table named 'mytable' that defines 3 NULLABLE fields 'A', 'B' and 'C'.
The following queries will work :

insert into mytable (A,B,C) values (1,1,NULL);
insert into mytable (A,B,C) values (1,1,NULL);
insert into mytable (A,B,C) values (1,1,NULL);
...

All the rows will be created.

This is not an obvious behaviour (to me at least) so I think the document should explain it.

Regards,
Sebastien.

I am finding difficulties in adding a foreign key
is there anything like a foreign key in mysql
and if so what is syntax for modifying the column in an existing table.
I developed the database in access and imported the whole database into mysql
now i am finding problem in making it work with asp pages
thanks and i appreciate any help from anyone
Sam

Add your own comment.