Приложения и Книги 

Синтаксис оператора alter table. Справочное руководство по MySQL

Если в Вашем проекте есть таблицы размер которых исчисляется гигабайтами, а для того чтобы поменять структуру такой таблицы вам на несколько часов приходится останавливать все сервисы - эта статья будет для Вас.

Дано: таблица размером в несколько десятков гигабайт данных. Задача - изменить структуру таблицы.

Сразу забегу в перед, работать метод будет только на транзакционных таблицах. Если у вас MyISAM-таблица на десятки гигабайт, то тут как в том анекдоте - «разбирайтесь сами со своими проблемами». Пример будет приведен для InnoDB таблицы.

Предположим что структура нашей таблицы такая:

CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
Мы хотим добавить в эту таблицу поле last_login.

Какие у нас есть варианты.

В лоб

ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;
Вариант прекрасно работет на мелких проектах где размер таблиц редко превышает 50 000 записей. Нам вариант не подходит т.к. ALTER будет выполнятся слишком долго и все это время таблица будет заблокирована как на запись так и на чтение. Соответственно сервис нужно будет останавливать на это время.

Включаем мозг

Можно вообще не трогать таблицу раз уж на то пошло, а сделать отдельную `users_lastvisits`:
CREATE TABLE `users_lastvisits` (`user_id` int(11) NOT NULL, `last_login` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Теперь можно во всех запросах где нужен last_login делать JOIN с таблицей last_login. Работать будет, конечно, медленнее, да и в запросах дописывать JOIN тоже лишнее время, но в целом этого иногда бывает достаточно и на этом пункте можно и остановится.

И все таки - нужно добавить поле

Можно поднять master-slave репликацию, сделать ALTER на slave-сервере а потом поменять их местами. Если честно я таким никогда не занимался, может это и проще следующего способа, но не всегда есть возможность поднять репликацию.

Мой способ заключается в следующем

Создаем новую таблицу с конечной структурой, делаем на первой таблицы триггеры, которые будут логировать все изменения, одновременно с этим начинаем переливать данные из первой таблицы во вторую, а по окончании «вливаем» изменившиеся данные и переименовываем таблицы.

Итак, подготавливаем 2 таблицы - первая с нужной структурой, вторая для логирования изменений.
CREATE TABLE `_users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", `lastvisit` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users_updated_rows` (`id` int(11) NOT NULL AUTO_INCREMENT, `row_id` int(11) NOT NULL DEFAULT "0", `action` enum("updated","deleted") NOT NULL DEFAULT "updated", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Ставим триггеры:
DELIMITER ;; CREATE TRIGGER users_after_delete AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, OLD.id, "deleted"); END;; CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, NEW.id, "updated"); END;; CREATE TRIGGER users_after_update AFTER UPDATE ON users FOR EACH ROW BEGIN IF (OLD.id != NEW.id) THEN INSERT INTO users_updated_rows VALUES (0, OLD.id, "deleted"); END IF; INSERT INTO users_updated_rows VALUES (0, NEW.id, "updated"); END;; DELIMITER ;
Теперь начинаем переливку. Для этого надо открыть 2 соединения с базой. В одном будет идти содственно переливка, в другом нужно будет ненадолго заблокировать таблицу на запись.
mysql> LOCK TABLES users WRITE; Query OK, 0 rows affected (0.00 sec) mysql> -- триггеры уже должны быть установлены mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.17 sec) mysql> -- в другой консоли пускаем переливку mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users; mysql> -- снова в первой консоли mysql> UNLOCK TABLES;
Все, теперь пока таблица переливается у нас есть время подумать как будем вливать изменившиеся с момента начала переливки данные. Тут вообщем то ничего сложного - скрипт приводить я не буду, нужно просто брать по одной записи из таблицы users_updated_rows в том порядке, в котором они добавлялись (сортировать по первичному ключу) и обновлять или удалять её в таблице _users;

Итак, переливка таблицы уже закончилась, нужно вливать остальные данные. Запускаем скрипт. Скрипт должен работать постоянно и обновлять все записи которые добавляются в лог, когда он перельет все данные нужно переименовать таблицы:
mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.16 sec) mysql> RENAME TABLE users TO __users, _users TO users; Query OK, 0 rows affected (0.11 sec)
Стоит заметит что в этот момент возможна небольшая потеря данных т.к. запросы выполняются не атомарно. Если это критично, лучше выключить сервис на некоторое время чтобы запросов на изменение не было. Можно например забрать права на запись у пользователя а выполнять команды под другим пользователем.

