MySql PHP从逗号分隔数据（标签）中选择不同值的计数

How can I select the count of distinct values from data that is stored as comma separated values in MySql? I'll be using PHP to output the data from MySql in the end.

如何从MySql中以逗号分隔值存储的数据中选择不同值的计数？我将使用PHP最终从MySql输出数据。

What's in there, are tags for each post. So in the end, I'm trying to output data just like the way * does with it's tags, like this:

那里有什么，每个帖子都有标签。所以最后，我正在尝试输出数据，就像*使用它的标签一样，如下所示：

tag-name x 5

This is how the data in the table looks like (sorry about the content, but it's a site for recipes).

这就是表中数据的样子（抱歉内容，但它是食谱的网站）。

"postId"    "tags"                                  "category-code"
"1"         "pho,pork"                              "1"
"2"         "fried-rice,chicken"                    "1"
"3"         "fried-rice,pork"                       "1"
"4"         "chicken-calzone,chicken"               "1"
"5"         "fettuccine,chicken"                    "1"
"6"         "spaghetti,chicken"                     "1"
"7"         "spaghetti,chorizo"                     "1"
"8"         "spaghetti,meat-balls"                  "1"
"9"         "miso-soup"                             "1"
"10"        "chanko-nabe"                           "1"
"11"        "chicken-manchurian,chicken,manchurain" "1"
"12"        "pork-manchurian,pork,manchurain"       "1"
"13"        "sweet-and-sour-pork,pork"              "1"
"14"        "peking-duck,duck"                      "1"

Output

产量

chicken             5 // occurs 5 time in the data above
pork                4 // occurs 4 time in the data above
spaghetti           3 // an so on
fried-rice          2
manchurian          2
pho                 1
chicken-calzone     1
fettuccine          1
chorizo             1
meat-balls          1
miso-soup           1
chanko-nabe         1
chicken-manchurian  1
pork-manchurian     1
sweet-n-sour-pork   1
peking-duck         1
duck                1

I'm attempting to select count of all distinct values in there, but since it's comma separated data, there appears to be no way to do this. select distinct will not work.

我试图在那里选择所有不同值的计数，但由于它是逗号分隔的数据，似乎没有办法做到这一点。 select distinct不起作用。

Can you think of a good way in either mysql or using php to get output like the way I've done?

你能想到一个很好的方式在mysql或使用PHP获取输出像我做的那样？

5 个解决方案

#1

Solution

I don't really know how to transform an horizontal list of comma-separated values to a list of rows without creating a table containing numbers, as many numbers as you may have comma-separated values. If you can create this table, here is my answer:

我真的不知道如何将逗号分隔值的水平列表转换为行列表而不创建包含数字的表，因为您可能有逗号分隔值的数字。如果你可以创建这个表，这是我的答案：

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(all_tags, ',', num), ',', -1) AS one_tag,
  COUNT(*) AS cnt
FROM (
  SELECT
    GROUP_CONCAT(tags separator ',') AS all_tags,
    LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags
  FROM test
) t
JOIN numbers n
ON n.num <= t.count_tags
GROUP BY one_tag
ORDER BY cnt DESC;

Returns:

返回：

+---------------------+-----+
| one_tag             | cnt |
+---------------------+-----+
| chicken             |   5 |
| pork                |   4 |
| spaghetti           |   3 |
| fried-rice          |   2 |
| manchurain          |   2 |
| pho                 |   1 |
| chicken-calzone     |   1 |
| fettuccine          |   1 |
| chorizo             |   1 |
| meat-balls          |   1 |
| miso-soup           |   1 |
| chanko-nabe         |   1 |
| chicken-manchurian  |   1 |
| pork-manchurian     |   1 |
| sweet-and-sour-pork |   1 |
| peking-duck         |   1 |
| duck                |   1 |
+---------------------+-----+
17 rows in set (0.01 sec)

See sqlfiddle

---

Explaination

Scenario

