Create a Generated Invisible Primary Key (GIPK) in MySQL

MySQL 8.0.30 introduced generated invisible primary keys (GIPKs), which are primary keys that are automatically created whenever we create a table without explicitly defining a primary key.

GIPKs only work with the InnoDB storage engine, and they only work when we have GIPKs enabled.

In this article, I check whether or not GIPKs are enabled on my system, I then enable GIPKs, and finally I create a table with a GIPK.

Check Whether GIPKs are Enabled

GIPKs are enabled when the sql_generate_invisible_primary_key server system variable is set to ON. By default this is set to OFF. So we first need to set this variable to ON in before we can create a table with a GIPK.

But before we do that, let’s check to see if GIPKs are enabled on our system:

SELECT @@sql_generate_invisible_primary_key;

Result:

+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+

In this case my sql_generate_invisible_primary_key variable is set to 0, which means OFF. In other words, GIPKs are disabled.

With this setting, creating a table without a primary key will not result in a GIPK being generated.

Enable GIPKs

Here’s how to set the sql_generate_invisible_primary_key variable to ON:

SET sql_generate_invisible_primary_key = ON;

GIPKs are now enabled.

We can verify this by selecting the sql_generate_invisible_primary_key again:

SELECT @@sql_generate_invisible_primary_key;

Result:

+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

This time it returns a value of 1, which means ON.

Create a GIPK

Now that GIPKs are enabled, if we create a table without a primary key, MySQL will automatically create a GIPK for the table.

So let’s go ahead and create a table without a primary key:

CREATE TABLE Person
(
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.02 sec)

The table was created and we got the usual “Query OK” message with zero rows affected. We can’t tell by this message whether a GIPK was created or not, but it was.

Check the GIPK

We can check that a GIPK was created by running the SHOW CREATE TABLE statement:

SHOW CREATE TABLE Person;

Result:

+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                        |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Person | CREATE TABLE `Person` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `FirstName` varchar(20) DEFAULT NULL,
  `LastName` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that a column called my_row_id was created along with our other columns. GIPKs are always named my_row_id.

The my_row_id column was created as an unsigned bigint using a NOT NULL constraint. The GIPK column is an AUTO_INCREMENT column, which means that its value automatically increments with each new row. It’s also an invisible column, which means that it won’t be returned by queries that don’t explicitly name the column in the SELECT list. In other words, queries that only use the asterisk wildcard (*) to return all columns will not return the GIPK.

One thing I should mention is that the fact that we could see the GIPK in the above SHOW CREATE TABLE statement is because the value of my show_gipk_in_create_table_and_information_schema system variable is set to ON. This is the default value though, and so I didn’t need to do anything to effect this. But if this variable was set to OFF then we wouldn’t be able to see the GIPK in the output of the SHOW CREATE TABLE statement (and other SHOW statements, such as SHOW COLUMNS and SHOW INDEX).