Как изменить структуру таблицы mysql

Изменение таблиц и столбцов

Последнее обновление: 11.05.2018

Если таблица уже была ранее создана, и ее необходимо изменить, то для этого применяется команда ALTER TABLE. Ее
сокращенный формальный синтаксис:

ALTER TABLE название_таблицы 
{ ADD название_столбца тип_данных_столбца [атрибуты_столбца] | 
  DROP COLUMN название_столбца |
  MODIFY COLUMN название_столбца тип_данных_столбца [атрибуты_столбца] |
  ALTER COLUMN название_столбца SET DEFAULT значение_по_умолчанию |
  ADD [CONSTRAINT] определение_ограничения |
  DROP [CONSTRAINT] имя_ограничения}

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

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

Добавим в таблицу Customers новый столбец Address:

ALTER TABLE Customers
ADD Address VARCHAR(50) NULL;

В данном случае столбец Address имеет тип VARCHAR и для него определен атрибут NULL.

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

Удалим столбец Address из таблицы Customers:

ALTER TABLE Customers
DROP COLUMN Address;

Изменение значения по умолчанию

Установим в таблице Customers для столбца Age значение по умолчанию 22:

ALTER TABLE Customers
ALTER COLUMN Age SET DEFAULT 22;

Изменение типа столбца

Изменим в таблице Customers тип данных у столбца FirstName на CHAR(100) и установим для него атрибут NULL:

ALTER TABLE Customers
MODIFY COLUMN FirstName CHAR(100) NULL;

Добавление и удаление внешнего ключа

Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:

CREATE TABLE Customers
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Age INT, 
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL
);
CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date
);

Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:

ALTER TABLE Orders
ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

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

ALTER TABLE Orders
ADD CONSTRAINT orders_customers_fk 
FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

В данном случае ограничение внешнего ключа называется orders_customers_fk. Затем по этому имени мы можем удалить ограничение:

ALTER TABLE Orders
DROP FOREIGN KEY orders_customers_fk;

Добавление и удаление первичного ключа

Добавим в таблицу Products первичный ключ:

CREATE TABLE Products
(
    Id INT,
    Model VARCHAR(20)
);

ALTER TABLE Products
ADD PRIMARY KEY (Id);

Теперь удалим первичный ключ:

ALTER TABLE Products
DROP PRIMARY KEY;

Mysql alter add/drop index (добавление и удаление индексов):

ALTER TABLE sites 
  DROP INDEX keyname;

// эквивалентно DROP INDEX
ALTER TABLE sites 
  DROP KEY keyname;

ALTER TABLE categories
  DROP PRIMARY KEY,
  ADD  PRIMARY KEY (`site_id`,`category_id`);

Редактирование структуры полей в mysql:

ALTER TABLE sites change `language` `language` varchar(5) DEFAULT NULL;

Удалить поля в mysql:

ALTER TABLE sites
  DROP `field1`,
  DROP `field2`;

Добавление полей в mysql:

ALTER TABLE sites
  ADD  `added_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ADD  `updated_at` timestamp NULL DEFAULT NULL,
  ADD  `adult` tinyint(1) NOT NULL DEFAULT '0' AFTER domain_name;

Можно комбинировать добавление и удаления полей одним alter запросом:

ALTER TABLE sites
  DROP `google_pr_sort`,
  ADD  `added_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ADD  `updated_at` timestamp NULL DEFAULT NULL,
  ADD  `adult` tinyint(1) NOT NULL DEFAULT '0' AFTER domain_name;

Добавить primary key (PK) в mysql таблицу

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

Запрос удаления первично ключа:

ALTER TABLE sites DROP PRIMARY KEY;

Пример добавления PK:

ALTER TABLE sites ADD `id` int(10) unsigned PRIMARY KEY AUTO_INCREMENT FIRST;

insert+select синтаксис запроса

INSERT INTO feedreader__moderate_feeds (feed_id) (SELECT id FROM feeds);

Видно, что опускается слово VALUES и в скобках пишется sql-запрос.

Узнать структуру таблицы в mysql

SHOW CREATE TABLE sites;
DESCRIBE sites;

Запрос SHOW CREATE TABLE показывает запрос для создания таблицы, пример

mysql> show create table sites;

