What is a Generated Invisible Primary Key (GIPK) in MySQL?

While primary keys are generally considered a necessity when it comes to relational databases, they’re usually included in the SQL code that creates the database table.

MySQL 8.0.30 introduced generated invisible primary keys which provide an alternative to explicitly specifying a primary key for a table.

A generated invisible primary key (GIPK) is a primary key that’s created implicitly by the MySQL server. If we create a table without an explicit primary key, the MySQL server automatically creates a generated invisible primary key for us (assuming it’s an InnoDB table and that GIPKs are enabled).

Checking Whether GIPKs are Enabled

GIPKs are only created when the sql_generate_invisible_primary_key server system variable is set to ON. By default this is set to OFF.

We can run the following code to check whether 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.

Enabling GIPKs

So given GIPKs are disabled by default, if we want to use them we’ll need to set the sql_generate_invisible_primary_key variable to ON.

Here’s how to do that:

SET sql_generate_invisible_primary_key = ON;

That’s all. 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.

Creating 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 message. There’s nothing in the message that would suggest that a GIPK was created, but it was created nonetheless.

Checking the GIPK

We can use the SHOW CREATE TABLE statement to check whether our GIPK was created or not:

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.

We can also see that the column has been declared as an invisible column. The fact that it’s invisible means that it won’t be returned by queries that don’t explicitly name the column in the SELECT list. Queries that only use the asterisk wildcard (*) to return all columns will not return the GIPK.

One thing I should point out 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).

GIPK in Action

Now let’s insert some rows into our table:

INSERT INTO Person( FirstName, LastName ) VALUES( 'Homer', 'Simpson' );
INSERT INTO Person( FirstName, LastName ) VALUES( 'Marge', 'Simpson' );
INSERT INTO Person( FirstName, LastName ) VALUES( 'Lisa', 'Simpson' );

Now let’s select all rows, including the GIPK:

SELECT
    my_row_id,
    FirstName,
    LastName
FROM Person;

Result:

+-----------+-----------+----------+
| my_row_id | FirstName | LastName |
+-----------+-----------+----------+
|         1 | Homer     | Simpson  |
|         2 | Marge     | Simpson  |
|         3 | Lisa      | Simpson  |
+-----------+-----------+----------+
3 rows in set (0.01 sec)

We can see that the my_row_id column is returned along with the other columns. Also, we can see that it has automatically been populated with a value that automatically increments with each row.

As mentioned, because the GIPK is an invisible column we must explicitly reference it in our SELECT list (like we did above). If we don’t do this, then it won’t be returned.

SELECT * FROM Person;

Result:

+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Homer     | Simpson  |
| Marge     | Simpson  |
| Lisa      | Simpson  |
+-----------+----------+
3 rows in set (0.00 sec)

Only two rows were returned because they are the only visible rows in the table.

It’s not to say that we can’t use the asterisk wildcard in the query. We can certainly use it, as long as we explicitly reference the GIPK too.

Like this:

SELECT
    *,
    my_row_id
FROM Person;

Result:

+-----------+----------+-----------+
| FirstName | LastName | my_row_id |
+-----------+----------+-----------+
| Homer     | Simpson  |         1 |
| Marge     | Simpson  |         2 |
| Lisa      | Simpson  |         3 |
+-----------+----------+-----------+
3 rows in set (0.00 sec)

Making a GIPK Visible

Although GIPKs are created as invisible columns, we do have the option of making them visible. We can do this using the same code we’d use when unhiding any other invisible column:

ALTER TABLE Person 
ALTER COLUMN my_row_id SET VISIBLE;

Result:

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now when we use SELECT * to select all columns, the GIPK is included:

SELECT * FROM Person;

Result:

+-----------+-----------+----------+
| my_row_id | FirstName | LastName |
+-----------+-----------+----------+
|         1 | Homer     | Simpson  |
|         2 | Marge     | Simpson  |
|         3 | Lisa      | Simpson  |
+-----------+-----------+----------+
3 rows in set (0.00 sec)

Naming Columns `my_row_id`

Given MySQL automatically names GIPKs my_row_id, we cannot name columns my_row_id if we have GIPKs enabled and we don’t explicitly define a primary key for the table.

Here’s an example of what happens when we try to do that:

CREATE TABLE Person2
(
    my_row_id INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Result:

ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.

In this case I provided a column called my_row_id but I didn’t define a primary key for the table. Since GIPKs are enabled, MySQL tried to create a column called my_row_id behind the scenes but it couldn’t due to one of my columns already having that name.

But we can certainly name a column my_row_id if we explicitly define it as the primary key:

CREATE TABLE Person2
(
    my_row_id INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.01 sec)

We can also create a column called my_row_id if we define another column as the primary key:

CREATE TABLE Person3
(
    PersonId INT NOT NULL PRIMARY KEY,
    my_row_id INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.01 sec)

Checking Whether GIPKs are Enabled

Enabling GIPKs

Creating a GIPK

Checking the GIPK

GIPK in Action

Making a GIPK Visible

Naming Columns my_row_id

Naming Columns `my_row_id`