1. We concatenate all tags using a comma to create only one list of tags instead of one per row
2. 我们使用逗号连接所有标签，只创建一个标签列表，而不是每行一个
3. We count how many tags we have in our list
4. 我们计算列表中有多少个标签
5. We find how we can get one value in this list
6. 我们发现如何在此列表中获取一个值
7. We find how we can get all values as distinct rows
8. 我们发现如何将所有值作为不同的行
9. We count tags grouped by their value
10. 我们统计按其值分组的标签

Context

Let's build your schema:

让我们构建你的架构：

CREATE TABLE test (
    id INT PRIMARY KEY,
    tags VARCHAR(255)
);

INSERT INTO test VALUES
    ("1",         "pho,pork"),
    ("2",         "fried-rice,chicken"),
    ("3",         "fried-rice,pork"),
    ("4",         "chicken-calzone,chicken"),
    ("5",         "fettuccine,chicken"),
    ("6",         "spaghetti,chicken"),
    ("7",         "spaghetti,chorizo"),
    ("8",         "spaghetti,meat-balls"),
    ("9",         "miso-soup"),
    ("10",        "chanko-nabe"),
    ("11",        "chicken-manchurian,chicken,manchurain"),
    ("12",        "pork-manchurian,pork,manchurain"),
    ("13",        "sweet-and-sour-pork,pork"),
    ("14",        "peking-duck,duck");

Concatenate all list of tags

We will work with all tags in a single line, so we use GROUP_CONCAT to do the job:

我们将在一行中使用所有标签，因此我们使用GROUP_CONCAT来完成工作：

SELECT GROUP_CONCAT(tags SEPARATOR ',') FROM test;

Returns all tags separated by a comma:

返回以逗号分隔的所有标记：

pho,pork,fried-rice,chicken,fried-rice,pork,chicken-calzone,chicken,fettuccine,chicken,spaghetti,chicken,spaghetti,chorizo,spaghetti,meat-balls,miso-soup,chanko-nabe,chicken-manchurian,chicken,manchurain,pork-manchurian,pork,manchurain,sweet-and-sour-pork,pork,peking-duck,duck

河粉，猪肉，炒大米，鸡肉，炒大米，猪肉，鸡肉，Calzone的，鸡肉，意大利宽面条，鸡肉，意大利面条，鸡肉，意大利面条，香肠，意大利面，肉球，味噌汤，相扑火锅，鸡生蛋满洲，鸡，manchurain，猪肉满洲，猪肉，manchurain，糖醋猪肉，猪肉，京鸭，鸭

Count all tags

To count all tags, we get the length of the full list of tags, and we remove the length of the full list of tags after replacing the , by nothing. We add 1, as the separator is between two values.

为了计算所有标签，我们得到完整标签列表的长度，并在替换之后删除完整标签列表的长度。我们加1，因为分隔符在两个值之间。

SELECT LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags
FROM test;

Returns:

返回：

+------------+
| count_tags |
+------------+
|         28 |
+------------+
1 row in set (0.00 sec)

Get the Nth tag in the tag list

We use the SUBSTRING_INDEX function to get

我们使用SUBSTRING_INDEX函数来获取

-- returns the string until the 2nd delimiter\'s occurrence from left to right: a,b
SELECT SUBSTRING_INDEX('a,b,c', ',', 2);

-- return the string until the 1st delimiter, from right to left: c
SELECT SUBSTRING_INDEX('a,b,c', ',', -1);

-- we need both to get: b (with 2 being the tag number)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c', ',', 2), ',', -1);

With such logic, to get the 3nd tag in our list, we use:

有了这样的逻辑，为了在我们的列表中获得第3个标记，我们使用：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(tags SEPARATOR ','), ',', 3), ',', -1)
FROM test;

Returns:

返回：

+-------------------------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(tags SEPARATOR ','), ',', 3), ',', -1) |
+-------------------------------------------------------------------------------------+
| fried-rice                                                                          |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Get all values as distinct rows

