В прошлом уроке мы навешивали на таблицу правила при создании. Но таблица живёт годами, и требования меняются: зрителям понадобились бонусные баллы, телефону не хватает длины, город решили искать быстрее. Пересоздавать таблицу с данными ради этого никто не будет. Сегодня меняем живые таблицы командой ALTER TABLE, а во второй половине урока перестраиваем films: выносим режиссёров в отдельную таблицу.
Ремонт без переезда
Когда в квартире нужна ещё одна розетка, ты не переезжаешь в новый дом. Мастер просверлит стену, и жильцы даже не заметят. А вот заменить все трубы в доме можно, только перекрыв воду и вскрыв каждую квартиру.
ALTER TABLE бывает и таким, и таким. Одни изменения MySQL делает мгновенно, поправив только описание таблицы. Для других он строит новую копию таблицы и переписывает в неё каждую строку. На шести зрителях разницы не видно, а на ста миллионах строк копирование идёт часами. Различать их научимся по ответу сервера.
ADD COLUMN
Добавим зрителям дату рождения сразу после имени и бонусный счёт:
ALTER TABLE viewers ADD COLUMN birth_date DATE AFTER name;Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0ALTER TABLE viewers ADD COLUMN bonus INT NOT NULL DEFAULT 0;Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0Без AFTER колонка встаёт в конец. Обрати внимание на 0 rows affected. Это и есть мгновенное изменение, INSTANT: InnoDB записал новую колонку в описание таблицы и не тронул ни одной строки. Значения при этом есть у всех:
SELECT id, name, birth_date, bonus FROM viewers;+----+---------------------------+------------+-------+
| id | name | birth_date | bonus |
+----+---------------------------+------------+-------+
| 1 | Анна Ковалёва | NULL | 0 |
| 2 | Борис Орлов | NULL | 0 |
| 3 | Вера Ильина | NULL | 0 |
| 4 | Глеб Соколов | NULL | 0 |
| 5 | Дарья Белова | NULL | 0 |
| 6 | Егор Ёлкин | NULL | 0 |
+----+---------------------------+------------+-------+
6 rows in set (0.00 sec)Дата рождения пустая, бонус 0 из DEFAULT. Колонку с NOT NULL без DEFAULT в непустую таблицу добавлять не стоит: старым строкам MySQL подставит пустое значение типа (0 или пустую строку), и отличить их от настоящих уже не выйдет.
MODIFY: когда переписывается вся таблица
MODIFY меняет тип колонки. Расширим телефон и город:
ALTER TABLE viewers MODIFY phone VARCHAR(30);Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0ALTER TABLE viewers MODIFY city VARCHAR(80);Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0Два похожих изменения, а ответы разные. Телефон расширился мгновенно, а город переписал все 6 строк. Причина в том, как InnoDB хранит VARCHAR. Перед строкой лежит её длина в байтах. Пока колонка вмещает до 255 байт, на длину уходит один байт, дальше два. Буква в utf8mb4 может занимать до 4 байт: VARCHAR(30) это до 120 байт, длина помещается в один байт, менять в строках нечего. VARCHAR(80) это уже до 320 байт, у каждой строки меняется заголовок, и таблицу приходится переписать.
Смена типа числа всегда копирует таблицу:
ALTER TABLE viewers MODIFY bonus BIGINT NOT NULL DEFAULT 0;Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0Правило на будущее: N rows affected у ALTER TABLE означает, что MySQL переписал N строк. На большой таблице в рабочей базе такую команду запускают не посреди дня, а после проверки на копии.
Учти, что MODIFY описывает колонку целиком. Если у колонки были NOT NULL и DEFAULT, а ты написал только тип, они пропадут. Поэтому выше мы повторили NOT NULL DEFAULT 0.
RENAME и DROP
Переименовать колонку и удалить её:
ALTER TABLE viewers RENAME COLUMN bonus TO bonus_points;Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0ALTER TABLE viewers DROP COLUMN birth_date;Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0Оба мгновенные. Но DROP COLUMN стирает значения колонки безвозвратно, корзины нет. А переименование ломает каждый запрос приложения, где было старое имя.
Когда ALTER отказывает
Сделаем город обязательным:
ALTER TABLE viewers MODIFY city VARCHAR(80) NOT NULL;ERROR 1138 (22004): Invalid use of NULL valueУ Глеба Соколова город не указан, и MySQL не может сделать колонку обязательной, пока в ней есть NULL. Сначала заполни пустые значения через UPDATE, потом меняй колонку.
Сузим имя до пяти символов:
ALTER TABLE viewers MODIFY name VARCHAR(5);ERROR 1265 (01000): Data truncated for column 'name' at row 1«Анна Ковалёва» в пять букв не влезает, и строгий режим запретил обрезать. Таблица осталась как была: ALTER TABLE либо проходит целиком, либо не меняет ничего. Опечатка в имени колонки тоже даёт ошибку:
ALTER TABLE viewers DROP COLUMN nosuch;ERROR 1091 (42000): Can't DROP 'nosuch'; check that column/key existsADD INDEX
Через ALTER TABLE добавляют и индексы. Город будем искать часто:
ALTER TABLE viewers ADD INDEX idx_city (city);
DESCRIBE viewers;+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| email | varchar(100) | NO | UNI | NULL | |
| phone | varchar(30) | YES | | NULL | |
| city | varchar(80) | YES | MUL | NULL | |
| registered_at | date | NO | | NULL | |
| bonus_points | bigint | NO | | 0 | |
+---------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)Метка MUL у города значит «обычный индекс, значения могут повторяться». Индекс строится, пока таблица доступна для чтения и записи. Как индексы ускоряют поиск, разберём в уроке про индексы.
Тарковский дважды
Теперь посмотри на фильмы. Режиссёр записан текстом прямо в строке фильма, и у «Сталкера» и «Соляриса» стоит одно и то же «Тарковский». Пока фильмов семь, это мелочь. Но представь тысячу фильмов: кто-то исправит опечатку в фамилии в одной строке и забудет про остальные, и база будет считать, что режиссёров два. Это аномалия обновления. А чтобы хранить про режиссёра что-то ещё, год рождения или страну, пришлось бы повторять это у каждого его фильма.
Лекарство называется нормализацией: каждый факт хранится в одном месте. Режиссёры получат свою таблицу, а фильм будет хранить только номер режиссёра. Теория нормальных форм разобрана в уроке про аномалии и нормальные формы. Здесь проделаем это руками за пять шагов.
Шаг 1. Таблица режиссёров, имя без повторов:
CREATE TABLE directors (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);Query OK, 0 rows affected (0.00 sec)Шаг 2. Заполняем её из фильмов. INSERT ... SELECT вставляет результат запроса, DISTINCT убирает повторы, NULL у «Новых имён» отсекаем:
INSERT INTO directors (name) SELECT DISTINCT director FROM films WHERE director IS NOT NULL;
SELECT * FROM directors;+----+----------------------+
| id | name |
+----+----------------------+
| 1 | Балабанов |
| 2 | Меньшов |
| 5 | Норштейн |
| 3 | Рязанов |
| 4 | Тарковский |
+----+----------------------+
5 rows in set (0.00 sec)Тарковский один. Заметь странный порядок: строки отсортированы по имени, а не по id. Таблица маленькая, и InnoDB прочитал её через индекс UNIQUE по имени, в нём есть всё нужное. Без ORDER BY порядок строк ничем не гарантирован. Нужен порядок, пиши ORDER BY id.
Шаг 3. Колонка для ссылки и её заполнение. UPDATE ... JOIN находит для каждого фильма режиссёра с тем же именем и копирует его номер. Соединения таблиц подробно разберём в уроке про JOIN, пока читай это как «сопоставь по имени»:
ALTER TABLE films ADD COLUMN director_id INT AFTER director;
UPDATE films JOIN directors ON directors.name = films.director SET films.director_id = directors.id;Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0Шесть из семи: у «Новых имён» режиссёра нет. Сверим старое и новое рядом, прежде чем что-то удалять:
SELECT id, title, director, director_id FROM films;+----+-------------------------------------------+----------------------+-------------+
| id | title | director | director_id |
+----+-------------------------------------------+----------------------+-------------+
| 1 | Брат | Балабанов | 1 |
| 2 | Москва слезам не верит | Меньшов | 2 |
| 3 | Ирония судьбы | Рязанов | 3 |
| 4 | Сталкер | Тарковский | 4 |
| 5 | Ёжик в тумане | Норштейн | 5 |
| 6 | Новые имена | NULL | NULL |
| 7 | Солярис | Тарковский | 4 |
+----+-------------------------------------------+----------------------+-------------+
7 rows in set (0.00 sec)Шаг 4. Внешний ключ из урока про внешние ключи, чтобы номер не указывал в пустоту:
ALTER TABLE films ADD FOREIGN KEY (director_id) REFERENCES directors (id);Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 07 rows affected: добавление внешнего ключа переписало таблицу, и заодно MySQL проверил каждую ссылку.
Шаг 5. Старая текстовая колонка больше не нужна:
ALTER TABLE films DROP COLUMN director;
SHOW CREATE TABLE films\G*************************** 1. row ***************************
Table: films
Create Table: CREATE TABLE `films` (
`id` int NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`director_id` int DEFAULT NULL,
`release_year` smallint NOT NULL,
`duration_min` smallint unsigned NOT NULL,
`age_rating` enum('0+','6+','12+','16+','18+') NOT NULL,
`info` json DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `director_id` (`director_id`),
CONSTRAINT `films_ibfk_1` FOREIGN KEY (`director_id`) REFERENCES `directors` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)Готово. Фамилию Тарковского теперь исправляют в одной строке directors, и оба фильма видят исправление. Порядок шагов важен: сначала новое рядом со старым, проверка, и только потом удаление. На рабочей базе между шагами 4 и 5 ещё обновляют приложение, чтобы оно читало режиссёра по director_id.
Переименовать таблицу
Напоследок RENAME TO меняет имя самой таблицы:
ALTER TABLE films RENAME TO movies;
SHOW TABLES;+----------------+
| Tables_in_kino |
+----------------+
| directors |
| halls |
| movies |
| screenings |
| tickets |
| viewers |
+----------------+
6 rows in set (0.00 sec)Внешние ключи сеансов перешли на новое имя сами. Вернём как было: ALTER TABLE movies RENAME TO films. Та же работа в PostgreSQL описана в уроке про ALTER TABLE в PostgreSQL.
Попробуй сам
В песочнице проделаем нормализацию режиссёров по шагам. Удаление старой колонки оставлено тебе:
Удали колонку director и посмотри SHOW CREATE TABLE films\G. Сравни ответы ALTER TABLE viewers MODIFY phone VARCHAR(30) и ALTER TABLE viewers MODIFY phone VARCHAR(100). Попробуй удалить режиссёра Тарковского и прочитай ошибку.
Проверь себя
Задание
Дальше
Таблицы, ключи и ограничения на месте. Пора задавать базе вопросы. В следующем уроке начинаем блок запросов: SELECT, фильтры WHERE, сортировка и LIMIT.
домашка