ALTER TABLE — изменение таблицы в SQL
Команда ALTER TABLE применяется в SQL при добавлении, удалении либо модификации колонки в существующей таблице. В этой статье будет рассмотрен синтаксис и примеры использования ALTER TABLE на примере MS SQL Server.
SQL-оператор ALTER TABLE способен менять определение таблицы несколькими способами: • добавлением/переопределением/удалением столбца (column); • модифицированием характеристик памяти; • включением, выключением либо удалением ограничения целостности.
При этом пользователю нужно обладать системной привилегией ALTER ANY TABLE либо таблица должна находиться в схеме пользователя.
Меняя типы данных существующих columns либо добавляя их в БД-таблицу, следует соблюдать некоторые условия. Принято, что увеличение есть хорошо, а уменьшение — не очень. Существует ряд допустимых увеличений: • добавляем новые столбцы в таблицу; • увеличиваем размер столбца CHAR либо VARCHAR2; • увеличиваем размер столбца NUMBER.
Нередко перед внесением изменений следует удостовериться, что в соответствующих columns все значения — это NULL-значения. Если выполняется операция над столбцами, которые содержат данные, следует найти либо создать область временного хранения данных. Можно создать таблицу посредством CREATE TABLE AS SELECT, где извлекаются данные из первичного ключа и изменяемых columns. Существует ряд допустимых изменений: • уменьшаем размер столбца NUMBER (лишь при наличии пустого column для всех строк); • уменьшаем размер столбца CHAR либо VARCHAR2 (лишь при наличии пустого column для всех строк); • меняем тип данных столбца (аналогично, что и в первых двух пунктах).
При добавлении column с ограничением NOT NULL, администратор баз данных либо разработчик обязан учесть некоторые обстоятельства. Вначале следует создать столбец без ограничения, потом ввести значения во все строки. Далее, когда значения column будут уже не NULL, к нему можно будет применить ограничение NOT NULL. Но если column с ограничением NOT NULL хочет добавить юзер, то вернётся сообщение об ошибке, судя по которому таблица должна быть либо пустой, либо содержать в столбце значения для каждой имеющейся строки (после наложения на column NOT NULL-ограничения, в нём не смогут присутствовать значения NULL ни в одной из имеющихся строк).
Синтаксис ALTER TABLE на примере MS SQL Server
Рассмотрим общий формальный синтаксис на примере SQL Server от Microsoft:
Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.
Добавляем новый столбец
Для примера добавим новый column Address в таблицу Customers:
В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:
Тогда, если в таблице существуют данные, для них для column Address добавится значение «Неизвестно».
Удаляем столбец
Теперь можно удалить column Address:
Меняем тип
Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).
Добавляем ограничения CHECK
Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.
При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:
По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.
Добавляем внешний ключ
Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:
Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):
Добавляем первичный ключ
Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:
Добавляем ограничения с именами
Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):
Удаляем ограничения
Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:
Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.
Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название «FK_Orders_To_Customers». Здесь для удаления внешнего подойдёт такое выражение:
Хотите знать про SQL Server больше? Добро пожаловать на курс «MS SQL Server Developer» в OTUS! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:
SQL Изменить таблицу
SQL ALTER TABLE
Инструкция ALTER TABLE используется для добавления, удаления или изменения столбцов в существующей таблице.
Инструкция ALTER TABLE также используется для добавления и удаления различных ограничений на существующую таблицу.
Чтобы добавить столбец в таблицу, используйте следующее Синтаксис:
Пример
Чтобы удалить столбец в таблице, используйте следующую команду синтаксис (обратите внимание, что некоторые системы баз данных не позволяют удалить столбец):
Следующий SQL удаляет столбец «Email» из таблицы «Customers»:
Пример
Чтобы изменить тип данных столбца в таблице, используйте следующие синтаксис:
SQL Сервер / MS Доступ:
My SQL / Оракул (предыдущая версия 10G):
Оракул 10G и более поздние версии:
SQL Пример ALTER TABLE
Посмотрите на таблицу «Persons»:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Теперь мы хотим добавить столбец с именем «DateOfBirth» в таблицу «Persons».
Мы используем следующую инструкцию SQL:
Обратите внимание, что новый столбец «DateOfBirth» имеет тип date и будет содержать дату. Тип данных указывает, какой тип данных может содержать столбец. Для получения полного описания всех типов данных, доступных в MS Access, MySQL и SQL Server, перейдите к нашему полному Справочнику типов данных.
Таблица «Persons» теперь будет выглядеть так:
| ID | LastName | FirstName | Address | City | DateOfBirth |
|---|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes | |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes | |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Пример изменения типа данных
Теперь мы хотим изменить тип данных столбца с именем «DateOfBirth» в таблице «Persons».
Мы используем следующую инструкцию SQL:
Обратите внимание, что столбец «DateOfBirth» теперь имеет тип year и будет содержать year в двух или четырехзначном формате.
Пример DROP COLUMN
В первую очередь, мы хотим удалить столбец с именем «DateOfBirth» в таблице «Persons».
Мы используем следующую инструкцию SQL:
Таблица «Persons» теперь будет выглядеть так:
CHANGETABLE (Transact-SQL)
Возвращает информацию отслеживания изменений для таблицы. Можно использовать эту инструкцию для возврата всех изменений таблицы или информации отслеживания изменений для конкретной строки.
Синтаксические обозначения в Transact-SQL
Синтаксис
Аргументы
table_name
Пользовательская таблица, в которой регистрируются отслеживаемые изменения. Отслеживание изменений необходимо включить в таблице. Может использоваться имя таблицы, состоящее из одной, двух, трех или четырех частей. Имя таблицы может быть синонимом таблицы.
ВЕРСИЯ table_name, < primary_key_values >
Возвращает информацию о последнем изменении указанной строки. Значения первичного ключа должны идентифицировать строку. primary_key_values идентифицирует столбцы первичного ключа и задает значения. Имена первичных ключевых столбцов могут быть указаны в любом порядке.
table_name
Пользовательская таблица для получения информации отслеживания изменений. Отслеживание изменений необходимо включить в таблице. Может использоваться имя таблицы, состоящее из одной, двух, трех или четырех частей. Имя таблицы может быть синонимом таблицы.
column_name
Указывает одно или несколько имен первичных ключевых столбцов. Несколько имен столбцов могут быть указаны в любом порядке.
FORCESEEK
Применимо к: SQL Server (Начиная с SQL Server 2016 (13.x); SP2 CU16, SQL Server 2017 (14.x); CU24 и SQL Server 2019 (15.x) CU11), База данных SQL Azure и Управляемый экземпляр SQL Azure
Необязательный параметр, который заставляет операцию поиска использоваться для доступа к table_name. В некоторых случаях, когда изменилось очень мало строк, операция просмотра может использоваться для доступа к table_name. Если операция просмотра создает ошибку производительности, используйте FORCESEEK параметр.
ТЕХ table_alias [(column_alias [. n ])]
Задает имена для результатов, возвращаемых функцией CHANGETABLE.
table_alias
Псевдоним таблицы, возвращаемый функцией CHANGETABLE. table_alias является обязательным и должен быть допустимым идентификатором.
column_alias
Необязательный псевдоним столбца или список псевдонимов столбцов, возвращаемых функцией CHANGETABLE. Обеспечивает возможность настройки имен столбцов в случае, если в результатах присутствуют повторяющиеся имена.
Типы возвращаемых данных
table
Возвращаемые значения
CHANGETABLE CHANGES
При указании ключевого слова CHANGES возвращается ноль или несколько строк, содержащих следующие столбцы.
| Имя столбца | Тип данных | Описание |
|---|---|---|
| SYS_CHANGE_VERSION | bigint | Значение версии, связанное с последним изменением в строке |
| SYS_CHANGE_CREATION_VERSION | bigint | Значения версии, связанные с последней операцией вставки. |
| SYS_CHANGE_OPERATION | nchar (1) | Задает тип изменения: U = обновление I = вставить D = удалить |
| SYS_CHANGE_COLUMNS | varbinary (4100) | Содержит список столбцов, измененных после last_sync_version (базовой версии). Обратите внимание, что вычисленные столбцы никогда не перечисляются как измененные. Принимает значение NULL, если выполняется любое из следующих условий. Отслеживание изменений столбцов не включено. Операция представляет собой операцию вставки или удаления. Все ключевые столбцы, не являющиеся первичными, были обновлены одной операцией. Это двоичное значение не следует интерпретировать непосредственно. Вместо этого, чтобы интерпретировать его, используйте CHANGE_TRACKING_IS_COLUMN_IN_MASK (). |
| SYS_CHANGE_CONTEXT | varbinary(128) | Изменение контекстной информации, которую можно дополнительно указать с помощью предложения with как части инструкции INSERT, UPDATE или DELETE. |
| Такие же, как столбцы таблицы пользователя | Значения первичного ключа для отслеживаемой таблицы. Эти значения уникально идентифицируют каждую строку в таблице пользователя. |
CHANGETABLE VERSION
При указании значения VERSION возвращается одна строка, содержащая следующие столбцы.
| Имя столбца | Тип данных | Описание |
|---|---|---|
| SYS_CHANGE_VERSION | bigint | Текущее значение версии изменений, связанное со строкой. Принимает значение NULL, если изменение не производилось в течение периода времени, превышающего срок хранения данных отслеживания изменений, либо если строка не изменялась с момента включения отслеживания изменений. |
| SYS_CHANGE_CONTEXT | varbinary(128) | Измените контекст, который указывается дополнительно с использованием предложения WITH как часть инструкции INSERT, UPDATE или DELETE. |
| Такие же, как столбцы таблицы пользователя | Значения первичного ключа для отслеживаемой таблицы. Эти значения уникально идентифицируют каждую строку в таблице пользователя. |
Комментарии
Функция CHANGETABLE обычно используется в предложении FROM запроса, как если бы она была таблицей.
CHANGETABLE(CHANGES. )
Чтобы получить данные для новых или измененных строк, соедините результирующий набор с пользовательской таблицей с помощью первичных ключевых столбцов. Для каждой строки в пользовательской таблице, которая была изменена, возвращается только одна строка, даже если с момента last_sync_version значения было внесено несколько изменений в одну строку.
Изменения первичного ключевого столбца никогда не помечаются как обновления. Если значение первичного ключа изменяется, это изменение рассматривается как удаление прежнего значения и вставка нового.
Если удалить, а затем вставить строку, содержащую тот же первичный ключ, такое изменение рассматривается как обновление для всех столбцов в строке.
В целом в пользовательских таблицах отслеживаются все операции вставки, обновления и удаления данных, включая инструкцию MERGE.
Не отслеживаются следующие операции, затрагивающие данные в пользовательских таблицах.
CHANGETABLE(VERSION. )
Если указан несуществующий первичный ключ, возвращается пустой результирующий набор.
Значение SYS_CHANGE_VERSION может быть равно null, если изменение не было выполнено дольше периода хранения (например, очистка удаляет сведения об изменениях) или строка никогда не была изменена, так как для таблицы включено отслеживание изменений.
Разрешения
SELECT VIEW CHANGE TRACKING Для получения сведений об отслеживании изменений требуется разрешение на столбцы первичного ключа и разрешение для таблицы, заданной параметром .
Примеры
A. Возврат строк для начальной синхронизации данных
В следующем примере показано, как получить данные для исходной синхронизации данных таблицы. Запрос возвращает все данные строк и их связанные версии. Можно затем вставить или добавить эти данные в систему, где будут содержаться синхронизированные данные.
Б. Список всех изменений, внесенных после определенной версии
В. Получение всех измененных данных для синхронизации
В следующем примере показано, как можно получить все измененные данные. Этим запросом данные отслеживания изменений объединяются с пользовательской таблицей таким образом, чтобы был выполнен возврат данных пользовательской таблицы. Ключевое слово LEFT OUTER JOIN используется для возврата строки для удаленных строк.
Г. Выявление конфликтов с помощью инструкции CHANGETABLE(VERSION. )
Change sql что это
ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment.
Following the table name, specify the alterations to be made. If none are given, ALTER TABLE does nothing.
The word COLUMN is optional and can be omitted, except for RENAME COLUMN (to distinguish a column-renaming operation from the RENAME table-renaming operation).
There are several additional aspects to the ALTER TABLE statement, described under the following topics in this section:
Table Options
For descriptions of all table options, see Section 13.1.20, “CREATE TABLE Statement”. However, ALTER TABLE ignores DATA DIRECTORY and INDEX DIRECTORY when given as table options. ALTER TABLE permits them only as partitioning options, and requires that you have the FILE privilege.
Use of table options with ALTER TABLE provides a convenient way of altering single table characteristics. For example:
If t1 is currently not an InnoDB table, this statement changes its storage engine to InnoDB :
See Section 15.6.1.5, “Converting Tables from MyISAM to InnoDB” for considerations when switching tables to the InnoDB storage engine.
When you specify an ENGINE clause, ALTER TABLE rebuilds the table. This is true even if the table already has the specified storage engine.
Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “ null ” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 15.11.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.
The outcome of attempting to change the storage engine of a table is affected by whether the desired storage engine is available and the setting of the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.11, “Server SQL Modes”.
To change the InnoDB table to use compressed row-storage format:
The ENCRYPTION clause enables or disables page-level data encryption for an InnoDB table. A keyring plugin must be installed and configured to enable encryption.
If the table_encryption_privilege_check variable is enabled, the TABLE_ENCRYPTION_ADMIN privilege is required to use an ENCRYPTION clause with a setting that differs from the default schema encryption setting.
Prior to MySQL 8.0.16, the ENCRYPTION clause was only supported when altering tables residing in file-per-table tablespaces. As of MySQL 8.0.16, the ENCRYPTION clause is also supported for tables residing in general tablespaces.
For tables that reside in general tablespaces, table and tablespace encryption must match.
Altering table encryption by moving a table to a different tablespace or changing the storage engine is not permitted without explicitly specifying an ENCRYPTION clause.
As of MySQL 8.0.16, specifying an ENCRYPTION clause with a value other than ‘N’ or » is not permitted if the table uses a storage engine that does not support encryption. Previously, the clause was accepted. Attempting to create a table without an ENCRYPTION clause in an encryption-enabled schema using a storage engine that does not support encryption is also not permitted.
To reset the current auto-increment value:
To change the default table character set:
To add (or change) a table comment:
Use ALTER TABLE with the TABLESPACE option to move InnoDB tables between existing general tablespaces, file-per-table tablespaces, and the system tablespace. See Moving Tables Between Tablespaces Using ALTER TABLE.
ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE options (available as of MySQL 8.0.21) are used to specify table, column, and index attributes for primary and secondary storage engines. The options are reserved for future use. Index attributes cannot be altered. An index must be dropped and added back with the desired change, which can be performed in a single ALTER TABLE statement.
Performance and Space Requirements
ALTER TABLE operations are processed using one of the following algorithms:
COPY : Operations are performed on a copy of the original table, and table data is copied from the original table to the new table row by row. Concurrent DML is not permitted.
INPLACE : Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.
INSTANT : Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)
The ALGORITHM clause is optional. If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INSTANT for storage engines and ALTER TABLE clauses that support it. Otherwise, ALGORITHM=INPLACE is used. If ALGORITHM=INPLACE is not supported, ALGORITHM=COPY is used.
Specifying an ALGORITHM clause requires the operation to use the specified algorithm for clauses and storage engines that support it, or fail with an error otherwise. Specifying ALGORITHM=DEFAULT is the same as omitting the ALGORITHM clause.
ALTER TABLE operations that use the COPY algorithm wait for other operations that are modifying the table to complete. After alterations are applied to the table copy, data is copied over, the original table is deleted, and the table copy is renamed to the name of the original table. While the ALTER TABLE operation executes, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table started after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table. The temporary copy of the table is created in the database directory of the original table unless it is a RENAME TO operation that moves the table to a database that resides in a different directory.
The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.
For InnoDB tables, an ALTER TABLE operation that uses the COPY algorithm on a table that resides in a shared tablespace can increase the amount of space used by the tablespace. Such operations require as much additional space as the data in the table plus indexes. For a table residing in a shared tablespace, the additional space used during the operation is not released back to the operating system as it is for a table that resides in a file-per-table tablespace.
For information about space requirements for online DDL operations, see Section 15.12.3, “Online DDL Space Requirements”.
ALTER TABLE operations that support the INPLACE algorithm include:
ALTER TABLE operations supported by the InnoDB online DDL feature. See Section 15.12.1, “Online DDL Operations”.
Renaming a table. MySQL renames files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.36, “RENAME TABLE Statement”.) Privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.
Operations that modify table metadata only. These operations are immediate because the server does not touch table contents. Metadata-only operations include:
Renaming a column. In NDB Cluster 8.0.18 and later, this operation can also be performed online.
Changing the default value of a column (except for NDB tables).
Modifying the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values, as long as the storage size of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.
Changing the definition of a spatial column to remove the SRID attribute. (Adding or changing an SRID attribute does require a rebuild and cannot be done in place because the server must verify that all values have the specified SRID value.)
As of MySQL 8.0.14, changing a column character set, when these conditions apply:
There is no index on the column.
As of MySQL 8.0.14, changing a generated column, when these conditions apply:
For InnoDB tables, statements that modify generated stored columns but do not change their type, expression, or nullability.
For non- InnoDB tables, statements that modify generated stored or virtual columns but do not change their type, expression, or nullability.
An example of such a change is a change to the column comment.
Adding or dropping a secondary index, for InnoDB and NDB tables. See Section 15.12.1, “Online DDL Operations”.
For NDB tables, operations that add and drop indexes on variable-width columns. These operations occur online, without table copying and without blocking concurrent DML actions for most of their duration. See Section 23.6.11, “Online Operations with ALTER TABLE in NDB Cluster”.
Modifying index visibility with an ALTER INDEX operation.
Column modifications of tables containing generated columns that depend on columns with a DEFAULT value if the modified columns are not involved in the generated column expressions. For example, changing the NULL property of a separate column can be done in place without a table rebuild.
ALTER TABLE operations that support the INSTANT algorithm include:
Adding or dropping a virtual column.
Adding or dropping a column default value.
Changing the index type.
The WITHOUT VALIDATION and WITH VALIDATION clauses affect whether ALTER TABLE performs an in-place operation for virtual generated column modifications. See Section 13.1.9.2, “ALTER TABLE and Generated Columns”.
NDB Cluster 8.0 supports online operations using the same ALGORITHM=INPLACE syntax used with the standard MySQL Server. NDB does not support changing a tablespace online; beginning with NDB 8.0.21, it is disallowed. See Section 23.6.11, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.
ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between all partitions, unless LINEAR HASH or LINEAR KEY was used; this is effectively the same as creating a new table, although the ADD or COALESCE operation is performed partition by partition. REORGANIZE operations copy only changed partitions and do not touch unchanged ones.
For MyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.
Concurrency Control
For ALTER TABLE operations that support it, you can use the LOCK clause to control the level of concurrent reads and writes on a table while it is being altered. Specifying a non-default value for this clause enables you to require a certain amount of concurrent access or exclusivity during the alter operation, and halts the operation if the requested degree of locking is not available.
The parameters for the LOCK clause are:
Maximum level of concurrency for the given ALGORITHM clause (if any) and ALTER TABLE operation: Permit concurrent reads and writes if supported. If not, permit concurrent reads if supported. If not, enforce exclusive access.
If supported, permit concurrent reads and writes. Otherwise, an error occurs.
If supported, permit concurrent reads but block writes. Writes are blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. If concurrent reads are not supported, an error occurs.
Enforce exclusive access. This is done even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.
Adding and Dropping Columns
Use ADD to add new columns to a table, and DROP to remove existing columns. DROP col_name is a MySQL extension to standard SQL.
If a table contains only one column, the column cannot be dropped. If what you intend is to remove the table, use the DROP TABLE statement instead.
If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well. If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.
Renaming, Redefining, and Reordering Columns
Can rename a column and change its definition, or both.
Can change a column definition but not its name.
More convenient than CHANGE to change a column definition without renaming it.
Can change a column name but not its definition.
More convenient than CHANGE to rename a column without changing its definition.
ALTER : Used only to change a column default value.
CHANGE is a MySQL extension to standard SQL. MODIFY and RENAME COLUMN are MySQL extensions for Oracle compatibility.
MODIFY is more convenient to change the definition without changing the name because it requires the column name only once:
RENAME COLUMN is more convenient to change the name without changing the definition because it requires only the old and new names:
This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.11, “Server SQL Modes”).
If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.
Indexes that refer to the old column, including invisible indexes and disabled MyISAM indexes.
Foreign keys that refer to the old column.
Generated column and partition expressions that refer to the renamed column. You must use CHANGE to redefine such expressions in the same ALTER TABLE statement as the one that renames the column.
Views and stored programs that refer to the renamed column. You must manually alter the definition of these objects to refer to the new column name.
To reorder columns within a table, use FIRST and AFTER in CHANGE or MODIFY operations.
Primary Keys and Indexes
DROP PRIMARY KEY drops the primary key. If there is no primary key, an error occurs. For information about the performance characteristics of primary keys, especially for InnoDB tables, see Section 8.3.2, “Primary Key Optimization”.
If the sql_require_primary_key system variable is enabled, attempting to drop a primary key produces an error.
If you add a UNIQUE INDEX or PRIMARY KEY to a table, MySQL stores it before any nonunique index to permit detection of duplicate keys as early as possible.
index_option values specify additional options for an index. USING is one such option. For details about permissible index_option values, see Section 13.1.15, “CREATE INDEX Statement”.
If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE ). This should make ALTER TABLE much faster when you have many indexes.
While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.
After an ALTER TABLE statement, it may be necessary to run ANALYZE TABLE to update index cardinality information. See Section 13.7.7.22, “SHOW INDEX Statement”.
The ALTER INDEX operation permits an index to be made visible or invisible. An invisible index is not used by the optimizer. Modification of index visibility applies to indexes other than primary keys (either explicit or implicit). This feature is storage engine neutral (supported for any engine). For more information, see Section 8.3.12, “Invisible Indexes”.
Foreign Keys and Other Constraints
MySQL silently ignores inline REFERENCES specifications, where the references are defined as part of the column specification. MySQL accepts only REFERENCES clauses defined as part of a separate FOREIGN KEY specification.
MySQL Server and NDB Cluster both support the use of ALTER TABLE to drop foreign keys:
Changes to the data type of foreign key columns that may be unsafe. For example, changing VARCHAR(20) to VARCHAR(30) is permitted, but changing it to VARCHAR(1024) is not because that alters the number of length bytes required to store individual values.
Changing a NULL column to NOT NULL in non-strict mode is prohibited to prevent converting NULL values to default non- NULL values, for which there are no corresponding values in the referenced table. The operation is permitted in strict mode, but an error is returned if any such conversion is required.
ALTER TABLE tbl_name RENAME new_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string “ tbl_name _ibfk_ ” to reflect the new table name. InnoDB interprets foreign key constraint names that begin with the string “ tbl_name _ibfk_ ” as internally generated names.
Prior to MySQL 8.0.16, ALTER TABLE permits only the following limited version of CHECK constraint-adding syntax, which is parsed and ignored:
As of MySQL 8.0.16, ALTER TABLE permits CHECK constraints for existing tables to be added, dropped, or altered:
Add a new CHECK constraint:
Drop an existing CHECK constraint named symbol :
Alter whether an existing CHECK constraint named symbol is enforced:
The DROP CHECK and ALTER CHECK clauses are MySQL extensions to standard SQL.
As of MySQL 8.0.19, ALTER TABLE permits more general (and SQL standard) syntax for dropping and altering existing constraints of any type, where the constraint type is determined from the constraint name:
Drop an existing constraint named symbol :
If the sql_require_primary_key system variable is enabled, attempting to drop a primary key produces an error.
Alter whether an existing constraint named symbol is enforced:
Only CHECK constraints can be altered to be unenforced. All other constraint types are always enforced.
The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema. Consequently, names for each type of constraint must be unique per schema, but constraints of different types can have the same name. When multiple constraints have the same name, DROP CONSTRAINT and ADD CONSTRAINT are ambiguous and an error occurs. In such cases, constraint-specific syntax must be used to modify the constraint. For example, use DROP PRIMARY KEY or DROP FOREIGN KEY to drop a primary key or foreign key.
If a table alteration causes a violation of an enforced CHECK constraint, an error occurs and the table is not modified. Examples of operations for which an error occurs:
Attempts to add the AUTO_INCREMENT attribute to a column that is used in a CHECK constraint.
Attempts to add an enforced CHECK constraint or enforce a nonenforced CHECK constraint for which existing rows violate the constraint condition.
Attempts to modify, rename, or drop a column that is used in a CHECK constraint, unless that constraint is also dropped in the same statement. Exception: If a CHECK constraint refers only to a single column, dropping the column automatically drops the constraint.
ALTER TABLE tbl_name RENAME new_tbl_name changes internally generated and user-defined CHECK constraint names that begin with the string “ tbl_name _chk_ ” to reflect the new table name. MySQL interprets CHECK constraint names that begin with the string “ tbl_name _chk_ ” as internally generated names.
Changing the Character Set
The statement also changes the collation of all character columns. If you specify no COLLATE clause to indicate which collation to use, the statement uses default collation for the character set. If this collation is inappropriate for the intended table use (for example, if it would change from a case-sensitive collation to a case-insensitive collation), specify a collation explicitly.
If charset_name is DEFAULT in a CONVERT TO CHARACTER SET operation, the character set named by the character_set_database system variable is used.
The CONVERT TO operation converts column values between the original and named character sets. This is not what you want if you have a column in one character set (like latin1 ) but the stored values actually use some other, incompatible character set (like utf8 ). In this case, you have to do the following for each such column:
The reason this works is that there is no conversion when you convert to or from BLOB columns.
To change only the default character set for a table, use this statement:
Importing InnoDB Tables
An InnoDB table created in its own file-per-table tablespace can be imported from a backup or from another MySQL server instance using DISCARD TABLEPACE and IMPORT TABLESPACE clauses. See Section 15.6.1.3, “Importing InnoDB Tables”.
Row Order for MyISAM Tables
ORDER BY enables you to create the new table with the rows in a specific order. This option is useful primarily when you know that you query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.
The table does not remain in the specified order after inserts and deletes.
ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are permitted as sort criteria; arbitrary expressions are not permitted. This clause should be given last after any other clauses.
ORDER BY does not make sense for InnoDB tables because InnoDB always orders table rows according to the clustered index.
Partitioning Options
partition_options signifies options that can be used with partitioned tables for repartitioning, to add, drop, discard, import, merge, and split partitions, and to perform partitioning maintenance.
