Fixing ManyToMany Relationships
I have the following legacy database setup:
CREATE TABLE `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) CREATE TABLE `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `category_ids` varchar(50) NOT NULL, PRIMARY KEY (`id`) )
- a string category identifiers separated Comas:
1, 10, 15, 6
. Is there a way to convert this database to a more traditional one (using three tables, one to store relationships) using just SQL and other scripts?
source to share
MySQL doesn't have CROSS APPLY or recursive CTEs that are the simplest routes.
But you only dong it once, so you only need to hack quickly.
First figure out the maximum number of items in the category list ...
SELECT MAX(LEN(category_ids) - LEN(REPLACE(category_ids, ',', '')) + 1) AS max_items FROM items
Then you can do something like this ...
SELECT items.id, SUBSTRING_INDEX( SUBSTRING_INDEX( items.category_ids, ',', map.id -- Get the first 'n' items from the list ), ',', -1 -- Get the last item from (the first 'n' items from the list) ) AS category_id FROM items INNER JOIN ( SELECT 1 as id UNION ALL SELECT 2 as id UNION ALL SELECT 3 as id etc, etc, up to the max number of items found previously ) AS map ON LEN(items.category_ids) - LEN(REPLACE(items.category_ids, ',', '')) + 1 >= map.id
I haven't tested it, but I guess it
SUBSTRING_INDEX('last', ',', -1)
I am not a MySQL expert, so this may not be optimal, but as a one-time quick win in struct type should work ...
source to share