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

Оператор ALTER TABLE

Можно выделить следующие уровни проверочных ограничений:

уровень атрибута (столбца),
уровень кортежа (строки),
уровень отношения (таблицы).

В ограничении уровня столбца проверяется значение только одного отдельного столбца, другими словами, в ограничении данного типа имеется ссылка только на один столбец той таблицы, в определении которой содержится данное ограничение. Чтобы привести пример такого ограничения, вернёмся к схеме «Компьютерная фирма». В таблице Product в столбце type может находиться одно из трех значений. Мы можем запретить ввод любой другой информации в этот столбец при помощи такого ограничения:

Давайте сделаем отступление, чтобы познакомиться с оператором ALTER TABLE , который позволит нам изменять структуру таблицы, не пересоздавая её всякий раз заново. Это тем более важно, что изменение структуры может потребоваться тогда, когда таблица уже содержит данные.

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

В настоящий момент нас интересует добавление ограничения на столбец type , поэтому вначале приведём синтаксис оператора для добавления ограничения:

Давайте теперь добавим наше ограничение и проверим, как оно работает.

Чтобы убедиться в том, что ограничение работает как мы того ожидаем, попробуем добавить модель нового типа:

Как и ожидалось, в ответ мы получим сообщение об ошибке:

The INSERT statement conflicted with the CHECK constraint “chk_type”. The conflict occurred in database “learn”, table “dbo.product”, column ‘type’. The statement has been terminated.

(Конфликт инструкции INSERT с ограничением CHECK “chk_type”. Конфликт произошел в базе данных “learn”, таблица “dbo.product”, столбец ‘type’. Выполнение данной инструкции было прервано.)

Как легко догадаться, ограничение уровня строки содержит ссылки на несколько столбцов. При этом ограничение проверяется для каждой изменяемой строки отдельно. Строка может быть добавлена (или изменена), если ограничение не нарушено.

В качестве примера давайте запретим производителю Z выпускать что-либо помимо принтеров.

Итак, ограничение проверяет, что модель в таблице Product должна быть принтером производителя Z (maker=’Z’ and type= ‘printer’) или любого другого производителя (но не Z).

Если мы попытаемся добавить модель ПК производителя Z,

Разумеется, другой производитель сможет выпускать все, что угодно:

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

Справочное руководство по MySQL

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

6 Справочник по языку MySQL
- 6.5 Определение данных: CREATE , DROP , ALTER
  - 6.5.1 Синтаксис оператора CREATE DATABASE
  - 6.5.2 Синтаксис оператора DROP DATABASE
  - 6.5.3 Синтаксис оператора CREATE TABLE
  - 6.5.4 Синтаксис оператора ALTER TABLE
  - 6.5.5 Синтаксис оператора RENAME TABL E
  - 6.5.6 Синтаксис оператора DROP TABLE
  - 6.5.7 Синтаксис оператора CREATE INDEX
  - 6.5.8 Синтаксис оператора DROP INDEX

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

Оператор 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 TABL E.
В выражении create_definition для ADD и CHANGE используется тот же синтаксис, что и для CREATE TABLE . Следует учитывать, что этот синтаксис включает имя столбца, а не просто его тип. See section 6.5.3 Синтаксис оператора CREATE TABLE .
Столбец можно переименовывать, используя выражение CHANGE имя_столбца create_definition . Чтобы сделать это, необходимо указать старое и новое имена столбца и его тип в настоящее время. Например, чтобы переименовать столбец INTEGER из a в b , можно сделать следующее: При изменении типа столбца, но не его имени синтаксис выражения CHANGE все равно требует указания обоих имен столбца, даже если они одинаковы. Например: Однако начиная с версии MySQL 3.22.16a можно также использовать выражение MODIFY для изменения типа столбца без переименовывания его:
При использовании 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.

Читать еще: Разница между iphone 7 и. Фотомодуль: одна камера хорошо, а две – лучше

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

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

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

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

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

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

Для того чтобы добавить новый числовой столбец AUTO_INCREMENT с именем 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 .

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

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

Данная статья является свободным переводом официальной документации MySQL версии 5.0.

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

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

index_col_name:
col_name [ ( length ) ] [ ASC | DESC ]

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

Синтаксис оператора ALTER TABLE во многих случаях подобен синтаксису CREATE TABLE . Для получения подробной информации см. раздел 11.1.5, “CREATE TABLE Syntax”.

