Storing multiple values in a single cell in MySQL

Question

I'm storing multiple numbers in a MySQL cell by using a delimiter (eg "1,5,10") and the TEXT datatype.

How do I perform lookups like this?

SELECT * FROM MyTable WHERE MultiVals CONTAINS "5"

And lastly, is this the preferred approach to store such values? I'm using this like a linker table, to link certain rows to multiple other rows in another table (via IDs). Since I'm trying to minimize the filesize of the DB, I thought this would be a more compact approach to linking instead of using another table like this:

Person ID       Product ID
-----------     -----------
3               1
3               2
3               3
7               5
7               7

Answer 1

It's advised to create a 3rd so called mapping table between the two linked table with two foreign key ID columns.

Project ID       Tag ID
-----------     -----------
3               1
3               2
3               3
7               5
7               7

Using these, the two foreign keys acts as an index too. So it will speed up dramatically the lookup compared to a FIND_IN_SET method. You are minimizing the file size, as the TEXT data type holds empty spaces for unused bits, while the numeric storage is the best, and uses minimal local storage.

Anyway if you want to keep your original way you go this:

SELECT * FROM MyTable WHERE FIND_IN_SET(5,MultiVals) >0

this will make sure the values are matched by using comma-s so a value of 5 will match just "5" and not "15"

Answer 2

Avoid this if at all possible as it violates the rules of database normalization and makes it difficult to index such values. Your values could be kept in a separate look-up table or similar.

If you really have no choice but to live with this design, then you can query the values like this:

select * from mytable where concat(',', multivals, ',') like '%,5,%' 

which will most likely kill performance for anything other than trivial amounts of data, or write a function that splits the row (based on your delimiter ",") and checks the resulting array, or use fulltext indexing.

EDIT: the find_in_set operator is much more elegant than using like, although I've no idea as to how it is implemented under the covers.

Answer 3

it is a bad idea like the other posts said, but you can still perform it using a simple regex

.. where field RLIKE '(^|,)5($|,)'

Answer 4

You shouldn't do that. You should create a table that links to your myTable and holds values in rows. There are too many reasons why you should do it like that, but my personal favorite is - consider it investment for the future.

Using your delimited data you will sooner or later run into problems, either due to increased load or large quantities of data.

MySql, as any other relational database, is way faster when searching over int's and the like than poking around strings. No to mention that string indexes cost more dn resources to maintain them.

All said, if you don't plan doing any serious sutff (at least milion of records in MyTable or more than 50 values in one MultiVal field) you can keep doing what you've been doing for now. - you won't see any performance gain.