Что делает команда 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 позволяет снять этот запрет.

Манипулирование таблицами - один из самых распространенных видов деятельности, который администраторы баз данных и программисты осуществляют при работе с объектами баз. В этом разделе подробно описывается, как нужно создавать и изменять таблицы.

Стандарт ANSI является чем-то вроде наименьшего общего знаменателя для всех производителей, хотя не каждая возможность стандартной версии инструкций CREATE TABLE и ALTER TABLE реализуется каждым производителем. Тем не менее стандарт ANSI представляет собой базовую форму, которую можно использовать на всех платформах.

В свою очередь, платформы предлагают разнообразные расширения и дополнения к инструкциям CREATE TABLE и ALTER TABLE стандарта ANSI.

Как правило, нужно как следует продумать проект таблицы и способ ее создания. Этот процесс называется проектированием базы данных. Процесс анализа взаимоотношений таблицы с ее собственными данными и другими таблицами в базе данных называется нормализацией. Мы рекомендуем программистам и администраторам баз данных изучить принципы проектирования и нормализации перед тем, как использовать команды CREATE DATABASE.

Синтаксис SQL 2003

При выполнении инструкции CREATE TABLE стандарта SQL 2003 в базе данных создается постоянная или временная таблица. Синтаксис следующий.

CREATE [{LOCAL TEMPORARY) GLOBAL TEMPORARY}] TABLE имя_таблицы (имя_столбца тип_данных атрибуты [, …]) | [имя_столбца WITH OPTIONS опции] | | {SYSTEM GENERATED | USER GENERATED | DERIVED}] [, …]] [определение_таблицы]] имя_таблицы тип_данных атрибуты] | имя_столбца SET DEFAULT значение_по_умолчанию] | имя_столбца DROP DEFAULT] | имя_столбца ADD SCOPE имя_таблицы | имя_столбца DROP SCOPE {RESTRICT | CASCADE}] | имя_столбца {RESTRICT | CASCADE}] | |

Ключевые слова

TEMPORARY

Объявляется постоянная или временная (TEMPORARY) таблица с локальной (LOCAL) или глобальной (GLOBAL) областью действия. Локальные временные таблицы доступны только из создавшего их сеанса, и они автоматически удаляются, когда завершается создавший их сеанс. Глобальные временные таблицы доступны из всех активных сеансов, но они автоматически удаляются, когда завершается создавший их сеанс. Не уточняйте имена временных таблиц именем схемы.

(имя_столбца тип_данных атрибуты [,])

Определяется список, в котором через запятую перечислены один или несколько столбцов, их типы данных и дополнительные атрибуты, например допустимость значений NULL (nullability). Каждое объявление таблицы должно включать, как минимум, один столбец, для которого можно указать:

имя_столбца

Указывается имя столбца. Оно должно представлять собой идентификатор, допустимый с точки зрения правил конкретной СУРБД. Имя должно быть осмысленным!

тип_данных

Связывает со столбцом с именем имя_столбца определенный тип данных. Для тех типов данных, которые позволяют указывать их длину, существует дополнительный параметр длина, например VARCHAR(255). Тип данных должен быть допустимым в СУРБД. За полным описанием допустимых типов данных и их вариантов у конкретных производителей обращайтесь к главе 2. атрибуты

Связывает со столбцом указанные атрибуты-ограничения. Для одного столбца с именем имя_столбца можно указывать несколько атрибутов. Запятые не требуются. К типичным атрибутам ANSI относятся следующие.

NOT NULL

В столбце запрещаются значения NULL (или разрешаются, если предложение NOT NULL опущено). Любые инструкции INSERT и UPDATE, которые попытаются поместить значение NULL, в столбец с атрибутом NOT NULL не будут выполнены, и произойдет откат.

DEFAULT выражение

Столбец будет использовать значение выражения, если инструкция INSERT или UPDATE не вводит никакого значения. Выражение должно быть допустимым для типа данных столбца; например, в столбце типа INTEGER нельзя использовать никакие буквенные символы. Выражение может представлять собой строку или числовой литерал, но вы также можете указать пользовательскую или системную функцию. Стандарт SQL 2003 позволяет использовать в предложении DEFAULT следующие системные функции: NULL, USER, CURRENTJJSER, SESSION_USER, SYSTEMJJSER, CURRENT_PATH, CURRENT_D А ТЕ, CURRENTJIME, LOCALTIME, CURRENTJIMESTAMP, LOCALTJMESTAMP, ARRAY или ARRAY.

COLLATE имя_сопоставления

Определяется используемое сопоставление (collation), то есть порядок сортировки в соответствующем столбце. Имя сопоставления зависит от платформы. Если имя сопоставления не определяется, по умолчанию принимается сопоставление по набору символов, используемому в столбце. REFERENCES ARE CHECKED Параметр определяет, будут ли проверяться ссылки в столбце REF, определенном с опцией области действия (scope). Дополнительное предложение ON DELETE определяет, будут ли значения в записях, на которые ссылалась удаленная запись, устанавливаться в NULL или же на выполнение операции будет наложено ограничение.

CONSTRAINT имя ограничения [тип_ограничения [ограничение]]

Параметр назначает столбцу ограничение и при желании имя ограничения. Типы ограничений обсуждаются в главе 2. Поскольку ограничение связывается с конкретным столбцом, в объявлении ограничения предполагается, что этот столбец в данном ограничении будет единственным. После того как таблица будет создана, ограничение будет считаться ограничением уровня таблицы.

имя_столбца

Столбец определяется со специальными опциями, такими, как опция области действия (scope), опция значения по умолчанию (default), ограничением уровня столбца или предложением COLLATE. Во многих реализациях предложение WITH OPTIONS ограничивается созданием объектных (typed) таблиц.

LIKE имя_таблицы

Новая таблица создается с теми же определениями столбцов, что и в существующей таблице имя_таблицы.

REFIS имя_столбца {SYSTEM GENERATED | USER GENERATED DERIVED]

Определяется столбец объектных идентификаторов (object identifier, OID) в объектных (typed) таблицах. Объектный идентификатор является необходимым для таблицы, являющейся корневой в иерархии таблиц. В соответствии с этим параметром столбец REF может генерироваться системой автоматически (SYSTEM GENERATED), вручную указываться пользователем при вводе строки (USER GENERATED) или создаваться на основе другого столбца REF (DERIVED). Параметр требует включать в столбец имя_стол6ца атрибут REFERENCES.

CONSTRAINT тип ограничения [имя ограничения] [, …]

Таблице присваивается одно или несколько ограничений. Этот параметр заметно отличается от ограничений уровня столбца, поскольку предполагается, что ограничения уровня столбца применяются только к столбцу, с которым они связаны. В случае ограничений уровня таблицы существует возможность связать с ограничением несколько столбцов. Например, в таблице продаж вам может понадобиться объявить уникальное ограничение на сцепленный ключ store_id, order_id и order_date. Сделать это можно только при помощи ограничения уровня таблицы. За подробным обсуждением ограничений обращайтесь к главе 2.

OF имя_типа [определение_таблицы]

Объявляется, что таблица основывается на готовом пользовательском типе. В этой ситуации таблица может иметь только один столбец для каждого атрибута структурированного типа плюс дополнительный столбец, определенный в предложении REF IS. Тип данных REF подробно описывается в разделе «Инструкция CREATE/ALTER ТУРЕ». Это предложение несовместимо с предложением LIKE имя_таблицы. Где:

UNDER супертаблица [определение/таблицы]

Объявляется непосредственная супертаблица для текущей таблицы в той же схеме (если существует). При желании вы можете указать для супертаблицы полное определение_таблицы, заполнив его столбцами, ограничениями и тому подобное.

ON COMMIT {PRESERVE ROWS DELETE ROWS]

Предложение ON COMMIT PRESERVE ROWS сохраняет строки данных временной таблицы при выполнении инструкции COMMIT. Предложение ON COMMIT DELETE RO WS удаляет все строки данных во временной таблице при выполнении инструкции COMMIT.

ADD имя_столбца тип_данных атрибуты

В таблицу добавляется столбец с соответствующим типом данных и атрибутами.

ALTER имя_столбца SET DEFAULT значение_по_умолчанию

В столбец добавляется значение по умолчанию (если оно не существует) или изменяется существующее значение.

ALTER имя_столбца DROP DEFAULT

Значение по умолчанию полностью удаляется из указанного столбца.

ALTER имя_столбца ADD SCOPE имя_таблицы

В указанный столбец добавляется область действия (scope). Область действия представляет собой ссылку на пользовательский тип данных.

ALTER имя_столбца DROP SCOPE

Область действия удаляется из указанного столбца. Предложения RESTRICT и CASCADE объясняются в конце данного списка.

DROP COLUMN имя_столбца

Указанный столбец удаляется из таблицы. Предложения RESTRICT и CASCADE объясняются в конце данного списка.

ADD табличное_ограничение

В таблицу добавляется ограничение с указанным именем и характеристиками.

DROP CONSTRAINT имя ограничения

Существующее ограничение удаляется из таблицы.

RESTRICT

При указании этого предложения СУРБД отменяет команду, если находит в базе данных объекты, зависящие от данного объекта.

При указании этого предложения СУРБД удаляет все прочие объекты, зависящие от данного объекта.

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 = #.