기존 테이블의 컬럼에 각각 있는 자료를 새로운 테이블에 하나씩 생성하는 방법 연구

picuploads라는 테이블에 별도도 파일 저장과 관련된 정보를 기억하고, 해당 데이터의 저장부분을 분리해서 관리하는 것이다. parentnum이라는 컬럼에 해당 레코드의 고유 num을 기록하고, tablename과 item으로 그 구분을 해서 저장하는 방식이다.

$sql = "SELECT num, created_at, updated_at, tablename, item, parentnum, picname, realname FROM chandj.picuploads";

우선 output의 테이블의 정보를 picuploads라는 테이블에 저장하고 싶다. item은 'attached'로 저장하고, parentnum은 num, picname은 서버에 저장된 이름, realname은 파일 선택시 실제파일이름

Sql 삽입명령으로 처리하는 방법 연구

1차 연구, chatGPT로 물어보기.

아래는 output 테이블의 파일 정보를 picuploads 테이블에 저장하기 위한 SQL 삽입 명령어입니다. output 테이블에서 데이터를 가져와 picuploads 테이블에 삽입하며, 각 파일 정보를 저장합니다.

INSERT INTO `chandj`.`picuploads` (`tablename`, `item`, `parentnum`, `picname`, `realname`)
SELECT 
    'output' AS `tablename`,         -- 테이블 이름
    'attached' AS `item`,           -- item 값
    `num` AS `parentnum`,           -- output 테이블의 num을 parentnum으로 저장
    CASE 
        WHEN file_copied_0 IS NOT NULL THEN file_copied_0
        WHEN file_copied_1 IS NOT NULL THEN file_copied_1
        WHEN file_copied_2 IS NOT NULL THEN file_copied_2
        WHEN file_copied_3 IS NOT NULL THEN file_copied_3
        WHEN file_copied_4 IS NOT NULL THEN file_copied_4
        ELSE NULL
    END AS `picname`,               -- 서버에 저장된 파일 이름
    CASE
        WHEN file_name_0 IS NOT NULL THEN file_name_0
        WHEN file_name_1 IS NOT NULL THEN file_name_1
        WHEN file_name_2 IS NOT NULL THEN file_name_2
        WHEN file_name_3 IS NOT NULL THEN file_name_3
        WHEN file_name_4 IS NOT NULL THEN file_name_4
        ELSE NULL
    END AS `realname`               -- 파일 선택 시의 실제 파일 이름
FROM `chandj`.`output`
WHERE 
    (file_name_0 IS NOT NULL OR file_name_1 IS NOT NULL OR file_name_2 IS NOT NULL OR file_name_3 IS NOT NULL OR file_name_4 IS NOT NULL);

설명

1. tablename: 'output'로 고정되어 picuploads에서 어느 테이블의 데이터인지 식별.
2. item: 항상 'attached'로 저장.
3. parentnum: output 테이블의 num 컬럼을 가져와 picuploads의 parentnum에 저장.
4. picname: file_copied_0 ~ file_copied_4 중 첫 번째로 값이 존재하는 컬럼을 선택.
5. realname: file_name_0 ~ file_name_4 중 첫 번째로 값이 존재하는 컬럼을 선택.
6. 조건문: file_name_* 컬럼 중 하나라도 값이 있는 경우에만 데이터를 삽입.

추가 고려 사항

• 만약 파일별로 picuploads에 개별 레코드로 저장하고 싶다면, 각 파일을 별도로 처리하는 반복 SQL을 생성해야 합니다. 알려주시면 해당 방식으로 수정할 수 있습니다.

추가 질문 ) 위의 코드는 만약에 예를들어, file_copied_0, file_copied_1 두개가 존재하면 그것이 2개의 자료로 만들어질까?

위의 SQL은 두 개 이상의 파일이 존재하더라도 첫 번째로 조건에 맞는 파일만 picuploads에 삽입합니다. 즉, file_copied_0과 file_copied_1이 모두 존재하더라도 하나의 레코드만 생성됩니다.

파일 개수만큼 각각의 레코드를 삽입하려면, UNION 또는 반복적인 SELECT를 사용하여 각 파일을 개별적으로 처리하는 방식으로 수정해야 합니다. 아래는 각 파일을 개별적으로 삽입하도록 수정된 SQL입니다.

수정된 SQL: 파일별로 개별 레코드 생성

INSERT INTO `chandj`.`picuploads` (`tablename`, `item`, `parentnum`, `picname`, `realname`)
SELECT 'output' AS `tablename`, 'attached' AS `item`, `num` AS `parentnum`, `file_copied_0` AS `picname`, `file_name_0` AS `realname`
FROM `chandj`.`output`
WHERE `file_name_0` IS NOT NULL

UNION ALL

SELECT 'output', 'attached', `num`, `file_copied_1`, `file_name_1`
FROM `chandj`.`output`
WHERE `file_name_1` IS NOT NULL

UNION ALL

SELECT 'output', 'attached', `num`, `file_copied_2`, `file_name_2`
FROM `chandj`.`output`
WHERE `file_name_2` IS NOT NULL

UNION ALL

SELECT 'output', 'attached', `num`, `file_copied_3`, `file_name_3`
FROM `chandj`.`output`
WHERE `file_name_3` IS NOT NULL

UNION ALL

SELECT 'output', 'attached', `num`, `file_copied_4`, `file_name_4`
FROM `chandj`.`output`
WHERE `file_name_4` IS NOT NULL;

동작 방식

1. UNION ALL 사용:
   • file_copied_0 ~ file_copied_4를 각각 개별적으로 picuploads에 삽입.
   • 파일이 존재하지 않으면 해당 SELECT는 결과를 반환하지 않음.
2. 각 파일별로 별도의 레코드 생성:
   • 예: file_copied_0와 file_copied_1이 존재하면 picuploads 테이블에 2개의 레코드 삽입.

결과

output 테이블에 file_copied_* 파일이 여러 개 존재하면, 각 파일이 별도의 레코드로 삽입됩니다.

실제 실행해 보기