In MySQL, GIPKs are invisible by definition. GIPK stands for generated invisible primary key, and it’s basically an invisible column automatically created by MySQL with a primary key constraint.

However, just because GIPKs are automatically created invisible, it doesn’t mean that we can’t “unhide” them – or make them visible.

We can make a GIPK visible just as we would make any other invisible column visible – use the ALTER TABLE statement to set it to VISIBLE.

Syntax

We can “unhide” a GIPK using the following syntax:

ALTER TABLE table_name 
ALTER COLUMN my_row_id SET VISIBLE;

All we do is change table_name to the name of the table that contains the GIPK.

Example

Let’s run a SELECT * statement against a table with a GIPK:

SELECT * FROM Person;

Result:

+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Homer     | Simpson  |
| Marge     | Simpson  |
| Lisa      | Simpson  |
+-----------+----------+

From these results, there’s nothing to suggest that there’s a GIPK on the table. But that’s not to say that there isn’t one. Given GIPKs are invisible by default, we wouldn’t see it with a SELECT * query (i.e. queries that use the asterisk wildcard to return all columns).

To have the GIPK column returned, we need to explicitly refer to it in our SELECT list:

SELECT
    my_row_id,
    FirstName,
    LastName
FROM Person;

Result:

+-----------+-----------+----------+
| my_row_id | FirstName | LastName |
+-----------+-----------+----------+
|         1 | Homer     | Simpson  |
|         2 | Marge     | Simpson  |
|         3 | Lisa      | Simpson  |
+-----------+-----------+----------+

This time the GIPK is returned. MySQL always names GIPKs my_row_id, and so that’s how I knew what column name to use in the SELECT list.

Before we “unhide” the GIPK, let’s take a quick look at the CREATE TABLE statement that would be used to recreate this table:

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 the GIPK defined as the first column and it’s got the INVISIBLE keyword against it, meaning that it’s an invisible column. We can also see the PRIMARY KEY clause at the end of the statement, which sets the my_row_id column as the primary key.

OK, now let’s “unhide” the GIPK (set it to VISIBLE):

ALTER TABLE Person 
ALTER COLUMN my_row_id SET VISIBLE;

That’s all.

The GIPK column is now visible. We can confirm this by running 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,
  `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 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This time the INVISIBLE part is nowhere to be found, which means that the column is now visible.

Now let’s run our SELECT * statement again:

SELECT * FROM Person;

Result:

+-----------+-----------+----------+
| my_row_id | FirstName | LastName |
+-----------+-----------+----------+
|         1 | Homer     | Simpson  |
|         2 | Marge     | Simpson  |
|         3 | Lisa      | Simpson  |
+-----------+-----------+----------+

As expected, the GIPK column now appears in the results.