How to add a varchar field for a table already in use?

Question

I've got a mysql database with a table (InnoDB) of Games:

gamerooms
id: bigint(20) unsigned not null auto_increment
PRIMARY KEY (`id`)

I'd like to start generating a UUID value for each row which I can share publicly, something like:

gamerooms
id  |   id_public  |
--------------------
 1  |   abcde
 2  |   ghijk
 3  |   lmnop
   ...

select * from gamerooms where id_public = ...

How do I add this new column, also keeping in mind that there are already records in the table? I'm confused because the column should be marked NOT NULL, but after adding the column, all records that already exist would have empty values.. Do I have to provide a default value?:

ALTER TABLE `gamerooms` ADD COLUMN `id_public` varchar(36) DEFAULT something AFTER `id`

I want to put an index on id_public of course after it's created, so not sure if null values after the column is first created will mess anything up.

Also, I can use varchar(36) with mysqls UUID() output, right?

Thank you

Answer 1

Your ALTER statement is correct:

ALTER TABLE `gamerooms`
ADD COLUMN `id_public` varchar(36) NOT NULL DEFAULT 'something' AFTER `id`

According to my MySQL Pocket Reference, if you don't provide a default value for a column that is defined as NOT NULL:

MySQL picks a value based on the type of the field

In this case, I'm guessing the default would be empty string. Once your column has been added, simply create a new index for the column, and rebuild the index using a null alteration instruction like so:

CREATE INDEX myIndex ON gamerooms(id_public);
ALTER TABLE gamerooms ENGINE = InnoDB;

You may be able to create the index at the same time you do the insert. My MySQL-fu isn't strong enough to know how to do that.