Beginning with MySQL 8.0.30, MySQL supports generated invisible
primary keys when running in GIPK mode. When running in this
mode, for any InnoDB
table that is
created without an explicit primary key, the MySQL server
automatically adds a generated invisible primary key (GIPK) to
the table. This section describes how to enable and disable GIPK
mode, and the behavior of CREATE TABLE
and of
the generated primary key when this mode is enabled.
GIPK mode is controlled by the
sql_generate_invisible_primary_key
server system variable. By default, the value of this variable
is OFF
, which means that GIPK mode is
disabled; to enable GIPK mode, set the variable to
ON
. To illustrate how table creation is
affected by GIPK mode, we begin by creating two identical
tables, neither having a primary key, the first (table
auto_0
) with GIPK mode disabled, and the
second (auto_1
) after enabling it, as shown
here:
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)
The setting for
sql_generate_invisible_primary_key
is not
replicated, and is ignored by replication applier threads. This
means that the replica does not generate a primary key for any
replicated table which was not created with a primary key on the
source.
Use SHOW CREATE TABLE
to see the
difference in how the tables were actually created, like this:
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)
Since auto_1
had no primary key specified by
the CREATE TABLE
statement used to create it,
GIPK mode causes MySQL to add to this table the invisible column
my_row_id
and a primary key on that column.
Since GIPK mode was disabled when auto_0
was
created, no such addition was performed on that table.
When a primary key is added to a table by the server in GIPK
mode, the column and key name is always
my_row_id
. For this reason, when GIPK mode is
enabled, you cannot create a table having a column named
my_row_id
unless the table creation statement
also specifies an explicit primary key. (You are not required to
name the column or key my_row_id
in such
cases.)
my_row_id
is an invisible column, which means
it is not shown in the output of
SELECT *
or
TABLE
; the column must be
selected explicitly, by name (see
Section 13.1.20.10, “Invisible Columns”).
When GIPK mode is in effect, the generated primary key cannot be
altered other than to switch it between
VISIBLE
and INVISIBLE
. To
make the generated invisible primary key on
auto_1
visible, execute this
ALTER TABLE
statement:
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)
To make this generated primary key invisible again, issue
ALTER TABLE auto_1 ALTER COLUMN my_row_id SET
INVISIBLE
.
A primary key generated by GIPK mode is always invisible by default.
Whenever GIPK mode is in effect, you cannot drop a generated primary key if either of the following 2 conditions would result:
The table is left with no primary key.
The primary key is dropped, but not the primary key column.
GIPK mode applies only to tables using the
InnoDB
storage engine. It has no effect on
tables using other MySQL storage engines. You can use an
ALTER TABLE
statement to change
the storage engine used by a table that has a generated
invisible primary key; the primary key and column remain in
place, but the table and key no longer receive any special
treatment.
By default, GIPKs are shown in the output of
SHOW CREATE TABLE
,
SHOW COLUMNS
, and
SHOW INDEX
, and are visible in
the Information Schema COLUMNS
and
STATISTICS
tables. You can cause
generated invisible primary keys to be hidden instead in such
cases by setting the
show_gipk_in_create_table_and_information_schema
system variable to OFF
. By default, this
variable is ON
, as shown here:
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)
As can be seen from the following query against the
COLUMNS
table, my_row_id
is visible among the columns of 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)
After
show_gipk_in_create_table_and_information_schema
is set to OFF
, my_row_id
can no longer be seen in the COLUMNS
table,
as shown here:
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)
GIPK mode supports row-based replication of
CREATE
TABLE ... SELECT
; the information written to the
binary log for this statement in such cases includes the GIPK
definition, and thus is replicated correctly. Statement-based
replication of CREATE TABLE ... SELECT
is not
supported in GIPK mode.
When creating or importing backups of installations in which
GIPK mode is in use, it is possible to exclude generated
invisible PK columns and values. The
--skip-generated-invisible-primary-key
option for mysqldump causes GIPK information
to be excluded in the program's output. If you are
importing a dump file that contains GIPK keys and values, you
can also use
--skip-generated-invisible-primary-key
with mysqlpump to cause these to be
suppressed (and thus not imported).