Обработка тегов в базе данных SQL

Это то, что хотя бы раз возникает у инженеров, работающих с веб-приложениями. Есть несколько способов добиться этого, с разными достоинствами и недостатками.

Заявление об ограничении ответственности

Эта статья посвящена хранению тегов в базе данных SQL; это не означает, что это лучший или единственный способ сделать это. Фактически, есть много других возможностей, доступных в таких системах, как Redis, которые делают фантастическую работу с этим. Однако, если вы застряли в SQL, чувствуете, что нет необходимости использовать другой инструмент или просто имеете существующие решения, подобные приведенным ниже, это может быть полезно в качестве шпаргалки.

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

Давайте перейдем к отношениям

Большинство людей порекомендуют, если вы используете реляционный язык, такой как SQL, вы должны хранить данные в реляционной манере, имеет смысл, верно? Если вы сделали это до того, как следующий код будет иметь смысл и даже может показаться очень знакомым:

CREATE TABLE artist (
    artist_id INTEGER,
    name      VARCHAR(64),
    PRIMARY KEY (artist_id)
);
CREATE TABLE artist_tag (
    artist_id INTEGER,
    tag       VARCHAR(16),
    PRIMARY KEY (artist_id, tag)
);

У вас может быть третья таблица посередине, которая объединяет уникальные теги с художниками, и это не такой уж большой шаг вперед по сравнению с тем, что у нас есть - я просто пока буду упрощен.

Теги в отношении ИЛИ; то есть получить любого исполнителя, у которого есть хотя бы один из следующих тегов, довольно просто:

-- Find all the artists from any of the 3 decades.
SELECT DISTINCT artist_id, name
FROM artist
JOIN artist_tag USING (artist_id)
WHERE tag IN ('60s', '70s', '80s’);

Большинство людей придерживаются отношения И; именно здесь вы хотите, чтобы исполнитель содержал все теги в данном наборе. Некоторые говорят об этом - вероятно, самый простой - добавить дополнительное предложение HAVING:

-- We only want artists that are british AND rock.
SELECT artist_id, name
FROM artist
JOIN artist_tag USING (artist_id)
WHERE tag IN ('british', 'rock')
GROUP BY artist_id
HAVING count(*) = 2;

count(*) = 2 более гибкий, чем вы думаете. Он также позволяет вам указать, сколько тегов должно соответствовать, чтобы вы могли сказать любые 3 из 5 тегов:

WHERE tag IN ('british', 'rock', '60s', 'pop', 'alternative')
HAVING count(*) >= 3

Если ваши теги не уникальны (как в этих примерах), вам просто нужно указать теги как DISTINCT:

WHERE tag IN ('british', 'rock')
HAVING count(DISTINCT tag) = 2

Менее желательный способ - использовать JOIN для каждого из тегов. Я говорю «менее желательно», потому что это может привести к более сложному SQL-запросу, который будет сложно построить. Однако, если у вас есть много тегов, которые нужно пересекать, и вы знаете, что хотя бы один тег встречается очень редко, планировщик запросов может сделать это более эффективно, чем описанный выше метод.

SELECT artist_id, name
FROM artist
JOIN artist_tag AS tag1 USING (artist_id)
JOIN artist_tag AS tag2 USING (artist_id)
WHERE tag1 = 'british' AND tag2 = 'rock’;

Первоначально опубликовано на http://elliot.land 26 ноября 2015 г.