3

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.

2

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

  • 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. – Vaelyr Jul 18 '17 at 17:26
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.
  • 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! – Vaelyr Jul 18 '17 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.