MySQL Update old data after adding new column

Question

I have a table called review, which looks like that

| id | business_id|
|----|------------|
| 1 | B1 |
| 2 | B1 |
| 3 | B1 |
| 4 | B2 |
| 5 | B2 |
| 6 | B2 |

To this table I have added a new column called review_number, which represents an incremental sequence separately for each business (referenced via business_id) and this is handled with BEFORE INSERT trigger. This means for each business the review_number sequence would start from 1 and each new record increments it by one. The new column is BIGINT type and for default value is set to 0 for all existing records.

| id | business_id| review_number |
|----|------------|---------------|
| 1 | B1 | 0 |
| 2 | B1 | 0 |
| 3 | B1 | 0 |
| 4 | B2 | 0 |
| 5 | B2 | 0 |
| 6 | B2 | 0 |

The update trigger is as simple as it can get:

BEFORE INSERT ON review
FOR EACH ROW BEGIN
 SET NEW.`review_number` = (SELECT MAX(review_number) + 1 FROM review WHERE business_id = NEW.business_id);

Problem

My problem and question is, how would I go about updating all the old records after I would update my database where I have live data already. Desired result would be after updating the old data (and when adding new ones after):

| id | business_id| review_number |
|----|------------|---------------|
| 1 | B1 | 1 |
| 2 | B1 | 2 |
| 3 | B1 | 3 |
| 4 | B2 | 1 |
| 5 | B2 | 2 |
| 6 | B2 | 3 |
| 7 | B1 | 4 | <--- new record for B1
| 8 | B2 | 4 | <--- new record for B2

I have tried achieving it with some procedures but haven't found a neat way of helping old records. As much as I know then MyISAM engine supports a way to have multiple auto-increment fields and composite primary key on multiple fields and there the auto-increment would be separate for each PK, which would get me the desired result without triggers, but unfortunately I think I cannot change from InnoDB currently.

Looking for suggestions how to update review_number for all existing records so so these wouldn't be defaulted to 0 after migration scripts finish.

Answer 1

First - your insert trigger may be vulnerable to a race condition, if this is a multi-user system. If two people are inserting a row for the same business_id at the same time, they might be able to get the same MAX(review_number), and thus both set the same review_number. Also, if you enter a new business_id, you may get a NULL value for the review_number (since there are no existing rows, the sub-query would either return NULL or error out). I would recommend setting a unique constraint on business_id and review_number, once you populate the data for the existing rows.

The following should populate all existing rows in review with incremental review_number IDs, starting at 1 for each business ID:

UPDATE `review` r
 INNER JOIN (SELECT id
 ,@review_num := if(@last_bus_id = business_id, @review_num + 1, 1) as `review_number`
 ,@last_bus_id := business_id
 FROM `review`
 ORDER BY business_id, id
 ) sq ON (r.id = sq.id)
 SET r.`review_number` := sq.`review_number`
;

Tested via db-fiddle

• Vaelyr: Thanks, that was exactly what I was looking! I have set the unique constraints in a follow-up migration script as well, the race conditions is taken care of too, I am more incline to move the increment logic to back-end anyway.

Answer 2

You gave away the answer; here are some more details.

1. create a TEMPORARY MyISAM table with a 2-column PK, second column being AUTO_INCREMENT
2. INSERT INTO myisam_table (business_id, id) SELECT business_id, id ORDER BY business_id, id -- to get it to fill in review_number.
3. Do a multi-table UPDATE ... SET your_table.review_number = myisam_table.review_number between the two tables to copy those ids.

• Vaelyr: Didn't think of that, would have solved the problem too, but I found the loop query more quicker and nicer way now. Thanks though!