How to Enable GIPKs in MySQL

In MySQL, GIPK stands for generated invisible primary key. A GIPK is created whenever we create an InnoDB table without explicitly defining a primary key and when we have GIPKs enabled.

To enable GIPKs, we need to set our sql_generate_invisible_primary_key server system variable to ON. By default this is set to OFF, which means GIPKs are disabled by default.

Example

Here’s how to enable GIPKs:

SET sql_generate_invisible_primary_key = ON;

Result:

Query OK, 0 rows affected (0.00 sec)

GIPKs are now enabled.

Now if we create an InnoDB table without a primary key, MySQL will automatically create one for us – a generated invisible primary key to be precise. The GIPK column is always called my_row_id and it’s an AUTO_INCREMENT column so that its value increments with each new row.

Note that the effects of sql_generate_invisible_primary_key apply to tables using the InnoDB storage engine only.

Checking the `sql_generate_invisible_primary_key` Variable

We can verify whether or not GIPKs are enabled by selecting the sql_generate_invisible_primary_key:

SELECT @@sql_generate_invisible_primary_key;

Result:

+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

In this case I got a value of 1, which means ON. Therefore GIPKs are enabled.

Example

Checking the sql_generate_invisible_primary_key Variable

Checking the `sql_generate_invisible_primary_key` Variable