Mysql выбирает разделение значений, разделенных запятыми [duplicate]

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

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

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

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

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

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

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

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

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

Вот что я получил до сих пор (нашел его на странице 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`)

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

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

Для столбца 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, будьте осторожны при запуске ее на неанитированной data

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

Вот как вы это делаете для 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, чтобы найти право, слово, позицию.

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

    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;

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

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

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

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

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

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