0

This code is work normally SELECT 2 IN('1','2','3') and the result is 1 that means the condition is true but something I don't understand.

I have a associated_roles table with data like this.

-------------------
|role_col         |
-------------------
|1,2,3,4,5        |
-------------------
|1,2              |
-------------------
|1,2,3,4,5,6,9,46 |
-------------------
|1,2,3,5,4,46,333 |
-------------------

The code SELECT replace(role_col,',', '\',\'') from associated_roles will return

--------------------------------
|role_col                       |
--------------------------------
|1','2','3','4','5              |
--------------------------------
|1','2                          |
--------------------------------
|1','2','3','4','5','6','9','46 |
--------------------------------
|1','2','3','5','4','46','333   |
--------------------------------

Finally I want to get row contain 2 and with IN and I have no idea why the below code doesn't work properly.

SELECT * from associated_roles where 2 IN(replace(role_col,',', '\',\''))

It's return nothing like this

--------------------------------
|role_col                       |
--------------------------------
|                               |
--------------------------------
Hoai Ngo
  • 3
  • 5
  • 1
    where do you have a column name called `2`? – treyBake Nov 15 '18 at 11:13
  • the `2` just a value want to check if it's in a comma separate string, it's not a column – Hoai Ngo Nov 15 '18 at 11:15
  • 1
    Your data model is terrible you should consider using normalisation. `IN()` operator works on records not comma separated values.. You are looking for the function `FIND_IN_SET` ( https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set ) which is designed to work with comma separated values – Raymond Nijland Nov 15 '18 at 11:15
  • @HoaiNgo then you're using IN wrong - IN works like this: WHERE `col_name` IN (wanted_value_1, wanted_value_2); – treyBake Nov 15 '18 at 11:17
  • thank for the recommendation, but `FIND_IN_SET` make the performance slower than using the other function – Hoai Ngo Nov 15 '18 at 11:18
  • It doesn't matter that you use the `col_name` or single value like `2`, you can double check at https://www.w3resource.com/mysql/comparision-functions-and-operators/in-function.php @treyBake – Hoai Ngo Nov 15 '18 at 11:24
  • 2
    "but FIND_IN_SET make the performance slower than using the other function " How do you compare performance from a working function vs a function that does not work on your current data model? .... it's not the FIND_IN_SET what makes things slow it's your comma separated values data model which can be hard to index it's can be done with MySQL's generated columns but do you really want to?, it's not where RDBMS databases are designed for if you want to use comma separated values you are better off using a text file or a NoSQL database. – Raymond Nijland Nov 15 '18 at 11:27
  • @HoaiNgo ah didn't know that .. never seen anyone use it that way.. seems easier to match col against wanted values rather than compare a value against a function result per row – treyBake Nov 15 '18 at 11:27
  • "It doesn't matter that you use the col_name or single value like 2, you can double check at " Ok try running `SELECT '1,2,3' IN(2)` which you are basiclly are trying to do here in your data model. `'1,2,3'` is a single value but i can tell you it does not work that way you need `FIND_IN_SET` see demo https://www.db-fiddle.com/f/6xbKtHhNYs8fyBBPGzR18K/2 – Raymond Nijland Nov 15 '18 at 11:32
  • `FIND_IN_SET()` does not use index whereas `IN()` do that, `FIND_IN_SET()` will try find all character in the comma separate string, by the way thank you @RaymondNijland – Hoai Ngo Nov 15 '18 at 11:36
  • 1
    "FIND_IN_SET() does not use index whereas IN() do that" read mine other comment " it's not the FIND_IN_SET what makes things slow it's your comma separated values data model which can be hard to index it's can be done with MySQL's generated columns but do you really want to?" – Raymond Nijland Nov 15 '18 at 11:38
  • Yes can use single value, I just try to make my case easy for you guys understand, in pact I do the same idea as you mention @treyBake – Hoai Ngo Nov 15 '18 at 11:41
  • Ok I understand, can you suggest any way to solve my case as long as don't use `FIND_IN_SET` @RaymondNijland – Hoai Ngo Nov 15 '18 at 11:48
  • "Ok I understand, can you suggest any way to solve my case" The other approach would be to make a SQL number generator to split the strings on the comma with `SUBSTRING_INDEX()` to records (something like this https://stackoverflow.com/questions/46890617/restructuring-a-bad-database-with-php-loops-or-mysql/46892143#46892143 post off mine).. Which still would require a full table scan and would be still far away from ideal but then you could copy the records into a temporary table which you can index and search with the `IN()` operator.. But still normalisation should be the way to go here – Raymond Nijland Nov 15 '18 at 11:55
  • because i hope you see now how hard things are going to get when you need to query and search within comma separted values data.. You should really drop using comma separted values from now on and use normalisation.. – Raymond Nijland Nov 15 '18 at 11:59

0 Answers0