Normalize many-to-many data-CodePudding

I have a non-normalized database that establishes a many-to-many relationship this way.

Article table:

| id  | category_ids | some_more_columns |
| --- | ------------ | ----------------- |
| 1   | []           |                   |
| 2   | [1, 2]       |                   |
| 3   | [3]          |                   |

Category table:

| id  | some_more_columns |
| --- | ----------------- |
| 1   |                   |
| 2   |                   |
| 3   |                   |

I want to make a proper many-to-many relationship like:

Article table:

| id  | some_more_columns |
| --- | ----------------- |
| 1   |                   |
| 2   |                   |
| 3   |                   |

Category table:

| id  | some_more_columns |
| --- | ----------------- |
| 1   |                   |
| 2   |                   |
| 3   |                   |

Finally, Article has categories table:

| id  | article_id | category_id |
| --- | ---------- | ----------- |
| 1   | 2          | 1           |
| 2   | 2          | 2           |
| 3   | 3          | 3           |

I want to write a script to populate the table of Article_has_categories using the column of category_ids in the Article table in MySQL. I'm very young in SQL, so this is very overwhelming. Please shine some light!

CodePudding user response：

You'll have to pivot the array of category id's into individual rows. You can do this by joining to a derived table of integers. It looks like the array is in JSON format, so you can use MySQL's JSON_EXTRACT() function.

INSERT INTO Article_has_categories (article_id, category_id)
 SELECT a.id, JSON_EXTRACT(a.category_ids, CONCAT('$[', num.num, ']'))
 FROM Article AS a
 CROSS JOIN (SELECT 0 AS num
  UNION SELECT 1 
  UNION SELECT 2 
  UNION SELECT 3 
  ...as many as the longest list of category_id...
 ) AS num
 WHERE JSON_EXTRACT(a.category_ids, CONCAT('$[', num.num, ']'));

(I have not tested this, but it should get you started.)

If that is too difficult, then an alternative solution is to develop code to do the conversion. You will have to write a program to fetch the category_ids array, explode it into individual values, and loop over them to insert rows into Article_has_categories.

If this is still too difficult, then you should hire a software developer to do it.