Good day.

There is a third-party handler that creates an array array (32,15,42,64) ;

On my part, there are records in the database in which one of the fields contains the same data but in an arbitrary order of 15.42 322.64 . The records cannot repeat the same data set from the array, the data is always numeric. Individual elements of the array are valid 32 can be. The number of records in the database increases quickly enough. Make a LIKE request for one element and then process the record array_diff 'in search of the long needed in my opinion (perhaps I am mistaken).

Question: Is there a way to do this by querying the database without specifying the exact order?

  • one
    @Tehek, and normalize the table is not an option? That is, instead of one record of the form ("id", "1,2,3,4") make 4 entries ("id", "1") , ("id", "2") , ("id", "3") , ("id", "4") . - fori1ton
  • values ​​can be more than 4, adding a column each time is not the best option. The table structure is approximately like id, team_name, regdate, tag, players, ... Sobst this field is players and needs to be checked. Players in one team can be 5 and in another 15. - Tehek
  • those. Is it more profitable for you to keep a bunch of identical records for each player of a team than to sort through a smaller number of records in the same way? In principle, I agree that in the rest of the program it will be somewhat easier to do the processing, if not to do explode, implode on every second function. - Tehek
  • @Tehek, these records can (and should) be stored in a separate table. That is, you will have a separate teams table in which you will store team information (id, team_name, regdate, tag), and a separate team_members table in which you will store lists of players in each team (team_id, player_id). - fori1ton
  • Yes, here I obviously did not think, thanks for the help. Will I go to redo the structure of the database and a ton of code :( PS How to put the name of the interlocutor in a comment in the hashcode ? - Tehek

1 answer 1

@Tehek , not the best option (to put it mildly), you have already done. Now you should think about how to fix it all. @ fori1ton , directs you in the right direction, even if there are hundreds of parameters. If you are going to stand on your own, then please, here is an option .

 SELECT `id`, `nums` FROM `mytbl` WHERE FIND_IN_SET('42', `nums`) AND FIND_IN_SET('32', `nums`) AND FIND_IN_SET('15', `nums`) AND FIND_IN_SET('64', `nums`);