MySQL 8.4 поддерживает сгенерированные невидимые первичные ключи для любой InnoDB таблицы, созданной без явного первичного ключа. Когда переменная сервера sql_generate_invisible_primary_key установлена в значение ON, сервер MySQL автоматически добавляет сгенерированный невидимый первичный ключ (GIPK) в любую такую таблицу. Это значение не влияет на таблицы, созданные с использованием других двигателей хранилища, кроме InnoDB.

По умолчанию значение sql_generate_invisible_primary_key равно OFF, что означает отключение автоматического добавления GIPK. Чтобы проиллюстрировать, как это влияет на создание таблиц, мы начнём с создания двух идентичных таблиц, ни одна из которых не имеет первичного ключа, единственное различие заключается в том, что первая (таблица auto_0) создаётся с sql_generate_invisible_primary_key, установленным в значение OFF, а вторая (auto_1) после установки его в значение ON, как показано ниже:

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE auto_0 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> SET sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE auto_1 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.04 sec)

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

mysql> SHOW CREATE TABLE auto_0\G
*************************** 1. row ***************************
       Table: auto_0
Create Table: CREATE TABLE `auto_0` (
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE auto_1\G
*************************** 1. row ***************************
       Table: auto_1
Create Table: CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Поскольку в таблице auto_1 не было указано первичный ключ в команде CREATE TABLE, используемой для её создания, установка sql_generate_invisible_primary_key = ON приводит к добавлению MySQL как невидимого столбца my_row_id в эту таблицу, так и первичного ключа на этом столбце. Поскольку sql_generate_invisible_primary_key было значением OFF в момент создания auto_0, такие добавления не были выполнены для этой таблицы.

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

my_row_id - это невидимый столбец, что означает, что он не отображается в результатах SELECT * или TABLE; столбец нужно выбрать явно по имени. См. Раздел 15.1.20.10, «Невидимые столбцы».

При включении GIPK, сгенерированный первичный ключ может быть изменён только на VISIBLE или INVISIBLE. Чтобы сделать сгенерированный невидимый первичный ключ в таблице auto_1 видимым, выполните эту ALTER TABLE команду:

mysql> ALTER TABLE auto_1 ALTER COLUMN my_row_id SET VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE auto_1\G
*************************** 1. row ***************************
       Table: auto_1
Create Table: CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

Чтобы сделать этот сгенерированный первичный ключ невидимым снова, выполните команду ALTER TABLE auto_1 ALTER COLUMN my_row_id SET INVISIBLE.

Сгенерированный невидимый первичный ключ по умолчанию всегда невидим.

При включении GIPK, вы не можете удалить сгенерированный первичный ключ, если выполнение этой операции приведёт к одному из следующих двух условий:

Действие sql_generate_invisible_primary_key применяется только к таблицам, использующим двигатель хранилища InnoDB. Вы можете использовать ALTER TABLE для изменения двигателя хранилища, используемого таблицей со сгенерированным невидимым первичным ключом; в этом случае первичный ключ и столбец остаются на месте, но таблица и ключ больше не получают специального обращения.

По умолчанию GIPK отображаются в выводе SHOW CREATE TABLE, SHOW COLUMNS и SHOW INDEX, а также видны в таблицах схемы информации COLUMNS и STATISTICS. Вы можете сделать сгенерированные невидимые первичные ключи скрытыми в таких случаях, установив переменную сервера show_gipk_in_create_table_and_information_schema в значение OFF. По умолчанию эта переменная равна ON, как показано ниже:

mysql> SELECT @@show_gipk_in_create_table_and_information_schema;
+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)

Как видно из следующего запроса к таблице COLUMNS, my_row_id отображается среди столбцов таблицы auto_1:

mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = "auto_1";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| my_row_id   |                1 | bigint    | PRI        |
| c1          |                2 | varchar   |            |
| c2          |                3 | int       |            |
+-------------+------------------+-----------+------------+
3 rows in set (0.01 sec)

После того, как show_gipk_in_create_table_and_information_schema установлено в значение OFF, my_row_id больше не отображается в таблице COLUMNS, как показано ниже:

mysql> SET show_gipk_in_create_table_and_information_schema = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@show_gipk_in_create_table_and_information_schema;
+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = "auto_1";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| c1          |                2 | varchar   |            |
| c2          |                3 | int       |            |
+-------------+------------------+-----------+------------+
2 rows in set (0.00 sec)

Настройки для sql_generate_invisible_primary_key не дублируются и игнорируются потоками прикладного программного обеспечения для репликации. Это означает, что настройка этой переменной на источнике не оказывает никакого влияния на реплику. Вы можете заставить реплику добавить GIPK для таблиц, реплицированных без первичных ключей на заданном канале репликации, используя REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE в качестве части оператора CHANGE REPLICATION SOURCE TO.

GIPK работают с репликацией строк CREATE TABLE ... SELECT; информация, записанная в двоичный журнал для этой команды в таких случаях, включает определение GIPK, и поэтому реплицируется правильно. Репликация команд CREATE TABLE ... SELECT не поддерживается с использованием sql_generate_invisible_primary_key = ON.

При создании или импорте резервных копий установок, где используются GIPK, можно исключить столбцы и значения сгенерированных невидимых первичных ключей. Параметр --skip-generated-invisible-primary-key для mysqldump приводит к исключению информации о GIPK в выводе программы.