[MySQL] 21.01.16 태그 조회, 신규 태그 추가 쿼리 복습

DROP DATABASE IF EXISTS `textBoardTest`;
CREATE DATABASE `textBoardTest`;
USE `textBoardTest`;


#article 테이블 생성
CREATE TABLE `article`(
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    title CHAR(100) NOT NULL,
    `body` TEXT NOT NULL
);

#게시물 생성
INSERT INTO article
SET 
regDate = NOW(),
updateDate = NOW(),
title = '테스트 제목 1',
`body` = '테스트 내용 1';

INSERT INTO article
SET 
regDate = NOW(),
updateDate = NOW(),
title = '테스트 제목 2',
`body` = '테스트 내용 2';

INSERT INTO article
SET 
regDate = NOW(),
updateDate = NOW(),
title = '테스트 제목 3',
`body` = '테스트 내용 3';

INSERT INTO article
SET 
regDate = NOW(),
updateDate = NOW(),
title = '테스트 제목 4',
`body` = '테스트 내용 4';

INSERT INTO article
SET 
regDate = NOW(),
updateDate = NOW(),
title = '테스트 제목 5',
`body` = '테스트 내용 5';

SELECT * FROM article;


#tag 테이블 생성
CREATE TABLE `tag`(
    id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    relTypeCode CHAR(20) NOT NULL,
    relId INT(10) UNSIGNED NOT NULL,
    `body` CHAR(20) NOT NULL
);

#tag에 인덱스 걸기(검색속도 향상을 위해)
#가장 많이 검색될만한 조합 1
ALTER TABLE `textBoardTest`.`tag` ADD INDEX (`relTypeCode`, `relId`); 
#가장 많이 검색될만한 조합 2
ALTER TABLE `textBoardTest`.`tag` ADD INDEX (`relTypeCode`, `body`); 

DESC `tag`;

#tag 생성
#1번 article에 '연습'이란 태그 걸기
INSERT INTO `tag`
SET
regDate = NOW(),
updateDate = NOW(),
relTypeCode = 'article',
relId = '1',
`body` = '연습';

#1번 article에 '태그'란 태그 걸기
INSERT INTO `tag`
SET
regDate = NOW(),
updateDate = NOW(),
relTypeCode = 'article',
relId = '1',
`body` = '태그';

#1번 article에 'sql'이란 태그 걸기
INSERT INTO `tag`
SET
regDate = NOW(),
updateDate = NOW(),
relTypeCode = 'article',
relId = '1',
`body` = 'sql';

#2번 article에 '연습'이란 태그 걸기
INSERT INTO `tag`
SET
regDate = NOW(),
updateDate = NOW(),
relTypeCode = 'article',
relId = '2',
`body` = '연습';

#2번 article에 '태그'란 태그 걸기
INSERT INTO `tag`
SET
regDate = NOW(),
updateDate = NOW(),
relTypeCode = 'article',
relId = '2',
`body` = '태그';

#3번 article에 'sql'이란 태그 걸기
INSERT INTO `tag`
SET
regDate = NOW(),
updateDate = NOW(),
relTypeCode = 'article',
relId = '3',
`body` = 'sql';

SELECT * FROM `tag`;

#article에 걸린 태그 조회하기(1)
SELECT A.id, T.body 
FROM article AS A
INNER JOIN tag AS T
ON A.id = T.relId;

#article에 걸린 태그 조회하기(2)
SELECT A.id, T.body 
FROM article AS A
INNER JOIN tag AS T
ON A.id = T.relId
GROUP BY A.id;

#article에 걸린 태그 조회하기(숨겨진 태그 모두 나열)(3)
SELECT A.id, GROUP_CONCAT(T.body) AS tags
FROM article AS A
INNER JOIN tag AS T
ON A.id = T.relId
GROUP BY A.id;

#article에 걸린 태그 조회하기(태그가 없는 게시물까지)(4)
SELECT A.id, GROUP_CONCAT(T.body) AS tags
FROM article AS A
LEFT JOIN tag AS T
ON A.id = T.relId
GROUP BY A.id;

#article에 걸린 태그 조회하기(태그가 없는 게시물까지, null값 치환)(5)
SELECT A.id, IFNULL(GROUP_CONCAT(T.body), 'x') AS tags
FROM article AS A
LEFT JOIN tag AS T
ON A.id = T.relId
GROUP BY A.id;

