Reputation: 191
In a MySQL table I have a VARCHAR column called ShareID.
I would like to grab all the rows where the ShareID is 1. i.e. ONLY the first and second rows here.
I have tried using the LIKE command, like so:
SELECT * FROM tablename WHERE ShareWithID LIKE '1%';
but this will catch ALL the rows that contain the number 1 in it, i.e. Row #3 which is not what I want.
I would like to run a command that would ONLY return rows #1 and #2 above because they have a ShareID of 1 contained within it.
I've tried a variety of commands, (including REGEXP, and IN) and managed a 'frig' solution where I'd place a comma after EVERY number in the ShareID column, including the last one (i.e. 10, 1,), and then execute this command:
SELECT * FROM tablename WHERE ShareWithID LIKE '%1,%';
But I would rather use a proper solution over a frigged solution.
Any guidance would be most welcome.
Upvotes: 0
Views: 789
Reputation: 563021
...the built-in feature is there to be used
FIND_IN_SET()
is actually not intended to be used for strings containing comma-separated lists. It's intended to be used with MySQL's SET data type. Hence the name FIND_IN_SET()
, not FIND_IN_COMMA_SEPARATED_LIST()
.
It saves having to waste time building a 250,000 row 'table' (was it??) to look after a few columns of IDs, when one column in the original 'table' could do the job just as well.
250k rows is not a problem for MySQL. I manage databases with billions of rows in a given table. If you do basic query optimization with indexes, most queries on a table of 250k rows are just fine.
Whereas using a comma-separated list, you spoil any chance of optimizing queries. An index does not help searching for substrings that may not be the leftmost prefix of the string, and searching for a number in a comma-separated list is basically searching for a substring.
You're making your queries impossible to optimize by using a comma-separated list. Every query using FIND_IN_SET()
will be a table-scan, which will get slower in a linear relationship to the number of rows in your table.
There are other disadvantages to using a comma-separated list besides indexing, which I wrote about in my answer to this old post: Is storing a delimited list in a database column really that bad?
I would rather use a proper solution over a frigged solution.
Then store one id per row. In a relational database, that's the proper solution.
Upvotes: 1
Reputation: 1271241
You should not be storing lists of numbers in a comma-delimited string. This is a really bad idea:
Sometimes, though, we are stuck with other peoples really, really, really, really bad decisions on designing databases. MySQL has a convenient function for this situation:
where find_in_set(1, ShareWithID) > 0
If you have spaces in the string, you need to remove them:
where find_in_set(1, replace(ShareWithID, ' ', '')) > 0
Upvotes: 2
Reputation: 191
The solution to this problem is to use Gordon Linoff's suggestion of the FIND_IN_SET command in conjunction with the correct configuration of the table column in question, like this:
SELECT * FROM tablename WHERE FIND_IN_SET('1', ShareWithID);
However, because the FIND_IN_SET command allows you to find the position of a string within a comma-separated list of strings, you MUST ensure that the contents of the column contains a comma after each item and DOES NOT contain spaces after the comma.
So this column content used in conjunction with the above command will return '0' rows: 111, 1
While this column content will return '1' row: 111,1
As will this one: 33,1
And this one: 44,1,415
Upvotes: 0