MySQL - разделенная запятая строка в строки без создания другой таблицы [duplicate]

Комментарий на hvd вполне уместен, и это только пример. В SQL, например, я думаю, что было бы более ясным:

where val like 'abc%123%xyz' and
      val not like 'abc%abc%' and
      val not like '%xyz%xyz'

Я предполагаю, что что-то совершенно похожее сделать в других средах.

52
задан slavoo 30 July 2013 в 09:59
поделиться

4 ответа

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

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

См. здесь скрипку .

Если вы не можете создать таблицу, то решение может быть следующим:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

пример скрипта - здесь .

74
ответ дан fthiella 18 August 2018 в 12:10
поделиться
  • 1
    спасибо, используя 2 таблицы? я хочу всего 1 стол – AFD 30 July 2013 в 10:26
  • 2
    @ user2577038 вы можете сделать это без таблицы чисел, см. здесь sqlfiddle.com / #! 2 / a213e4 / 1 – fthiella 30 July 2013 в 10:33
  • 3
    Важно отметить, что во втором примере максимальное количество "полей" разделенное запятой 5. Вы можете проверить # вхождения в строке с помощью метода, подобного здесь: stackoverflow.com/questions/12344795/… . Продолжайте добавлять 'select [number] union all' clauses к встроенному представлению 'numbers' до тех пор, пока количество возвращаемых строк не перестанет увеличиваться. – Bret Weinraub 27 July 2016 в 21:04
  • 4
    Как обычно, я постоянно спотыкаюсь о вашем полезном коде. Если кто-то хочет быстрый способ создать таблицу, похожую на верхнюю часть, показанную здесь, вот ссылка, использующая эту процедуру здесь . Этот op был для одной строки, а не для таблицы. – Drew 26 September 2016 в 07:40
  • 5
    Как будет выглядеть SQLite-версия? Я получаю следующую ошибку: could not prepare statement (1 no such function: SUBSTRING_INDEX) – Remi Sture 22 November 2016 в 09:29

Мой вариант: хранимая процедура, которая принимает имя таблицы, имена полей и разделитель в качестве аргументов. Вдохновленный post http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/

delimiter $$

DROP PROCEDURE IF EXISTS split_value_into_multiple_rows $$
CREATE PROCEDURE split_value_into_multiple_rows(tablename VARCHAR(20),
    id_column VARCHAR(20), value_column VARCHAR(20), delim CHAR(1))
  BEGIN
    DECLARE id INT DEFAULT 0;
    DECLARE value VARCHAR(255);
    DECLARE occurrences INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE splitted_value VARCHAR(255);
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM 
        tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @expr = CONCAT('CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value VARCHAR(255)) ENGINE=Memory SELECT ',
        id_column,' id, ', value_column,' value FROM ',tablename);
    PREPARE stmt FROM @expr;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DROP TEMPORARY TABLE IF EXISTS tmp_table2;
    CREATE TEMPORARY TABLE tmp_table2 (id INT NOT NULL, value VARCHAR(255) NOT NULL) ENGINE=Memory;

    OPEN cur;
      read_loop: LOOP
        FETCH cur INTO id, value;
        IF done THEN
          LEAVE read_loop;
        END IF;

        SET occurrences = (SELECT CHAR_LENGTH(value) -
                           CHAR_LENGTH(REPLACE(value, delim, '')) + 1);
        SET i=1;
        WHILE i <= occurrences DO
          SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(
              SUBSTRING_INDEX(value, delim, i), delim, -1)));
          INSERT INTO tmp_table2 VALUES (id, splitted_value);
          SET i = i + 1;
        END WHILE;
      END LOOP;

      SELECT * FROM tmp_table2;
    CLOSE cur;
    DROP TEMPORARY TABLE tmp_table1;
  END; $$

delimiter ;

Пример использования (нормализация ):

CALL split_value_into_multiple_rows('my_contacts', 'contact_id', 'interests', ',');

CREATE TABLE interests (
  interest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  interest VARCHAR(30) NOT NULL
) SELECT DISTINCT value interest FROM tmp_table2;

CREATE TABLE contact_interest (
  contact_id INT NOT NULL,
  interest_id INT NOT NULL,
  CONSTRAINT fk_contact_interest_my_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id),
  CONSTRAINT fk_contact_interest_interests_interest_id FOREIGN KEY (interest_id) REFERENCES interests (interest_id)
) SELECT my_contacts.contact_id, interests.interest_id
    FROM my_contacts, tmp_table2, interests
    WHERE my_contacts.contact_id = tmp_table2.id AND interests.interest = tmp_table2.value;
2
ответ дан ashkufaraz 18 August 2018 в 12:10
поделиться

Я приведу здесь ссылку с измененным именем столбца.

DELIMITER $$

CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER) 
RETURNS VARCHAR(65000)
BEGIN
  DECLARE output VARCHAR(65000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END $$


CREATE PROCEDURE BadTableToGoodTable()
BEGIN
  DECLARE i INTEGER;

  SET i = 1;
  REPEAT
    INSERT INTO GoodTable (id, name)
      SELECT id, strSplit(name, ',', i) FROM BadTable
      WHERE strSplit(name, ',', i) IS NOT NULL;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
END $$

DELIMITER ;
5
ответ дан Community 18 August 2018 в 12:10
поделиться
  • 1
    спасибо, но у меня есть только привилегия только для чтения – AFD 30 July 2013 в 10:25
CREATE PROCEDURE `getVal`()
BEGIN
        declare r_len integer;
        declare r_id integer;
        declare r_val varchar(20);
        declare i integer;
        DECLARE found_row int(10);
        DECLARE row CURSOR FOR select length(replace(val,"|","")),id,val from split;
        create table x(id int,name varchar(20));
      open row;
            select FOUND_ROWS() into found_row ;
            read_loop: LOOP
                IF found_row = 0 THEN
                         LEAVE read_loop;
                END IF;
            set i = 1;  
            FETCH row INTO r_len,r_id,r_val;
            label1: LOOP        
                IF i <= r_len THEN
                  insert into x values( r_id,SUBSTRING(replace(r_val,"|",""),i,1));
                  SET i = i + 1;
                  ITERATE label1;
                END IF;
                LEAVE label1;
            END LOOP label1;
            set found_row = found_row - 1;
            END LOOP;
        close row;
        select * from x;
        drop table x;
END
1
ответ дан Imanez 18 August 2018 в 12:10
поделиться
Другие вопросы по тегам:

Похожие вопросы: