Можете ли вы разбить / разбить поле в запросе 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 ... 

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

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

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

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

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

Для столбца 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 в разумную структуру БД и не использоваться в любом месте вашего кода. Если вы должны использовать его в производстве, переосмыслите структуру БД

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

 /** * 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... 

Основываясь на ответе Alex выше ( https://stackoverflow.com/a/11022431/1466341 ), я придумал еще лучшее решение. Решение, которое не содержит точного идентификатора записи.

Предполагая, что список, разделенный запятыми, находится в таблице 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'), 

Единственная функция разделения строк 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-й элемент # -сепаратированной строки (при условии, что вы знаете, что определенно имеет не менее n элементов):

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

Внутренний вызов SUBSTRING_INDEX отбрасывает n-й разделитель и все после него, а затем внешний вызов SUBSTRING_INDEX отбрасывает все, кроме 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) 

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

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

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

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

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

Предполагая, что список, разделенный запятыми, находится в 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, ',?'); 

Можно взорвать строку в инструкции 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 

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

Есть более простой способ, есть таблица ссылок, то есть:

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

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

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

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

 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 

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

 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' 

Я использовал вышеприведенную логику, но немного изменил ее. Мой вход имеет формат: «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; 

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

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

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 и т. Д.).

Вот как вы это делаете для 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 большое количество) и запустить SubString для поиска правильного слова, позиции.

Вот что я получил до сих пор (нашел его на странице, упомянутой Бен Алпертом ):

 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`) 

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

  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; 
  • MySQL match () против () - порядок по релевантности и столбцу?
  • Есть ли опция / функция MySQL для отслеживания истории изменений записей?
  • Что противоположно GROUP_CONCAT в MySQL?
  • Использование прокручиваемых результатов Hibernate для медленного чтения 90 миллионов записей
  • MySQL: ALTER IGNORE TABLE дает «Нарушение ограничения целостности»
  • Есть ли у MySQL триггеры времени?
  • Как сделать автоматический приращение первичного ключа таблицы MySQL некоторым префиксом
  • Как сжать / очистить файл ibdata1 в MySQL
  • MySQL условная вставка
  • Будет ли запрос, который может занять часы для выполнения, будет выполняться на удаленном сервере, если я закрою соединение после выполнения команды?
  • MySQL Нелегальное сочетание сортировок
  • Давайте будем гением компьютера.