Если все сделать правильно данные не будут потеряны и перерывов в работе сервиса практически не будет. Что нам и требовалось. Таким же способом можно перелить данные на другой сервер, поменяется только способ переливки. Вместо
mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;
нужно перелить через mysqldump:
$ mysqldump -h host1 db users --single-transaction -ecQ | pv | mysql -h host2
Таким способом мне удалось перелить без остановки работы сервисов на другой сервер таблицу размером в 60Gb и 400 млн строк где то за 12 часов.

Кстати, велосипед уже изобретен Facebook и называется

Заданным образом модифицирует структуру таблицы.

Синтаксис:

ALTER TABLE TableName1 ADD | ALTER FieldName1 FieldType [(nFieldWidth [, nPrecision])] ] ] - Или - ALTER TABLE TableName1 ALTER FieldName2 ] - Или - ALTER TABLE TableName1 FieldName3] ] ] TAG TagName4 REFERENCES TableName2 ] ]

Параметры:
TableName1
Задает имя таблицы, структура которой модифицируется.
ADD FieldName1
Задает имя добавляемого поля.
ALTER FieldName1
Задает имя существующего поля, подлежащего модификации.
FieldType [(nFieldWidth [, nPrecision]])
Задает тип поля, ширину поля и точность поля (число десятичных знаков) для нового или модифицируемого поля.
FieldType это одна буква, указывающая тип данных поля. Для некоторых типов полей требуется задавать nFieldWidth и/или nPrecision .
В следующей таблице перечислены значения параметра FieldType ; для каждого из них указывается, требуются ли параметры nFieldWidth и nPrecision .

FieldType nFieldWidth nPrecision Описание C n - Символьное поле ширины n D - - Дата T - - Поле типа DateTime N n d Числовое поле ширины n, имеющее d десятичных знаков F n d Поле данных с плавающей запятой ширины n, имеющее d десятичных знаков I - - Поле целых чисел B - d Поле данных с двойной точностью Y - - Поле типа Currency L - - Логическое M - - Поле типа Memo G - - Поле типа General

Параметры nFieldWidth и nPrecision игнорируются в случае типов D, T, I, Y, L, M, G и P. Если для типа N, F или B значение nPrecision не указано, оно принимается равным нулю по умолчанию (нет десятичных знаков).
NULL
Разрешает полю принимать значения NULL .
NOT NULL
Запрещает полю принимать значения NULL .
Если опущены предложения NULL и NOT NULL , то допустимость значений NULL для поля определяется текущей установкой SET NULL . Однако если опустить предложения NULL и NOT NULL и задать PRIMARY KEY или UNIQUE , текущая установка SET NULL будет игнорироваться и по умолчанию для поля будет установлено NOT NULL .
CHECK lExpression1
Задает правило проверки достоверности уровня поля. Вычисление lExpression1 должно давать логическое выражение; это может быть функция, определенная пользователем, или хранимая процедура. Учтите, что это правило проверяется при добавлении пустой записи. Если данное правило не допускает пустые значения полей в присоединяемой записи, генерируется ошибка.
ERROR cMessageText1
Задает текст сообщения об ошибке, отображаемого при обнаружении ошибки в соответствии с правилом поля. Это сообщение выдается только при изменении данных в окне просмотра или окне редактирования.
DEFAULT eExpression1
Задает для поля значение, принимаемое по умолчанию. Тип данных выражения eExpression1 должен совпадать с типом данных поля.
PRIMARY KEY
Создает тег первичного индекса. Имя тега совпадает с именем поля.
UNIQUE
Создает тег индекса-кандидата с тем же именем, которое носит поле. Подробнее об индексах-кандидатах см. главу 7 "Работа с таблицами" Руководства разработчика.
Замечание Индексы-кандидаты (созданные с помощью опции UNIQUE , включенной в команды ALTER TABLE и CREATE TABLE для совместимости с ANSI ) отличаются от индексов, созданных с помощью опции UNIQUE команды INDEX . Индекс, созданный опцией UNIQUE в команде INDEX , допускает дублирование ключей индекса; в индексах-кандидатах повторяющиеся ключи не разрешены.
В поле, используемом для первичного индекса или индекса-кандидата, не допускаются значения NULL и повторяющиеся записи.
Если вы создаете новое поле с помощью предложения ADD COLUMN, то при создании первичного индекса или индекса-кандидата для поля, поддерживающего значения NULL, Visual FoxPro не будет генерировать ошибку. Однако если вы попытаетесь ввести значение NULL или дубликат в поле, используемое для первичного индекса или индекса-кандидадата, Visual FoxPro выдаст ошибку.
Если вы модифицируете существующее поле, а выражение первичного индекса или индекса-кандидата состоит только из имени одного этого поля, Visual FoxPro проверит поле не содержит ли оно значений NULL или повторяющихся записей. Если такие значения будут обнаружены, Visual FoxPro сгенерирует ошибку и таблица остается без изменений. Если выражение индекса содержит несколько полей или выражение, включающее одиночное поле, Visual FoxPro не будет проверять наличие значений NULL или дублирующих записей.
REFERENCES TableName2 TAG TagName1
Задает родительскую таблицу, с которой устанавливается постоянное отношение. TAG TagName1 задает тег индекса родительской таблицы, на котором базируется данное отношение. Имя тега индекса может содержать не более 10 символов.
NOCPTRANS
Запрещает для символьных и memo-полей трансляцию в другую кодовую страницу. Если таблица преобразуется в другую кодовую таблицу, поля с опцией NOCPTRANS не участвуют в трансляции. NOCPTRANS можно задать только для символьных полей и memo-полей.
В следующем примере создается таблица с именем MYTABLE , которая содержит два символьных поля и два memo-поля. Второе символьное поле char2 и второе memo-поле memo2 определены с опцией NOCPTRANS для предотвращения трансляции.

CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,; memo1 M, memo2, M NOCPTRANS)

ALTER FieldName2
Задает имя существующего поля, которое предстоит модифицировать.
SET DEFAULT eExpression2
Задает новое значение, которое будет приниматься по умолчанию для существующего поля. Тип данных выражения eExpression2 должен совпадать с типом данных, определенным для поля.
SET CHECK lExpression2
Задает новое правило для существующего поля. Вычисление lExpression2 должно давать логическое значение; это может быть определенная пользователем функция или хранимая процедура.
ERROR cMessageText2
Задает текст сообщения об ошибке. Это сообщение будет отображаться при обнаружении ошибки в соответствии с правилом уровня поля. Сообщение выдается только в том случае, когда происходит изменение данных в окне просмотра или окне редактирования.
DROP DEFAULT
Отменяет значение, принимаемое по умолчанию для существующего поля.
DROP CHECK
Отменяет правило для существующего поля.
DROP FieldName3
Задает поле, которое нужно удалить из таблицы. При удалении поля из таблицы также отменяется значение, принимаемое по умолчанию для этого поля.
Если для поля указывает выражение ключа индекса или выражение триггера, то после удаления поля эти выражения становятся недействительными. В таком случае при удалении поля не будет генерироваться ошибка, однако ошибки возникнут для недействительных выражений ключа индекса или триггера на этапе выполнения.
SET CHECK lExpression3
Задает правило уровня таблицы. Вычисление lExpression3 должно давать логическое выражение, это может быть определенная пользователем функция или хранимая процедура.
ERROR cMessageText3
Задает текст сообщения об ошибке, отображаемого при обнаружении ошибки в соответствии с правилом уровня таблицы. Это сообщение выдается только при изменении данных в окне просмотра или окне редактирования.
DROP CHECK
Отменяет правило уровня таблицы.
ADD PRIMARY KEY eExpression3 TAG TagName2
Добавляет в таблицу первичный индекс. eExpression3 задает выражение ключа первичного индекса, а TagName2 имя тега первичного индекса. Имя тега индекса может содержать не более 10 символов. Если TAG TagName2 опущено, а eExpression3 определяет одно поле, тег первичного индекса получает то же имя, что и поле, заданное в выражении eExpression3 .
DROP PRIMARY KEY
Удаляет первичный индекс и его тег. Поскольку таблица может иметь только один первичный ключ, его имя можно не указывать. При удалении первичного индекса удаляются и все постоянные отношения, основанные на первичном ключе.
ADD UNIQUE eExpression4
Добавляет в таблицу индекс-кандидат. eExpression4 задает выражение ключа индекса-кандидата, а TagName3 имя тега индекса-кандидата. Имя тега индекса может содержать не более 10 символов. Если выражение TAG TagName3 опущено, а eExpression4 определяет одно поле, тег индекса-кандидата получает то же имя, что и поле, заданное в выражении eExpression4 .
DROP UNIQUE TAG TagName4
Удаляет индекс-кандидат и его тег. Поскольку таблица может иметь несколько ключей-кандидатов, необходимо задать имя удаляемого тега индекса-кандидата.
ADD FOREIGN KEY TAG TagName4
Добавляет в таблицу внешний (отличный от первичного) индекс. eExpression5 задает выражение ключа внешнего индекса, а TagName4 имя тега внешнего индекса. Имя тега индекса может содержать не более 10 символов.
REFERENCES TableName2
Задает родительскую таблицу, с которой устанавливается постоянное отношение. Чтобы установить отношение с родительской таблицей на базе тега существующего индекса, следует указать TAG TagName5 . Имя тега индекса может содержать не более 10 символов. Если TAG TagName5 опущено, отношение устанавливается с помощью тега первичного индекса родительской таблицы.
DROP FOREIGN KEY TAG TagName6
Удаляет внешний ключ, у которого тег индекса имеет имя TagName6. Если опустить SAVE , тег индекса будет удален из структурного индекса. Включив SAVE , вы предотвратите удаление тега из структурного индекса.
RENAME COLUMN FieldName4 TO FieldName5
Позволяет изменять имя поля в таблице. FieldName4 задает имя поля, которое следует изменить. FieldName5 задает новое имя поля.
Внимание Будьте осторожны при переименовании полей таблиц выражения индекса, правила проверки достоверности полей и таблиц, команды, функции и т.п. могут продолжать ссылаться на старые имена полей.
NOVALIDATE
Указывает, что Visual FoxPro допускает внесение изменений в структуру таблицы, которые могут нарушить целостность табличных данных. По умолчанию Visual FoxPro запрещает команде ALTER TABLE вносить в структуру подобные разрушающие изменения. Включение опции NOVALIDATE позволяет снять этот запрет.

