MySQL 카테고리 테이블과 SELECT 조회 INSERT 입력 쿼리 만들기

mysql 에서 카테고리 테이블의 형식은 자식키와 부모키의 조합으로 만들 수 있다. 이 형식은 지금도 많이 사용되지만 SELECT 시 난해한 점이 여럿 발생한다. WITH 을 사용하면 편하지만 사용하지 못하는 구버전 mysql 8.0 이전의 것들은 고전적인 방법을 통해 구현할 수 있다. 문자열 정렬로써 가능하다.

MySQL 카테고리 테이블과 SELECT 조회 쿼리 만들기

데이터베이스 프로그래밍에서 카테고리 테이블을 만들고 조작하는 것은 쉽다면 쉽고 어렵다면 어렵다.

버전이 높은 DB 를 사용한다면 DB 에서 제공하는 구문을 사용하면 되지만, 버전이 낮은 경우라면 이건 또 얘기가 다르다.

DB 에서 처리가 안될 수도 있고, 백엔드에서 처리할 수 밖에 없을 수도 없다.

어쨌든 처리만 하면 그만이지만, 데이터가 많을 경우 DB 에서 처리하는 것이 속도면에서 유리하다.

1세대 선배들은 어떻게 했을까?

디비에서 처리하는 분들이 있었고, 서버사이드에서 처리한 분들도 있었으며 극단적으로 자바스크립트를 이용하기도 했다.

과거 윈도우용 프로그램이라면 반복문으로 처리했었다.

MySQL 8.0 이전 버전들이라면 with 구문을 사용하지 못할 수 있기에 고전적인 방법을 사용할 필요가 있다.

최신기술도 좋지만, 옛날의 고전적 방법은 어려운 난제를 해결하는 도움이 된다.

이것을 해결하는 방법은 문자열 정렬을 통해 가능하다.

카테고리 테이블 구조

카테고리 테이블은 부모키와 자식키의 연관관계를 이용한 테이블 구조를 말한다.

고유한 숫자 또는 문자를 이용하여 자식키로 연결되어 SELECT 의 조회 결과를 쉽게 하도록 한다.

아래의 이미지는 부모키와 자식키를 보여준다.

위의 이미지는 부모키와 자식키에 대한 것을 보여준다.

부모키가 1인 데이터 2, 3 의 인덱스키는 자식키이다.

부모키가 2인 데이터 4 의 인덱스키는 자식키이다.

부모키가 3인 데이터 5 의 인덱스키는 자식키이다.

만드는 방법

여기서는 테이블의 구조와 SELECT 와, INSERT 를 위한 개략적인 순서를 나열한다.

UPDATE DELETE 는 INSERT 만 가능하면 쉽다.

아래는 카테고리 테이블과 조작을 위한 방법을 보여준다.

1. 테이블 만들기
2. SELECT 쿼리 만들기
3. INSERT 쿼리 테스트

테이블 만들기

아래는 mysql 에서 만든 카테고리 테이블을 보여준다.

가장 위에 있는 idx 는 고유키이면서 자식키가 될 수 있고, 부모키가 될 수 있다.

컬럼의 idxParent 는 부모키를 가리키며 고유키인 idx 를 가리킨다.

부모키로 지정한 idxParent 으로 SELECT 조회 쿼리에서 조건을 준다면 해당 데이터만 불러올 것이다.

이런 형식은 카테고리로써 동작을 할 수 있도록 해 준다.

이제 테이블을 만들어 본다.

아래는 위의 mysql 카테고리 테이블을 만드는 CREATE 테이블 생성 쿼리를 보여준다.

insDate, updDate 는 없어도 그만이다.

/* 테이블 생성 */
CREATE TABLE tb_category (
	idx varchar(17) COMMENT '인덱스',
	insDate timestamp COMMENT '입력일',
	updDate timestamp COMMENT '수정일',
	idxParent varchar(17) COMMENT '부모 인덱스',
	cateOrder varchar(100) COMMENT '순서',
	cateTitle varchar(25) COMMENT '제목'
) COMMENT '카테고리'
;

