컬럼 구조 (Columns Definition)
18개 컬럼| 컬럼명 | 데이터 타입 | Null 허용 | Key | 기본값 (Default) | 추가 속성 (Extra) | 코멘트 / 설명 |
|---|---|---|---|---|---|---|
|
id
|
int(11) | NO | PRI | NULL | auto_increment | - |
|
keyword_id
|
int(11) | NO | MUL | NULL | - | - |
|
title
|
varchar(500) | NO | MUL | NULL | - | - |
|
url
|
varchar(1000) | NO | - | NULL | - | - |
|
url_hash
|
char(32) | NO | UNI | NULL | - | - |
|
source
|
varchar(100) | YES | MUL | NULL | - | - |
|
published_at
|
datetime | YES | - | NULL | - | - |
|
raw_text
|
mediumtext | YES | MUL | NULL | - | - |
|
summary_3lines
|
text | YES | - | NULL | - | - |
|
importance_score
|
tinyint(4) | YES | - | 3 | - | - |
|
insight
|
text | YES | - | NULL | - | - |
|
is_mailed
|
tinyint(1) | YES | - | 0 | - | - |
|
created_at
|
timestamp | YES | MUL | current_timestamp() | - | - |
|
word_count
|
smallint(5) unsigned | YES | - | 0 | - | 본문 단어수 |
|
read_count
|
int(10) unsigned | YES | - | 0 | - | 조회수 |
|
collected_at
|
datetime | YES | - | NULL | - | 수집 시각(=created_at 과 구분) |
|
is_featured
|
tinyint(1) | YES | - | 0 | - | 추천/고정 기사 |
|
tags
|
varchar(500) | YES | - | NULL | - | 자동 태그 (콤마 구분) |
인덱스 정의 (Indexes)
| Key Name | 컬럼명 | 고유성 (Unique) | 인덱스 타입 |
|---|---|---|---|
| PRIMARY | id | UNIQUE | BTREE |
| url_hash | url_hash | UNIQUE | BTREE |
| idx_keyword | keyword_id | NON-UNIQUE | BTREE |
| idx_created | created_at | NON-UNIQUE | BTREE |
| idx_keyword_date_score | keyword_id | NON-UNIQUE | BTREE |
| idx_keyword_date_score | published_at | NON-UNIQUE | BTREE |
| idx_keyword_date_score | importance_score | NON-UNIQUE | BTREE |
| idx_source | source | NON-UNIQUE | BTREE |
| ft_articles_search | title | NON-UNIQUE | FULLTEXT |
| ft_articles_search | summary_3lines | NON-UNIQUE | FULLTEXT |
| ft_articles_search | insight | NON-UNIQUE | FULLTEXT |
| ft_articles_rawtext | raw_text | NON-UNIQUE | FULLTEXT |
CREATE TABLE DDL
CREATE TABLE `articles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `keyword_id` int(11) NOT NULL, `title` varchar(500) NOT NULL, `url` varchar(1000) NOT NULL, `url_hash` char(32) NOT NULL, `source` varchar(100) DEFAULT NULL, `published_at` datetime DEFAULT NULL, `raw_text` mediumtext DEFAULT NULL, `summary_3lines` text DEFAULT NULL, `importance_score` tinyint(4) DEFAULT 3, `insight` text DEFAULT NULL, `is_mailed` tinyint(1) DEFAULT 0, `created_at` timestamp NULL DEFAULT current_timestamp(), `word_count` smallint(5) unsigned DEFAULT 0 COMMENT '본문 단어수', `read_count` int(10) unsigned DEFAULT 0 COMMENT '조회수', `collected_at` datetime DEFAULT NULL COMMENT '수집 시각(=created_at 과 구분)', `is_featured` tinyint(1) DEFAULT 0 COMMENT '추천/고정 기사', `tags` varchar(500) DEFAULT NULL COMMENT '자동 태그 (콤마 구분)', PRIMARY KEY (`id`), UNIQUE KEY `url_hash` (`url_hash`), KEY `idx_keyword` (`keyword_id`), KEY `idx_created` (`created_at`), KEY `idx_keyword_date_score` (`keyword_id`,`published_at` DESC,`importance_score` DESC), KEY `idx_source` (`source`(50)), FULLTEXT KEY `ft_articles_search` (`title`,`summary_3lines`,`insight`), FULLTEXT KEY `ft_articles_rawtext` (`raw_text`) ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci