Блог

Главная - Вопросы по программированию - Как я могу прочитать строковый столбец в виде списка в MySQL?

Как я могу прочитать строковый столбец в виде списка в MySQL?

#mysql #sql #regex

Вопрос:

У меня есть столик. Он имеет две колонки class_id и student . Колонка студент — это список студентов. Тип данных столбца «учащийся» является varchar . Я хочу написать SQL-запрос, который возвращает строки, в которых студенческие столбцы являются подмножеством большего списка, например ["A", "B", "C", "D", "E", "F", "G"] 

 class_id      student
1             ["A","B"]
2             ["G","K","E"]
3             ["A","B","I"]

В приведенном выше примере мой запрос должен возвращать только одну строку с class_id 1 .

Вот что у меня пока есть

 select * from A where student in ("A", "B", "C", "D", "E", "F", "G")

но это не работает.

Комментарии:

1. Какой тип mysql student указан в вашем create table заявлении?

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

3. Верно. Mysql не имеет собственного типа «список». То, что вы здесь имеете, не является хорошим дизайном базы данных. Должна быть таблица с «идентификатором» и «учеником», где каждая строка имеет одно отношение идентификатор/ученик. Таким образом, в приведенном выше примере будет 8 строк, а не 3.

4. Вероятно, вам придется сделать это в программе. Я полагаю,вы могли бы поискать любую запись, содержащую символ, не входящий в ваш алфавит ([]», ABCDEFG), но это безнадежно, если ваши фактические данные состоят из более чем односимвольных имен.

5. @TimRoberts на самом деле они не являются идентификаторами и студентами, я просто использовал идентификатор и студентов для лучшего понимания, предположим, что студенты-это имя студентов и идентификатор в качестве идентификатора класса. итак,в классе 1 есть ученики a,b. в классе 2 есть ученики g,k, e. и в реальной таблице это не алфавиты, а несколько букв

Ответ №1:

Это не отвечает на ваш вопрос, но показывает, как это должно было быть сделано. Запишите свои данные как:

 class    student
 1        A
 1        B
 2        G
 2        K
 2        E
 3        A
 3        B
 3        I

Теперь вы можете ответить на такие вопросы, как «какие занятия вы посещаете?»

 SELECT UNIQUEROW class FROM A WHERE student='A'

и «какие классы у A и B есть вместе?»

 SELECT class, COUNT(*) AS count FROM A WHERE student IN ('A','B') GROUP BY class HAVING count = 2;

Ответ №2:

Я не буду повторять приведенные выше предложения по дизайну (но, пожалуйста, обратите на них внимание). Мы все еще можем обрабатывать ваши данные как есть, приложив немного усилий. Вот пример использования тегов вместо имен:

Рабочий пример:

https://dbfiddle.uk/?rdbms=mysql_8.0amp;fiddle=84641f571cb8e0bd2cb531f5c4ec586d

Стол:

 CREATE TABLE pivot (
   id     int AUTO_INCREMENT PRIMARY KEY
 , tags   varchar(255)
);

Данные теста:

 INSERT INTO pivot (tags) VALUES
   ('tag1,tag2,tag3,tag1')
 , ('tag2')
 , ('tag4,tag5,tag4,tag4')
 , ('tag5,tag5,tag4')
 , ('tag5,tag5,tag4')
 , ('tag1,tag3,tag2,tag1')
;

Вот способ динамической нормализации данных:

 WITH RECURSIVE seq (n) AS (
            SELECT 1
             UNION ALL
            SELECT n   1 FROM seq WHERE n <= 9
       )
SELECT DISTINCT t1.*
     , REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.tags,',',seq.n-1) FROM SUBSTRING_INDEX(t1.tags,',',seq.n)), ',','') AS tag
  FROM pivot AS t1
  JOIN seq
    ON seq.n > 0 AND SUBSTRING_INDEX(t1.tags,',',seq.n-1) <> SUBSTRING_INDEX(t1.tags,',',seq.n)
 ORDER BY id, tag
;

Результат:

  ---- --------------------- ------ 
| id | tags                | tag  |
 ---- --------------------- ------ 
|  1 | tag1,tag2,tag3,tag1 | tag1 |
|  1 | tag1,tag2,tag3,tag1 | tag2 |
|  1 | tag1,tag2,tag3,tag1 | tag3 |
|  2 | tag2                | tag2 |
|  3 | tag4,tag5,tag4,tag4 | tag4 |
|  3 | tag4,tag5,tag4,tag4 | tag5 |
|  4 | tag5,tag5,tag4      | tag4 |
|  4 | tag5,tag5,tag4      | tag5 |
|  5 | tag5,tag5,tag4      | tag4 |
|  5 | tag5,tag5,tag4      | tag5 |
|  6 | tag1,tag3,tag2,tag1 | tag1 |
|  6 | tag1,tag3,tag2,tag1 | tag2 |
|  6 | tag1,tag3,tag2,tag1 | tag3 |
 ---- --------------------- ------