ALTER TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD create_definition или ADD (create_definition, create_definition,...) или ADD INDEX (index_col_name,...) или ADD PRIMARY KEY (index_col_name,...) или ADD UNIQUE (index_col_name,...) или ADD FULLTEXT (index_col_name,...) или ADD FOREIGN KEY index_name (index_col_name,...) или ALTER col_name {SET DEFAULT literal | DROP DEFAULT} или CHANGE old_col_name create_definition или MODIFY create_definition или DROP col_name или DROP PRIMARY KEY или DROP INDEX index_name или DISABLE KEYS или ENABLE KEYS или RENAME new_tbl_name или ORDER BY col или table_options

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

Если оператор 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 TABL E .
  • В выражении 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

В этом учебном пособии вы узнаете, как использовать MySQL оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с синтаксисом и примерами).

Описание

MySQL оператор ALTER TABLE используется для добавления, изменения или удаления столбцов в таблице. Оператор MySQL ALTER TABLE также используется для переименования таблицы.

Добавить столбец в таблицу

Синтаксис

Синтаксис добавления столбца в таблицу MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name




FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, то новый столбец будет добавлен в конец таблицы.

Пример

Рассмотрим пример, который показывает, как добавить столбец в таблицу MySQL, используя оператор ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id;

ALTER TABLE contacts

AFTER contact_id;

Этот MySQL пример ALTER TABLE добавит столбец с именем last_name в таблицу contacts . Он будет создан как столбец NOT NULL и появится в таблице после поля contact_id .

Добавить несколько столбцов в таблицу

Синтаксис

Синтаксис добавления нескольких столбцов в таблицу MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition

ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — имя таблицы для изменения.
new_column_name — имя нового столбца для добавления в таблицу.
column_definition — тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, новый столбец будет добавлен в конец таблицы.

Пример

Рассмотрим пример, который показывает, как добавить несколько столбцов в таблицу MySQL, используя оператор ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id, ADD first_name varchar(35) NULL AFTER last_name;

ALTER TABLE contacts

ADD last_namevarchar (40)NOT NULL

AFTER contact_id,

ADD first_namevarchar (35)NULL

AFTER last_name;

Этот пример ALTER TABLE добавит в таблицу contacts два столбца — last_name и first_name .

Поле last_name будет создано как столбец varchar (40) NOT NULL и появится в таблице contacts после столбца contact_id . Столбец first_name будет создан как столбец NULL varchar (35) и появится в таблице после столбца last_name .

