MySQL - как получить количество частот одного элемента в таблице значений CSV

#mysql #sql

Вопрос:

У меня есть таблица mysql под названием «проекты» с одним полем, содержащим CSV-списки идентификаторов проектов. Предположим, что я не могу изменить структуру таблицы.

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

 select count(*) from projects where '4' in (project_ids);

Это возвращает только 1 результат, что неверно (должно быть 3 результата), но я думаю, что это иллюстрирует то, что я пытаюсь сделать.

 CREATE TABLE `projects` (
  `project_ids` varchar(255) DEFAULT NULL
);
INSERT INTO `projects` (`project_ids`)
VALUES
    ('1,2,4'),
    ('1,2'),
    ('4'),
    ('4,5,2'),
    ('1,2,5');

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

1. Исправьте свою модель данных! Не храните числа в строках! Не храните несколько значений в одном столбце! Правильно объявляйте отношения внешнего ключа!

2. В качестве аргумента предположим, что модель не может быть изменена.

3. Хотя это можно сделать, используя подобное условие, я согласен с комментариями @GordonLinoff

Ответ №1:

Вы могли бы использовать этот подход:

 SELECT COUNT(*)
FROM projects
WHERE CONCAT(',', project_ids, ',') LIKE '%,4,%';

Или использовать FIND_IN_SET для встроенного способа:

 SELECT COUNT(*)
FROM projects
WHERE FIND_IN_SET('4', project_ids) > 0;

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

 PK | project_id
1  | 1
1  | 2
1  | 4
2  | 1
2  | 2
3  | 4
4  | 4
4  | 5
4  | 2
5  | 1
5  | 2
5  | 5

При таком дизайне, если вы хотите найти количество PK, имеющих project_id значение 4, вам понадобится только гораздо более простой (и доступный) запрос:

 SELECT COUNT(*)
FROM junction_table
WHERE project_id = 4;

1. Вы случайно не знаете, есть ли какая-либо встроенная функция mysql, которая достигла бы того же результата?

2. Да, используйте FIND_IN_SET … Я обновлю свой ответ с помощью этого варианта.

3. Ах, я понимаю… выберите count(*) из проектов, где find_in_set(‘4’, project_ids);

Ответ №2:

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

 select count(*) 
  from projects 
 where concat(',',project_ids,',') like '%,4,%';

1. Поверьте мне, я не занимался плагиатом, я написал запрос в то же время, когда вы опубликовали. Далее следует объяснение. Мы ищем шаблон ‘,4,’ и это будет сравниваться со значениями в столбце project_id. Чтобы компенсировать начальные и конечные ‘,’ к полю добавляется ‘,’ с помощью оператора concat