SQL: How to search set datatype by array?-CodePudding

I want to know if the data exist in set datatype by using an array.

For example: The table is

id  role
1   'a'
2   'b'
3   'a','b','c'

$role = ['a']

SQL = SELECT * FROM ... WHERE $role in role (have 'a')

And the result is

id  role
1   'a'
3   'a','b','c'

Or $role = ['a','b']

SQL = SELECT * FROM ... WHERE $role in role (have 'a' and 'b')

And the result is

id  role
3   'a','b','c'

I tried to use FIND_IN_SET(('a','b'),role)>0; but it's not work. I need to use a loop. Have any method better than this?

CodePudding user response：

MySQL doesn't have an array type. What you are using is a string, that happens to contain commas.

The better method in a relational database is to store multi-valued attributes in a second table, with one value per row.

CREATE TABLE role (
  entity_id INT NOT NULL,
  role CHAR(1) NOT NULL,
  PRIMARY KEY(entity_id, role),
  FOREIGN KEY (entity_id) REFERENCES mytable (id)
);

INSERT INTO role VALUES
(1, 'a'), (2, 'b'), (3, 'a'), (3, 'b'), (3, 'c');

If you need to find an entry that has both role 'a' and role 'b', this is called relational-division. There are several solutions when using a normalized table. Here's one:

SELECT mytable.*
FROM mytable 
JOIN role AS r1 ON mytable.id = r1.entity_id
JOIN role AS r2 ON mytable.id = r2.entity_id
WHERE r1.role = 'a' AND r2.role = 'b';