Синтаксис оператора alter table - статті

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

ALTER # 91; IGNORE # 93; TABLE tbl_name
alter_specification # 91 ;. alter_specification # 93 ;.

alter_specification:
table_option.
| ADD # 91; COLUMN # 93; col_name column_definition
# 91; FIRST | AFTER col_name # 93;
| ADD # 91; COLUMN # 93; # 40; col_name column_definition. # 41;
| ADD # 123; INDEX | KEY # 125; # 91; index_name # 93;
# 91; index_type # 93; # 40; index_col_name. # 41; # 91; index_type # 93;
| ADD # 91; CONSTRAINT # 91; symbol # 93; # 93; PRIMARY KEY
# 91; index_type # 93; # 40; index_col_name. # 41; # 91; index_type # 93;
| ADD # 91; CONSTRAINT # 91; symbol # 93; # 93;
UNIQUE # 91; INDEX | KEY # 93; # 91; index_name # 93;
# 91; index_type # 93; # 40; index_col_name. # 41; # 91; index_type # 93;
| ADD # 91; FULLTEXT | SPATIAL # 93; # 91; INDEX | KEY # 93; # 91; index_name # 93;
# 40; index_col_name. # 41; # 91; index_type # 93;
| ADD # 91; CONSTRAINT # 91; symbol # 93; # 93;
FOREIGN KEY # 91; index_name # 93; # 40; index_col_name. # 41;
reference_definition
| ALTER # 91; COLUMN # 93; col_name # 123; SET DEFAULT literal | DROP DEFAULT # 125;
| CHANGE # 91; COLUMN # 93; old_col_name new_col_name column_definition
# 91; FIRST | AFTER col_name # 93;
| MODIFY # 91; COLUMN # 93; col_name column_definition
# 91; FIRST | AFTER col_name # 93;
| DROP # 91; COLUMN # 93; col_name
| DROP PRIMARY KEY
| DROP # 123; INDEX | KEY # 125; index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME # 91; TO # 93; new_tbl_name
| ORDER BY col_name # 91 ;. col_name # 93 ;.
| CONVERT TO CHARACTER SET charset_name # 91; COLLATE collation_name # 93;
| # 91; DEFAULT # 93; CHARACTER SET # 91; = # 93; charset_name # 91; COLLATE # 91; = # 93; collation_name # 93;
| DISCARD TABLESPACE
| IMPORT TABLESPACE

index_col_name:
col_name # 91; # 40; length # 41; # 93; # 91; ASC | DESC # 93;

index_type:
USING # 123; BTREE | HASH | RTREE # 125;

Синтаксис оператора ALTER TABLE в багатьох випадках подібний до синтаксису CREATE TABLE. Для отримання докладної інформації див. Розділ 11.1.5, "CREATE TABLE 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 t1 ENGINE = InnoDB;

Успішність спроби змінити механізм зберігання таблиці буде залежати від того, чи доступний призначається механізм зберігання і встанов чи режим SQL NO_ENGINE_SUBSTITUTION. як описано в розділі 5.1.7, "Server SQL Modes".
Починаючи з версії MySQL 5.0.23, для запобігання ненавмисної втрати даних оператор ALTER TABLE не може бути використаний для зміни механізму зберігання таблиці на MERGE або BLACKHOLE.
Для зміни значення лічильника AUTO_INCREMENT. використовуваного для нумерації нових рядків, виконайте такий вираз:

ALTER TABLE t2 AUTO_INCREMENT = value;

Не можна встановити значення лічильника менше або дорівнює максимальному значенню первинного ключа серед даних, що містяться в таблиці. Якщо для MyISAM -таблиця встановлюється значення менше або дорівнює поточному максимального значення в AUTO_INCREMENT колонці, то встановлене значення буде на одиницю перевершувати поточний максимальне значення. Для InnoDB -таблиця можна використовувати оператор ALTER TABLE. AUTO_INCREMENT = value. починаючи з версії MySQL 5.0.3, але якщо встановлюється значення менше, ніж поточний максимальне значення в колонці, то повідомлення про помилку не виникне і поточне значення лічильника залишиться незмінним.

