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`)
)
in which category_ids
- 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)
returns 'last'
.
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