Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Tuesday, June 12, 2007

Unique strings in a text field

So, I want to reduce data usage of a text field, by storing unique strings separated by a delimiter. So to do so I came up with this:

Given a table


CREATE TABLE `hmm` (
`a` int(10) unsigned NOT NULL default '0',
`b` text NOT NULL,
`c` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO hmm VALUES (1, 'hmm8',1) ON DUPLICATE KEY UPDATE b = IF(FIND_IN_SET('hmm8', b)>0, b, CONCAT_WS(',',b, 'hmm8')), c = c+1;

SELECT * FROM hmm;
+---+--------------------------+---+
| a | b | c |
+---+--------------------------+---+
| 1 | hmm4,hmm5,hmm6,hmm7,hmm8 | 7 |
+---+--------------------------+---+



Let's break apart the INSERT statement, if column 'a' produces a duplicate ON DUPLICATE KEY UPDATE will issue an IF statement

FIND_IN_SET will return the position of the found string. The logic of IF say if FIND_IN_SET is is greater then 0 i.e. the string was found, update column b with b itself (do nothing) else concat the column with the column contents and the new string separated by a comma. Additionally in all cases increment column c by 1.

Why do it this way? Well I removed the need to do a SELECT and provided a solution to store distinct text values instead of the raw value, thus saving space and resources on network transfers etc.

A little quick statement that might be useful for others.