如何计算逗号分隔列表 MySQL 中的项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7020001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count items in comma separated list MySQL
提问by Tomas
So my question is pretty simple:
所以我的问题很简单：
I have a column in SQL which is a comma separated list (ie cats,dogs,cows,) I need to count the number of items in it using onlysql (so whatever my function is (lets call it fx for now) would work like this:
我在 SQL 中有一个列，它是一个逗号分隔的列表（即cats,dogs,cows,）我需要仅使用sql来计算其中的项目数（所以无论我的函数是什么（现在称之为 fx）都可以这样工作：
SELECT fx(fooCommaDelimColumn) AS listCount FROM table WHERE id=...
I know that that is flawed, but you get the idea (BTW if the value of fooCommaDelimColumnis cats,dogs,cows,, then listCount should return 4...).
我知道这是有缺陷的，但你明白了（顺便说一句，如果值为fooCommaDelimColumnis cats,dogs,cows,，那么 listCount 应该返回 4...）。
That is all.
就这些。
回答by zerkms
There is no built-in function that counts occurences of substring in a string, but you can calculate the difference between the original string, and the same string without commas:
没有内置函数可以计算字符串中子字符串的出现次数，但您可以计算原始字符串与不带逗号的相同字符串之间的差异：
LENGTH(fooCommaDelimColumn) - LENGTH(REPLACE(fooCommaDelimColumn, ',', ''))
It was edited multiple times over the course of almost 8 years now (wow!), so for sake of clarity: the query above does not need a + 1, because OPs data has an extra trailing comma.
它在近 8 年的时间里被多次编辑（哇！），所以为了清楚起见：上面的查询不需要+ 1，因为 OPs 数据有一个额外的尾随逗号。
While indeed, in general case for the string that looks like this: foo,bar,bazthe correct expression would be
虽然确实，对于看起来像这样的字符串的一般情况：foo,bar,baz正确的表达式是
LENGTH(col) - LENGTH(REPLACE(col, ',', '')) + 1
回答by Vincent Savard
zerkms' solution works, no doubt about that. But your problem is created by an incorrect database schema, as Steve Wellens pointed out. You should not have more than one value in one column because it breaks the first normal law. Instead, you should make at least two tables. For instance, let's say that you have memberswho own animals:
zerkms 的解决方案是有效的，这一点毫无疑问。但是正如 Steve Wellens 指出的那样，您的问题是由不正确的数据库架构造成的。一列中不应有多个值，因为它违反了第一条正常规律。相反，您应该至少制作两张桌子。例如，假设您有拥有动物的成员：
table member (member_id, member_name)
table member_animal (member_id, animal_name)
Even better: since many users can have the same type of animal, you should create 3 tables :
更好的是：由于许多用户可以拥有相同类型的动物，您应该创建 3 个表：
table member (member_id, member_name)
table animal (animal_id, animal_name)
table member_animal (member_id, animal_id)
You could populate your tables like this, for instance :
你可以像这样填充你的表，例如：
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)
And, to answer your initial question, this is what you would do if you wanted to know how many animals each user has :
而且，为了回答您最初的问题，如果您想知道每个用户拥有多少只动物，您会这样做：
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;
回答by tauanz
Following the suggestion from @zerkms.
遵循@zerkms 的建议。
If you dont know if there is a trailing comma or not, use the TRIM function to remove any trailing commas:
如果您不知道是否有尾随逗号，请使用 TRIM 函数删除所有尾随逗号：
(
LENGTH(TRIM(BOTH ',' FROM fooCommaDelimColumn))
- LENGTH(REPLACE(TRIM(BOTH ',' FROM fooCommaDelimColumn), ',', ''))
+ 1
) as count
Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
参考：http: //dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
I also agree that a refactoring of the tables is the best option, but if this is not possible now, this snippet can do the work.
我也同意重构表是最好的选择，但如果现在不可能，这个片段可以完成工作。
回答by arlomedia
This version doesn't support leading or trailing commas, but supports an empty value with a count of 0:
此版本不支持前导或尾随逗号，但支持计数为 0 的空值：
IF(values, LENGTH(values) - LENGTH(REPLACE(values, ',', '')) + 1, 0) AS values_count
回答by Steve Wellens
The answer is to correct the database schema. It sounds like a many-to-many relationship which requires a junction table. http://en.wikipedia.org/wiki/Junction_table
答案是纠正数据库模式。这听起来像是需要连接表的多对多关系。 http://en.wikipedia.org/wiki/Junction_table
