Adding an incrementing value to an existing table in MySQL

I’ve been working on the Yelp dataset, and realized that I had forgotten to have some simple way to order the table. There is a review ID and date, but those can take a lot of time to work with. I wanted to add a row_id field, after creating the table, and then fill it with incrementing numbers. That took a little work to figure out, but here’s a full toy example based on this stackoverflow post. The table is very simple:

I initially populate it with only str values:

insert into table_test(str) values ('qwerty'), ('asdfgh'), ('zxcvbn'), ('qwerty');

That sets values in the table:

I then create the procedure with a delimiter:

/* set delimiter */
DELIMITER $$
/* remove procedure if exists... */
DROP PROCEDURE IF EXISTS insert_it $$
/* create procedure */
CREATE PROCEDURE insert_it ()
BEGIN
    DECLARE varcount INT DEFAULT 1;
    DECLARE varmax INT DEFAULT 4;

    WHILE varcount <= varmax DO
            UPDATE table_test set row_id = varcount where row_id IS NULL LIMIT 1;
            SET varcount = varcount + 1;
        END WHILE;
END $$
/* reset delimiter back to normal */
DELIMITER ;

Then you can run it and check the results

/* call procedure */
CALL insert_it();
select * from table_test;

Which fills out the row_id in the table!