Учитывая приведенный выше нормализованный список, мы можем затем определить, является ли некоторый заданный набор подмножеством сохраненного набора:

 WITH RECURSIVE seq (n) AS (
            SELECT 1
             UNION ALL
            SELECT n   1 FROM seq WHERE n <= 9
       )
   , norm AS (
            SELECT DISTINCT t1.*
                 , REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.tags,',',seq.n-1) FROM SUBSTRING_INDEX(t1.tags,',',seq.n)), ',','') AS tag
              FROM pivot AS t1
              JOIN seq
                ON seq.n > 0 AND SUBSTRING_INDEX(t1.tags,',',seq.n-1) <> SUBSTRING_INDEX(t1.tags,',',seq.n)
     )
SELECT id
     , tags
  FROM norm
 WHERE tag IN ('tag2', 'tag3')
 GROUP BY id
HAVING COUNT(DISTINCT tag) = 2
 ORDER BY id, tags
;

Результат:

  ---- --------------------- 
| id | tags                |
 ---- --------------------- 
|  1 | tag1,tag2,tag3,tag1 |
|  6 | tag1,tag3,tag2,tag1 |
 ---- --------------------- 
2 rows in set (0.003 sec)

Учитывая приведенный выше нормализованный список, мы можем затем определить, является ли какой-либо данный набор надмножеством (или совпадением) сохраненного набора (который отвечает на ваш конкретный вопрос).:

 WITH RECURSIVE seq (n) AS (
            SELECT 1
             UNION ALL
            SELECT n   1 FROM seq WHERE n <= 9
       )
   , norm AS (
            SELECT DISTINCT t1.*
                 , REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.tags,',',seq.n-1) FROM SUBSTRING_INDEX(t1.tags,',',seq.n)), ',','') AS tag
              FROM pivot AS t1
              JOIN seq
                ON seq.n > 0 AND SUBSTRING_INDEX(t1.tags,',',seq.n-1) <> SUBSTRING_INDEX(t1.tags,',',seq.n)
     )
SELECT id
     , tags
  FROM norm
 GROUP BY id
HAVING COUNT(DISTINCT CASE WHEN tag IN ('tag2', 'tag3', 'tag8', 'tag9') THEN tag END) = COUNT(DISTINCT tag)
 ORDER BY id, tags
;

Результат:

  ---- --------------------- 
| id | tags                |
 ---- --------------------- 
|  2 | tag2                |
 ---- --------------------- 
1 row in set (0.002 sec)

С вашими данными:

 INSERT INTO pivot (tags) VALUES 
    ('A,B')
  , ('G,K,E')
  , ('A,B,I')
;

Решение:

 WITH RECURSIVE seq (n) AS (
            SELECT 1
             UNION ALL
            SELECT n   1 FROM seq WHERE n <= 9
       )
   , norm AS (
            SELECT DISTINCT t1.*
                 , REPLACE(TRIM(LEADING SUBSTRING_INDEX(t1.tags,',',seq.n-1) FROM SUBSTRING_INDEX(t1.tags,',',seq.n)), ',','') AS tag
              FROM pivot AS t1
              JOIN seq
                ON seq.n > 0 AND SUBSTRING_INDEX(t1.tags,',',seq.n-1) <> SUBSTRING_INDEX(t1.tags,',',seq.n)
     )
SELECT id
     , tags
  FROM norm
 GROUP BY id
HAVING COUNT(DISTINCT CASE WHEN tag IN ('A','B','C','D','E','F','G') THEN tag END) = COUNT(DISTINCT tag)
 ORDER BY id, tags
;

Результат:

  ---- ------ 
| id | tags |
 ---- ------ 
|  1 | A,B  |
 ---- ------ 
1 row in set (0.003 sec)

Комментарии:

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

Ответ №3:

Ваши требования могут быть выполнены, если вы используете MySQL 8 / MariabDB 10.6

Использование функций JSON_TABLE

JSON_TABLE(expr, СТОЛБЦЫ пути (список столбцов) [КАК] псевдоним)

Извлекает данные из документа JSON и возвращает их в виде реляционной таблицы с указанными столбцами.

Давайте создадим примерную таблицу с данными

 CREATE TABLE `table1` (
 `class_id` int(11) NOT NULL AUTO_INCREMENT,
 `student` longtext NOT NULL,
 PRIMARY KEY (`class_id`)
) ENGINE=InnoDB   DEFAULT CHARSET=latin1;

INSERT INTO `table1`(`student`) VALUES ('["A","B"]'), ('["G","K","E"]'), ('["A","B","I"]');

Для выполнения ваших требований select * from A where student in ("A", "B", "C", "D", "E", "F", "G") мы можем использовать JSON_TABLE

 SELECT DISTINCT(class_id), student
FROM
    table1,
    JSON_TABLE(
        student,
        "$[*]" COLUMNS(
    VALUE TEXT
        PATH "$"
    )
    ) DATA
WHERE DATA
    .Value IN("A", "B", "C", "D", "E", "F", "G")

Результатом будет

class_id Студенты
1 [«A»,»B»]
2 [«G»,»K»,»E»]
3 [«A»,»B»,»I»]

бд<>скрипка <>здесь

Метки: