44 lines
1.4 KiB
SQL
44 lines
1.4 KiB
SQL
/*
|
||
* ----------------------------------------------------------------
|
||
* 迁移脚本: 03_20260811_room_member_nickname.sql
|
||
* 目的:
|
||
* 为 room_members 表补充 nickname 列（群名片/群内显示昵称）。
|
||
* 背景: 模型中 Nickname 原先是 gorm:"-" 非持久化字段，后端从未写入，
|
||
* 前端"我在群里的昵称"功能（POST /groups/:room_id/nickname）一直不可用。
|
||
*
|
||
* 执行方式:
|
||
* mysql -u root -p nl_im_plus < migrations/03_20260811_room_member_nickname.sql
|
||
*
|
||
* 回滚方式:
|
||
* ALTER TABLE `room_members` DROP COLUMN `nickname`;
|
||
* ----------------------------------------------------------------
|
||
*/
|
||
|
||
SET NAMES utf8mb4;
|
||
|
||
-- 请按实际库名修改
|
||
USE `nl_im_plus`;
|
||
|
||
-- MySQL 不支持 ADD COLUMN IF NOT EXISTS，通过 information_schema 判断后再执行，
|
||
-- 保证脚本可重复运行
|
||
SET @col_exists = (
|
||
SELECT COUNT(*)
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'room_members'
|
||
AND COLUMN_NAME = 'nickname'
|
||
);
|
||
|
||
SET @sql = IF(
|
||
@col_exists = 0,
|
||
'ALTER TABLE `room_members` ADD COLUMN `nickname` varchar(100) NOT NULL DEFAULT '''' COMMENT ''群名片(群内显示昵称)'' AFTER `muted_until`',
|
||
'SELECT ''nickname column already exists'' AS notice'
|
||
);
|
||
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 迁移完成
|
||
SELECT '20260811_room_member_nickname migration finished' AS status;
|