Is it possible in mysql to dynamically translate IDs stored in a string?

user_3396705 0
Viewed 49

I'm importing comment data in mysql from one database to another where some data may already exist. Relevant here, our comments table has a column display_text of type text. Inside this column we have mentions to other users stored in the format "@{{"user":id}}@" where id is a number representing the user's id in another table. It is possible for a single comment to have multiple mentions needing updated ids as well.

Since these users may already exist in the new database, part of our import process populates an id_translations table with old_id and new_id columns mapping the user's id in the source database to their id in the new database. Is it possible to write a mysql UPDATE statement to find the format of our mentions, and replace only the id with the new id?

I already have a method for selecting only the comments that will need ids updated, so statements can be written as though they would update the entire table.

MySQL version 5.6. Here's a sample of what my tables look like.

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(6) unsigned NOT NULL,
  `display_text` text NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `id_translations` (
  `old_id` int(6) unsigned NOT NULL,
  `new_id` int(6) unsigned NOT NULL,
  PRIMARY KEY (`old_id`, `new_id`)
);

INSERT INTO `comments` (`id`, `display_text`) VALUES
(1, 'Hey @{{"user":12}}@, look at this'),
(2, 'Thats pretty cool'),
(3, '@{{"user":41}}@ could you take a peek at this?');

INSERT INTO `id_translations` (old_id, new_id) VALUES (12, 100), (41, 101);

The goal would be for comments to look like:

1, 'Hey @{{"user":100}}@, look at this'
2, 'Thats pretty cool'
3, '@{{"user":101}}@ could you take a peek at this?'
mysql replace
1 Answers
CREATE PROCEDURE replacing ()
BEGIN
    REPEAT
        UPDATE comments
        SET display_text = CONCAT( LEFT(display_text, LOCATE('@{{"user":', display_text) - 1),
                                   CHAR(0),
                                   ( SELECT new_id 
                                     FROM id_translations 
                                     WHERE old_id = 0 + SUBSTRING(display_text FROM LOCATE('@{{"user":', display_text) + 10)),
                                   CHAR(1),
                                   SUBSTRING(display_text FROM LOCATE('}}@', display_text) + 3))
        
        WHERE LOCATE('@{{"user":', display_text);
    UNTIL !ROW_COUNT() END REPEAT;
    UPDATE comments
    SET display_text = REPLACE(REPLACE(display_text, CHAR(0), '@{{"user":'), CHAR(1), '}}@');
END

DEMO fiddle

user_101387340
Related