One of the more recent additions to MySQL’s list of system variables is the sql_generate_invisible_primary_key variable. This variable was introduced in MySQL 8.0.30 along with the introduction of generated invisible primary keys (GIPKs).

The purpose of this variable is to allow us to specify whether or not the system will generate a GIPK when we omit a primary key from a table’s definition when creating the table.

By default the sql_generate_invisible_primary_key is set to OFF, which basically means that GIPKs are disabled by default. But we can change this to ON in order to enable GIPKs.

View the Current Setting

We can check the current value of the sql_generate_invisible_primary_key variable like this:

SELECT @@sql_generate_invisible_primary_key;

Result:

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

So we can use a simple SELECT statement to select the value. We prefix the variable name with two @ symbols.

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

Change the Setting

We can change the setting of sql_generate_invisible_primary_key with a SET statement:

SET sql_generate_invisible_primary_key = ON;

This setting means that 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.

Example of Usage

Now that I’ve set the sql_generate_invisible_primary_key system variable to ON, if I create a table without explicitly defining a primary key, then MySQL will automatically add its own primary key – a GIPK – to the table:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

So here, I created a table without a primary key. But MySQL created a GIPK behind the scenes.

Let’s run a SHOW COLUMNS statement to take a look at all of the columns in our table:

SHOW COLUMNS FROM Person FROM test;

Result:

+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| FirstName | varchar(20)     | YES  |     | NULL    |                          |
| LastName  | varchar(20)     | YES  |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+

We can see that there’s an invisible column called my_row_id that’s set as the primary key for the table. It’s also an AUTO_INCREMENT column (which means that its value automatically increments with each row). This is the GIPK.

Let’s now change sql_generate_invisible_primary_key to OFF and then drop the table and create it again using the same definition:

SET sql_generate_invisible_primary_key = OFF;
DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

SHOW COLUMNS FROM Person FROM test;

Result:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| FirstName | varchar(20) | YES  |     | NULL    |       |
| LastName  | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

This time no GIPK was created.

One important thing to remember is that there’s also another system variable that determines whether or not GIPKs are visible in the various SHOW statements like above, as well as in the output of information schema queries.

So a scenario could arise where SHOW COLUMNS doesn’t show a GIPK against the table when in fact one does exist.

The system variable in question is show_gipk_in_create_table_and_information_schema. The default setting for this variable is ON, which means that GIPKs are visible in the various SHOW statements and information schema output. But if this is set to OFF, then you still won’t see the GIPK (if one exists) in such statements.

Anyway, that’s just something to remember when checking for the existence of GIPKs.