I need to store a string of comma separated values.
'1,23,47' or '1' or '' (null)
What data type would work best for comma separated values?
Note: I am only asking which data type would be appropriate for this scenario.
views:
116answers:
5
+2
A:
Literal answer: a VARCHAR of some sort
A better answer: Don't store a list of comma separated values. Store one value per row, and use a SELECT query with GROUP_CONCAT to generate the comma separated value when you access the database.
Jason S
2009-12-17 04:21:52
Is group_concat a mysql only extension?
jpartogi
2009-12-17 04:27:00
@jpartogi: SQLite is the only other db I've come across that has a GROUP_CONCAT function.
OMG Ponies
2009-12-17 04:30:02
not sure... probably.
Jason S
2009-12-17 04:30:32
Yes...group_concat is mysql only
Luke101
2009-12-17 04:34:45
Except when it's SQLite: http://www.sqlite.org/lang_aggfunc.html
OMG Ponies
2009-12-17 06:51:11
+1
A:
VARCHAR, it can store anything.
TEXT if you think it will be long.
ari_aaron
2009-12-17 04:22:02
+2
A:
Depending on how big the string you expect world determine the size of varchar. If you have no idea how long the string would be you can just use TEXT data type.
Luke101
2009-12-17 04:24:14
+1
A:
If you will never need to query specific sub-items in the list of values, then use varchar. However, if you will need to query the values I strongly recommend you consider a different database design, such as a value table where each row contains a join key to the main table and a single value.
Jim Garrison
2009-12-17 04:24:46