CODEWITHSUNDEEP
mysql
Andy
Andy

Reputation: 950

Set Data Type in mySQL

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.

Upvotes: 13

Views: 27987

Answers (4)

Jehad Keriaki
Jehad Keriaki

Reputation: 545

"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");

Upvotes: 0

Jakub Kania
Jakub Kania

Reputation: 16477

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

Upvotes: 0

Barmar
Barmar

Reputation: 780798

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.

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562260

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?

Upvotes: 16

Related Questions