12

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Andy
  • 942
  • 1
  • 10
  • 23
  • 3
    You don't want that, look into [database normalization](http://en.wikipedia.org/wiki/Database_normalization), you probably want a second table with `(id,value)`, where a 2nd (3rd, 4th,..) values would be a _new_ row with the same `id` again, and the new/other `value`. – Wrikken Jun 11 '13 at 21:29

4 Answers4

16

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?

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Would that mean that I would need to create a separate table for each list? Hence, I would need either a different name for each of the list tables? – Andy Jun 11 '13 at 23:24
  • Yes. Think of this analogy: you would also need a different variable name for each value or array you use in application code! :-) – Bill Karwin Jun 12 '13 at 00:56
3

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
0

MySQL doesn't support arrays, lists or other data structures like that. It does however support strings so use that and FIND_IN_SET() function: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
0

"SET" data type won't be a good choice here.
You can use the "VARCHAR" and store the values in CSV format. You handle them at application level.
Example: INSERT into my_table(id, myset) values(1, "3,4,7");

Jehad Keriaki
  • 545
  • 5
  • 10
  • I would never suggest someone store a delimited list in a database column. See the response by @BillKarwin above. And see his other post: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574 – ajon Aug 27 '15 at 23:31