#article에 걸린 태그 조회하기(#을 구분자로 되어 나오게)(6)
SELECT A.id, A.title, IFNULL(GROUP_CONCAT(T.body SEPARATOR " #"), "") AS tagsStr
FROM article AS A
LEFT JOIN tag AS T
ON T.relTypeCode = 'article'
AND A.id = T.relId
GROUP BY A.id;

#article에 걸린 태그 조회하기(#을 구분자로 되어 나오게, 첫번째 태그까지)(7)
SELECT A.id, A.title, 
IF(
    GROUP_CONCAT(T.body SEPARATOR " #") IS NOT NULL,
    CONCAT('#', GROUP_CONCAT(T.body SEPARATOR " #")),
    "없음"
)
FROM article AS A
LEFT JOIN tag AS T
ON T.relTypeCode = 'article'
AND A.id = T.relId
GROUP BY A.id;




#새 태그 넣기 전 사전 준비#
##변수 설정
###새 태그가 들어갈 게시물 id
SET @articleId = 1;
###새 태그들
SET @hashStr = '#sql #DB #table';
###새 태그들 구분자
SET @hashSign = '#';     

##@hashsCount 산출 과정
SET @hashsCount = LENGTH(@hashStr);
SELECT @hashsCount;

SET @hashsCount = REPLACE(@hashStr, '#', '');  #@hashStr내에서 #을 ""으로 교체
SELECT @hashsCount;

SET @hashsCount = LENGTH(REPLACE(@hashStr, @hashSign, ''));  #@hashStr내에서 @hashSign을 ""으로 교체
SELECT @hashsCount;

SET @hashsCount = LENGTH(@hashStr) - LENGTH(REPLACE(@hashStr, @hashSign, ''));
SELECT @hashsCount;

SET @hashsCount = (LENGTH(@hashStr) - LENGTH(REPLACE(@hashStr, @hashSign, ''))) / LENGTH(@hashSign);
SELECT @hashsCount;

## @hashCount를 @hashSign으로 나누는 이유는 @hashSign 방식(ex: #,* 등)에 따라 @hashCount가 변하기 때문에 정확한 @hashStr 수를 파악하기 위해 @hashSign으로 나누어 주는 것
SET @hashsCount = (LENGTH(@hashStr) - LENGTH(REPLACE(@hashStr, @hashSign, ''))) / LENGTH(@hashSign);
SET @hashsCount = FLOOR(@hashsCount);         #소수점 반올림으로 정수화
SELECT @hashsCount;


##1부터 N까지 나오게하는 함수
WITH RECURSIVE rgen(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM rgen
    WHERE n < @hashCount

)
SELECT n
FROM rgen;


## SUBSTRING_INDEX() 함수 예제
### @hashSign을 기준으로 앞에서부터 n개까지 나오게
SELECT SUBSTRING_INDEX(@hashStr, @hashSign, 1);
SELECT SUBSTRING_INDEX(@hashStr, @hashSign, 2);
SELECT SUBSTRING_INDEX(@hashStr, @hashSign, 3);
SELECT SUBSTRING_INDEX(@hashStr, @hashSign, 4);

### @hashSign을 기준으로 뒤에서부터 n개까지 나오게
SELECT SUBSTRING_INDEX(@hashStr, @hashSign, -1);
SELECT SUBSTRING_INDEX(@hashStr, @hashSign, -2);
SELECT SUBSTRING_INDEX(@hashStr, @hashSign, -3);
SELECT SUBSTRING_INDEX(@hashStr, @hashSign, -4);

## 문장을 테이블화
### 태그들 중 DB만 나오게 하기 예제
SET @selectedNo = 2;
SET @selectedTagBody = SUBSTRING_INDEX(@hashStr, @hashSign, @selectedNo+1);
SELECT @selectedTagBody;
SET @selectedTagBody = SUBSTRING_INDEX(@selectedTagBody, @hashSign, -1);
SELECT @selectedTagBody;


### 문장을 테이블화 ver1.
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@hashStr, @hashSign, NT.n+1), @hashSign, -1) AS tagBody
FROM(
    WITH RECURSIVE rgen(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM rgen
    WHERE n < @hashsCount

)
SELECT n
FROM rgen
) AS NT;

### 문장을 테이블화 ver2. (쿼리 다이어트 + trim 적용)
WITH RECURSIVE rgen(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM rgen
    WHERE n < @hashsCount

)
SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@hashStr, @hashSign, n+1), @hashSign, -1)) AS tagBody
FROM rgen;



#새로 들어갈 태그와 기존 태그들 중복 검사 후 태그 추가, 삭제#

##변수 설정
###새 태그가 들어갈 게시물 id
SET @articleId = 1;
###새 태그들
SET @hashStr = '#sql #DB #table';
###새 태그들 구분자
SET @hashSign = '#';  
   
   
## 1.새로 들어갈 태그의 테이블
WITH RECURSIVE rgen(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM rgen
    WHERE n < @hashsCount

)
SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@hashStr, @hashSign, n+1), @hashSign, -1)) AS `body`
FROM rgen;


## 2.해당 게시물에 있는 기존 태그의 테이블
SELECT `body`
FROM tag
WHERE relTypeCode = 'article'
AND relId = 1;



## 3. 중복이 아닌 태그 조회1(이제는 삭제되어야 할 것들)
SELECT OT.body
FROM(
    SELECT `body`
    FROM tag
    WHERE relTypeCode = 'article'
    AND relId = 1
) AS OT
LEFT JOIN(
    WITH RECURSIVE rgen(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM rgen
    WHERE n < @hashsCount
    )
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@hashStr, @hashSign, n+1), @hashSign, -1)) AS `body`
    FROM rgen
) AS NT
ON OT.body = NT.body
WHERE NT.body IS NULL;


## 3. 중복이 아닌 태그 조회2(이제는 삭제되어야 할 것들)
SELECT OT.id AS tagId
FROM tag AS OT
LEFT JOIN(
    WITH RECURSIVE rgen(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM rgen
    WHERE n < @hashsCount
    )
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@hashStr, @hashSign, n+1), @hashSign, -1)) AS `body`
    FROM rgen
) AS NT
ON OT.body = NT.body
WHERE NT.body IS NULL
AND relTypeCode = 'article'
AND relId = 1;

# 삭제되어야 할 것 삭제, v1
DELETE OT
FROM tag AS OT
LEFT JOIN (
    WITH RECURSIVE rgen(n) AS (
        SELECT 1
        UNION ALL
        SELECT n + 1
        FROM rgen
        WHERE n < @hashsCount
    )
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@hashStr, @hashSign, n + 1), @hashSign, -1)) AS `body`
    FROM rgen
) AS NT
ON OT.body = NT.body
WHERE NT.body IS NULL
AND relTypeCode = 'article'
AND relId = 1;

# 삭제되어야 할 것 삭제, v2
DELETE FROM tag
WHERE id IN (
    SELECT OT.id
    FROM tag AS OT
    LEFT JOIN (
        WITH RECURSIVE rgen(n) AS (
            SELECT 1
            UNION ALL
            SELECT n + 1
            FROM rgen
            WHERE n < @hashsCount
        )
        SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@hashsStr, @hashSign, n + 1), @hashSign, -1)) AS `body`
        FROM rgen
    ) AS NT
    ON OT.body = NT.body
    WHERE NT.body IS NULL
    AND relTypeCode = 'article'
    AND relId = 1
);



# 추가 되어야 하는 녀석들 가져오기
SELECT NT.body
FROM (
    WITH RECURSIVE rgen(n) AS (
        SELECT 1
        UNION ALL
        SELECT n + 1
        FROM rgen
        WHERE n < @hashsCount
    )
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@hashStr, @hashSign, n + 1), @hashSign, -1)) AS `body`
    FROM rgen
) AS NT
LEFT JOIN tag AS OT
ON OT.relTypeCode = 'article'
AND OT.relId = @articleId
AND NT.body = OT.body
WHERE OT.id IS NULL;

# 추가 되어야 하는 녀석들 추가
INSERT INTO tag
(regDate, updateDate, relTypeCode, relId, `body`)
SELECT NOW(), NOW(), 'article', @articleId, NT.body
FROM (
    WITH RECURSIVE rgen(n) AS (
        SELECT 1
        UNION ALL
        SELECT n + 1
        FROM rgen
        WHERE n < @hashsCount
    )
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@hashStr, @hashSign, n + 1), @hashSign, -1)) AS `body`
    FROM rgen
) AS NT
LEFT JOIN tag AS OT
ON OT.relTypeCode = 'article'
AND OT.relId = @articleId
AND NT.body = OT.body
WHERE OT.id IS NULL;