My idea is a little tricky:

我的想法有点棘手：

1. I know we can create rows by joining tables
2. 我知道我们可以通过连接表来创建行
3. I need to get the Nth tag in the list using the request above
4. 我需要使用上面的请求获取列表中的第N个标记

So we will create a table containing all numbers from 1 to the maximum number of tags you may have in your list. If you can have 1M values, create 1M entries from 1 to 1,000,000. For 100 tags, this will be:

因此，我们将创建一个表，其中包含从1到您列表中可能包含的最大标记数的所有数字。如果您可以拥有1M值，请创建1到1,000,000的1M条目。对于100个标签，这将是：

CREATE TABLE numbers (
  num INT PRIMARY KEY
);

INSERT INTO numbers VALUES
    ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 ), ( 10 ), 
    ( 11 ), ( 12 ), ( 13 ), ( 14 ), ( 15 ), ( 16 ), ( 17 ), ( 18 ), ( 19 ), ( 20 ), 
    ( 21 ), ( 22 ), ( 23 ), ( 24 ), ( 25 ), ( 26 ), ( 27 ), ( 28 ), ( 29 ), ( 30 ), 
    ( 31 ), ( 32 ), ( 33 ), ( 34 ), ( 35 ), ( 36 ), ( 37 ), ( 38 ), ( 39 ), ( 40 ), 
    ( 41 ), ( 42 ), ( 43 ), ( 44 ), ( 45 ), ( 46 ), ( 47 ), ( 48 ), ( 49 ), ( 50 ), 
    ( 51 ), ( 52 ), ( 53 ), ( 54 ), ( 55 ), ( 56 ), ( 57 ), ( 58 ), ( 59 ), ( 60 ), 
    ( 61 ), ( 62 ), ( 63 ), ( 64 ), ( 65 ), ( 66 ), ( 67 ), ( 68 ), ( 69 ), ( 70 ), 
    ( 71 ), ( 72 ), ( 73 ), ( 74 ), ( 75 ), ( 76 ), ( 77 ), ( 78 ), ( 79 ), ( 80 ), 
    ( 81 ), ( 82 ), ( 83 ), ( 84 ), ( 85 ), ( 86 ), ( 87 ), ( 88 ), ( 89 ), ( 90 ), 
    ( 91 ), ( 92 ), ( 93 ), ( 94 ), ( 95 ), ( 96 ), ( 97 ), ( 98 ), ( 99 ), ( 100 );

Now, we get the numth (num being a row in number) using the following query:

现在，我们使用以下查询获取numth（num是一行数）：

SELECT n.num, SUBSTRING_INDEX(SUBSTRING_INDEX(all_tags, ',', num), ',', -1) as one_tag
FROM (
  SELECT
    GROUP_CONCAT(tags SEPARATOR ',') AS all_tags,
    LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags
  FROM test
) t
JOIN numbers n
ON n.num <= t.count_tags

Returns:

返回：

+-----+---------------------+
| num | one_tag             |
+-----+---------------------+
|   1 | pho                 |
|   2 | pork                |
|   3 | fried-rice          |
|   4 | chicken             |
|   5 | fried-rice          |
|   6 | pork                |
|   7 | chicken-calzone     |
|   8 | chicken             |
|   9 | fettuccine          |
|  10 | chicken             |
|  11 | spaghetti           |
|  12 | chicken             |
|  13 | spaghetti           |
|  14 | chorizo             |
|  15 | spaghetti           |
|  16 | meat-balls          |
|  17 | miso-soup           |
|  18 | chanko-nabe         |
|  19 | chicken-manchurian  |
|  20 | chicken             |
|  21 | manchurain          |
|  22 | pork-manchurian     |
|  23 | pork                |
|  24 | manchurain          |
|  25 | sweet-and-sour-pork |
|  26 | pork                |
|  27 | peking-duck         |
|  28 | duck                |
+-----+---------------------+
28 rows in set (0.01 sec)