Можна використовувати декілька конструкцій ADD. ALTER. DROP і CHANGE в одному операторі ALTER TABLE. розділяючи їх комою. Це розширення MySQL до стандарту SQL, який дозволяє тільки одну конструкцію в операторі ALTER TABLE. Наприклад, для видалення декількох колонок однією командою, використовуйте такий вираз:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

Конструкції CHANGE col_name. DROP col_name і DROP INDEX - розширення MySQL до стандарту SQL.

Ключове слово MODIFY - розширення Oracle оператора ALTER TABLE.

Ключове слово COLUMN необов'язково і може бути опущено.

У частині column_definition для конструкцій ADD і CHANGE використовується той же синтаксис, що і для оператора CREATE TABLE. Див. Розділ 12.1.10, "CREATE TABLE Syntax".

Перейменувати колонку можна, використовуючи конструкцію CHANGE old_col_name new_col_name column_definition. Для цього потрібно вказати старе і нове ім'я колонки і її поточні властивості. Наприклад, для перейменування INTEGER колонки з a в b використовуйте такий вираз:

ALTER TABLE t1 CHANGE a b INTEGER;

Якщо потрібно змінити тільки тип колонки, синтаксис конструкції CHANGE все одно вимагає старого і нового імен колонки, незважаючи на те, що вони збігаються. наприклад:

ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

Для зміни тільки типу колонки без перейменування можна використовувати конструкцію MODIFY.

ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

Якщо при використанні операцій CHANGE або MODIFY зменшується довжина даних для колонки, що має індекс, і вийшла довжина менше, ніж довжина індексу, MySQL автоматично зменшує його розмір.

Якщо за допомогою операцій CHANGE або MODIFY змінюється тип даних, MySQL намагається перетворити знаходяться в колонці величини до нового типу даних.

Зверніть увагу
Таке перетворення може призвести до спотворення даних. Наприклад, при зменшенні довжини для строкової колонки дані можуть бути обрізані. Для запобігання операцій по перетворенню даних, результатом яких стане їх втрата, необхідно включити SQL-режим "strict" до використання оператора ALTER TABLE (див. Розділ 5.1.6, "SQL Modes").

Зверніть увагу
Механізм зберігання InnoDB ігнорує використання конструкції REFERENCES в частині визначення колонки. Допускається використовувати конструкцію REFERENCES тільки при визначенні зовнішнього ключа.

InnoDB підтримує використання оператора ALTER TABLE для видалення зовнішніх ключів:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Для отримання докладної інформації див. Розділ 13.2.4.4, "FOREIGN KEY Constraints".

Не можна додавати і видаляти зовнішні ключі одним оператором ALTER TABLE. Для цього потрібно використовувати окремі вирази.

Для таблиць InnoDB. створених з власним табличним простором в .ibd файлі, цей файл може бути відключений і імпортовано. Для відключення .ibd файлу використовуйте такий вираз:

ALTER TABLE tbl_name DISCARD TABLESPACE;

Ця операція видаляє поточний .ibd файл, тому будьте впевнені, що ви попередньо зробили бекап. Спроба завантажити таблиці при відключеному файлі табличного простору призведе до помилки.
Для імпорту в таблицю бекап файлу .ibd. скопіюйте його в директорію бази даних і виконайте такий вираз:

ALTER TABLE tbl_name IMPORT TABLESPACE;

Див. Розділ 13.2.2.1, "Using Per-Table Tablespaces".

Що знаходиться в черзі на виконання оператор INSERT DELAYED скасовується, якщо оператор ALTER TABLE змінює структуру таблиці.

