A string of the form '1,2,10,12,30' is stored in the table. Here the user’s groups are registered. How to check if this group is suitable for this user? For example, group 1 should be suitable for '1,2,11 ' but not for '11,21' , so LIKE not suitable. What other options are there? 11 IN(uedb_specializations) works only if the line starts with a given number.

    2 answers 2

    The best solution is to have the `groups' table and keep all groups there separately. Then the solution becomes elementary.

    Wrong decision to enclose the string with commas and use LIKE

     SELECT * FROM mytable WHERE CONCAT(',', groups, ',') LIKE '%,1,%'
    • your wrong decision is working. What problems will I face if I use it? - VK
    • 2
      @WebDev inability to use indexes and, as a result, a very long search. Plus, you are rigidly sharpening the comma separator, and if there is a “comma and a space” somewhere, you will look for an error for a long time. True from this, RLIKE can protect and regular expression, but then the search will work even longer - Anton Shchyrov
    • about speed, I think we will survive, since there will be 100 records there, maximum, besides other than numbers and comma there cannot be other symbols, since everything is rigidly formed - VK

    It will work slowly and sadly. Get an additional table USER_GROUPS . It should have columns

     ID | ID_USER | ID_GROUP

    Then put a unique index on it in the fields ID_USER, ID_GROUP and everything will work great for you.

    • most likely you are right, but I can’t do that, this is a cms table, and I can’t change anything there, just contact .. - VK
    • @WebDev or CMS wrote doodles, or you have an aggregated overview, and somewhere there is a normalized table. - gbg
    • in the case of groups, I may find such a table, but the bottom line is that here is the majority, where you can choose several options stored in this form, you need a general principle of how to work with it - VK