14.6.1.1 Creating InnoDB Tables — MySQL 5.7

14.6.1.1 Создание таблиц InnoDB

Таблицы создаются с помощью оператора создания таблицы; например:

CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

Оператор ENGINE=InnoDB не требуется, если движок InnoDB определен в качестве значения по умолчанию, что и происходит по умолчанию. Однако, оператор ENGINE полезен, если оператор создания таблицы нужно выполнить на другом экземпляре MySQL Server, где движок по умолчанию не InnoDB или неизвестен. Движок по умолчанию на экземпляре MySQL Server можно определить, выполнив следующий оператор:

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

Таблицы InnoDB создаются в файловых таблицах по умолчанию. Чтобы создать таблицу InnoDB в системной таблице InnoDB, отключите переменную innodb_file_per_table перед созданием таблицы. Чтобы создать таблицу InnoDB в общей таблице, используйте синтаксис CREATE TABLE ... TABLESPACE. Дополнительная информация приведена в разделе 14.6.3, «Табличные пространства».

.frm файлы

MySQL хранит информацию о словаре данных для таблиц в каталогах баз данных. В отличие от других движков MySQL, InnoDB также кодирует информацию о таблице в собственном внутреннем словаре данных внутри системного пространства таблиц. При удалении MySQL таблицы или базы данных, он также удаляет один или несколько файлов .frm, а также соответствующие записи в словаре данных InnoDB. Вы не можете перемещать таблицы InnoDB между базами данных, просто переместив файлы .frm. Сведения о перемещении таблиц InnoDB см. в разделе 14.6.1.4, «Перемещение или копирование таблиц InnoDB».

Форматы строк

Формат строк таблицы InnoDB определяет, как строки физически хранятся на диске. InnoDB поддерживает четыре формата строк, каждый со своими характеристиками хранения. Поддерживаемые форматы строк включают REDUNDANT, COMPACT, DYNAMIC и COMPRESSED. Формат строк DYNAMIC является по умолчанию. Сведения о характеристиках формата строк см. в разделе 14.11, «Форматы строк InnoDB».

Переменная innodb_default_row_format определяет формат строк по умолчанию. Формат строк таблицы также можно явно определить с помощью опции таблицы ROW_FORMAT в операторах CREATE TABLE или ALTER TABLE. См. определение формата строк таблицы.

Первичные ключи

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

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

Например, в таблице, содержащей информацию о людях, вы не будете создавать первичный ключ на (firstname, lastname), так как более одного человека могут иметь одинаковое имя, столбец имени может быть оставлен пустым, и иногда люди меняют свои имена. При наличии столь многих ограничений часто не существует очевидного набора столбцов для использования в качестве первичного ключа, поэтому вы создаете новый столбец с числовым идентификатором, который будет служить всем или частью первичного ключа. Вы можете объявить столбец таким образом, чтобы возрастающие значения заполнялись автоматически при вставке строк:

# The value of ID can act like a pointer between related items in different tables.
CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));

# The primary key can consist of more than one column. Any autoinc column must come first.
CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

Дополнительная информация об автоинкрементируемых столбцах приведена в разделе 14.6.1.6, «Обработка AUTO_INCREMENT в InnoDB».

Хотя таблица работает правильно без определения первичного ключа, первичный ключ связан со многими аспектами производительности и является важным аспектом проектирования любой большой или часто используемой таблицы. Рекомендуется всегда указывать первичный ключ в операторе CREATE TABLE. Если вы создаете таблицу, загружаете данные, а затем выполняете оператор ALTER TABLE для добавления первичного ключа позже, эта операция будет намного медленнее, чем определение первичного ключа при создании таблицы. Дополнительную информацию о первичных ключах см. в разделе 14.6.2.1, «Кластеризованные и вторичные индексы».

Просмотр свойств таблицы InnoDB

Чтобы просмотреть свойства таблицы InnoDB, выполните оператор SHOW TABLE STATUS:

mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-02-18 12:18:28
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

Сведения о выводе SHOW TABLE STATUS см. в разделе 13.7.5.36, «Оператор SHOW TABLE STATUS».

Вы также можете получить доступ к свойствам таблицы InnoDB, запросив таблицы системной схемы InnoDB:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 45
         NAME: test/t1
         FLAG: 1
       N_COLS: 5
        SPACE: 35
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

Дополнительную информацию см. в разделе 14.16.3, «Системные таблицы InnoDB INFORMATION_SCHEMA».