Как подсчитать элементы в списке, разделенном запятыми MySQL

Итак, мой вопрос довольно прост:

У меня есть столбец в SQL, который является разделенным запятой списком (т.е. cats,dogs,cows,). Мне нужно подсчитать количество элементов в нем, используя только sql (так что независимо от моей функции (назовем ее fx на данный момент) например:

 SELECT fx(fooCommaDelimColumn) AS listCount FROM table WHERE id=...

Я знаю, что это неверно, но вы получаете идею (BTW, если значение fooCommaDelimColumn равно cats,dogs,cows,, тогда listCount должен возвращать 4...).

Это все.

Ответ 1

Нет встроенной функции для подсчета вхождений подстроки в строку, но вы можете вычислить разницу между исходной строкой и той же строкой без запятых:

LENGTH(fooCommaDelimColumn) - LENGTH(REPLACE(fooCommaDelimColumn, ',', ''))

Ответ 2

Решение zerkms работает, без сомнения. Но ваша проблема создается неправильной схемой базы данных, как указал Стив Велленс. Вы не должны иметь более одного значения в одном столбце, потому что он нарушает первый нормальный закон. Вместо этого вы должны сделать как минимум две таблицы. Например, скажем, что у вас участники, владеющие животными:

table member (member_id, member_name)
table member_animal (member_id, animal_name)

Еще лучше: поскольку многие пользователи могут иметь одного и того же типа животных, вы должны создать 3 таблицы:

table member (member_id, member_name)
table animal (animal_id, animal_name)
table member_animal (member_id, animal_id)

Вы можете заполнить свои таблицы следующим образом:

member (1, 'Tomas')
member (2, 'Vincent')
animal (1, 'cat')
animal (2, 'dog')
animal (3, 'turtle')
member_animal (1, 1)
member_animal (1, 3)
member_animal (2, 2)
member_animal (2, 3)

И, чтобы ответить на ваш первоначальный вопрос, это то, что вы сделали бы, если бы хотели узнать, сколько животных у каждого пользователя:

SELECT member_id, COUNT(*) AS num_animals
FROM member
INNER JOIN member_animal
    USING (member_id)
INNER JOIN animal
    USING (animal_id)
GROUP BY member_id;

Ответ 3

Следуя предложению @zerkms.

Если вы не знаете, есть ли запятая или нет, используйте функцию TRIM для удаления любых запятых:

(
    LENGTH(TRIM(BOTH ',' FROM fooCommaDelimColumn))
  - LENGTH(REPLACE(TRIM(BOTH ',' FROM fooCommaDelimColumn), ',', ''))
  + 1
) as count

Ссылка: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

Я также согласен с тем, что рефакторинг таблиц является наилучшим вариантом, но если это невозможно сейчас, этот фрагмент может выполнять работу.

Ответ 4

Эта версия не поддерживает начальную или конечную запятую, но поддерживает пустое значение с числом 0:

IF(values, LENGTH(values) - LENGTH(REPLACE(values, ',', '')) + 1, 0) AS values_count

Ответ 5

Ответ заключается в исправлении схемы базы данных. Это звучит как отношение "многие ко многим", которое требует таблицы соединений. http://en.wikipedia.org/wiki/Junction_table