패턴이 발생할 때마다 열 값을 분할하여 MySQL의 여러 행에 배치

패턴이 발생할 때마다 열 값을 분할하여 MySQL의 여러 행에 배치

패턴이 발생할 때마다 MySQL 테이블의 열을 여러 행으로 분할해야 합니다.열 데이터의 모양은 다음과 같습니다.

                            Column_1
  page1:message1,page2:message2,page3:message3,page4:message4

다음과 같은 데이터를 보여주고 싶습니다 :-

Column_2  Column_1
page1     message1
page2     message2
page3     message3
page4     message4

REGEXP_REPLACE() 및 SUBSTRING_INDEX() MySQL 함수를 찾아봤지만 쿼리를 구성하지 못했습니다.

누가 좀 도와주세요.

MySQL 버전이 Windows 기능을 지원하지 않는 경우 다음과 같이 숫자 테이블(문자열의 최대 길이)을 만들 수 있습니다.

create table numbers (
  nr int
);

insert into numbers values (1),(2),(3),(4),(5); 

그런 다음 2단계 프로세스를 수행합니다.

먼저 다음을 사용하여 쉼표로 문자열을 나눕니다.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', nr),',', -1) AS new_col1        
FROM test t
JOIN numbers n ON CHAR_LENGTH(col1) - CHAR_LENGTH(REPLACE(col1, ',', '')) >= nr - 1 

다음과 같은 결과를 얻을 수 있습니다.

new_col1
page1:message1
page2:message2
page3:message3
page4:message4

둘째, 다음을 사용하여 행을 나눕니다.:아래와 같이:

select SUBSTRING_INDEX(new_col1,':',1) as page,
       SUBSTRING_INDEX(new_col1,':',-1) as message
from (   SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', nr),',', -1) AS new_col1        
         FROM test 
         JOIN numbers n ON CHAR_LENGTH(col1) - CHAR_LENGTH(REPLACE(col1, ',', '')) >= nr - 1 
  ) as t2;

최종 결과:

page  message
page1 message1
page2 message2
page3 message3
page4 message4

https://dbfiddle.uk/acWncQ8y

액션을 사용하여 인덱스가 있는 행의 각 발생에 대한 행을 가져올 수 있습니다.

WITH RECURSIVE expand (column1, n) AS (
select column1, 1 from
   (select
     'page1:message1,page2:message2,page3:message3,page4:message4' as column1) as 
      table1
      UNION ALL
      select column1 , n+1 from expand where n < CHAR_LENGTH(column1) - 
        CHAR_LENGTH(REPLACE(column1,',',''))
    )

그러면 다음과 같은 결과가 표시됩니다.

column1                                                        n
page1:message1,page2:message2,page3:message3,page4:message4    1
page1:message1,page2:message2,page3:message3,page4:message4    2
page1:message1,page2:message2,page3:message3,page4:message4    3

이제 로 구분된 부품의 n번째 발생을 잡고 해당 부품을 두 개의 열로 분할할 수 있습니다.

 Select 
 SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(column1,',',n),',',-1),':',1) as page,
 SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(column1,',',n),',',-1),':',-1) 
 as message from expand

그러면 당신은

page    message
page1   message1
page2   message2
page3   message3

언급URL : https://stackoverflow.com/questions/73671396/split-column-values-on-each-occurrence-of-a-pattern-and-put-it-in-multiple-rows