Depending on our configuration, if we create a table without a primary key column, MySQL will automatically create one behind the scenes. This is called a generated invisible primary key (GIPK).

We can normally use statements such as SHOW CREATE TABLE, SHOW COLUMNS, SHOW INDEX or even check the information schema to see if a table has a GIPK.

But this depends on the setting of our show_gipk_in_create_table_and_information_schema system variable.

This variable can be set to ON or OFF. When set to ON, we will see GIPKs in the output of the aforementioned statements. When set to OFF, we won’t see any GIPKs.

Check the Current Setting

We can run the following statement to return the current value of show_gipk_in_create_table_and_information_schema:

SELECT @@show_gipk_in_create_table_and_information_schema;

Result:

+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+

In this case it’s set to 1, which means ON.

That means that GIPKs will be returned whenever we run the various SHOW statements, as well as when we query the various information schema tables.

Change the Setting

As with any other system variable, we can change the setting of show_gipk_in_create_table_and_information_schema with a SET statement:

SET show_gipk_in_create_table_and_information_schema = OFF;

Output:

Query OK, 0 rows affected (0.00 sec)

This effectively removes GIPKs from any of the SHOW statements as well as the information schema.

Example

Let’s run the SHOW COLUMNS statement against a table that I know has a GIPK:

SHOW COLUMNS FROM Person;

Output:

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

This query didn’t return any GIPKs, even though I know it does actually have one. If a GIPK was going to show here, there would be a third row with the details of the GIPK.

The reason it didn’t show any GIPKs is because I set my show_gipk_in_create_table_and_information_schema system variable to OFF in the previous example.

Let’s now set it to ON and then run the SHOW COLUMNS statement again:

SET show_gipk_in_create_table_and_information_schema = ON;
SHOW COLUMNS FROM Person;

Output:

+-----------+-----------------+------+-----+---------+--------------------------+
| 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    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+

This time three rows are returned (each representing a column in the specified table). The first row is the GIPK. MySQL automatically names GIPKs my_row_id and makes them an invisible column and sets them to AUTO_INCREMENT.

So we can see that having show_gipk_in_create_table_and_information_schema set to ON results in the GIPK being visible in the output of such queries, while OFF makes it invisible.