With MySQL 8.0.23, the concept of invisible columns was introduced. Columns marked as invisible do not appear in standard SELECT * queries and don’t require changes to existing application code. Later, in MySQL 8.0.30, support for automatically generated invisible primary keys (GIPKs) was added for InnoDB tables created without an explicit primary key. This enhancement, controlled by the sql_generate_invisible_primary_key server variable, allows MySQL to add a GIPK to any table lacking a primary key. However, there are scenarios where an invisible primary key must be manually added to an existing table.
Procedure for Adding an Invisible Primary Key
We’ll apply a Data Definition Language (DDL) command to the table to add an invisible primary key. Let’s walk through an example.
1. Inspect the Table Structure
First, inspect the table to understand its current structure.
|
1 |
mysql > SHOW CREATE TABLE vinnie\G |
Example Output:
|
1 2 3 4 5 6 7 |
*************************** 1. row *************************** Table: vinnie Create Table: CREATE TABLE `vinnie` ( `i` int DEFAULT NULL, `c` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
2. Add the Invisible Primary Key
Run the following command to add an invisible primary key:
|
1 |
ALTER TABLE vinnie ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY INVISIBLE, ALGORITHM=inplace, LOCK=shared; |
Example Output:
|
1 2 |
Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 |
3. Verify the Change
After running the command, verify the new structure of the table to ensure the invisible primary key was successfully added:
|
1 |
mysql > SHOW CREATE TABLE vinnie\G |
Example Output:
|
1 2 3 4 5 6 7 8 9 |
*************************** 1. row *************************** Table: vinnie Create Table: CREATE TABLE `vinnie` ( `i` int DEFAULT NULL, `c` varchar(50) DEFAULT NULL, `id` int NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
4. Confirm the Data Remains Unaffected
Ensure the table data remains unchanged:
|
1 |
mysql > SELECT * FROM vinnie; |
Expected Output:
|
1 2 3 4 5 6 7 8 |
+------+------+ | i | c | +------+------+ | 1 | b | | 1 | b | | 1 | b | +------+------+ 3 rows in set (0.00 sec) |
Important Considerations
Adding an invisible primary key has some performance and operational considerations:
•In-Place Rebuild: When a primary key is added, MySQL rebuilds the table in place, a resource-intensive process. This operation uses ALGORITHM=INPLACE, which can reduce locking time but may not be supported if the columns need conversion to NOT NULL.
•Clustered Index Impact: Changing the clustered index requires copying table data, making it ideal to define primary keys at table creation rather than altering them later.
•Parameter Adjustments for Heavy Write Workloads: If the table receives high write traffic, consider increasing the innodb_online_alter_log_max_size parameter to accommodate larger temporary log files during the DDL operation.
Using pt-online-schema-change
The pt-online-schema-change tool from Percona Toolkit is widely used for making online schema changes with minimal locking. However, adding an invisible primary key with this tool is not recommended due to potential compatibility issues. An attempt to add an invisible primary key using pt-online-schema-change may result in errors, as shown below:
|
1 2 3 |
$ pt-online-schema-change --alter "ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY INVISIBLE" \ --user=root --password=msandbox --host=127.0.0.1 --port=8033 --execute \ D=test,t=vinnie; |
Example Output:
|
1 2 3 |
The new table index PRIMARY would be used for the DELETE trigger, but it uses column id which does not exist in the original table and the original table does not have a PRIMARY KEY or a unique index to use for the DELETE trigger. Dropping new table... Dropped new table OK. |
Making the Primary Key Visible
If you decide to make the invisible primary key visible, use this command:
|
1 |
ALTER TABLE vinnie ALTER COLUMN id SET VISIBLE; |
Confirm the visibility change:
|
1 |
mysql > SELECT * FROM vinnie; |
Expected Output:
|
1 2 3 4 5 6 7 |
+------+------+----+ | i | c | id | +------+------+----+ | 1 | b | 1 | | 1 | b | 2 | | 1 | b | 3 | +------+------+----+ |
References
For further details, consult:
•MySQL 8.0 Invisible Columns Documentation
•InnoDB Online DDL Operations Documentation
This guide should equip you to add and manage invisible primary keys in MySQL 8.0+, improving table flexibility while maintaining application compatibility.