1

In MySQL you can SELECT SET values from a table using this syntax:

CREATE TABLE `test_set` (
  `int` int(11) DEFAULT NULL,
  `set_2` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT * FROM test_set WHERE set_2 & 1;

or equivalent using = operator.

For a given table:

int | set_2
-----------
12  | a,b
-----------
34  | c,b

I'd like to make a query that would return:

int | set_2
-----------
12  | a
-----------
12  | b
-----------
34  | c
-----------
34  | b

How would such query look in mysql/mariadb? According to the book I am reading, it should be something in the lines of:

SELECT T.int, S as set_value
FROM test_set T, T.set_2 as S 

Similarly, set_gen() function can be applied to reverse normalized representation to the one containing sets:

SELECT T.int, set_gen(T.set_2) 
FROM test_set T
GROUP BY T.int

What is the syntax for denormalization in mysql/mariadb?

1
SELECT DISTINCT `int`, SUBSTRING_INDEX(SUBSTRING_INDEX(`set_2`, ',', `mask`), ',', -1) `set`
FROM `test_set`, 
      -- generate masks based on SET values list count
     (SELECT 1 `mask` UNION 
      SELECT 2 UNION 
      SELECT 3) `mask`
WHERE `set_2` & 1;

fiddle

If server version allows build mask in recursive CTE (dynamic count may be used)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.