Files
jianli-boss/docs/upgrade_1.8.sql
2026-08-26 20:25:05 +08:00

69 lines
3.3 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- ============================================================
-- Boss直聘 AI 投递系统 - 老库升级脚本(v1.8)
-- 适用场景:数据库已存在旧表,需要补充本次新增的字段/表
--
-- 本次变更:
-- 1. apply_sessions 新增 prompt 字段(AI评分引导词)
-- 2. 新增 apply_templates 表(投递条件模板)
--
-- 执行方式(Docker MySQL):
-- docker exec -i jianli-boss-mysql mysql -uroot -p<密码> jianli_boss < docs/upgrade_1.8.sql
--
-- 注意:脚本可重复执行安全处理
-- · ALTER 用存储过程判断列不存在才添加
-- · CREATE TABLE 使用 IF NOT EXISTS
-- ============================================================
USE jianli_boss;
-- ------------------------------------------------------------
-- 1. apply_sessions 新增 prompt 列(列已存在时自动跳过)
-- ------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_add_session_prompt;
DELIMITER $$
CREATE PROCEDURE sp_add_session_prompt()
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'jianli_boss'
AND TABLE_NAME = 'apply_sessions'
AND COLUMN_NAME = 'prompt'
) THEN
ALTER TABLE apply_sessions
ADD COLUMN prompt TEXT NULL COMMENT 'AI评分引导词' AFTER salary_max;
END IF;
END$$
DELIMITER ;
CALL sp_add_session_prompt();
DROP PROCEDURE IF EXISTS sp_add_session_prompt;
-- ------------------------------------------------------------
-- 2. 新增投递条件模板表
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS apply_templates (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
name VARCHAR(128) NOT NULL COMMENT '模板名称',
keywords VARCHAR(512) NULL COMMENT '搜索关键词(逗号分隔)',
city VARCHAR(20) NULL COMMENT '城市名称',
salary_min INT NULL COMMENT '最低薪资(K)',
salary_max INT NULL COMMENT '最高薪资(K)',
min_score INT NOT NULL DEFAULT 65 COMMENT 'AI最低匹配分',
daily_limit INT NOT NULL DEFAULT 0 COMMENT '每日投递上限(0=不限)',
boss_online_only TINYINT(1) NOT NULL DEFAULT 0 COMMENT '只投递Boss在线的职位',
allow_headhunter TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否考虑猎头职位',
prompt TEXT NULL COMMENT 'AI评分引导词',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY ix_apply_templates_user_id (user_id),
CONSTRAINT fk_apply_templates_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='投递条件模板';
-- ------------------------------------------------------------
-- 3. 验证(执行后应能看到 prompt 列与 apply_templates 表)
-- ------------------------------------------------------------
SHOW COLUMNS FROM apply_sessions LIKE 'prompt';
SHOW TABLES LIKE 'apply_templates';