MySQL / 8. Изменение таблицы с помощью предложения ALTER TABLE

Предложение ALTER TABLE, значения NULL и значения по умолчанию

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

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

Обсуждение
Если вы изменяете столбец посредством MODIFY или CHANGE, то можете указать, допускает ли столбец значения NULL и каковы его значения по умолчанию. Если не сделать этого, то MySQL автоматически присвоит этим атрибутам некоторые значения, в результате чего столбцы могут быть определены не совсем так, как вам хотелось бы. Давайте попробуем выполнить такую последовательность команд. Сначала изменим столбец j так, чтобы он не мог содержать значения NULL и имел значение по умолчанию, равное 100, тогда результат SHOW COLUMNS будет таким:

mysql> ALTER TABLE mytbl MODIFY j INT NOT NULL DEFAULT 100;
mysql> SHOW COLUMNS FROM mytbl LIKE 'j';

+------+--------+-----+----+---------+--------+
| Field | Type    | Null | Key | Default | Extra |
+-----+---------+----+------+---------+-------+
| j       | int(11) |        |        | 100      |           |
+-----+---------+-----+-----+--------+--------+

Пока все хорошо. Теперь, если вы захотите изменить тип столбца j с INT на BIGINT, попробуйте выполнить такое предложение:

mysql> ALTER TABLE mytbl MODIFY j BIGINT;

Однако его выполнение приведет к отмене установок NULL и DEFAULT предыдущего предложения ALTER TABLE:

mysql> SHOW COLUMNS FROM mytbl LIKE 'j';

+------+-----------+-----+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+------+------------+------+-----+---------+-------+
| j        | bigint(20) | YES  |        | NULL     |           |
+------+------------+------+-----+----------+-------+

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

mysql> ALTER TABLE mytbl MODIFY j BIGINT NOT NULL DEFAULT 100;
mysql> SHOW COLUMNS FROM mytbl LIKE 'j';

+-----+------------+-----+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-----+------------+-----+-----+----------+-------+
| j       | bigint(20) |        |        | 100       |          |
+-----+------------+-----+-----+----------+-------+

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

Это соображение учитывается в некоторых рецептах главы 9. Например, одна из программ преобразует таблицу так, чтобы в ней использовались столбцы типа VARCHAR, а не CHAR, а вторая добавляет элементы в столбцы ENUM или SET. В обоих случаях программы заботятся о том, чтобы избежать нежелательных изменений столбцов, включая спецификаторы NULL и DEFAULT в формируемые ею предложения ALTER TABLE.

Статьи по MySQL на эту тему:

Добавление и удаление индексов
Изменение значения столбца по умолчанию
Изменение типа таблицы
Использование предложения ALTER TABLE для нормализации таблицы
Переименование таблицы