이러한 경우에 쿼리를 어떻게 작성해야 할까요?

Question

환경은 MySQL 이구요 영화관 좌석 예매 관련해서 개발 중입니다.T1 테이블의 특정 행의 좌석 컬럼에 'A1, A3, A4' (VARCHAR) 가 들어가 있고 (, 다음에 공백 O) 해당 컬럼과 비교할 문자열이 'A2,A4,A5' 일때 (, 다음에 공백 X)겹치는 게 있는지를 WHERE 절에서 판단하고 싶습니다. 위에 예로 써놓은 경우 A4 한 개가 겹치는 경우에요.이를 어떻게 판별할까요?

Clickin · Answer

일단 설계가 잘못되었고요 좌석은 각각 개별 행으로 떨어지는 게 맞겠습니다.그럼에도 굳이 기존 테이블 구조를 유지하면서 조회하려면CTE로 콤마 기준 분리하여 열을 행으로 만든 후공백은 trim해서 동일한 조건으로 변경하고 나서 행끼리 비교하시면 되겠습니다.콤마 구분자 분리방법 링크입니다. https://stackoverflow.com/questions/73446990/mysql-how-to-split-comma-separated-text-and-create-table

keeradsf · Answer

sql은 설계자가 주는게 정석 입니다.테이블 만들고 쿼리 짜보고 어 되네 하고 테이블 생성 해야 합니다.

olivahn · Answer

암만못해도 1정규화는 하세요. 특별한 이유 없이 1정규화조차 안하는 설계자들은 뚝배기를 깨버려야 합니다.

르매 · Answer

현재 상태에서 억지로 결과를 만들어내려면 SUBSTRING_INDEX() 두 번에 REPLACE() 한 번, LENGTH()를 두 번 써야 하는데.. 이렇게 문자열 함수를 많이 사용하고 인덱스도 활용하지 못하는 쿼리는 성능 문제가 심각합니다.테이블이 정규화되어 있지 않은 건 정말 아쉽지만, 문제를 해결하셔야 하니.. 지금 선택할 수 있는 가장 최상의 방법을 알려드리겠습니다.성능까지 한 번에 다 잡으면서 이미 예약된 좌석을 빠르게 조회할 수 있습니다.방법은..'A1, A3, A4' 라는 값이 들어 있는 컬럼을 [&#34;A1&#34;, &#34;A3&#34;, &#34;A4&#34;]와 같이 json array로 변환하는 virtual generated column을 생성합니다. virtual 이기 때문에 물리적으로 디스크에 데이터가 만들어지지는 않습니다.생성된 컬럼에 조금 특수한 형태의 인덱스를 만듭니다. 이때 인덱스가 디스크에 물리적으로 만들어지기 때문에 이 작업은 테이블이 잠기고 데이터 양에 따라 오랜 시간이 걸릴 수 있습니다.마지막으로 조회할 좌석의 목록이 예약이 완료된 좌석의 목록과 겹치는지 확인해야 하는데, 이때 JSON_OVERLAPS() 함수를 사용하면 인덱스를 사용해 빠르게 검색할 수 있습니다.아래 샘플 데이터와 쿼리를 작성했으니, 참고해서 적용해 보시기 바랍니다.-- 테스트 테이블 t1 생성 DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, seats varchar(500) NOT NULL ); -- 테스트 데이터 삽입 INSERT t1 (seats) VALUES ('A1, A3, A4'), ('B14, B15'); -- 가상 유형의 generated 컬럼을 생성 ALTER TABLE t1 ADD seats_json json GENERATED ALWAYS AS (CONCAT('[&#34;', REPLACE(seats, ', ', '&#34;,&#34;'), '&#34;]')) VIRTUAL; -- generated 컬럼에 인덱스 생성 -- ㄴ 구문이 특이하니 주의하시고, char(5)는 좌석 1개당 최대 문자열 길이를 넣으면 됩니다. -- ㄴ char대신 varchar를 쓰는게 불가능하니 이 점 주의!! CREATE INDEX ix_t1_seats_json ON t1 ((CAST(seats_json AS char(5) ARRAY))); -- 이제 예약하려는 좌석 목록이 실제 예약이 된 좌석과 겹치는지 조회하는 쿼리입니다. SET &#64;keyword &#61; 'A2,A4,A5'; SELECT * FROM t1 WHERE JSON_OVERLAPS(seats_json, CONCAT('[&#34;', REPLACE(&#64;keyword, ',', '&#34;,&#34;'), '&#34;]')); /* p.s 편의상 위와 같이 작성했지만, 조회하는 'A2,A4,A5'를 json array 형태 ([&#34;A2&#34;, &#34;A4&#34;, &#34;A5&#34;])로 변환하는 작업은 DB에서 하지 말고 백엔드에서 처리 후 DB에 넘기는 식으로 하시기 바랍니다. 보시면 아래 쿼리가 훨씬 깔끔하고 불필요한 문자열 함수 사용을 배제할 수 있어서 더 좋습니다. */ SET &#64;keyword &#61; '[&#34;A2&#34;, &#34;A4&#34;, &#34;A5&#34;]'; SELECT * FROM t1 WHERE JSON_OVERLAPS(seats_json, &#64;keyword);단, MySQL 8.0.17 이상의 버전을 사용하고 계셔야 합니다.

이러한 경우에 쿼리를 어떻게 작성해야 할까요?

4개의 답변

Q&A 목록