6.5.4. Синтаксис оператора ALTER TABLE
Якщо оператор ALTER TABLE використовується для зміни визначення типу стовпця, але DESCRIBE tbl_name показує, що стовпець не змінився, то, можливо, MySQL ігнорує дану модифікацію по одній з причин, описаних в розділі Розділ 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 Розділ 6.5.5, «Синтаксис оператора RENAME TABLE».
У вираженні create_definition для ADD і CHANGE використовується той же синтаксис, що і для CREATE TABLE. Слід враховувати, що цей синтаксис включає ім'я стовпчика, а не просто його тип. See Розділ 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 призначає значення за замовчуванням так, як описано в розділі Розділ 6.5.3, «Синтаксис оператора CREATE TABLE».
Опція DROP INDEX видаляє індекс. Це є розширенням MySQL по відношенню до ANSI SQL92. See Розділ 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) - скільки записів було видалено через дублювання значень унікальних ключів.
Нижче наводяться приклади, що показують деякі випадки вживання команди 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 Розділ 5.5.6, «Синтаксис команди SET».
Якщо стовпець AUTO_INCREMENT для таблиць MyISAM. не змінюється, то номер послідовності залишається колишнім. При видаленні стовпчика AUTO_INCREMENT і подальшому додаванні іншого шпальти AUTO_INCREMENT номера будуть починатися знову з 1.