CODEWITHSUNDEEP
mysql
Glenn
Glenn

Reputation: 265

datatype to store a delimited list

I want to store strings like this

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

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

Upvotes: 1

Views: 1380

Answers (2)

user2099484
user2099484

Reputation: 4559

OMG Ponies' response is the 'traditional' SQL-oriented response but does not really address the question. Postgres has built-in list types for columns. If you do as I have and more-or-less given up on building applications entirely in SQL, then in Python, for example, you can pickle your list into a string, store it in the varchar column in MySQL and then unpickle it on retreival before using it. Using pickle ensures that quoted strings and other peculiarities in the list will be taken care of correctly.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 6

Related Questions