이상한 점을 하나 발견했을 것이다. idx 와 idxParent 컬럼이 보통은 숫자형인 number 를 사용할 수 있지만, 여기서는 문자를 사용했다.

상관없다. number 로 수정하여 사용해도 된다.

단지, 본인의 버릇이 있기에 문자인 varchar 로 생성했을 뿐이다.

mysql 의 CREATE 테이블 생성 쿼리에 대한 내용은 아래를 참고한다.

mysql 테이블 생성 템플릿 만들기 CREATE TABLE

SELECT 조회 쿼리 만들기

위에서 만든 카테고리 테이블을 구조를 확인해 보고, 아래의 코드를 보자.

정렬을 위해 cateOrder 컬럼에 오름차순 정렬을 설정하고 있다.

cateOrder 컬럼의 데이터는 문자열임을 확인하자.

문자열도 오름차순 내림차순 할 수 있으며, 숫자형태의 정렬도 가능하다.

이것을 이용하여 WITH 구문을 사용하지 않아도 전체적으로 정렬된 카테고리를 볼 수 있도록 하는데 목적이 있다.

SELECT 쿼리는 의외로 간단하다.

SELECT *
  FROM tb_category
 ORDER BY cateOrder ASC
;

입력쿼리 INSERT 쿼리 만들기

이제 입력쿼리를 만들어 본다.

카테고리 테이블에 필요한 것은 게시물의 고유키와 고유키를 지정할 부모카테고리 그리고 카테고리의 순서를 결정할 컬럼이다.

고유키는 idx 컬럼의 최대값에서 1 을 하나 증가시켜 작업한다.
여기서는 idx 컬럼이 varchar 형식이기 때문에 1 하는 것이지만 숫자형 auto_increment 라면 상관없다.

부모카테고리는 이미 존재하는 idx 카테고리 컬럼 번호를 지정한다.

카테고리 순서는 문자열로 순서를 결정하는데 여기서는 점을 이용한다.

이 구조가 조금 독특한다. 점 ( . ) 이 증가하면서 depth 가 정의되는 것을 볼 수 있다.

SET @idxParent = 19 -- 부모카테고리
;
SET @cateTitle = 'concept-E-1-1' -- 카테고리 제목
;
SET @idx = ( SELECT ( MAX(CAST(idx AS UNSIGNED))+1 )  FROM tb_category ) -- 카데고리 인덱스 설정
;
SET @cateOrder = ( SELECT cateOrder FROM tb_category WHERE idx = @idxParent ) -- 카데고리 순서 설정
;
SET @cateOrder = CONCAT( @cateOrder, '.', LPAD( @idx, 2, '0' ) ) -- 카데고리 순서 설정
;

INSERT INTO tb_category 
( idx, insDate, updDate, idxParent, cateOrder, cateTitle )
VALUES
( @idx, NOW(), NOW(), @idxParent, @cateOrder, @cateTitle )
;

결과 화면

아래는 최종적으로 구현한 카테고리 테이블의 구조와 SELECT 한 결과를 보여준다.

주목할 점은 cateOrder 컬럼부분이다.

지금은 WITH 구문을 사용하여 카테고리 테이블의 SELECT 결과를 편하게 구현할 수 있지만, WITH 구문을 사용하지 못하는 경우 아래와 같은 형식을 이용할 수 있다.

인덱스키와 부모키를 조합하고 정렬을 위한 숫자를 이용하여 문자열 정렬을 통해 구현한 것이다.

테스트를 해보자.

INSERT 쿼리를 보면 입력하는 값은 부모카테고리와 카테고리 제목 부분 뿐이다.

이 2개만을 입력으로 받고 나머지는 자동으로 계산하고 INSERT 를 처리한후 조회를 하도록 한다.

전체쿼리는 다음과 같다.

최소한의 입력값은 부모카테고리 idx 값과 카데고리명 만을 입력받아 처리하게 된다.

USE test
;

SET @idxParent = 1 -- 부모카테고리
;
SET @cateTitle = 'ROOT' -- 카테고리 제목
;

SET @idx = ( SELECT IFNULL(MAX(CAST( idx AS UNSIGNED)), 0)+1 FROM tb_category ) -- 카데고리 인덱스 설정
;
SET @cateOrder = ( SELECT IFNULL(cateOrder, '1') FROM tb_category WHERE idx = @idxParent ) -- 카데고리 순서 설정
;
SET @cateOrder = IFNULL ( CONCAT( @cateOrder, '.', LPAD( @idx, 2, '0' ) ), 1 ) -- 카데고리 순서 설정
;

/*
SELECT @idx
	  , @idxParent
     , @cateOrder
     , @cateTitle
;

use test
;
delete from tb_category
;
*/

INSERT INTO tb_category 
( idx, insDate, updDate, idxParent, cateOrder, cateTitle )
VALUES
( @idx, NOW(), NOW(), @idxParent, @cateOrder, @cateTitle )
;

SELECT *
  FROM tb_category
 ORDER BY cateOrder ASC
;

SELECT *
  FROM tb_category
 ORDER BY CAST( idx AS UNSIGNED) ASC
;

위의 코드를 실행하기 위해 테스트한 값과 순서는 다음과 같다.

무작위로 입력한다는 가정하에 이것저것 입력하여 보았다.

위의 전체코드의 가장 위에 있는 mysql 변수에 값을 하나씩 지정하고 입력하고 테스트하였다.

/**
 * @idxParent : 부모카테고리
 *  @cateTitle : 카테고리명
 */
SET @idxParent = 1; SET @cateTitle = 'ROOT';
SET @idxParent = 1; SET @cateTitle = '프로그래밍';
SET @idxParent = 1; SET @cateTitle = '자동차';
SET @idxParent = 2; SET @cateTitle = 'C언어';
SET @idxParent = 3; SET @cateTitle = '현대자동차';
SET @idxParent = 2; SET @cateTitle = 'C++';
SET @idxParent = 2; SET @cateTitle = 'C#';
SET @idxParent = 2; SET @cateTitle = 'JAVA';
SET @idxParent = 3; SET @cateTitle = '쌍용자동차';
SET @idxParent = 5; SET @cateTitle = '전기차';
SET @idxParent = 5; SET @cateTitle = '1600cc';
SET @idxParent = 7; SET @cateTitle = '콘솔';
SET @idxParent = 7; SET @cateTitle = '윈폼(WinForm)';
SET @idxParent = 7; SET @cateTitle = 'WPF';
SET @idxParent = 7; SET @cateTitle = 'UWP';
SET @idxParent = 7; SET @cateTitle = 'ASP.NET';
SET @idxParent = 7; SET @cateTitle = 'MVC5';
SET @idxParent = 8; SET @cateTitle = 'SPRING';

입력이 잘 되었는지 idx 를 기준으로 조회를 해 보도록 한다.

입력한 순서대로 잘 들어가고 있으며 이제는 cateOrder 를 기준으로 정렬을 하면 된다.

이번엔 cateOrder 컬럼을 기준으로 정렬을 해 보도록 한다.

위에서도 언급했듯이 이 방법은 고전적인 방법이기도 하며 문자열 정렬을 이용한 것이다.

정렬이 잘 되고 있음을 보여준다.

조금 난해할 수도 있다.

위의 쿼리는 mysql 8.0 이전 버전에서 WITH 와 같은 편한 구문을 사용하지 못할 경우 사용할 수 있는 고전적인 방법이다.

쿼리보다는 이미지의 데이터가 입력된 형태를 보면 어떻게 구현을 하면 될지 이해가 될 것이다.

위의 카테고리 테이블의 중요한 점은 cateOrder 컬럼을 문자열로 일정한 값을 만들고 문자열 정렬을 이용한다는 점에 있다.