MySQL 9.2 поддерживает сгенерированные невидимые первичные ключи для любых 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.21.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, вы не можете удалить сгенерированный первичный ключ, если выполнение любой из следующих 2 условий приведет к:

Эффекты 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 в выводе программы.