테이블 생성 query
게시판 좋아요 테이블과 댓글 좋아요 테이블을 따로 만든 이유는 모든 게시판과 댓글을 공통 로직으로 EVENT Query를 처리할 수 있기 때문
CURRENT_TIMESTAMP는 현재 날짜와 시간을 자동으로 삽입
g-- 사용자 정보 테이블
CREATE TABLE users (
user_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 사용자 고유 ID
email VARCHAR(255) NOT NULL UNIQUE, -- 사용자 이메일 (중복 불가)
password VARCHAR(255) NOT NULL, -- 비밀번호 (암호화 권장)
nickname VARCHAR(100), -- 닉네임
phone_number VARCHAR(20) UNIQUE, -- 전화번호
is_phone_verified BOOLEAN DEFAULT FALSE, -- 전화번호 인증 여부
profile_image_url VARCHAR(512), -- 프로필 이미지 경로(URL)
profile_image_name VARCHAR(255), -- 실제 저장된 이미지 파일명
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 가입일
-- 계정 상태(일반 사용자인지 정지된 사용자인지 구분)
status ENUM('active', 'suspended') DEFAULT 'active'
);
-- 사용자 정지 이력 테이블
CREATE TABLE suspensions (
suspension_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 정지 이력 고유 ID
user_id BIGINT NOT NULL, -- 정지 대상 사용자 ID
reason TEXT, -- 정지 사유
-- 정지 시작일, 정지 기록 생성일과 동일
start_date DATETIME DEFAULT CURRENT_TIMESTAMP,
end_date DATETIME, -- 정지 종료일
FOREIGN KEY (user_id) REFERENCES users(user_id) -- 외래키: 사용자 참조
);
-- 신고 정보 테이블
CREATE TABLE reports (
report_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 신고 고유 ID
reporter_id BIGINT NOT NULL, -- 신고자 ID
reported_id BIGINT NOT NULL, -- 신고 대상자 ID
reason TEXT, -- 신고 사유
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 신고일
FOREIGN KEY (reporter_id) REFERENCES users(user_id), -- 외래키: 신고자 ID
-- 외래키: 신고 대상자 ID = 게시글 작성자 ID
FOREIGN KEY (reported_id) REFERENCES users(user_id)
);
-- 자유 게시판 테이블
CREATE TABLE free_board (
post_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 게시글 ID
user_id BIGINT NOT NULL, -- 작성자 ID
title VARCHAR(255), -- 제목
content TEXT, -- 내용
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 작성일
updated_at DATETIME, -- 수정일
views INT DEFAULT 0, -- 조회수
is_popular BOOLEAN DEFAULT FALSE, -- 인기 게시물 여부
FOREIGN KEY (user_id) REFERENCES users(user_id) -- 외래키: 작성자 ID
);
-- 지도 게시판 테이블
CREATE TABLE map_posts (
post_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 게시글 ID
user_id BIGINT NOT NULL, -- 작성자 ID
title VARCHAR(255), -- 제목
content TEXT, -- 내용
latitude DOUBLE, -- 위도
longitude DOUBLE, -- 경도
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 작성일
is_popular BOOLEAN DEFAULT FALSE, -- 인기 여부
FOREIGN KEY (user_id) REFERENCES users(user_id) -- 외래키: 작성자 ID
);
-- 익명 게시판 테이블
CREATE TABLE anonymous_board (
post_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 게시글 ID
user_id BIGINT NOT NULL, -- 작성자 ID
content TEXT, -- 내용
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 작성일
is_popular BOOLEAN DEFAULT FALSE, -- 인기 여부
FOREIGN KEY (user_id) REFERENCES users(user_id) -- 외래키: 작성자 ID
);
-- 익명 댓글 테이블
CREATE TABLE anonymous_comments (
comment_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 댓글 ID
post_id BIGINT NOT NULL, -- 익명 게시글 ID
user_id BIGINT NOT NULL, -- 댓글 작성자 ID
anonymous_nickname VARCHAR(20), -- 익명 닉네임 (예: 익명1)
content TEXT, -- 댓글 내용
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 작성일
FOREIGN KEY (post_id) REFERENCES anonymous_board(post_id), -- 외래키: 게시판 ID
FOREIGN KEY (user_id) REFERENCES users(user_id) -- 외래키: 댓글 작성자 ID
);
-- 사용자 위치 핀 저장 테이블
CREATE TABLE user_pins (
pin_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 핀 ID
user_id BIGINT NOT NULL, -- 사용자 ID
latitude DOUBLE, -- 위도
longitude DOUBLE, -- 경도
label VARCHAR(255), -- 핀 이름/설명
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 생성일
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- 게시판 좋아요 기록 테이블 (게시판별 분기)
CREATE TABLE likes (
like_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 좋아요 ID
user_id BIGINT NOT NULL, -- 좋아요 누른 사용자 ID
post_type ENUM('free', 'map', 'anonymous') NOT NULL, -- 게시글 타입 구분
post_id BIGINT NOT NULL, -- 게시글 ID
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 좋아요 누른 시간
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- 댓글 좋아요 테이블
CREATE TABLE comment_likes (
like_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 댓글 좋아요 ID
user_id BIGINT NOT NULL, -- 좋아요 누른 사용자 ID
comment_id BIGINT NOT NULL, -- 좋아요 대상 댓글 ID
comment_type ENUM('anonymous', 'general') NOT NULL, -- 댓글 타입 구분
post_type ENUM('free', 'map') DEFAULT NULL, -- 일반 댓글(general)일 때 게시판 종류
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 좋아요 누른 시간
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- 일반 댓글 테이블 (자유, 지도 게시판 용)
CREATE TABLE comments (
comment_id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 댓글 ID
user_id BIGINT NOT NULL, -- 작성자 ID
post_type ENUM('free', 'map') NOT NULL, -- 게시판 구분
post_id BIGINT NOT NULL, -- 게시글 ID
content TEXT, -- 댓글 내용
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 작성일
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- 세션 메타데이터를 저장하는 테이블
CREATE TABLE SPRING_SESSION (
PRIMARY_ID CHAR(36) NOT NULL, -- 내부적으로 사용하는 기본 키 (UUID)
SESSION_ID CHAR(36) NOT NULL, -- JSESSIONID와 매핑되는 외부 세션 ID
CREATION_TIME BIGINT NOT NULL, -- 세션 생성 시각 (Unix 시간)
LAST_ACCESS_TIME BIGINT NOT NULL, -- 마지막 요청 시간 (Unix 시간)
MAX_INACTIVE_INTERVAL INT NOT NULL, -- 비활성 세션 만료까지의 시간 (초 단위)
EXPIRY_TIME BIGINT NOT NULL, -- 세션 만료 시각 (CREATION_TIME + MAX_INACTIVE_INTERVAL)
PRINCIPAL_NAME VARCHAR(100), -- 세션에 연결된 사용자 이름 (Spring Security의 username)
CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (PRIMARY_ID) -- 기본 키 제약 조건
);
-- 세션 ID에 대한 고유 인덱스
CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);
-- 만료 시간 기준 조회를 위한 인덱스 (스케줄러가 주기적으로 정리할 때 사용)
CREATE INDEX SPRING_SESSION_IX2 ON SPRING_SESSION (EXPIRY_TIME);
-- 사용자 이름(Principal) 기반 조회용 인덱스 (로그인 사용자 세션 조회 등에 사용)
CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);
-- 세션에 저장되는 속성들을 저장하는 테이블 (key-value 구조)
CREATE TABLE SPRING_SESSION_ATTRIBUTES (
SESSION_PRIMARY_ID CHAR(36) NOT NULL, -- SPRING_SESSION의 기본 키와 연결되는 외래 키
ATTRIBUTE_NAME VARCHAR(200) NOT NULL, -- 저장할 속성 이름 (예: "SPRING_SECURITY_CONTEXT")
ATTRIBUTE_BYTES BLOB NOT NULL, -- 직렬화된 속성 값
CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME), -- 복합 기본 키
CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID)
REFERENCES SPRING_SESSION(PRIMARY_ID) ON DELETE CASCADE -- 부모 세션 삭제 시 속성도 함께 삭제
);
인기 게시물 조회 query
-- 인기 게시물 업데이트 이벤트 (1일 1회)
CREATE EVENT update_popular_posts
ON SCHEDULE EVERY 1 DAY -- 매일 1회 실행
-- 이벤트 수행할 SQL문
DO
BEGIN
-- 자유 게시판 인기 여부 갱신
UPDATE free_board fb
SET is_popular = TRUE -- 조건을 만족하는 게시글의 is_popular 컬럼을 TRUE로 설정
WHERE fb.post_id IN (
SELECT fb.post_id
FROM free_board fb
LEFT JOIN likes l ON l.post_type = 'free' AND l.post_id = fb.post_id
LEFT JOIN comments c ON c.post_type = 'free' AND c.post_id = fb.post_id
WHERE fb.created_at >= NOW() - INTERVAL 7 DAY
GROUP BY fb.post_id
HAVING COUNT(DISTINCT l.like_id) >= 10 AND COUNT(DISTINCT c.comment_id) >= 5
);
-- 지도 게시판 인기 여부 갱신
UPDATE map_posts mp
SET is_popular = TRUE
WHERE mp.post_id IN (
SELECT mp.post_id
FROM map_posts mp
LEFT JOIN likes l ON l.post_type = 'map' AND l.post_id = mp.post_id
LEFT JOIN comments c ON c.post_type = 'map' AND c.post_id = mp.post_id
WHERE mp.created_at >= NOW() - INTERVAL 7 DAY
GROUP BY mp.post_id
HAVING COUNT(DISTINCT l.like_id) >= 10 AND COUNT(DISTINCT c.comment_id) >= 5
);
-- 익명 게시판 인기 여부 갱신
UPDATE anonymous_board ab
SET is_popular = TRUE
WHERE ab.post_id IN (
SELECT ab.post_id
FROM anonymous_board ab
LEFT JOIN likes l ON l.post_type = 'anonymous' AND l.post_id = ab.post_id
LEFT JOIN anonymous_comments ac ON ac.post_id = ab.post_id
WHERE ab.created_at >= NOW() - INTERVAL 7 DAY
GROUP BY ab.post_id
HAVING COUNT(DISTINCT l.like_id) >= 10 AND COUNT(DISTINCT ac.comment_id) >= 5
);
END;
ALTER EVENT update_popular_posts
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
-- 자유 게시판 인기 여부 갱신
UPDATE free_board fb
SET is_popular = TRUE
WHERE fb.post_id IN (
SELECT fb.post_id
FROM free_board fb
LEFT JOIN likes l ON l.post_type = 'free' AND l.post_id = fb.post_id
LEFT JOIN comments c ON c.post_type = 'free' AND c.post_id = fb.post_id
WHERE fb.created_at >= NOW() - INTERVAL 7 DAY
GROUP BY fb.post_id
HAVING COUNT(DISTINCT l.like_id) >= 20 AND COUNT(DISTINCT c.comment_id) >= 8
);
-- 지도 게시판 인기 여부 갱신
UPDATE map_posts mp
SET is_popular = TRUE
WHERE mp.post_id IN (
SELECT mp.post_id
FROM map_posts mp
LEFT JOIN likes l ON l.post_type = 'map' AND l.post_id = mp.post_id
LEFT JOIN comments c ON c.post_type = 'map' AND c.post_id = mp.post_id
WHERE mp.created_at >= NOW() - INTERVAL 7 DAY
GROUP BY mp.post_id
HAVING COUNT(DISTINCT l.like_id) >= 20 AND COUNT(DISTINCT c.comment_id) >= 8
);
-- 익명 게시판 인기 여부 갱신
UPDATE anonymous_board ab
SET is_popular = TRUE
WHERE ab.post_id IN (
SELECT ab.post_id
FROM anonymous_board ab
LEFT JOIN likes l ON l.post_type = 'anonymous' AND l.post_id = ab.post_id
LEFT JOIN anonymous_comments ac ON ac.post_id = ab.post_id
WHERE ab.created_at >= NOW() - INTERVAL 7 DAY
GROUP BY ab.post_id
HAVING COUNT(DISTINCT l.like_id) >= 20 AND COUNT(DISTINCT ac.comment_id) >= 8
);
END;