78 lines
3.9 KiB
SQL
78 lines
3.9 KiB
SQL
/*
|
|
* ----------------------------------------------------------------
|
|
* 迁移脚本: 07_20260812_ai_bots.sql
|
|
* 目的:
|
|
* AI 机器人功能:
|
|
* 1. users 表增加 is_bot 列(机器人复用 users 表作为虚拟用户,
|
|
* 消息/群成员/会话等现有链路零改造);
|
|
* 2. 新建 ai_configs 表(AI 提供商配置,全局一条,仅管理员 id=1 维护);
|
|
* 3. 新建 ai_bots 表(机器人定义:名称/头像/角色设定,关联虚拟用户ID)。
|
|
*
|
|
* 执行方式:
|
|
* mysql -u root -p nl_im_plus < migrations/07_20260812_ai_bots.sql
|
|
*
|
|
* 回滚方式:
|
|
* ALTER TABLE `users` DROP COLUMN `is_bot`;
|
|
* DROP TABLE IF EXISTS `ai_configs`;
|
|
* DROP TABLE IF EXISTS `ai_bots`;
|
|
*
|
|
* 本脚本可重复执行:列/表已存在时跳过,不会报错。
|
|
* ----------------------------------------------------------------
|
|
*/
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
-- 请按实际库名修改
|
|
USE `nl_im_plus`;
|
|
|
|
-- ============================================================
|
|
-- 1. users 表增加 is_bot 列(已存在则跳过)
|
|
-- ============================================================
|
|
SET @col_exists = (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'users'
|
|
AND COLUMN_NAME = 'is_bot'
|
|
);
|
|
SET @sql = IF(
|
|
@col_exists = 0,
|
|
'ALTER TABLE `users` ADD COLUMN `is_bot` tinyint(1) NOT NULL DEFAULT 0 COMMENT ''是否AI机器人'' AFTER `moment_cover`',
|
|
'SELECT ''is_bot column already exists'' AS notice'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- ============================================================
|
|
-- 2. ai_configs AI提供商配置表
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS `ai_configs` (
|
|
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`provider` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '提供商标识(openai/deepseek/dashscope/moonshot/ollama/custom)',
|
|
`base_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'API基础地址(空用默认)',
|
|
`api_key` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'API密钥',
|
|
`model` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '模型名称',
|
|
`enabled` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否启用',
|
|
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
|
|
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
|
|
PRIMARY KEY (`id`) USING BTREE
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'AI提供商配置表(全局一条,仅管理员维护)' ROW_FORMAT = Dynamic;
|
|
|
|
-- ============================================================
|
|
-- 3. ai_bots 机器人定义表
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS `ai_bots` (
|
|
`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关联虚拟用户ID(users.id, bot_前缀)',
|
|
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '机器人名称(群聊@名称触发)',
|
|
`avatar` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '机器人头像',
|
|
`role_prompt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '角色设定(system提示词)',
|
|
`enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
|
|
`created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
|
|
`updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
|
|
PRIMARY KEY (`id`) USING BTREE,
|
|
UNIQUE INDEX `uk_ai_bots_user_id`(`user_id` ASC) USING BTREE
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'AI机器人定义表' ROW_FORMAT = Dynamic;
|
|
|
|
SELECT '07_20260812_ai_bots migration finished' AS result;
|