Изменить столбец в таблице

Синтаксис

Синтаксис для изменения столбца в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name

[ FIRST | AFTER column_name ];

table_name — имя таблицы для изменения.


Пример

Рассмотрим пример, который показывает, как изменить столбец в таблице MySQL с помощью оператора ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts MODIFY last_name varchar(50) NULL;

ALTER TABLE contacts

MODIFY last_namevarchar (50)NULL ;

Этот пример ALTER TABLE изменит столбец с именем last_name как тип данных varchar (50) и установит для столбца значения NULL.

Изменить несколько столбцов в таблице

Синтаксис

Синтаксис для изменения нескольких столбцов в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — имя таблицы для изменения.
column_name — имя столбца для изменения в таблице.
column_definition — измененный тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.

Пример

Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице MySQL, используя оператор ALTER TABLE.

MySQL

ALTER TABLE contacts MODIFY last_name varchar(55) NULL AFTER contact_type, MODIFY first_name varchar(30) NOT NULL;

ALTER TABLE contacts

MODIFY last_namevarchar (55)NULL

AFTER contact_type,

MODIFY first_namevarchar (30)NOT NULL ;

Этот пример ALTER TABLE будет изменять в таблице contacts два столбца — last_name и first_name .
Поле last_name будет изменено на столбец NULL varchar (55) и появится в таблице после столбца contact_type . Столбец first_name будет изменен на столбец varchar (30) NOT NULL (и не изменит позицию в определении таблицы contacts , так как не указано FIRST | AFTER).

Удаление столбца из таблицы

Синтаксис

Синтаксис для удаления столбца из таблицы в MySQL (с использованием оператора ALTER TABLE):
Например:

ALTER TABLE table_name
DROP COLUMN column_name;

table_name — имя таблицы для изменения.
column_name — имя столбца для удаления из таблицы.

Пример

Рассмотрим пример, который показывает, как удалить столбец из таблицы в MySQL с помощью оператора ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts DROP COLUMN contact_type;

ALTER TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:

ADD create_definition

или ADD (create_definition, create_definition,...)

или ADD INDEX (index_col_name,...)

или ADD PRIMARY KEY (index_col_name,...)

или ADD UNIQUE (index_col_name,...)

или ADD FULLTEXT (index_col_name,...)

или ADD FOREIGN KEY index_name (index_col_name,...)

или ALTER col_name {SET DEFAULT literal | DROP DEFAULT}

или CHANGE old_col_name create_definition

или MODIFY create_definition

или DROP col_name

или DROP PRIMARY KEY

или DROP INDEX index_name

или DISABLE KEYS

или ENABLE KEYS

или RENAME new_tbl_name

или ORDER BY col

или table_options

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

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

Следует отметить, что при использовании любой другой опции для ALTER TABLE,кромеRENAME, 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 просто переименовывает файлы, соответствующие заданной таблице. В этом случае нет необходимости создавать временную таблицу. В выраженииcreate_definitionдляADDиCHANGEиспользуется тот же синтаксис, что и для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илиMODIFYMySQL пытается преобразовать данные в новый тип как можно корректнее.

В версии MySQL 3.22 и более поздних можно использовать FIRSTилиADD ... AFTER имя_столбцадля добавления столбца на заданную позицию внутри табличной строки. По умолчанию столбец добавляется в конце. Начиная с версии MySQL 4.0.1, можно также использовать ключевые словаFIRSTиAFTERв опцияхCHANGEилиMODIFY.

Опция ALTER COLUMNзадает для столбца новое значение по умолчанию или удаляет старое. Если старое значение по умолчанию удаляется и данный столбец может принимать значениеNULL, то новое значение по умолчанию будетNULL. Если столбец не может бытьNULL, то MySQL назначает значение по умолчанию. ОпцияDROP INDEXудаляет индекс. Это является расширением MySQL по отношению к ANSI SQL92. Если столбцы удаляются из таблицы, то эти столбцы удаляются также и из любого индекса, в который они входят как часть. Если все столбцы, составляющие индекс, удаляются, то данный индекс также удаляется.

Если таблица содержит только один столбец, то этот столбец не может быть удален. Вместо этого можно удалить данную таблицу, используя команду 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 и запуск приложений, создающих таблицы со ссылками.

Ниже приводятся примеры, показывающие некоторые случаи употребления команды 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,

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

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



Есть вопросы?

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Отправить