datatype to store a delimited list

Question

I want to store strings like this

"1,5,6,7,9,45,20,45,78,81..."

• Numbers can't be negative or decimal and vary form 0-+-200
• Numbers are separated by ','
• List can be 0-150 items long max.

What Datatype should I use for the column that will hold these strings?

Answer 1

What you're looking to store is referred to as denormalized data. MySQL has some functionality specifically for dealing with this, but the best approach is to store a single value per row, like:

id  |  value
---------------
1   |  1
1   |  5
1   |  6
1   |  7

..and so on. Because a comma separated list:

1. is difficult to look for specific values within it

2. can be generated using MySQL's GROUP_CONCAT function:

     SELECT t.id, 
            GROUP_CONCAT(t.value)
       FROM TABLE
   GROUP BY t.id
   

Here's the CREATE TABLE statement for the table setup I recommend:

DROP TABLE IF EXISTS `example`.`list_values`;
CREATE TABLE  `example`.`list_values` (
  `id` int(10) unsigned NOT NULL default '0',
  `val` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`,`val`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The primary key being both columns ensures you can't have duplicates for a set - remove it if that's not true for the data you want to store.

List can be 0-150 items long max.

You'll need to use a trigger to enforce that business rule.