Для зміни кодування за замовчуванням таблиці і всіх строкових колонок (CHAR. VARCHAR. TEXT) використовуйте такий вираз:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Для колонок з типом даних VARCHAR або одним з типів сімейства TEXT операція CONVERT TO CHARACTER SET змінить тип даних таким чином, щоб колонка могла містити настільки ж багато символів як і до зміни кодування. Наприклад, в колонці типу TEXT два байта відводяться на зберігання інформації про довжину даних в байтах, яка в цьому випадку не може бути більше 65535. У випадку кодування latin1 для зберігання кожного символу потрібно один байт, таким чином колонка типу TEXT з кодуванням latin1 може містити до 65535 символів. Якщо колонку перекодувати в utf8. то для зберігання одного символу потрібно три байта, а для зберігання максимально можливого для даного типу кількості символів буде потрібно 3 × 65,535 = 196,605 байт. Дана кількість перевищує довжину даних в байтах, яку може містити тип даних TEXT. тому MySQL перетворює тип даних в MEDIUMTEXT. який є найменшим з строкових типів, для якого довжина даних в байтах може дорівнювати 196,605. Подібним чином колонка типу VARCHAR може бути перетворена в MEDIUMTEXT.
Для запобігання зміни типів даних як описано вище не використовуйте операцію CONVERT TO CHARACTER SET. Замість цього використовуйте MODIFY для зміни окремих колонок. наприклад:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR # 40; M # 41; CHARACTER SET utf8;

Якщо ви указиватся CONVERT TO CHARACTER SET binary. колонки типів CHAR. VARCHAR і TEXT перетворюються в відповідні їм бінарні рядкові типи (BINARY. VARBINARY. BLOB). Це означає. що колонки більше не мають кодування і наступні операції CONVERT TO не застосовуються до них.
Якщо в якості charset_name вказано значення DEFAULT. буде використана кодування бази даних.

Зверніть увагу
Операція CONVERT TO перетворює дані з одного кодування в іншу. Це може привести до небажаних наслідків, якщо колонка має одну систему кодування (наприклад, latin1), а дані реально мають деяку іншу систему кодування, несумісну з першої (наприклад, utf8). В цьому випадку для кожної подібної колонки необхідно виконати наступні команди:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

Причина таких дій полягає в тому, що при перетворенні даних в або з бінарного типу, перетворення між кодуваннями не відбувається.

Для зміни тільки кодування за замовчуванням таблиці використовуйте такий вираз:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

Ключове слово DEFAULT необов'язково. Кодування по умочанію таблиці визначає кодування, яка буде використана при подальшому додаванні колонки без явної вказівки її кодування (наприклад, за допомогою оператора ALTER TABLE. ADD column).

використовуючи mysql_info # 40; # 41; C API функції, ви можете визначити скільки рядків було скопійовано і (в разі використання ключового слова IGNORE) скільки рядків було видалено внаслідок дублювання унікального ключа. Див. Розділ 20.9.3.35, "mysql_info ()".

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

CREATE TABLE t1 # 40; a INTEGER, b CHAR # 40; 10 # 41; # 41; ;

Для перейменування таблиці з t1 в t2:

ALTER TABLE t1 RENAME t2;

Для зміни колонки a з INTEGER на TINYINT NOT NULL (без перейменування) і зміни колонки b з CHAR # 40; 10 # 41; на CHAR # 40; 20 # 41 ;. одночасно перейменуємо її з b в c:

ALTER TABLE t2 MODIFY a TINYINT NOT NULL. CHANGE b c CHAR # 40; 20 # 41; ;

Щоб додати новий колонки d типу TIMESTAMP:

ALTER TABLE t2 ADD d TIMESTAMP;

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

ALTER TABLE t2 ADD INDEX # 40; d # 41 ;. ADD UNIQUE # 40; a # 41; ;

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

ALTER TABLE t2 DROP COLUMN c;

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

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY # 40; c # 41; ;

Зверніть увагу, що ми зробили колонку 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 колонку:

CREATE TABLE t2 # 40; id INT AUTO_INCREMENT PRIMARY KEY # 41;
SELECT * FROM t1 ORDER BY col1, col2;

Тут передбачається, що таблиця t1 має колонки col1 і col2.

Наступні оператори також створюють нову таблицю t2 ідентичну t1. але має AUTO_INCREMENT колонку:

CREATE TABLE t2 LIKE t1;
ALTER TABLE T2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

Зверніть увагу
Для гарантії однакового порядку рядків на майстра і slave все колонки таблиці t1 повинні бути перераховані в частині ORDER BY.

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

DROP t1;
ALTER TABLE t2 RENAME t1;