When we have a table with a generated invisible primary key (GIPK) in MySQL, we can usually see its definition when we use various SHOW statements such as SHOW CREATE TABLE, SHOW COLUMNS, and SHOW INDEX, as well as when we query information schema tables such as information_schema.columns.

But there is a way of hiding the GIPK from such statements. It all comes down to the show_gipk_in_create_table_and_information_schema variable. Yes, there’s actually a system variable that allows us to hide GIPKs from the output of various SHOW statements and information schema tables.

Hide GIPKs from SHOW and information_schema Output

We can hide GIPKs from the output of various SHOW statements and information schema queries by setting our show_gipk_in_create_table_and_information_schema variable to 0 or OFF:

SET show_gipk_in_create_table_and_information_schema = OFF;

Result:

Query OK, 0 rows affected (0.00 sec)

The default setting is 1, which means ON. In other words, GIPKs are visible to the SHOW statements and information schema output by default. So by setting it to 0, we’re making GIPKs invisible to those statements.

Check the Setting

We can use a SELECT statement to check the system variable:

SELECT @@show_gipk_in_create_table_and_information_schema;

Result:

+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+

The value of 0 tells us that GIPKs are invisible to the various queries mentioned above.

Example

To demonstrate the effect of the above setting, let’s run the SHOW CREATE TABLE statement against a table that I know contains a GIPK:

SHOW CREATE TABLE Person;

Result:

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                             |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Person | CREATE TABLE `Person` (
  `FirstName` varchar(20) DEFAULT NULL,
  `LastName` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The output here shows us that the table consists of two columns. None of them are primary keys.

However, what it doesn’t show us is that the table contains a GIPK.

As mentioned, it’s not just the SHOW statements that are affected. It also hides GIPKs from information schema tables like information_schema.columns:

SELECT 
    COLUMN_NAME, 
    ORDINAL_POSITION, 
    DATA_TYPE, 
    COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "Person"
ORDER BY ORDINAL_POSITION;

Result:

+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| FirstName   |                2 | varchar   |            |
| LastName    |                3 | varchar   |            |
+-------------+------------------+-----------+------------+

Let’s make GIPKs visible to these statements:

SET show_gipk_in_create_table_and_information_schema = ON;

Now let’s run the SHOW CREATE TABLE statement again:

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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This time we can see a column called my_row_id, which is the GIPK column (MySQL automatically names all GIPKs my_row_id).

Let’s run our query against the information_schema.columns table again:

SELECT 
    COLUMN_NAME, 
    ORDINAL_POSITION, 
    DATA_TYPE, 
    COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "Person"
ORDER BY ORDINAL_POSITION;

Result:

+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| my_row_id   |                1 | bigint    | PRI        |
| FirstName   |                2 | varchar   |            |
| LastName    |                3 | varchar   |            |
+-------------+------------------+-----------+------------+

We can see the GIPK as expected.

As mentioned, this is the default setting – GIPKs are visible to such statements unless we explicitly hide them like we did above.