Set Data Type in mySQL

Question

My knowledge of relational databases is more limited, but is there a SQL command that can be used to create a column that contains a set in each row?

I am trying to create a table with 2 columns. 1 for specific IDs and a 2nd for sets that correspond to these IDs.

I read about

http://dev.mysql.com/doc/refman/5.1/en/set.html

However, the set data type requires that you know what items may be in your set. However, I just want there to be a variable-number list of items that don't repeat.

Answer 1

It would be much better to create that list of items as multiple rows in a second table. Then you could have as many items in the list you want, you could sort them, search for a specific item, make sure they're unique, etc.

See also my answer to Is storing a delimited list in a database column really that bad?

Answer 2

No, there's no MySQL data type for arbitrary sets. You can use a string containing a comma-delimited list; there are functions like FIND_IN_SET() that will operate on such values.

But this is poor database design. If you have an open-ended list, you should store it in a table with one row per value. This will allow them to be indexed, making searching faster.