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?

+3


source to share


1 answer


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 ...

+1


source







All Articles