longandCursor
679e61057b
去掉「同日+同渠道」全局唯一，回到按录入人判重；唯一索引补 delete_time 避免软删占键导致 1062；错误提示带冲突记录 ID，迁移脚本兼容多次执行。 Co-authored-by: Cursor <cursoragent@cursor.com>
84 lines
3.3 KiB
SQL
84 lines
3.3 KiB
SQL
-- 自录业绩/账户消耗：把唯一索引补上 delete_time，使软删后可重新录入。
|
||
--
|
||
-- 背景：
|
||
-- 旧索引 uk_creator_date_media = (creator_id, date, media_source) 不含 delete_time，
|
||
-- 软删的历史记录会继续占用唯一键，导致同账号同日同渠道再次新增触发 MySQL 1062。
|
||
--
|
||
-- 新索引 uk_creator_date_media = (creator_id, date, media_source, delete_time)
|
||
-- MySQL 中 NULL ≠ NULL：
|
||
-- - 活跃记录 delete_time = NULL，最多 1 条（行为不变，仍唯一）
|
||
-- - 软删后 delete_time 变成时间戳，与新插入的 NULL 不冲突 → 允许重新录入
|
||
--
|
||
-- 可重复执行：先检查再操作，避免 1091/1061 报错。
|
||
|
||
-- ========== 查看当前索引（可选） ==========
|
||
SELECT
|
||
TABLE_NAME,
|
||
INDEX_NAME,
|
||
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLUMNS
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME IN ('zyt_personal_yeji', 'zyt_personal_account_cost')
|
||
AND INDEX_NAME IN ('uk_date_media', 'uk_creator_date_media')
|
||
GROUP BY TABLE_NAME, INDEX_NAME
|
||
ORDER BY TABLE_NAME, INDEX_NAME;
|
||
|
||
-- ========== zyt_personal_yeji ==========
|
||
-- 清理可能存在的旧版「全局唯一」索引
|
||
SET @sql = IF(
|
||
(SELECT COUNT(*) FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'zyt_personal_yeji'
|
||
AND INDEX_NAME = 'uk_date_media') > 0,
|
||
'ALTER TABLE `zyt_personal_yeji` DROP INDEX `uk_date_media`',
|
||
'SELECT ''zyt_personal_yeji: 无 uk_date_media，跳过删除'' AS message'
|
||
);
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 删除旧的 3 列唯一索引（若存在）
|
||
SET @sql = IF(
|
||
(SELECT COUNT(*) FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'zyt_personal_yeji'
|
||
AND INDEX_NAME = 'uk_creator_date_media') > 0,
|
||
'ALTER TABLE `zyt_personal_yeji` DROP INDEX `uk_creator_date_media`',
|
||
'SELECT ''zyt_personal_yeji: 无 uk_creator_date_media，跳过删除'' AS message'
|
||
);
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 添加 4 列唯一索引（包含 delete_time）
|
||
ALTER TABLE `zyt_personal_yeji`
|
||
ADD UNIQUE KEY `uk_creator_date_media` (`creator_id`, `yeji_date`, `media_source`, `delete_time`);
|
||
|
||
-- ========== zyt_personal_account_cost ==========
|
||
SET @sql = IF(
|
||
(SELECT COUNT(*) FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'zyt_personal_account_cost'
|
||
AND INDEX_NAME = 'uk_date_media') > 0,
|
||
'ALTER TABLE `zyt_personal_account_cost` DROP INDEX `uk_date_media`',
|
||
'SELECT ''zyt_personal_account_cost: 无 uk_date_media，跳过删除'' AS message'
|
||
);
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
SET @sql = IF(
|
||
(SELECT COUNT(*) FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'zyt_personal_account_cost'
|
||
AND INDEX_NAME = 'uk_creator_date_media') > 0,
|
||
'ALTER TABLE `zyt_personal_account_cost` DROP INDEX `uk_creator_date_media`',
|
||
'SELECT ''zyt_personal_account_cost: 无 uk_creator_date_media，跳过删除'' AS message'
|
||
);
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
ALTER TABLE `zyt_personal_account_cost`
|
||
ADD UNIQUE KEY `uk_creator_date_media` (`creator_id`, `cost_date`, `media_source`, `delete_time`);
|