CREATE TABLE tally(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO tally (n)
SELECT
NULL
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
;
Test Table: Code (SQL)
DROP TABLE IF EXISTS `content`;
CREATE TABLE IF NOT EXISTS `content` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`chub` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
INSERT INTO `content` (`id`, `chub`) VALUES
(1, '8,10'),
(2, '23'),
(3, NULL),
(4, '8'),
(5, '8,10'),
(6, '10,11');
Query: Code (SQL)
SELECT
`tag`,
COUNT(*) AS `num_rows`
FROM
(
SELECT
c.`id`,
SUBSTRING_INDEX(SUBSTRING_INDEX(c.`chub`, ',', n.`n`), ',', -1) AS `tag`
FROM
`content` c
CROSS JOIN
tally `n`
WHERE
n.`n` <= 1 + (CHAR_LENGTH(c.`chub`) - CHAR_LENGTH(REPLACE(c.`chub`, ',', '')))
) q
WHERE
CHAR_LENGTH(`tag`) > 0
GROUP BY
`tag`
ORDER BY
CONVERT(`tag`, SIGNED INTEGER) ASC;