Count tags occurences

As soon as we now have classic rows, we can easily count occurrences of each tags.

一旦我们现在有经典行，我们就可以轻松计算每个标签的出现次数。

See the top of this answer to see the request.

请参阅此答案的顶部以查看请求。

#2

Alain Tiembo has a nice answer which explains a lot of the mechanics underneath. However, his solution requires a temporary table (numbers) to solve the problem. As a follow up answer, I am combining all his steps into one single query (using tablename for your original table):

Alain Tiembo有一个很好的答案，解释了很多下面的机制。但是，他的解决方案需要一个临时表（数字）来解决问题。作为后续回答，我将他的所有步骤合并为一个单独的查询（使用原始表的tablename）：

    SELECT t.tags, count(*) AS occurence FROM
    (SELECT
      tablename.id,
      SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.tags, ',', numbers.n), ',', -1) tags
    FROM
      (SELECT 1 n UNION ALL SELECT 2
       UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename
      ON CHAR_LENGTH(tablename.tags)
         -CHAR_LENGTH(REPLACE(tablename.tags, ',', ''))>=numbers.n-1
    ORDER BY
      id, n) t
    GROUP BY t.tags
    ORDER BY occurence DESC, t.tags ASC

See the SQLFiddle for demonstration purposes.

有关演示目的，请参阅SQLFiddle。

#3

First, you should store this using a junction table, with one row per post and tag. Sometimes, however, we cannot control the structure of data we are working with.

首先，您应该使用联结表存储它，每个帖子和标记一行。但是，有时我们无法控制我们正在使用的数据结构。

You can do what you want assuming you have a list of valid tags:

假设您有一个有效标签列表，您可以执行您想要的操作：

select vt.tag, count(t.postid) as cnt
from validtags vt left join
     table t
     on find_in_set(vt.tag, t.tags) > 0
group by vt.tag
order by cnt desc;

#4

The recommended way of doing this is to not store multiple values in a single column, but create an intersection table.

建议的方法是不在单个列中存储多个值，而是创建一个交集表。

So, your tables would have these columns:
1. tags: tag_id, name
2. posts: post_id, category_code
3. int_tags_to_posts: post_id, tag_id

因此，您的表将包含以下列：1。tags：tag_id，name 2. posts：post_id，category_code 3. int_tags_to_posts：post_id，tag_id

To get the counts:
select t.name, count(*) from tags t, posts p, int_tags_to_posts i where i.post_id = p.post_id and i.tag_id = t.tag_id group by i.tag_id order by count(*) desc;

要获得计数：选择t.name，count（*）from tags t，posts p，int_tags_to_posts i其中i.post_id = p.post_id和i.tag_id = t.tag_id group by i.tag_id order by count（*）说明;

#5

This should work:

这应该工作：

SELECT tag, count(0) count FROM (
    SELECT tOut.*, REPLACE(SUBSTRING(SUBSTRING_INDEX(tags, ',', ocur_rank), LENGTH(SUBSTRING_INDEX(tags, ',', ocur_rank - 1)) + 1), ',', '') tag
      FROM (
        SELECT @num_type := if(@id_check = tY.id, @num_type + 1, 1) AS ocur_rank, @id_check := tY.id as id_check, tY.*
          FROM (
            SELECT LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) AS num_ocur, id, tags FROM tablename
          ) tX
          INNER JOIN (SELECT LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) AS num_ocur, id, tags FROM tablename) tY
          INNER JOIN (SELECT @num_type := 0, @id_check := 'some_id') tZ
       ) tOut
     WHERE ocur_rank <= num_ocur + 1
) tempTable GROUP BY tag ORDER BY count DESC;

Replace "tablename" with the name of your table.

将“tablename”替换为表的名称。

This answer was derived from a solution by Jesse Perring posted on this page:

这个答案来自Jesse Perring在此页面上发布的解决方案：

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#c12113

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#c12113