Search the MySQL manual:
MySQL Manual

Buy this Reference Manual in softcover from Barnes & Noble!

/ / Up / Table of Contents

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. Пример начинается с таблицы 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

Posted by Tobias Nitsche on Friday May 17 2002, @6:24am [Delete] [Edit]

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?

Posted by [name withheld] on Thursday May 30 2002, @12:23pm [Delete] [Edit]

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

Posted by [name withheld] on Friday May 17 2002, @6:24am [Delete] [Edit]

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

Posted by Marian Vasile on Friday May 17 2002, @6:24am [Delete] [Edit]

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)

Posted by on Friday May 17 2002, @6:24am [Delete] [Edit]

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

Posted by Nils Hammar on Friday May 17 2002, @6:24am [Delete] [Edit]

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

Posted by Tom S on Wednesday December 18 2002, @5:27pm [Delete] [Edit]

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;

Posted by John Simpson on Thursday September 12 2002, @8:10pm [Delete] [Edit]

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

Posted by on Monday September 30 2002, @12:18am [Delete] [Edit]

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

or is it already available?

Posted by Jean-Francois Bouthillier on Sunday November 3 2002, @9:53am [Delete] [Edit]

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

Posted by Jorge Andrade on Monday November 4 2002, @2:43am [Delete] [Edit]

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

Posted by [name withheld] on Wednesday November 6 2002, @5:07pm [Delete] [Edit]

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.

Posted by on Friday November 15 2002, @6:18pm [Delete] [Edit]

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.

Posted by [name withheld] on Tuesday November 19 2002, @5:07pm [Delete] [Edit]

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;

Posted by Bae Hun Jong on Monday January 13 2003, @4:55pm [Delete] [Edit]


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

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

for Column Infomation.

^^

Posted by [name withheld] on Monday February 3 2003, @3:25pm [Delete] [Edit]

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

Posted by on Tuesday February 4 2003, @9:04am [Delete] [Edit]

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.

Posted by Sameer Gante on Thursday February 20 2003, @12:54pm [Delete] [Edit]

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.

Top / / / Up / Table of Contents