#mysql #sql
#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