CREATE TABLE payload ( pid CHAR(22) NOT NULL PRIMARY KEY , title VARCHAR(100) NOT NULL , subtitle VARCHAR(100) , body VARCHAR(255) NOT NULL , alert_yn BOOLEAN NOT NULL DEFAULT TRUE, category CHAR(4) NOT NULL , content TEXT ); CREATE TABLE push_cabinet ( uid CHAR(6) NOT NULL , pid CHAR(22) NOT NULL , send_date DATETIME NOT NULL , check_yn BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY (uid, pid), CONSTRAINT fk_pushcabinet_uid_to_user FOREIGN KEY (uid) REFERENCES user(uid) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_pushcabinet_pid_to_payload FOREIGN KEY (pid) REFERENCES payload(pid) ON UPDATE CASCADE ON DELETE CASCADE ); INSERT INTO payload(pid, title, subtitle, body, category, content) VALUES ( 'AP202503054Q81114002W7', 'PUSH 확인 TEST', 'PUSH 확인 TEST - SUB', 'PUSH를 테스트하기 위해서 작성된 내용입니다. TEST 메시지입나다.', 'PC00', NULL ); INSERT INTO payload(pid, title, subtitle, body, category, content) VALUES ( 'AP202503051S41516009J9', 'PUSH 확인 TEST2', NULL, 'PUSH를 테스트하기 위해서 작성된 두번째 내용입니다. 두번째 TEST 메시지입나다.', 'PC00', NULL ); ALTER TABLE push_cabinet DROP FOREIGN KEY fk_pushcabinet_uid_to_user; ALTER TABLE push_cabinet ADD CONSTRAINT fk_pushcabinet_uid_to_user FOREIGN KEY (uid) REFERENCES user(uid) ON UPDATE CASCADE ON DELETE CASCADE; CREATE TABLE log_push ( bid CHAR(6) NOT NULL , pid CHAR(22) NOT NULL , create_date DATETIME NOT NULL , modify_date DATETIME NOT NULL , create_uid VARCHAR(70) NOT NULL , modify_uid VARCHAR(70) NOT NULL , PRIMARY KEY (bid, pid), CONSTRAINT fk_logpush_to_payload FOREIGN KEY (bid, pid) REFERENCES payload(bid, pid) ON UPDATE CASCADE ON DELETE CASCADE ); SELECT cabinet.uid, cabinet.pid, cabinet.send_date, payload.title FROM (select push_cabinet.pid, push_cabinet.uid, push_cabinet.send_date FROM push_cabinet where uid = 'AMsean.kim20241201') As cabinet inner JOIN (select payload.title, payload.pid FROM payload) as payload on cabinet.pid = payload.pid order by send_date ; CREATE TABLE push_cabinet ( id INT AUTO_INCREMENT PRIMARY KEY, -- 대리 키로 추가 uid VARCHAR(70) NOT NULL, bid CHAR(6) DEFAULT 'AA0000' NOT NULL, pid CHAR(22) NOT NULL, send_date DATETIME NOT NULL, check_yn TINYINT(1) DEFAULT 0 NOT NULL, content TEXT, -- 커스텀 이벤트 내용 저장 (필요 시 TEXT나 VARCHAR로 조정) CONSTRAINT fk_pushcabinet_bid_to_academy FOREIGN KEY (bid) REFERENCES academy (bid) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_pushcabinet_payload FOREIGN KEY (bid, pid) REFERENCES payload (bid, pid) ON UPDATE CASCADE ON DELETE CASCADE ); -- 조회 성능 향상을 위해 pid 컬럼에 대한 인덱스 추가 (필요 시 uid, send_date 등의 인덱스도 고려) CREATE INDEX idx_pushcabinet_pid ON push_cabinet (pid); ALTER TABLE push_cabinet ADD CONSTRAINT fk_pushcabinet_bid_to_academy FOREIGN KEY (bid) REFERENCES academy (bid) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE push_cabinet ADD CONSTRAINT fk_pushcabinet_payload FOREIGN KEY (bid, pid) REFERENCES payload (bid, pid) ON UPDATE CASCADE ON DELETE CASCADE;