CREATE TABLE `sites` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT COMMENT '#',
  `domain_name` varchar(100) DEFAULT NULL,
  `title` text,
  `keywords` text,
  `description` text,
  `is_english` tinyint(2) NOT NULL DEFAULT '0',
  `alexa_position` int(10) DEFAULT NULL,
  `lvl` int(3) unsigned NOT NULL,
  `added_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `enqueued_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `google_pr_sort` smallint(2) unsigned DEFAULT NULL,
  `adult` tinyint(1) NOT NULL DEFAULT '0',
  `fixed_adult` tinyint(1) DEFAULT NULL,
  `ip_whois_id` int(11) unsigned DEFAULT NULL,
  `whois_id` int(11) DEFAULT NULL,
  `disable_parsing` tinyint(1) NOT NULL DEFAULT '0',
  `is_malicious` tinyint(1) NOT NULL DEFAULT '0',
  `is_dead` tinyint(1) NOT NULL DEFAULT '0',
  `language` varchar(2) DEFAULT NULL,
  `imported_adult` tinyint(1) DEFAULT NULL,
  `parsed_domain` varchar(200) DEFAULT NULL,
  `is_popular` tinyint(1) NOT NULL DEFAULT '0',
  `not_prioritized_parse` tinyint(1) NOT NULL DEFAULT '1',
  `frame_deny` tinyint(1) NOT NULL DEFAULT '0',
  `is_suspicious` tinyint(1) NOT NULL DEFAULT '0',
  `main_feed_id` int(11) DEFAULT NULL,
  `daily_pageviews` bigint(21) unsigned NOT NULL DEFAULT '0',
  `daily_visitors` bigint(21) unsigned NOT NULL DEFAULT '0',
  `domain_zone` varchar(30) DEFAULT NULL,
  `owner_id` int(10) unsigned DEFAULT NULL,
  `hoster_id` int(10) unsigned DEFAULT NULL,
  `is_show_ads` tinyint(1) DEFAULT NULL,
  `authority` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_domain_name` (`domain_name`),
  KEY `ip_whois_id` (`ip_whois_id`)
) ENGINE=MyISAM AUTO_INCREMENT=59027066 DEFAULT CHARSET=utf8

Задать AI в mysql (yстановить начальное значение)

mysql изменить AI (значение auto_increment):

ALTER TABLE tbl AUTO_INCREMENT = 5;

Дата добавления:
9 лет назад

Просмотров: 1.4k

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

Содержание:

Синтаксис многих допустимых изменений подобен инструкциям CREATE TABLE. По этому, в материале будут рассмотрены только особенности и различие синтаксиса ALTER TABLE.

• Синтаксис инструкции ALTER TABLE;
  • Описание параметров, используемых ALTER TABLE;
  • Комментарии к синтаксису ALTER TABLE;
• Добавление/изменение/удаление столбцов таблицы;
• Добавление/изменение/удаление PRIMARY KEY и INDEX;
• Добавление/изменение/удаление внешнего FOREIGN KEY;
• Добавление/изменение/удаление проверки CHECK;
• Изменение набора символов (кодировки);
• Примеры практического использования ALTER TABLE.

Синтаксис инструкции ALTER TABLE.

ALTER TABLE tbl_name
  -- изменение свойств таблицы 
    RENAME [TO | AS] new_tbl_name
    CHARACTER SET=charset_name [COLLATE=collation_name]
    CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
    -- добавление/изменение/удаление столбцов 
    -- изменяемой таблицы  
    ADD COLUMN col_name column_definition [FIRST | AFTER col_name]
    CHANGE COLUMN old_col_name new_col_name column_definition 
          [FIRST | AFTER col_name]
    MODIFY COLUMN col_name column_definition
          [FIRST | AFTER col_name]
    ALTER COLUMN col_name SET DEFAULT literal | (expr) 
    ALTER COLUMN col_name DROP DEFAULT
    RENAME COLUMN old_col_name TO new_col_name
    DROP COLUMN col_name
    -- добавление/изменение/удаление индексированных
    -- столбцов изменяемой таблицы  
    ADD INDEX [index_name] (col_name1[, col_name2, ... ]) [USING BTREE | HASH]
    ADD UNIQUE KEY [index_name] [USING BTREE | HASH]
    ADD FULLTEXT | SPATIAL [INDEX] [index_name]
    RENAME INDEX old_index_name TO new_index_name
    DROP INDEX index_name
    ADD PRIMARY KEY reference_definition
    DROP PRIMARY KEY
    ADD [CONSTRAINT index_name] FOREIGN KEY reference_definition
    DROP FOREIGN KEY index_name
    -- добавление/изменение/удаление индексированных
    -- столбцов изменяемой таблицы  
    ADD [CONSTRAINT check_name] CHECK (expr) [[NOT] ENFORCED]
    ALTER CHECK check_name [NOT] ENFORCED
    DROP CHECK check_name

Описание параметров, используемых ALTER TABLE.

• tbl_name: имя изменяемой таблицы
• new_tbl_name: новое уникальное имя изменяемой таблицы
• charset_name: имя кодировки, в которой будут храниться символьные и текстовые типы данных столбцов таблицы. Список доступных кодировок можно посмотреть командой SHOW CHARACTER SET;. Подробнее в материале «Преобразование типов и кодировки в БД MySQL».
• collation_name: имя кодировки, которая будет использоваться при поиске/сравнении/сортировке символьных и текстовых типов данных. Список доступных кодировок можно посмотреть командой SHOW COLLATION;. Подробнее в материале «Преобразование типов и кодировки в БД MySQL».
• col_name: имя столбца изменяемой таблицы
• column_definition: определение столбца изменяемой таблицы подобно синтаксису CREATE TABLE. Подробнее в материале «Создание таблиц CREATE TABLE в БД MySQL».
• old_col_name: имя изменяемого столбца
• new_col_name: новое уникальное имя изменяемого столбца
• literal: значение по умолчанию, которое будет принимать столбец, если его значение не указано в инструкции INSERT при добавлении записи в таблицу. Тип этого значения должен соответствовать значению data_type.
• (expr) — выражение заключенное в скобки. Это выражение, значение которого будет использоваться соответствующим оператором. Обычно выражение содержит ограниченные конструкции. Если выражение будет содержать запрещенные конструкции, то возникает ошибка.
• col_name1/col_name2: строка, уникальное имя столбца/колонки в создаваемой таблице.
• old_index_name: имя изменяемого индекса
• index_name: уникальное имя индекса
• new_index_name: новое уникальное имя изменяемого индекса
• check_name необязательное уникальное имя ограничения, которое используется для проверки вставляемых значений в колонки таблицы. Если оно не задано, то MySQL генерирует его самостоятельно.

Комментарии к синтаксису ALTER TABLE:

1. Чтобы использовать инструкцию ALTER TABLE, необходимы привилегии пользователя ALTER, CREATE и INSERT для таблицы. Переименование таблицы требует привилегий ALTER и DROP.

2. После имени таблицы tbl_name необходимо указать изменения, которые необходимо внести. Если ничего не указано, то инструкция ALTER TABLE ничего не делает.

3. Для операторов ADD и CHANGE в параметре column_definition используется тот же синтаксис, что и для инструкции CREATE TABLE.

4. Слово COLUMN является необязательным и может быть опущено, за исключением RENAME COLUMN (чтобы отличить операцию переименования столбца от операции переименования таблицы RENAME).

5. В одном операторе ALTER TABLE допускается использование нескольких операторов ADD, ALTER, DROP и CHANGE, разделенных запятыми. Это расширение MySQL для стандартного SQL, которое допускает только одно из каждого предложения для оператора ALTER TABLE. Например, чтобы удалить несколько столбцов в одном запросе, можно сделать следующее:

   ALTER TABLE t2 
     DROP COLUMN c, 
     DROP COLUMN d;
   

6. Если при использовании ALTER TABLE для изменения CHARACTER SET или COLLATE появляется ошибка повторяющегося ключа, то причина либо в том, что новое COLLATE сопоставляет два ключа с одним и тем же значением, либо в том, что таблица повреждена. В последнем случае необходимо запустить команду REPAIR TABLE для таблицы. REPAIR TABLE работает с таблицами MyISAM, ARCHIVE и CSV.

7. Если ALTER TABLE используется для движка InnoDB или MySQL работает под Windows, то ALTER TABLE разблокирует таблицу, если она заблокирована LOCK TABLE. Это происходит потому, что InnoDB и Windows не могут удалить таблицу, которая используется.

Добавление/изменение/удаление столбцов таблицы.

Инструкции ALTER ... SET DEFAULT ... или ALTER ... DROP DEFAULT указывает новое значение по умолчанию для столбца или удаляют старое значение по умолчанию соответственно. Если старое значение по умолчанию удалено, то новое значение по умолчанию будет равно NULL. Если столбец не может быть NULL, то MySQL будет пытаться присвоить значение по умолчанию которое возможно для установленного в столбце типа.

Операторы CHANGE, MODIFY, RENAME COLUMN и ALTER позволяют изменять имена и определения существующих столбцов. Они имеют следующие сравнительные характеристики:

• CHANGE:

  • Можно переименовать столбец и изменить его определение или и то, и другое.
  • Имеет больше возможностей, чем MODIFY или RENAME COLUMN за счет удобства некоторых операций.
    • если нужно только переопределить параметры хранения данных, то необходимо дважды указать имя столбца.
    • если нужно только переименовать столбец, то необходимо повторно переопределить параметры хранения данных.
  • С атрибутами FIRST или AFTER col_name можно изменить порядок столбцов.

• MODIFY:

  • Можно изменить определение столбца, но не его имя.
  • Более удобно, чем CHANGE, чтобы изменить определение столбца, не переименовывая его.
  • С атрибутами FIRST или AFTER col_name можно изменить порядок столбцов.

• RENAME COLUMN:

  • Можно изменить имя столбца, но не его определение.
  • Более удобно, чем CHANGE, для переименования столбца без изменения его определения.

• ALTER:

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

Инструкция CHANGE — это расширение MySQL для стандартного SQL. MODIFY и RENAME COLUMN — это расширения MySQL для совместимости с Oracle.

Для изменения имени И определения столбца, лучше использовать оператор CHANGE, который позволяет менять все за один запрос. Например, чтобы переименовать столбец с a на b и изменить его определение INT NOT NULL для использования типа данных BIGINT, сохранив при этом атрибут NOT NULL, нужно сделать следующее:

ALTER TABLE t1 
  CHANGE a b BIGINT NOT NULL;

Чтобы изменить ТОЛЬКО определение столбца, можно использовать CHANGE или MODIFY. Синтаксис CHANGE требует дважды указывать одно и то же имя столбца, чтобы оставить его без изменений. Например, чтобы изменить определение столбца b, нужно сделать следующее:

ALTER TABLE t1 
  CHANGE b b INT NOT NULL;

Оператор MODIFY более удобен для изменения определения без изменения имени:

ALTER TABLE t1 
  MODIFY b INT NOT NULL;

Чтобы ТОЛЬКО переименовать столбец, а определение оставить прежним, можно использовать CHANGE или RENAME COLUMN. С оператором CHANGE синтаксис требует повторного указания определения столбца (которое столбец имеет в настоящее время). Например, чтобы переименовать столбец b, который имеет определение INT NOT NULL на a с тем же определением, нужно сделать следующее:

ALTER TABLE t1 
  CHANGE b a INT NOT NULL;

С оператором RENAME COLUMN удобнее изменять имя столбца без изменения его определения:

ALTER TABLE t1 RENAME COLUMN b TO a;

Для изменений определения столбца с помощью CHANGE или MODIFY определение должно включать тип данных и все атрибуты, которые должны применяться к новому столбцу, кроме атрибутов индекса, таких как PRIMARY KEY или UNIQUE. Атрибуты, присутствующие в исходном определении, но не указанные для нового определения, не переносятся. Предположим, что столбец col1 определен как INT UNSIGNED DEFAULT 1 COMMENT 'столбец'. Пробуем изменить столбец, намереваясь изменить только INT на BIGINT:

ALTER TABLE t1 
  MODIFY col1 BIGINT;

Этот запрос изменяет тип данных с INT на BIGINT, но также удаляет атрибуты UNSIGNED, DEFAULT и COMMENT. Чтобы сохранить их, инструкция должна включать их явно:

ALTER TABLE t1 
  MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'столбец';

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

Предупреждение

. Это преобразование может привести к изменению данных. Например, если сократить строковый столбец, то значения могут быть усечены. Чтобы предотвратить потерю информации, если преобразование в новый тип приведет к изменению данных, необходимо включить строгий режим SQL.

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

Для столбцов, переименованных CHANGE или RENAME COLUMN, MySQL не переименовывает автоматически ссылки на эти столбцы, которые используются в представлениях и/или сохраненных функциях и процедурах. Все эти ссылки необходимо изменить вручную.

Чтобы изменить порядок столбцов в таблице, необходимо в операциях CHANGE или MODIFY использовать атрибуты FIRST и AFTER col.

Добавление/изменение/удаление PRIMARY KEY и INDEX.

Инструкция DROP PRIMARY KEY удаляет первичный ключ. Если первичного ключа нет, то возникает ошибка.

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

Если в таблицу добавлен UNIQUE INDEX или PRIMARY KEY, то MySQL сохранит его перед любым неуникальным индексом, чтобы разрешить обнаружение повторяющихся ключей как можно раньше.

Инструкция DROP INDEX удаляет индекс. Это расширение MySQL для стандартного SQL. Чтобы посмотреть имена индексов таблицы, необходимо использовать команду SHOW INDEX FROM tbl_name.

Некоторые механизмы хранения позволяют указать тип индекса при их создании. Синтаксис USING BTREE | HASH.

Инструкция RENAME INDEX old_index_name TO new_index_name переименовывает индекс. Это расширение MySQL для стандартного SQL. Содержимое таблицы остается неизменным. Параметр old_index_name должен быть именем существующего индекса в таблице, который не удаляется тем же оператором ALTER TABLE. Параметр new_index_name это новое имя индекса, которое не может дублировать имя индекса в результирующей таблице после применения изменений. Ни одно из имен индексов не может иметь имя PRIMARY.

Если вы используете ALTER TABLE для таблицы MyISAM, все неуникальные индексы создаются в отдельном пакете (как для REPAIR TABLE). Это должно сделать ALTER TABLE намного быстрее, когда у вас много индексов.

После операций по изменению индексов может потребоваться запустить команду ANALYZE TABLE для обновления информации о количестве элементов индекса.

Добавление/изменение/удаление внешнего FOREIGN KEY.

Для ALTER TABLE, в отличие от CREATE TABLE, инструкция ADD FOREIGN KEY игнорирует index_name, если оно задано, и использует автоматически сгенерированное имя внешнего ключа. В качестве обходного пути, чтобы указать имя внешнего ключа, необходимо дополнительно указать оператор CONSTRAINT index_name:

ALTER TABLE t1 
  ADD CONSTRAINT index_name FOREIGN KEY (....) ...

Важно. MySQL молча игнорирует встроенные спецификации REFERENCES, где ссылки определены как часть спецификации столбца. MySQL принимает только предложения REFERENCES, определенные как часть отдельной спецификации FOREIGN KEY.

Добавление/изменение/удаление проверки CHECK.

До версии MySQL 8.0.16 (узнать версию SELECT VERSION();) оператор ALTER TABLE допускал только ограниченную версию синтаксиса: ADD CHECK (expr), которая анализировалась и игнорировалась.

Начиная с MySQL 8.0.16, оператор ALTER TABLE позволяет добавлять, удалять или изменять ограничения CHECK.

Добавление нового ограничения CHECK. Значение элементов синтаксиса такое же, как и для CREATE TABLE:

ALTER TABLE tbl_name
    ADD [CONSTRAINT check_name] CHECK (expr) [[NOT] ENFORCED];

Удаление существующего ограничения CHECK с именем check_name:

ALTER TABLE tbl_name
    DROP CHECK check_name;

Включение ENFORCED или выключение NOT ENFORCED проверки ограничения CHECK с именем check_name:

ALTER TABLE tbl_name
    ALTER CHECK check_name [NOT] ENFORCED;

Предложения DROP CHECK и ALTER CHECK являются расширениями MySQL для стандартного SQL.

Начиная с MySQL 8.0.19 (узнать версию SELECT VERSION();), ALTER TABLE допускает более общий (и стандартный для SQL) синтаксис для удаления и изменения существующих ограничений любого типа, где тип ограничения определяется по имени ограничения:

Удаление существующего ограничения с именем check_name:

ALTER TABLE tbl_name
    DROP CONSTRAINT check_name;

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

Включение/выключение проверки ограничения CHECK с именем check_name:

ALTER TABLE tbl_name
    ALTER CONSTRAINT symbol [NOT] ENFORCED;

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

• Попытки добавить атрибут AUTO_INCREMENT к столбцу, используемому в ограничении CHECK.
• Попытки добавить принудительное ограничение CHECK или применить непринудительное ограничение CHECK, для которых существующие строки нарушают условие ограничения.
• Попытки изменить, переименовать или удалить столбец, который используется в ограничении CHECK, если это ограничение также не удалено в том же операторе. Исключение: если ограничение CHECK относится только к одному столбцу, то удаление столбца автоматически удаляет ограничение.

Изменение набора символов (кодировки).

Чтобы изменить набор символов (кодировку) таблицы по умолчанию и все столбцы символов CHAR, VARCHAR, TEXT на новый набор символов (кодировку), нужно использовать следующий запрос:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Запрос также изменяет сопоставление COLLATE всех столбцов символов. Если не указать предложение COLLATE, то оператор CHARACTER SET использует сопоставление по умолчанию для указанного набора символов (кодировки). Если это сопоставление не подходит для предполагаемого использования таблицы (например, если оно изменится с сопоставления с учетом регистра на сопоставление без учета регистра), то необходимо указать сопоставление COLLATE явно.

Для столбца с типом данных VARCHAR или одним из типов TEXT команда CONVERT TO CHARACTER SET изменяет тип данных по мере необходимости. Такое изменение необходимо, чтобы новый столбец был достаточно длинным для хранения такого же количества символов, как исходный столбец. Например, столбец TEXT имеет два байта длины, которые хранят длину значений в байтах в столбце, максимум до 65 535. Для столбца latin1 TEXT каждому символу требуется один байт, поэтому столбец может хранить до 65 535 символов. Если столбец преобразован в utf8, то для каждого символа может потребоваться до трех байтов, при максимально возможной длине 3 × 65535 = 196605 байт. Эта длина не умещается в байтах длины столбца TEXT, поэтому MySQL преобразует тип данных в MDIUMTEXT, который является наименьшим строковым типом, для которого байты длины могут записывать значение 196 605. Точно так же столбец VARCHAR может быть преобразован в MEDIOMTEXT.

Чтобы избежать только что описанных изменений типа данных, не используйте CONVERT TO CHARACTER SET. Вместо этого, для изменения отдельных столбцов нужно использовать оператор MODIFY. Например:

ALTER TABLE t 
  MODIFY colText_latin1 TEXT CHARACTER SET utf8;
ALTER TABLE t 
  MODIFY colVarchar_latin1 VARCHAR(M) CHARACTER SET utf8;

Предупреждение

. Операция CONVERT TO преобразует значения столбца между исходным и именованным наборами символов. Это не то, что обычно нужно. Например, если есть столбец с одной кодировкой (например, latin1), но сохраненные значения фактически используют другую, несовместимую кодировку (например, utf8), то в этом случае необходимо сделать следующее для каждого такого столбца:

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

Причина, по которой это работает, заключается в том, что при преобразовании в тип BLOB или из BLOB — преобразования не происходит.

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

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

Оператор DEFAULT не является обязательным. Набор символов (кодировка) по умолчанию — это набор символов, который используется для хранения данных символьных и текстовых столбцов, если столбец добавляется позже (например, с помощью ALTER TABLE ... ADD column).

Если включена системная переменная foreign_key_checks (это настройкой по умолчанию), то преобразование набора символов не разрешено для таблиц, содержащих столбец, используемый в FOREIGN KEY. Обходной путь заключается в отключении foreign_key_checks перед выполнением преобразования набора символов. Необходимо выполнить преобразование в обеих таблицах, участвующих в ограничении внешнего ключа, прежде чем включить foreign_key_checks. Если включить foreign_key_checks после преобразования только одной из таблиц, то операция ON DELETE CASCADE или ON UPDATE CASCADE может повредить данные в ссылочной таблице из-за неявного преобразования, которое происходит во время этих операций.

Примеры практического использования ALTER TABLE.

Начнем с таблицы t1, созданной, как показано здесь:

CREATE TABLE t1 (a INTEGER, b CHAR(10));

Переименуем таблицу с t1 на t2:

ALTER TABLE t1 RENAME t2;

Изменим столбец a с INTEGER на TINYINT NOT NULL (оставив имя прежним) и изменим столбец b с CHAR(10) на CHAR(20), а также переименуем его с b на c:

ALTER TABLE t2 
  MODIFY a TINYINT NOT NULL, 
  CHANGE b c CHAR(20);

Добавим новый столбец TIMESTAMP с именем d:

ALTER TABLE t2 ADD d TIMESTAMP;

Добавим индекс для столбца d и индекс UNIQUE для столбца a:

ALTER TABLE t2 
  ADD INDEX (d), 
  ADD UNIQUE (a);

Удалим столбец c:

ALTER TABLE t2 DROP COLUMN c;

Добавим новый целочисленный столбец AUTO_INCREMENT с именем c:

ALTER TABLE t2 
  ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (c);

Запрос проиндексировал столбец c (как PRIMARY KEY), т.к. столбцы AUTO_INCREMENT должны быть индексированными. Также в запросе объявлен столбец c как NOT NULL, т.к. первичные ключи не могут быть NULL.

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

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

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

CREATE TABLE t2 (
  id INT AUTO_INCREMENT PRIMARY KEY
) 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;

Важно. Чтобы гарантировать одинаковый порядок как в источнике, так и в копии, все столбцы t1 должны быть указаны в операторе ORDER BY.

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

DROP TABLE t1;
ALTER TABLE t2 RENAME t1;