Как создать таблицу в MySQL и не хранить всё текстом
Как создать таблицу в MySQL для каталога: выбрать типы полей, записать CREATE TABLE, добавить данные и проверить, что цена и дата сохранили свой смысл.
Денис
Продакт LibraChat
9 мин чтения
Я уже набросал каталог игр для приятеля, когда он спросил: «А можно показать сначала недорогие?» На листе всё выглядело прилично: название, цена, дата покупки. Но цену я записал как «900 рублей», а дату как «в начале октября». Для глаз удобно. Для будущего запроса придётся заново разбирать каждую ячейку. Вот с такой ошибки я начинаю объяснение, как создать таблицу в MySQL: сначала реши, что будешь спрашивать у данных.
У приятеля небольшая полка настолок, которые он даёт поиграть знакомым. Мой первый план был скромным: перенести список и больше не искать цену в переписке. Потом захотелось отсортировать покупки, найти коробки без даты, отличить экземпляры одной игры. Каждое такое желание меняло схему. А я-то собирался просто подписать колонки.
Пять колонок, которые казались достаточными
Я собрал названия полей в черновике и проверил их вопросами. Не «нравится ли нам такая таблица», а «сможет ли она ответить без чтения примечаний человеком». Разница обнаружилась быстро.
Цена должна участвовать в числовом сравнении. Дата покупки нужна для отбора за период. Название помогает найти игру, но не отличает одну коробку от другой. Примечание остаётся текстом, потому что «потерялась синяя фишка» пока не участвует ни в каком расчёте.
Сначала я сделал для цены текстовое поле: туда можно вписать и сумму, и пояснение. Потом представил три значения: «450», «900», «1200». При текстовой сортировке это последовательности символов. Числового порядка я от такого поля требовать не могу. Придётся преобразовывать значения, а с приписанными рублями задача станет ещё неприятнее.
Убрал подпись валюты из самой суммы. Сумма хранится числом, слово «рубли» будет рядом в интерфейсе. Маленькая правка. Зато будущему запросу не нужно догадываться, где кончилось число.
Что хотелось записывать · Что я оставил в схеме · Зачем это нужно
«Остров маршрутов, вторая коробка» — Название и отдельный инвентарный код — Название можно менять, код экземпляра остаётся
«900 рублей» — `DECIMAL(10,2)` — Сумму можно сравнивать и складывать как число
«В начале октября» — `DATE` или отсутствие известной даты — Неточная запись не превращается в выдуманный день
«Пока не знаю» в любой ячейке — `NULL` только там, где отсутствие допустимо — Не смешиваются значение и комментарий о нём
Пометка о фишках — Короткое текстовое поле — Не теряется полезная бытовая подробность
Для денег я выбрал `DECIMAL`, который хранит точные десятичные значения. В обозначении `DECIMAL(10,2)` десять цифр всего и две после точки. Это решение для нашего каталога, не универсальный размер на все случаи. Суммы с другой точностью потребуют другого определения.
А с датой пришлось смириться. Если никто не помнит день покупки, я оставляю его неизвестным. Первое октября выглядело бы аккуратнее, но уже означало бы конкретный факт. Такая аккуратность нам не нужна.
Мой короткий список перед SQL теперь выглядит так:
Что считается одной записью?
Какие вопросы я хочу задавать этим записям?
Какие значения должны быть числами или датами?
Что может отсутствовать?
Как обнаружить повторный ввод уже учтённой вещи?
Я потратил на эти вопросы больше внимания, чем на названия колонок. И не жалею. Переименовать поле в черновике легко; объяснить себе через месяц, почему цена лежит вместе с припиской, гораздо скучнее.
Как создать таблицу в MySQL, когда поля уже выбраны
Дальше нужен работающий сервер MySQL и подключение к нему. Команды можно отправлять из SQL-редактора клиента. Сам текст запроса на диске таблицу не создаёт. Ниже код для MySQL 8.4: используй отдельную учебную базу и проверяй результат в своём подключении.
Сначала выбери место для упражнения:
```sql
CREATE DATABASE game_shelf_lab
CHARACTER SET utf8mb4;
USE game_shelf_lab;
SELECT DATABASE();
```
`CREATE DATABASE` создаёт базу, `USE` выбирает её для следующих команд. Последний запрос позволяет прочитать, какая база сейчас выбрана. Я бы сверил это имя прежде, чем отправлять создание таблицы: соседнее открытое подключение легко принять за нужное.
Если имя уже занято, выбери другое свободное. Если у пользователя нет права создавать базы, используй учебную базу, которую выдал администратор. Удалять существующие объекты для продолжения упражнения не требуется.
А вот схема каталога:
```sql
CREATE TABLE game_copies (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
inventory_code VARCHAR(24) NOT NULL,
title VARCHAR(150) NOT NULL,
purchase_price DECIMAL(10,2) NOT NULL,
acquired_on DATE NULL,
note VARCHAR(250) NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_inventory_code (inventory_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
Я оставил шесть полей вместо первоначальных пяти: добавил внутренний номер `id`. Имена здесь латинские, значения названий могут быть русскими. `utf8mb4` задаёт набор символов таблицы; настройки кодировки подключения тоже должны ему соответствовать.
`VARCHAR` хранит текст переменной длины. Число в скобках ограничивает длину в символах. Для названия я заложил больше места, чем для наклейки с кодом. `DATE` отведён дате, а `NULL` у этого поля разрешает оставить её неизвестной.
`NOT NULL` у цены запрещает отсутствие значения. Нулевая цена при этом возможна: ноль и «не знаю» означают разное. Например, подаренная коробка могла действительно ничего не стоить. Записывать ноль вместо забытой суммы было бы удобным способом испортить будущий подсчёт затрат.
С ключами у меня отдельная договорённость. `PRIMARY KEY (id)` задаёт первичный ключ, а `AUTO_INCREMENT` позволяет назначать номер при добавлении записи. Уникальный ключ на `inventory_code` отвечает за наклейку: второй раз тот же код занести нельзя.
Тут легко успокоиться раньше времени. Два разных кода на одной и той же коробке база сама не распознает. Поэтому я не делаю вывод «повторов больше не будет». У нас есть конкретная защита от повторения кода и человеческое правило присваивать его один раз.
Пустая таблица молчит о наших ошибках
Когда я закончил определение, в нём уже не было рублей внутри текста цены. Хотелось переходить ко всему каталогу. Но сначала я выписал несколько неудобных записей: коробку без даты, вторую коробку с тем же названием, случайно повторённый код. На пустой таблице эти вопросы вообще не видны.
После выполнения создания посмотри устройство:
```sql
SHOW TABLES;
DESCRIBE game_copies;
SHOW CREATE TABLE game_copies;
```
Список таблиц помогает найти объект, описание показывает колонки, а `SHOW CREATE TABLE` возвращает его фактическое определение. Меня здесь интересуют тип цены, допустимость отсутствующей даты и уникальность инвентарного кода. Одного совпавшего названия недостаточно.
Колонки перечислены явно. Сумма записана с десятичной точкой, без валютного значка. Дату я не назначил: в этом примере она неизвестна. Внутренний номер сервер назначает при вставке, потому что для него задан `AUTO_INCREMENT`.
А теперь та часть, которую я раньше пропускал. Проверь, соответствует ли поведение твоим правилам:
Добавь другую коробку под новым кодом и с тем же названием. Само название не объявлено уникальным.
Попробуй повторить занятый инвентарный код. Ожидается отказ из-за уникального ключа.
Оставь дату неизвестной. Для неё разрешён `NULL`.
Посмотри, какие значения цены запрещены, а какие пока допустимы. Одного `NOT NULL` для всех правил о деньгах недостаточно.
Вопрос об отрицательной цене заставил меня перечитать схему внимательнее. Отдельного запрета на неё здесь пока нет. Если такая запись недопустима по смыслу каталога, это нужно выразить дополнительным правилом. Компьютер не узнает наши намерения из удачного имени поля.
При ошибке сначала прочитай её сообщение. Отказ в доступе, занятое имя таблицы и синтаксическая ошибка требуют разных действий. Повторное нажатие кнопки выполнения не превращает одно в другое.
Существует и `CREATE TABLE IF NOT EXISTS`. Но эта оговорка не проверяет, совпадает ли старая таблица с новым определением. Если объект уже есть, пропущенное создание не исправит его колонки. Для начала прочитай существующую схему. Изменение — отдельная работа.
Каталог ещё маленький, зато вопросы к нему уже взрослые
Я начал со списка для приятеля, а закончил черновиком правил. Что считаем деньгами. Как обозначаем неизвестную дату. Откуда берём код коробки. Теперь любую новую колонку я встречаю вопросом: «Что мы будем с ней делать?»
Например, срок «игра у знакомого уже 12 дней» я бы не стал хранить вместо даты выдачи: число дней меняется. Для истории выдач понадобится отдельная таблица событий. В каталоге оставляю сведения о самих коробках, а не пытаюсь засунуть весь прокат в одну строку.
Такие решения удобно обсуждать в LibraChat. Он помогает с кодом и объяснением задач. Я формулирую запрос от будущих действий с данными, чтобы разговор не сводился к проверке синтаксиса. Открой чат и попробуй на своём черновике:
Проектирую таблицу MySQL 8.4 для [задача]. Одна запись означает [что именно]. Хочу искать [условие], сортировать по [поле], считать [итог]. Вот мой CREATE TABLE: [код]. Объясни, подходят ли типы этим действиям. Отдельно укажи, какие правила уже выражены ограничениями, а какие пока существуют только в описании. Предложи несколько записей для проверки в учебной базе.
Пароли и строку подключения я в такой запрос не добавляю. Для обсуждения структуры они не нужны. Код из ответа всё равно предстоит прочитать и проверить на своём сервере; переписка не подтверждает, что таблица там создана.
А свой первый SQL я бы сохранил рядом с тремя вопросами к каталогу. В моём случае это «что дешевле», «что куплено за период» и «какие даты неизвестны». Если для ответа приходится сначала вынимать смысл из красивой текстовой ячейки, вернись к типам полей. Именно там у меня и лежала первоначальная ошибка.
