Note: I did look up other answers, but they all seem to be for MySQL 5.x. MySQL 8 has made huge strides in ease of altering tables so I am looking for mySQL 8 specific answers.

I’m using MySQL 8.0.36

I need to add a new column to a large table that must be unique (null OK) and that has an index on that column.

I thought, yeah!, MySQL 8 now has ALGORITHM=INSTANT for adding columns. However, ALGORITHM=INSTANT isn’t accepted for making the column unique or adding an index. So I did:

ALTER TABLE mytable ADD COLUMN new_col CHAR(20), ALGORITHM=INSTANT;

As promised, it was instant. 0.97 seconds.

Then:

CREATE INDEX xx ON mytable(new_col) ALGORITHM=INPLACE LOCK=NONE;

That took 7 minutes, but didn’t lock the database. So OK.

However, lock=None isn’t accepted for unique constraint. It gives me a syntax error. I’d have to do:

ALTER TABLE mytable ADD UNIQUE (new_col), ALGORITHM=INPLACE;

I’m afraid to do that. I can’t lock my production table for 7 minutes or however long it’s going to take.

Am I missing something? MySQL 8 adding this powerful instant algorithm for adding columns, but I still can’t do what I need.

My work-around, which I’ve used in the past, was to create a new table with the new column and join in my queries. That gets messy!