Question 353360 in Mysql Question posted in
The official MySQL documentation can be found here.

For a MYSQL table which has a column containing an array, how do I select all the entries for which the column contains all the elements in a list?

So I have a MYSQL table, and one of the columns (call it TAGS) is an array which contains a list of tag names. For example, a given entry could have a TAGS column value of "TAG A, TAG B, TAG C."

Now I want to select all the entries from the table which have a TAGS column value which contains all of the tag names in a given array. For example, I might want to get all the entries for which TAGS contains TAG A, TAG B, and TAG C.

Right now I have it set up where it will return all the entries with a TAGS column value which contains the exact same array of tag names. However, as the question states, I want to return all the entries with a TAGS column which only contains at least all the given tag names, the TAG column could contain additional tag names and it would still be returned.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    "WHERE find_in_set('TAG A',TAGS) and find_in_set('TAG B',TAGS)"

    This line seems to accomplish what I'm needing to do for now, however I understand for future reference this is improper design

    Credit to @ysth


  2. 0

    You can use FIND_IN_SET function of MySQL.

    Of course, you can use LIKE. But if your tags are comma-seperated and you want to ensure exact matches, it is better to select FIND_IN_SET. However, it requires that your tags do not contain comma themselves.

    SELECT * FROM your_table
WHERE FIND_IN_SET('TAG A', TAGS) > 0
  AND FIND_IN_SET('TAG B', TAGS) > 0
  AND FIND_IN_SET('TAG C', TAGS) > 0;

    If the value of tags column contains a space after comma, you need to remove that space using REPLACE function. Following query shows that.

    SELECT * FROM your_table
WHERE FIND_IN_SET('TAG A', REPLACE(TAGS, ' ', '')) > 0
  AND FIND_IN_SET('TAG B', REPLACE(TAGS, ' ', '')) > 0
  AND FIND_IN_SET('TAG C', REPLACE(TAGS, ' ', '')) > 0;

    In your programming languge, you can create dynamic query. This is Node.js code.

    const tags = ["TAG A", "TAG B", "TAG C"]  // Example array of tags
let sql = "SELECT * FROM TAGS WHERE "

const conditions = []
for (let tag of tags) {
    const condition = `FIND_IN_SET('${tag}', REPLACE(TAGS, ' ', '')) > 0`
    conditions.push(condition)
}
sql += conditions.join(" AND ") + ";"

    You can check in this link how to use FIND_IN_SET function.

    Thank you.

    Login or Signup to reply.
  3. 0

    A better design would be to not store a comma separated string. (It does not matter if you call it an Array, or not)

    It is always possible to generate a comma separated string afterward, using GROUP_CONCAT

    When having a normalized table like (indexes left out, you (might) need to add them yourself!):

    CREATE TABLE `NewTags` (
  `id` int DEFAULT NULL,
  `n` bigint DEFAULT NULL,
  `tag` varchar(200) DEFAULT NULL
)

    one can do:

    SELECT id, GROUP_CONCAT(tag ORDER BY tag) as tags
FROM NewTags
WHERE tag='TAG A' or tag='TAG B' or tag='TAG C'
GROUP BY id
HAving count(*)=3

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search