Можете ли вы разбить/разбить поле в запросе MySQL?

Мне нужно создать отчет о завершении обучения. Каждый ученик принадлежит одному клиенту. Вот таблицы (упрощенные для этого вопроса).

CREATE TABLE  `clients` (
  `clientId` int(10) unsigned NOT NULL auto_increment,
  `clientName` varchar(100) NOT NULL default '',
  `courseNames` varchar(255) NOT NULL default ''
)

В поле courseNames содержится строка имен, обозначенная запятыми, например, "AB01, AB02, AB03"

CREATE TABLE  `clientenrols` (
  `clientEnrolId` int(10) unsigned NOT NULL auto_increment,
  `studentId` int(10) unsigned NOT NULL default '0',
  `courseId` tinyint(3) unsigned NOT NULL default '0'
)

В поле courseId указан индекс имени курса в поле clients.courseNames. Итак, если клиентом courseNames являются "AB01, AB02, AB03", а courseId для регистрации - 2, тогда учащийся находится в AB03.

Есть ли способ, которым я могу сделать один выбор в этих таблицах, который включает название курса? Имейте в виду, что будут студенты из разных клиентов (и, следовательно, имеют разные названия курсов, не все из которых являются последовательными, например: "NW01, NW03" )

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

SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`]
FROM ...

Ответ 1

До сих пор я хотел сохранить эти разделенные запятыми списки в своем SQL-db - хорошо осведомленный обо всех предупреждениях!

Я продолжал думать, что они имеют преимущества по сравнению с таблицами поиска (которые обеспечивают способ нормализованной базы данных). После нескольких дней отказа, я видел свет:

Использование поисковых таблиц НЕ выдает больше кода, чем эти уродливые строковые операции при использовании значений, разделенных запятыми, в одном поле.
Таблица поиска позволяет создавать собственные числовые форматы и, следовательно, НЕ больше, чем те поля csv. Это МАЛЕНЬКО, хотя.
Применяемые строковые операции тонкие в языке высокого уровня (SQL и PHP), но дорогостоящие по сравнению с использованием массивов целых чисел.
Базы данных не предназначены для чтения человеком, и в большинстве случаев глупо пытаться придерживаться структур из-за их удобочитаемости/прямой редактируемости, как и я.

Короче говоря, есть причина, почему в MySQL нет встроенной функции SPLIT().

Ответ 2

Увидев, что это довольно популярный вопрос - ответ ДА.

Для столбца column в таблице table, содержащем все ваши данные, разделенные комой:

CREATE TEMPORARY TABLE temp (val CHAR(255));
SET @S1 = CONCAT("INSERT INTO temp (val) VALUES ('",REPLACE((SELECT GROUP_CONCAT( DISTINCT  `column`) AS data FROM `table`), ",", "'),('"),"');");
PREPARE stmt1 FROM @s1;
EXECUTE stmt1;
SELECT DISTINCT(val) FROM temp;

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

Per @Mark Amery - поскольку это преобразует значения, разделенные комой, в инструкцию INSERT, будьте осторожны при запуске на несаминированных данных

Чтобы повторить, не хранить CSV в своей БД; эта функция предназначена для преобразования CSV в разумную структуру БД и не использоваться в любом месте вашего кода. Если вы должны использовать его в производстве, пожалуйста, переосмыслите структуру своей базы данных

Ответ 3

Вы можете создать для этого функцию:

/**
* Split a string by string (Similar to the php function explode())
*
* @param VARCHAR(12) delim The boundary string (delimiter).
* @param VARCHAR(255) str The input string.
* @param INT pos The index of the string to return
* @return VARCHAR(255) The (pos)th substring
* @return VARCHAR(255) Returns the [pos]th string created by splitting the str parameter on boundaries formed by the delimiter.
* @{@example
*     SELECT SPLIT_STRING('|', 'one|two|three|four', 1);
*     This query
* }
*/
DROP FUNCTION IF EXISTS SPLIT_STRING;
CREATE FUNCTION SPLIT_STRING(delim VARCHAR(12), str VARCHAR(255), pos INT)
RETURNS VARCHAR(255) DETERMINISTIC
RETURN
    REPLACE(
        SUBSTRING(
            SUBSTRING_INDEX(str, delim, pos),
            LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1
        ),
        delim, ''
    );

Преобразование магического псевдокода для его использования:

SELECT e.`studentId`, SPLIT_STRING(',', c.`courseNames`, e.`courseId`)
FROM...

Ответ 4

Основываясь на ответе Alex выше (fooobar.com/questions/108502/...), я придумал еще лучшее решение. Решение, которое не содержит точного идентификатора записи.

Предполагая, что список, разделенный запятыми, находится в таблице data.list и содержит список кодов из другой таблицы classification.code, вы можете сделать что-то вроде:

SELECT 
    d.id, d.list, c.code
FROM 
    classification c
    JOIN data d
        ON d.list REGEXP CONCAT('[[:<:]]', c.code, '[[:>:]]');

Итак, если у вас есть таблицы и данные вроде этого:

CLASSIFICATION (code varchar(4) unique): ('A'), ('B'), ('C'), ('D')
MY_DATA (id int, list varchar(255)): (100, 'C,A,B'), (150, 'B,A,D'), (200,'B')

выше SELECT вернет

(100, 'C,A,B', 'A'),
(100, 'C,A,B', 'B'),
(100, 'C,A,B', 'C'),
(150, 'B,A,D', 'A'),
(150, 'B,A,D', 'B'),
(150, 'B,A,D', 'D'),
(200, 'B', 'B'),

Ответ 5

Функция разделения строк только в MySQL SUBSTRING_INDEX(str, delim, count). Вы можете использовать это, например:

Вернуть элемент перед первым разделителем в строке:

mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', 1);
+--------------------------------------------+
| SUBSTRING_INDEX('foo#bar#baz#qux', '#', 1) |
+--------------------------------------------+
| foo                                        |
+--------------------------------------------+
1 row in set (0.00 sec)

Вернуть элемент после последнего разделителя в строке:

mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', -1);
+---------------------------------------------+
| SUBSTRING_INDEX('foo#bar#baz#qux', '#', -1) |
+---------------------------------------------+
| qux                                         |
+---------------------------------------------+
1 row in set (0.00 sec)

Вернуть все до третьего разделителя в строке:

mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', 3);
+--------------------------------------------+
| SUBSTRING_INDEX('foo#bar#baz#qux', '#', 3) |
+--------------------------------------------+
| foo#bar#baz                                |
+--------------------------------------------+
1 row in set (0.00 sec)

Верните второй элемент в строке, связав два вызова:

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('foo#bar#baz#qux', '#', 2), '#', -1);
+----------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('foo#bar#baz#qux', '#', 2), '#', -1) |
+----------------------------------------------------------------------+
| bar                                                                  |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

В общем, простой способ получить n-й элемент строки # -separated (при условии, что вы точно знаете, что он содержит хотя бы n элементов), заключается в следующем:

SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, '#', n), '#', -1);

Внутренний вызов SUBSTRING_INDEX отбрасывает n-й разделитель и все после него, а затем внешний вызов SUBSTRING_INDEX отбрасывает все, кроме последнего элемента, который остается.

Если вам нужно более надежное решение, которое возвращает NULL, если вы запрашиваете элемент, который не существует (например, запрашивает 5-й элемент 'a#b#c#d'), то вы можете подсчитать разделители, используя REPLACE, а затем условно вернуть NULL, используя IF():

IF(
    LENGTH(your_string) - LENGTH(REPLACE(your_string, '#', '')) / LENGTH('#') < n - 1,
    NULL,
    SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, '#', n), '#', -1)
)

Конечно, это довольно уродливо и трудно понять! Так что вы можете захотеть обернуть его в функцию:

CREATE FUNCTION split(string TEXT, delimiter TEXT, n INT)
RETURNS TEXT DETERMINISTIC
RETURN IF(
    (LENGTH(string) - LENGTH(REPLACE(string, delimiter, ''))) / LENGTH(delimiter) < n - 1,
    NULL,
    SUBSTRING_INDEX(SUBSTRING_INDEX(string, delimiter, n), delimiter, -1)
);

Затем вы можете использовать функцию следующим образом:

mysql> SELECT SPLIT('foo,bar,baz,qux', ',', 3);
+----------------------------------+
| SPLIT('foo,bar,baz,qux', ',', 3) |
+----------------------------------+
| baz                              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SPLIT('foo,bar,baz,qux', ',', 5);
+----------------------------------+
| SPLIT('foo,bar,baz,qux', ',', 5) |
+----------------------------------+
| NULL                             |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SPLIT('foo###bar###baz###qux', '###', 2);
+------------------------------------------+
| SPLIT('foo###bar###baz###qux', '###', 2) |
+------------------------------------------+
| bar                                      |
+------------------------------------------+
1 row in set (0.00 sec)

Ответ 6

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

SELECT * 
FROM `TABLE`
WHERE `field` REGEXP ',?[SEARCHED-VALUE],?';

жадный вопросительный знак помогает искать в начале или в конце строки.

Надеюсь, что это поможет любому в будущем

Ответ 7

Основываясь на решении Альвина Кеслера, здесь немного более практичный пример в реальном мире.

Предполагая, что список, разделенный запятыми, находится в my_table.list, и это список идентификаторов для my_other_table.id, вы можете сделать что-то вроде:

SELECT 
    * 
FROM 
    my_other_table 
WHERE 
    (SELECT list FROM my_table WHERE id = '1234') REGEXP CONCAT(',?', my_other_table.id, ',?');

Ответ 8

В инструкции MySQL SELECT можно взорвать строку.

Сначала создайте серию чисел с наибольшим количеством разграниченных значений, которые вы хотите взорвать. Либо из таблицы целых чисел, либо путем объединения чисел вместе. Следующее генерирует 100 строк, давая значения от 1 до 100. Его можно легко расширить, чтобы получить большие диапазоны (добавьте еще один дополнительный запрос, дающий значения от 0 до 9 для сотен - отсюда от 0 до 999 и т.д.).

SELECT 1 + units.i + tens.i * 10 AS aNum
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens

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

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(clients.courseNames, ',', sub0.aNum), ',', -1) AS a_course_name
FROM clients
CROSS JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10 AS aSubscript
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0

Как вы можете видеть, здесь есть небольшая проблема, что последнее разделимое значение повторяется много раз. Чтобы избавиться от этого, вам нужно ограничить диапазон чисел в зависимости от количества разделителей. Это можно сделать, взяв длину поля с разделителями и сравнивая его с длиной поля с разделителями, когда разделители изменены на "" (чтобы удалить их). Из этого вы можете получить количество разделителей: -

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(clients.courseNames, ',', sub0.aNum), ',', -1) AS a_course_name
FROM clients
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON (1 + LENGTH(clients.courseNames) - LENGTH(REPLACE(clients.courseNames, ',', ''))) >= sub0.aNum

В исходном поле примера вы можете (например) подсчитать количество студентов на каждом курсе на основе этого. Обратите внимание, что я изменил sub-запрос, который получает диапазон чисел, чтобы вернуть 2 числа, 1 используется для определения имени курса (поскольку они основаны на старте на 1), а другой получает индекс (поскольку они основаны на запуске при 0).

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(clients.courseNames, ',', sub0.aNum), ',', -1) AS a_course_name, COUNT(clientenrols.studentId)
FROM clients
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10 AS aSubscript
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON (1 + LENGTH(clients.courseNames) - LENGTH(REPLACE(clients.courseNames, ',', ''))) >= sub0.aNum
LEFT OUTER JOIN clientenrols
ON clientenrols.courseId = sub0.aSubscript
GROUP BY a_course_name

Как вы можете видеть, это возможно, но довольно грязно. И с небольшой возможностью использовать индексы он не будет эффективным. Далее диапазон должен справляться с наибольшим количеством разделимых значений и работает, исключая множество дубликатов; если максимальное количество разделимых значений очень велико, это значительно замедлит работу. В целом, как правило, гораздо лучше правильно нормализовать базу данных.

Ответ 9

Там проще, есть таблица ссылок, т.е.:

Таблица 1: клиенты, информация о клиенте, бла-бла-бла

Таблица 2: курсы, информация о курсе, бла-бла

Таблица 3: clientid, courseid

Затем сделайте ПРИСОЕДИНЕНИЕ, и вы отправитесь на гонки.

Ответ 10

SELECT
  tab1.std_name, tab1.stdCode, tab1.payment,
  SUBSTRING_INDEX(tab1.payment, '|', 1) as rupees,
  SUBSTRING(tab1.payment, LENGTH(SUBSTRING_INDEX(tab1.payment, '|', 1)) + 2,LENGTH(SUBSTRING_INDEX(tab1.payment, '|', 2))) as date
FROM (
  SELECT DISTINCT
    si.std_name, hfc.stdCode,
    if(isnull(hfc.payDate), concat(hfc.coutionMoneyIn,'|', year(hfc.startDtae), '-',  monthname(hfc.startDtae)), concat(hfc.payMoney, '|', monthname(hfc.payDate), '-', year(hfc.payDate))) AS payment
  FROM hostelfeescollection hfc
  INNER JOIN hostelfeecollectmode hfm ON hfc.tranId = hfm.tranId
  INNER JOIN student_info_1 si ON si.std_code = hfc.stdCode
  WHERE hfc.tranId = 'TRAN-AZZZY69454'
) AS tab1

Ответ 11

Если вам нужна таблица из строки с разделителями:

SET @str = 'function1;function2;function3;function4;aaa;bbbb;nnnnn';
SET @delimeter = ';';
SET @sql_statement = CONCAT('SELECT '''
                ,REPLACE(@str, @delimeter, ''' UNION ALL SELECT ''')
                ,'''');
SELECT @sql_statement;
SELECT 'function1' UNION ALL SELECT 'function2' UNION ALL SELECT 'function3' UNION ALL SELECT 'function4' UNION ALL SELECT 'aaa' UNION ALL SELECT 'bbbb' UNION ALL SELECT 'nnnnn'

Ответ 12

Я использовал приведенную выше логику, но немного изменил ее. Мой ввод имеет формат: "apple: 100 | pinapple: 200 | orange: 300", хранящихся в переменной @updtAdvanceKeyVal

Вот функциональный блок:

set @res = "";

set @i = 1;
set @updtAdvanceKeyVal = updtAdvanceKeyVal;

REPEAT


 -- set r =  replace(SUBSTRING(SUBSTRING_INDEX(@updtAdvanceKeyVal, "|", @i),
 --  LENGTH(SUBSTRING_INDEX(@updtAdvanceKeyVal, "|", @i -1)) + 1),"|","");

-- wrapping the function in "replace" function as above causes to cut off a character from
 -- the 2nd splitted value if the value is more than 3 characters. Writing it in 2 lines causes no such problem and the output is as expected
-- sample output by executing the above function :
-- orange:100
-- pi apple:200    !!!!!!!!strange output!!!!!!!!
-- tomato:500

      set @r =  SUBSTRING(SUBSTRING_INDEX(@updtAdvanceKeyVal, "|", @i),
                  LENGTH(SUBSTRING_INDEX(@updtAdvanceKeyVal, "|", @i -1)) + 1);

      set @r = replace(@r,"|","");

      if @r <> "" then

              set @key = SUBSTRING_INDEX(@r, ":",1);
              set @val = SUBSTRING_INDEX(@r, ":",-1);

              select @key, @val;
      end if;

      set @i = @i + 1;

     until @r = ""
END REPEAT;

Ответ 13

У меня была аналогичная проблема с подобным полем, которое я решил по-другому. Моему варианту использования понадобилось взять эти идентификаторы в списке, разделенном запятыми, для использования в соединении.

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

keys "1","2","6","12"

Из-за этого я смог сделать LIKE

SELECT twwf.id, jtwi.id joined_id FROM table_with_weird_field twwf INNER JOIN join_table_with_ids jtwi ON twwf.delimited_field LIKE CONCAT("%\"", jtwi.id, "\"%")

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

Это сработало для моего случая использования, когда я имел дело с плагином Wordpress, который управлял отношениями так, как описано. Кавычки действительно помогают, потому что иначе вы рискуете частичными совпадениями (aka - id 1 в течение 18 и т.д.).

Ответ 14

Вы можете сделать это с помощью JSON в более поздних версиях MySQL. Это взрыв. У нас будет быстрая подготовка к созданию таблицы чисел. Затем сначала мы создаем промежуточную таблицу для преобразования строк, разделенных запятыми, в массив json, затем мы будем использовать json_extract для их разделения. Я инкапсулирую строки в кавычки, тщательно избегая существующих кавычек, потому что у меня были строки, разделенные точкой с запятой, содержащие запятые.

Поэтому, чтобы создать таблицу чисел, надеюсь, у вас будет больше клиентов, чем курсов, выберите достаточно большую таблицу, если нет.

CREATE TABLE numbers (n int PRIMARY KEY);
INSERT INTO numbers 
SELECT @row := @row + 1
FROM clients JOIN (select @row:=0) t2;

Добавьте LIMIT 50, если вы знаете, что у вас есть только 50 курсов. Это было легко, не так ли? Теперь перейдем к реальной работе, честно говоря, это цитаты, которые делают ее более уродливой, но, по крайней мере, более общей:

CREATE TABLE json_coursenames 
SELECT clientId,clientName,CONCAT('["', REPLACE(REPLACE(courseName,'"','\\"'), ',', '","'), '"]') AS a
FROM clients;

CREATE TABLE extracted
SELECT clientId,clientName,REPLACE(TRIM(TRIM('"' FROM JSON_EXTRACT(a, concat('$[', n, ']')))), '\\"', '"')
FROM json_coursenames
INNER JOIN numbers ON n < JSON_LENGTH(a);

Wheee!

Мясо здесь вот эти два: CONCAT('["', REPLACE(coursename, ',', '","'), '"]') (я опустил второй REPLACE, чтобы сделать его более заметным) преобразует foo,bar,bar в "foo","bar","baz". Другой трюк - JSON_EXTRACT(a, concat('$[', n, ']') станет JSON_EXTRACT(a, $[12]), и это 13-й элемент массива, см. Синтаксис JSON Path.

Ответ 15

Вот как вы это делаете для SQL Server. Кто-то еще может перевести его в MySQL. Анализ значений CSV в нескольких строках.

SELECT Author, 
NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word 
FROM Tally, Quotes 
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ',' 
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0

Идея состоит в том, чтобы перекрестно присоединиться к предопределенной таблице Tally, которая содержит целое число от 1 до 8000 (или сколько угодно большое число) и запустить SubString, чтобы найти правильное, слово, положение.

Ответ 16

Вот что я получил до сих пор (нашел это на странице Ben Alpert):

SELECT REPLACE(
    SUBSTRING(
        SUBSTRING_INDEX(c.`courseNames`, ',', e.`courseId` + 1)
        , LENGTH(SUBSTRING_INDEX(c.`courseNames`, ',', e.`courseId`)
    ) + 1)
    , ','
    , ''
)
FROM `clients` c INNER JOIN `clientenrols` e USING (`clientId`)

Ответ 17

Ну, я ничего не использовал, поэтому решил создать настоящую простую функцию разделения, надеюсь, что это поможет:

    DECLARE inipos INTEGER;
    DECLARE endpos INTEGER;
    DECLARE maxlen INTEGER;
    DECLARE item VARCHAR(100);
    DECLARE delim VARCHAR(1);

    SET delim = '|';
    SET inipos = 1;
    SET fullstr = CONCAT(fullstr, delim);
    SET maxlen = LENGTH(fullstr);

    REPEAT
        SET endpos = LOCATE(delim, fullstr, inipos);
        SET item =  SUBSTR(fullstr, inipos, endpos - inipos);

        IF item <> '' AND item IS NOT NULL THEN           
            USE_THE_ITEM_STRING;
        END IF;
        SET inipos = endpos + 1;
    UNTIL inipos >= maxlen END REPEAT;