eafonyang
54 lines
2.7 KiB
SQL
54 lines
2.7 KiB
SQL
-- ============================================================
|
||
-- 05_media.sql
|
||
-- 素材库：媒体资源、标签
|
||
-- 对应 proposal.md 第 6 节
|
||
-- ============================================================
|
||
|
||
-- -----------------------------------------------------------
|
||
-- 6.2 素材库
|
||
-- -----------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `www_media` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`filename` VARCHAR(255) NOT NULL COMMENT '原始文件名',
|
||
`file_path` VARCHAR(500) NOT NULL COMMENT '存储路径（相对 uploads/）',
|
||
`file_type` VARCHAR(10) NOT NULL COMMENT '类型: image / video / file',
|
||
`mime_type` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'MIME 类型',
|
||
`file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '文件大小（字节）',
|
||
`width` INT DEFAULT NULL COMMENT '图片/视频宽度（px）',
|
||
`height` INT DEFAULT NULL COMMENT '图片/视频高度（px）',
|
||
`category` VARCHAR(20) NOT NULL DEFAULT 'other' COMMENT '分类: product / banner / news / brand / video / document / other',
|
||
`thumbnail_url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '缩略图路径（300px 宽）',
|
||
`webp_url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'WebP 版本路径（图片自动生成）',
|
||
`ref_count` INT NOT NULL DEFAULT 0 COMMENT '引用计数',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`deleted_at` DATETIME DEFAULT NULL COMMENT '软删除时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_category` (`category`),
|
||
KEY `idx_filename` (`filename`),
|
||
KEY `idx_created` (`created_at`),
|
||
KEY `idx_deleted` (`deleted_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
||
COMMENT='素材库';
|
||
|
||
-- -----------------------------------------------------------
|
||
-- 素材标签
|
||
-- -----------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `www_media_tags` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`tag_name` VARCHAR(50) NOT NULL COMMENT '标签名',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_tag_name` (`tag_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
||
COMMENT='素材标签';
|
||
|
||
-- -----------------------------------------------------------
|
||
-- 素材-标签多对多关联
|
||
-- -----------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `www_media_tag_map` (
|
||
`media_id` BIGINT UNSIGNED NOT NULL,
|
||
`tag_id` BIGINT UNSIGNED NOT NULL,
|
||
PRIMARY KEY (`media_id`, `tag_id`),
|
||
KEY `idx_tag` (`tag_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
||
COMMENT='素材-标签关联';
|