Некоторые операции могут вызвать предупреждения, если совершать их над таблицей, механизм хранения которой не поддерживает такие операции. Эти предупреждения можно просмотреть с помощью оператора SHOW WARNINGS . См. раздел 11.5.4.28, “SHOW WARNINGS Syntax”

Если вы используете оператор ALTER TABLE для изменения колонки, но оператор DESCRIBE tbl_name показывает, что ваша колонка осталась неизменной, то возможно, что сервер MySQL проигнорировал ваши изменения по одной из причин, описанных в разделе 11.1.5.1, “Silent Column Specification Changes”

В большинстве случаев оператор ALTER TABLE работает, создавая временную копию исходной таблицы. Изменения осуществляются на копии, затем исходная таблица удаляется и новая переименовывается. Пока оператор ALTER TABLE выполняется, исходная таблица остается доступной для чтения другим клиентам. Обновление и запись данных в таблицу задерживаются до тех пор пока новая таблица не будет готова, и затем автоматически направляются к новой таблице без каких-либо уведомлений о неудавшихся изменениях. Временная таблица создается в директории базы данных новой таблицы. Она может отличаться от директории базы данных исходной таблицы, если оператор ALTER TABLE с помощью переименования таблицы переносит её в другую базу данных.

Если вы используете оператор ALTER TABLE tbl_name RENAME TO new_tbl_name без каких-либо других опций, MySQL просто переименует файлы, относящиеся к таблице tbl_name . (Для переименования таблиц вы также можете использовать оператор RENAME TABLE . См. раздел 11.1.9, “RENAME TABLE Syntax”.) Права доступа пользователей, относящиеся именно к переименованной таблице, не перенесутся на новое имя. Они должны быть изменены вручную с помощью операторов GRANT и REVOKE .

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

Информацию о возможных проблемах при использовании оператора ALTER TABLE см. в разделе B.1.7.1. “Problems with ALTER TABLE”.

Для выполнения оператора ALTER TABLE необходимо иметь привелегии ALTER , INSERT и CREATE на изменяемую таблицу.
Ключевое слово IGNORE – это расширение MySQL к стандарту SQL. Оно определяет как будет работать оператор ALTER TABLE , если в результате изменения таблицы произойдет дублирование уникального ключа или возникнет предупреджение при работе в SQL-режиме strict. Если ключевое слово IGNORE не указано, то при возникновении ошибки копирование прерывается и откатывается назад. Если указано ключевое слово IGNORE , то используется только первая строка среди строк с дублирующимся уникальным ключом, остальные конфликтующие строки удаляются. Некорректные значения приводятся к ближайшему допустимому значению.
table_option обозначает опции таблицы, которые могут быть использованы в операторе CREATE TABLE , такие как ENGINE , AUTO_INCREMENT или AVG_ROW_LENGTH . (В разделе 12.1.10, “CREATE TABLE Syntax” перечисленны все табличные опции). Однако, оператор ALTER TABLE игнорирует опции DATA DIRECTORY и INDEX DIRECTORY .
Например, для преобразования механизма хранения таблицы к InnoDB используется следующее выражение:

Используя mysql_info ( ) C API функции, вы можете определить сколько строк было скопировано и (в случае использования ключевого слова IGNORE ) сколько строк было удалено вследствии дублирования уникального ключа. См. раздел 20.9.3.35, “mysql_info()”.

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

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

Для изменения колонки a с INTEGER на TINYINT NOT NULL (без переименования) и изменения колонки b с CHAR ( 10 ) на CHAR ( 20 ) , одновременно переименуя её из b в c :

Для добавления новой колонки d типа TIMESTAMP :

Для добавления индекса на колонку d и уникального индекса на колонку a :

Для удаления колонки c :

Для добавления новой целочисленной AUTO_INCREMENT колонки c :

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

При добавлении AUTO_INCREMENT колонки она автоматически заполняется последовательными значениями целых чисел. Для MyISAM -таблиц можно указать величину первого значения, выполнив команду SET INSERT_ID=value перед оператором ALTER TABLE или используя табличную опцию AUTO_INCREMENT =value . См. раздел 5.1.4, “Session System Variables”.

В случае MyISAM -таблиц если не происходит смены AUTO_INCREMENT колонки нумерация чисел не меняется. Если сначала удалить AUTO_INCREMENT колонку, а затем создать новую AUTO_INCREMENT колонку, то её заполнение начнется с единицы.

При использовании репликации добавление AUTO_INCREMENT колонки не гарантирует одинаковый порядок строк на мастере и slave. Это поисходит вследствии того, что порядок нумерации строк зависит от особенностей механизма хранения используемого для таблицы и того в каком порядке были добавлены строки в таблицу. Если важно иметь одинаковый порядок строк на мастере и slave, строки должны быть упорядочены до назначения AUTO_INCREMENT номера. Предположим, что вы хотите добавить AUTO_INCREMENT колонку к таблице t1 , следующий оператор новую таблицу t2 идентичную t1 , но имеющую AUTO_INCREMENT колонку:

Здесь предполагается, что таблица t1 имеет колонки col1 и col2 .

Следующие операторы также создают новую таблицу t2 идентичную t1 , но имеющую AUTO_INCREMENT колонку:

Независимо от метода, используемого для создания и наполнения копии, имеющей AUTO_INCREMENT колонку, заключительный этап совпадает: удаление исходной таблицы и переименование копии:

SQL ALTER TABLE

Команда ALTER TABLE используется для добавления, удаления или модификации колонки в уже существующей таблице.

Команда ALTER TABLE

Команда ALTER TABLE изменяет определение таблицы одним из следующих способов:

добавляет столбец
добавляет ограничение целостности
переопределяет столбец (тип данных, размер, умалчиваемое значение)
удаляет столбец
модифицирует характеристики памяти или иные параметры
включает, выключает или удаляет ограничение целостности или триггер.

Условие: Таблица должна быть в схеме пользователя, или пользователь должен иметь системную привилегию ALTER ANY TABLE.

Добавляя столбец с ограничением NOT NULL, разработчик или администратор БД должны учесть ряд обстоятельств. Сначала нужно создать столбец без ограничения, а затем ввести значения во все его строки. После того как все значения столбца станут не NULL-значениями, к нему можно применить ограничение NOT NULL. Если столбец с ограничением NOT NULL пытается добавить пользователь, возвращается сообщение об ошибке, говорящее о том, что либо таблица должна быть пустой, либо в столбце должны содержаться значения для каждой существующей строки (напомним, что после наложения на столбец ограничения NOT NULL в нем не могут присутствовать NULL-значения ни в одной из существующих строк). В СУБД Oracle, начиная с версии 8i, можно удалять столбцы.

Изменяя типы данных существующих столбцов или добавляя столбцы в таблицу базы данных, нужно соблюдать ряд условий. Общепринято, что увеличение – это хорошо, а уменьшение, как правило, — не очень. Допустимые увеличения:

Увеличение размера столбца CHAR или VARCHAR2
Увеличение размера столбца NUMBER
Добавление новых столбцов в таблицу

Уменьшение различных характеристик таблицы, в том числе некоторых типов данных столбцов и реального числа столбцов таблицы, требует особых действий. Часто перед внесением изменения нужно убедиться в том, что в соответствующем столбце или столбцах все значения являются NULL-значениями. Для выполнения подобных операций над столбцами таблицы, содержащими данные, разработчик должен найти или создать какую-то область для временного хранения этих данных. Например, создать таблицу с помощью команды CREATE TABLE AS SELECT, в которой извлекаются данные из первичного ключа и изменяемого столбца или столбцов. Допустимые изменения:

Уменьшение размера столбца NUMBER (только при пустом столбце для всех строк)
Уменьшение размера столбца CHAR или VARCHAR2 (только при пустом столбце для всех строк)
Изменение типа данных столбца (только при пустом столбце для всех строк)

ALTER TABLE Пример 1

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

ALTER TABLE t1(pole1 char(10));

ALTER TABLE Пример 2

Изменение размера столбца таблицы:

ALTER TABLE t1 MODIFY (pole1 char(20));

ALTER TABLE Пример 3

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

ALTER TABLE t1 DROP COLUMN pole1;

С помощью команды ALTER TABLE можно изменить имя таблицы без реального переноса физической информации в БД:

ALTER TABLE t1 RENAME TO t2;

Аналогичную операцию можно выполнить с помощью команды RENAME:

RENAME t1 TO t2;

