| |
| CREATE TABLE IF NOT EXISTS `tpl_app_star_data` ( |
| `id` INT(11) NOT NULL COMMENT '明星ID（使用接口返回的id字段）', |
| `name` VARCHAR(255) DEFAULT NULL COMMENT '姓名', |
| `avatar` VARCHAR(512) DEFAULT NULL COMMENT '头像URL（带https:前缀）', |
| `avatar_oss` VARCHAR(512) DEFAULT NULL COMMENT '头像OSS存储路径', |
| `alias` VARCHAR(255) DEFAULT NULL COMMENT '别名', |
| `nationality` VARCHAR(100) DEFAULT NULL COMMENT '国籍（英文）', |
| `constellation` VARCHAR(50) DEFAULT NULL COMMENT '星座', |
| `weight` VARCHAR(50) DEFAULT NULL COMMENT '体重', |
| `birthplace` VARCHAR(255) DEFAULT NULL COMMENT '出生地', |
| `agency` VARCHAR(255) DEFAULT NULL COMMENT '经纪公司', |
| `graduated_school` VARCHAR(255) DEFAULT NULL COMMENT '毕业院校', |
| `foreign_name` VARCHAR(255) DEFAULT NULL COMMENT '外文名', |
| `ethnicity` VARCHAR(50) DEFAULT NULL COMMENT '民族', |
| `blood_type` VARCHAR(50) DEFAULT NULL COMMENT '血型（纯字母）', |
| `height` VARCHAR(50) DEFAULT NULL COMMENT '身高', |
| `birthday` VARCHAR(100) DEFAULT NULL COMMENT '出生日期（YYYY-MM-DD格式）', |
| `profession` VARCHAR(255) DEFAULT NULL COMMENT '职业', |
| `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间', |
| `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间', |
| PRIMARY KEY (`id`), |
| KEY `idx_name` (`name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='明星数据表'; |
|
|
| CREATE TABLE `tpl_app_user_devices` ( |
| `client_source` varchar(16) NOT NULL DEFAULT 'unknown' COMMENT '来源: android/wechat/ios/web/unknown', |
| `device_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户设备唯一ID (x-device-id)', |
| `display_name` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户自定义公开昵称，空表示未设置', |
| `display_name_initialized` tinyint(1) NOT NULL DEFAULT 0 COMMENT '昵称已设置或主动清空，阻止自动初始化覆盖', |
| `device_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备类型 (x-device-type)', |
| `device_model` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备型号 (x-device-model)', |
| `os_version` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作系统版本 (x-os-version)', |
| `app_version` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'App 版本号 (x-app-version)', |
| `timezone` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '客户端时区 (x-timezone)', |
| `region` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户地区信息 (x-region)', |
| `language` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '语言 (Accept-Language)', |
| `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', |
| `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后活跃时间', |
| PRIMARY KEY (`device_id`), |
| KEY `idx_updated_at` (`updated_at`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
|
|
| CREATE TABLE `tpl_app_processed_images` ( |
| `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
| `client_source` varchar(16) NOT NULL DEFAULT 'unknown' COMMENT '来源: android/wechat/ios/web/unknown', |
| `hostname` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', |
| `region` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户地区代码 (x-region)', |
| `file_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, |
| `category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'other', |
| `nickname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, |
| `score` decimal(10,4) NOT NULL DEFAULT '0.0000', |
| `is_cropped_face` tinyint(1) NOT NULL DEFAULT '0', |
| `size_bytes` bigint(20) unsigned NOT NULL DEFAULT '0', |
| `last_modified` datetime NOT NULL, |
| `bos_uploaded` tinyint(1) NOT NULL DEFAULT '0', |
| `extra_metadata` json DEFAULT NULL, |
| `processing_duration_ms` int(11) DEFAULT NULL, |
| `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| PRIMARY KEY (`id`), |
| KEY `idx_source_category_updated` (`client_source`, `category`, `updated_at`), |
| KEY `idx_source_category_region_updated` (`client_source`, `category`, `region`, `updated_at`), |
| UNIQUE KEY `uq_face_image_file_path` (`file_path`), |
| KEY `idx_category` (`category`), |
| KEY `idx_nickname` (`nickname`), |
| KEY `idx_last_modified` (`last_modified`), |
| KEY `idx_region` (`region`) |
| ) ENGINE=InnoDB AUTO_INCREMENT=62501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
|
|