Ограничения целостности столбцов и таблиц БД можно изменять, а также запрещать, разрешать и удалять. Это дает разработчику возможность создавать, модифицировать и удалять бизнес-правила, ограничивающие данные. Рассмотрим добавление ограничений в БД. Простота или сложность этого процесса зависит от определенных обстоятельств. Если вместе с БД создать ограничение нельзя, проще всего добавить его перед вводом данных:

ALTER TABLE Пример 4

Модификация структуры таблицы

ALTER TABLE t1 MODIFY (pole1 NOT NULL);

CREATE TABLE t2

(pole1 CHAR(10) PRIMARY KEY);

ALTER TABLE t1 ADD

(CONSTRAINT fk_t1 FOREIGN KEY (pole1)

REFERENCES t2 (pole1));

ALTER TABLE t1 ADD (UNIQUE (p_name));

ALTER TABLE t1 ADD (p_size CHAR(4) CHECK

В первой из приведенных выше команд для добавления ограничения NOT NULL для столбца используется конструкция MODIFY, а для добавления всех табличных ограничений целостности других типов – конструкция ADD. Столбец, для которого добавляется ограничение, должен уже существовать в таблице БД; в противном случае создать ограничение не удастся.

ALTER TABLE Пример 5

Для добавления ограничений целостности можно не указывать имя создаваемого ограничения с помощью ключевого слова CONSTRAINT. В этом случае команда будет выглядеть следующим образом:

ALTER TABLE t1 ADD FOREIGN KEY (pole1) REFERENCES t2 (pole1);

Существует ряд условий создания ограничений:

Первичные ключи: в столбцах не могут содержаться NULL-значения, и все значения должны быть уникальны.
Внешние ключи: в тех столбцах других таблиц, на которые производятся ссылки, должны содержаться значения, соответствующие всем значениям ссылающихся столбцов, либо значения этих последних должны быть NULL-значениями.
Ограничения UNIQUE: все значения столбцов должны быть уникальными или NULL-значениями.
Ограничения CHECK: новое ограничение будет применяться только по отношению к данным, добавляемым или модифицируемым после его создания.
NOT NULL: NULL-значения в столбцах запрещены.

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

ALTER TABLE Пример 6

ALTER TABLE t1 DISABLE PRIMARY KEY;
ALTER TABLE t1 DISABLE UNIQUE (p_name);

ALTER TABLE Пример 7

В некоторых случаях запрещение первичного ключа, от которого зависят внешние ключи, может вызвать определенные сложности, например:

ALTER TABLE t2 DISABLE PRIMARY KEY;

Error at line 1: Cannot disable constraint …. – dependencies exist (невозможно запретить ограничение – существуют зависимости)

Для удаления первичного ключа при наличии зависящих от него внешних ключей в команде ALTER TABLE DISABLE обязательна конструкция CASCADE:

ALTER TABLE t2 DISABLE PRIMARY KEY CASCADE;

ALTER TABLE Пример 8

Запрещенное ограничение разрешается следующим образом:

ALTER TABLE t1 ENABLE PRIMARY KEY;

ALTER TABLE t1 ENABLE UNIQUE (p_name);

Разрешить можно только те ограничения, которые были установлены ранее, а в данный момент запрещены.

Ограничение, процесс создания которого завершился неудачей, не будет существовать в запрещенном виде, ожидая своего разрешения после устранения ошибки. Как правило, владелец таблицы или тот, кому предоставлены соответствующие права, может удалить ограничение:

ALTER TABLE t1 DROP UNIQUE (p_name);

Вы должны войти, чтобы оставить комментарий.

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

В некоторых случаях MySQL тихо изменяет спецификацию столбца, заданную в инструкции CREATE TABLE . Это может также происходить с ALTER TABLE :

VARCHAR -столбцы с длиной меньше, чем четыре, будут изменены на CHAR .
Если любой столбец в таблице имеет переменную длину, вся строка будет в результате переменной длины. Следовательно, если таблица содержит любые столбцы переменной длины ( VARCHAR , TEXT или BLOB ), все столбцы CHAR длиной более, чем три символа, будут изменены на VARCHAR . В MySQL VARCHAR всего лишь иной способ хранить символы. MySQL выполняет это преобразование потому, что оно экономит место и делает операции с таблицей быстрее.
TIMESTAMP должен быть четный и в диапазоне от 2 до 14. Если Вы определяете 0 или больше, чем 14, размер принудительно выставится в 14. Нечетно-оцененные размеры в диапазоне от 1 до 13 принудительно выставятся в следующее более высокое четное число.
Вы не можете сохранять литеральный NULL в столбце TIMESTAMP . Установка его в NULL реально устанавливает его к текущей (актуальной) дате и времени. Потому, что столбцы TIMESTAMP ведут себя именно так, атрибуты NULL и NOT NULL не применяются нормальным способом и игнорируются, если Вы определяете их. DESCRIBE tbl_name всегда сообщает, что столбцу TIMESTAMP может быть назначено значение NULL .
MySQL отображает некоторые типы столбца, используемые другими SQL-серверами баз данных к типам MySQL. Подробности в разделе “5.5 Использование типов столбцов из других баз данных”.

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

Некоторые другие изменения типа столбца могут происходить, если Вы сжимаете таблицу, используя myisampack .

ALTER TABLE позволяет Вам изменять структуру существующей таблицы. Например, Вы можете добавлять или удалять столбцы, создавать или уничтожать индексы, изменять тип существующих столбцов, переименовывать столбцы или непосредственно таблицу. Вы можете также изменять комментарий для таблицы и тип таблицы. Подробности в разделе “7.3 Синтаксис CREATE TABLE “.

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

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

Обратите внимание, что, если Вы используете любую другую опцию ALTER TABLE , чем RENAME , MySQL будет всегда создавать временную таблицу, даже если данные не должны быть строго скопированы (подобно тому, когда Вы изменяете имя столбца). Авторы планируют исправить это в будущем, но поскольку ALTER TABLE вызывается относительно редко, это не имеет высокого приоритета в TODO.

Чтобы использовать 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 tbl_name RENAME TO new_name без любых других параметров, MySQL просто переименовывает файлы, которые соответствуют таблице tbl_name . Нет никакой потребности создавать временную таблицу. Подробности в разделе “7.5 Синтаксис RENAME TABLE “.
Начиная с MySQL 4.0 вышеупомянутое свойство может быть активизировано явно. ALTER TABLE . DISABLE KEYS предписывает прекратить модифицировать неуникальные индексы для таблиц MyISAM . ALTER TABLE . ENABLE KEYS затем должен использоваться, чтобы освежить отсутствующие индексы. MySQL делает это специальным алгоритмом, который намного быстрее, чем вставка ключей один за другим, блокировка обработки ключей может давать значительное ускорение работы системы на оптовых вставках.
Предложение create_definition используют тот же самый синтаксис для ADD и CHANGE , что и CREATE TABLE . Обратите внимание, что этот синтаксис включает имя столбца, а не только тип столбца. Подробности в разделе “7.3 Синтаксис CREATE TABLE “.
Вы можете переименовывать столбец, используя CHANGE old_col_name create_definition . Чтобы сделать так, определите старые и новые имена столбца и тип, который столбец в настоящее время имеет. Например, чтобы переименовать столбец типа INTEGER из a в b , Вы можете сделать: Если Вы хотите изменять тип столбца, но не имя, синтаксис CHANGE все еще требует имен двух столбцов, даже если это тот же самый столбец: Однако, начиная с MySQL Version 3.22.16a, Вы можете также использовать MODIFY , чтобы изменить тип столбца без переименования:
Если Вы используете CHANGE или MODIFY , чтобы сократить столбец, для которого индекс существует как часть столбца (например, если Вы имеете индекс на первых 10 символах столбца VARCHAR ), Вы не можете делать столбец короче, чем число символов, которые индексированы.
Когда Вы изменяете тип столбца, используя CHANGE или MODIFY , MySQL пробует преобразовывать данные к новому типу.
В MySQL Version 3.22 или позже Вы можете использовать FIRST или ADD . AFTER col_name , чтобы добавить столбец в специфической позиции внутри строки таблиц. Значение по умолчанию должно добавить столбец в последнюю позицию.
ALTER COLUMN определяет новое значение по умолчанию для столбца или удаляет старое значение по умолчанию. Если старое значение по умолчанию удалено, и столбец может быть NULL , новое значение по умолчанию будет NULL . Если столбец не может быть NULL , MySQL назначает значение по умолчанию, как описано в разделе “7.3 Синтаксис CREATE TABLE “.
DROP INDEX удаляет индекс. Это расширение MySQL для ANSI SQL92. Подробности в разделе “7.8 Синтаксис DROP INDEX “.
Если столбцы удалены из таблицы, столбцы будут также удалены из любого индекса, частью которого они являются. Если все столбцы, которые составляют индекс, удалены, то будет удален и сам индекс.
Если таблица содержит только один столбец, он не может быть удален.
DROP PRIMARY KEY удаляет первичный индекс. Если такой индекс не существует, это удаляет первый UNIQUE -индекс в таблице (MySQL отмечает первый ключ UNIQUE как PRIMARY KEY , если никакой PRIMARY KEY не был определен явно). Если Вы добавляете UNIQUE INDEX или PRIMARY KEY к таблице, он будет сохранен перед любым не- UNIQUE индексом так, чтобы MySQL мог обнаружить двойные ключи как можно раньше.
ORDER BY позволяет Вам создавать новую таблицу со строками в специфическом порядке. Обратите внимание, что таблица не будет оставаться в этом порядке после вставок и удалений. В некоторых случаях это может сделать сортировку проще для MySQL, если таблица сделана так, как Вы желаете упорядочить ее позже. Эта опция главным образом полезна, когда Вы знаете, что Вы обычно собираетесь сделать запрос строк в некотором порядке. Используя эту опцию после больших изменений для таблицы, Вы можете получить более высокую эффективность.
Если Вы используете ALTER TABLE на таблице MyISAM , все неуникальные индексы будут созданы в отдельном пакете (подобно тому, как это происходит в REPAIR ). Это ускорит ALTER TABLE , когда Вы имеете много индексов.

Имеется пример, который показывает, некоторые из способов применения ALTER TABLE . Мы начинаем с таблицы t1 , которая создана как показано ниже:

Чтобы переименовывать таблицу из t1 в t2 , надо:

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

Чтобы добавить новый столбец типа TIMESTAMP с именем d , надо сделать:

Чтобы добавить индекс на столбце d , и сделать столбец a первичным ключом, надо:

Чтобы удалить столбец c :

Чтобы добавить новый целочисленный столбец c с поддержкой AUTO_INCREMENT , надо:

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

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

С таблицами MyISAM, если Вы не изменяете столбец на AUTO_INCREMENT , номер последовательности не будут изменен. Если Вы удаляете столбец с поддержкой AUTO_INCREMENT , а затем добавляете другой с AUTO_INCREMENT , числа снова начнутся с 1.

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

Переименование выполняется слева направо, это означает, что, если Вы хотите менять два имени таблицы, Вы должны сделать так:

Таблицы можно не только переименовывать, но еще и перемещать между разными базами данных. Например:

Когда Вы выполняете RENAME , Вы не можете иметь блокированные таблицы или активные транзакции. Вы должны также иметь привилегии ALTER и DROP на первоначальной таблице и привилегии CREATE и INSERT на новой таблице.

Если MySQL сталкивается с любыми ошибками в процессе переименования, он будет делать обратное переименование для всех переименованных таблиц, чтобы вернуть все обратно в первоначальное состояние.

DROP TABLE удаляет одну или большее количество таблиц. Все данные таблицы и определение таблицы будут удалены, так что будьте внимательны с этой командой!

В MySQL Version 3.22 или позже Вы можете использовать ключевые слова IF EXISTS , чтобы предотвратить ошибку для таблиц, которых нет.

RESTRICT и CASCADE ничего не делают и нужны лишь для совместимости с другими СУБД.

ОБРАТИТЕ ВНИМАНИЕ: DROP TABLE не транзакционно-безопасна и автоматически завершает любые активные транзакции.

Инструкция CREATE INDEX ничего не делает до Version 3.22. В Version 3.22 или позже CREATE INDEX она отображена к инструкции ALTER TABLE , чтобы создать индексы. Подробности в разделе “7.4 Синтаксис ALTER TABLE “.

Обычно Вы создаете все индексы на таблице во время непосредственного создания таблицы с помощью вызова CREATE TABLE . Подробности в разделе “7.3 Синтаксис CREATE TABLE “. CREATE INDEX позволяет Вам добавлять индексы к существующим в базе таблицам.

Список столбцов в форме (col1,col2. ) создает индекс по многим столбцам. Индексные значения будут сформированы, связывая значения из заданных для индексации столбцов.

Для столбцов CHAR и VARCHAR индексы могут быть созданы с использованием только части столбца, применяя синтаксис col_name(length) . На столбцах BLOB и TEXT длина требуется обязательно. Инструкция, показанная ниже, создает индекс с использованием первых 10 символов из столбца name :

Поскольку большинство имен обычно отличается по первым 10 символам, этот индекс не должен быть намного медленнее, чем индекс, созданный из всего столбца name . Также использование частичных столбцов для индексов может делать индексный файл намного меньше, что сохранит много дискового пространства и ускорит операции INSERT !

Обратите внимание, что Вы можете добавлять индекс на столбце, который может иметь значения NULL , и на столбцах типов BLOB и TEXT только, если Вы используете MySQL Version 3.23.2 или более новую и применяете тип таблицы MyISAM .

Индексы FULLTEXT могут индексировать только столбцы типов VARCHAR и TEXT в таблицах типа MyISAM . Индексы FULLTEXT доступны в MySQL Version 3.23.23 и более новых. Подробности в разделе “9.4 Полнотекстовый поиск в MySQL”.

DROP INDEX удаляет индекс с именем index_name из таблицы tbl_name . DROP INDEX ничего не делает до версии Version 3.22, а начиная с нее, этот вызов перенацелен на соответствующий вызов ALTER TABLE для удаления индекса. Подробности в разделе “7.4 Синтаксис ALTER TABLE “.

ALTER TABLE. Изменение таблиц в базе данных MySQL

Команда ALTER TABLE в MySQL

Команда ALTER TABLE используется для осуществления изменений таблицы:

Добавление колонок
Удаление колонок
Модификация колонок
Изменения имени таблицы
Изменения кодировки таблицы
Добавление и удаление ограничений

Для дальнейших примеров будем использовать таблицу books из базы данных Bookstore, которую создали в одном из предыдущих постов.

Чтобы просмотреть изменения колонок в таблице, воспользуйтесь командой:
SHOW COLUMNS FROM table_name;

Перед началом работ выберем базу данных, с которой будем работать.

Как добавить колонку к таблице MySQL?

Синтаксис добавления колонки в таблицу:

Для примера добавим к таблице books колонку с именем discount, в которой будет хранится процент скидки на книги и amount для хранения количества книг.

Можно добавлять, удалять и модифицировать сразу несколько колонок за раз.

Каждая новая колонка добавляется в конец таблицы. Если вы хотите добавить новую колонку после определенной колонки, то используйте команду AFTER .

Добавим колонку shelf_position сразу после колонки price.

Как удалить колонку из таблицы MySQL?

Синтаксис удаления колонки из таблицы:

Если в таблице осталась только одна колонка, то ее удалить нельзя.

Удалим колонку edition с номером издания книги.

Как переставить колонки в таблице MySQL?

Чтобы переставить колонку используйте команду AFTER, также понадобится повторно определить тип данных.

Переместим колонку с ценой Price на место после колонки Author:

Как изменить имя и тип данных у колонки в таблице MySQL?

Синтаксис смены имени и типа данных у колонки:

Сменим имя у колонки сreation_date на entry_date и тип данных c TIMESTAMP на DATE .

Если вы хотите сменить только тип данных, оставив имя колонки прежним, то воспользуйтесь следующим синтаксисом:

Как изменить значение по умолчанию в колонке таблицы MySQL?

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

Синтаксис установки значения по умолчанию для колонки:

Установим для колонки discount значение по умолчанию:

Чтобы удалить значение по умолчанию, используйте синтаксис:

Удалим созданное значение по умолчанию для колонки discount:

Как изменить имя таблицы MySQL?

Синтаксис переименования таблицы:

Изменим имя таблицы books на books_collection;

Как изменить метаданные о таблице в MySQL?

С помощью команды ALTER можно также изменить некоторые метаданные о таблице.

Для начала давайте их выведем.

Вывод для таблицы books будет выглядеть следующим образом.

mysql> SHOW TABLE STATUS LIKE ‘books’G;
*************************** 1. row ***************************
Name: books
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 24
Avg_row_length: 682
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 5242880
Auto_increment: 25
Create_time: 2019-01-18 20:00:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

Для примера мы можем изменить тип движка таблиц:

И добавить комментарий к таблице:

Понравилась статья? Расскажите о ней друзьям!

голоса

Рейтинг статьи