caojia
143 lines
6.5 KiB
SQL
143 lines
6.5 KiB
SQL
-- MySQL 5.7
|
||
-- 客户业务员工号 -> 系统用户ID 存量迁移。
|
||
-- 上线前先执行 20261028_customer_sales_user_classification.sql 并核对 ID_USERNAME_COLLISION。
|
||
-- 自动转换仅处理唯一 user_name 匹配；未匹配、重复匹配和 ID/工号冲突会备份原值、清空 sales_user_id，留给管理员在客户编辑页处理。
|
||
-- 本脚本不连接数据库执行；可重复运行。完成标记写入后，重复运行不会再次处理存量或改写备份。
|
||
|
||
-- 新增内部源工号列（兼容 MySQL 5.7，不依赖 ADD COLUMN IF NOT EXISTS）。
|
||
SET @add_source_sales_code_sql = (
|
||
SELECT IF(COUNT(*) = 0,
|
||
'ALTER TABLE ctr_customer ADD COLUMN source_sales_code varchar(255) DEFAULT NULL COMMENT ''客户同步源业务员工号（内部字段）'' AFTER sales_user_id',
|
||
'SELECT 1')
|
||
FROM information_schema.columns
|
||
WHERE table_schema = DATABASE()
|
||
AND table_name = 'ctr_customer'
|
||
AND column_name = 'source_sales_code'
|
||
);
|
||
PREPARE add_source_sales_code_stmt FROM @add_source_sales_code_sql;
|
||
EXECUTE add_source_sales_code_stmt;
|
||
DEALLOCATE PREPARE add_source_sales_code_stmt;
|
||
|
||
-- 未匹配时使用 NULL，避免空字符串伪装成用户ID。
|
||
ALTER TABLE ctr_customer
|
||
MODIFY COLUMN sales_user_id varchar(64) DEFAULT NULL COMMENT '业务员系统用户ID（sys_user.user_id）';
|
||
|
||
-- 备份表同时保留分类结果和候选ID；PRIMARY KEY + INSERT IGNORE 保证原始值不会被后续重跑覆盖。
|
||
CREATE TABLE IF NOT EXISTS ctr_customer_sales_user_migration_backup (
|
||
customer_id bigint(20) NOT NULL COMMENT '客户ID',
|
||
customer_code varchar(50) NOT NULL COMMENT '客户编码',
|
||
original_sales_user_id varchar(64) DEFAULT NULL COMMENT '迁移前 sales_user_id 原值',
|
||
classification varchar(40) NOT NULL COMMENT '迁移前分类',
|
||
user_id_match_count int NOT NULL DEFAULT 0 COMMENT '精确 user_id 匹配数',
|
||
user_name_match_count int NOT NULL DEFAULT 0 COMMENT '精确 user_name 匹配数',
|
||
user_id_candidates text COMMENT 'user_id 匹配候选',
|
||
user_name_candidates text COMMENT 'user_name 匹配候选',
|
||
backup_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '备份时间',
|
||
PRIMARY KEY (customer_id),
|
||
KEY idx_customer_sales_user_backup_classification (classification)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户业务员ID迁移原值及分类备份';
|
||
|
||
-- 防止成功执行后重跑时把后来新建/人工维护的客户重新当作存量数据处理。
|
||
CREATE TABLE IF NOT EXISTS ctr_customer_sales_user_migration_control (
|
||
migration_key varchar(64) NOT NULL,
|
||
status varchar(16) NOT NULL DEFAULT 'PENDING',
|
||
completed_at datetime DEFAULT NULL,
|
||
PRIMARY KEY (migration_key)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户业务员ID迁移执行状态';
|
||
|
||
INSERT IGNORE INTO ctr_customer_sales_user_migration_control (migration_key, status)
|
||
VALUES ('20261028_customer_sales_user', 'PENDING');
|
||
|
||
START TRANSACTION;
|
||
|
||
-- 首次运行时先保留旧值和候选；后续事务失败可安全重跑，成功后由 control 表阻止再次处理。
|
||
INSERT IGNORE INTO ctr_customer_sales_user_migration_backup (
|
||
customer_id, customer_code, original_sales_user_id, classification,
|
||
user_id_match_count, user_name_match_count, user_id_candidates, user_name_candidates
|
||
)
|
||
SELECT c.customer_id,
|
||
c.customer_code,
|
||
c.sales_user_id,
|
||
CASE
|
||
WHEN c.sales_user_id IS NULL OR TRIM(c.sales_user_id) = '' THEN 'EMPTY'
|
||
WHEN id_match.match_count > 0 AND name_match.match_count > 0 THEN 'ID_USERNAME_COLLISION'
|
||
WHEN id_match.match_count > 0 THEN 'VALID_USER_ID'
|
||
WHEN name_match.match_count = 1 THEN 'UNIQUE_USERNAME'
|
||
WHEN name_match.match_count > 1 THEN 'AMBIGUOUS_USERNAME'
|
||
ELSE 'NO_MATCH'
|
||
END,
|
||
COALESCE(id_match.match_count, 0),
|
||
COALESCE(name_match.match_count, 0),
|
||
id_match.candidate_user_ids,
|
||
name_match.candidate_user_ids
|
||
FROM ctr_customer c
|
||
JOIN ctr_customer_sales_user_migration_control migration
|
||
ON migration.migration_key = '20261028_customer_sales_user'
|
||
AND migration.status = 'PENDING'
|
||
LEFT JOIN (
|
||
SELECT CAST(user_id AS CHAR) AS lookup_value,
|
||
COUNT(*) AS match_count,
|
||
GROUP_CONCAT(CAST(user_id AS CHAR) ORDER BY user_id SEPARATOR ',') AS candidate_user_ids
|
||
FROM sys_user
|
||
WHERE del_flag = '0'
|
||
GROUP BY CAST(user_id AS CHAR)
|
||
) id_match ON BINARY id_match.lookup_value = BINARY TRIM(c.sales_user_id)
|
||
LEFT JOIN (
|
||
SELECT BINARY TRIM(user_name) AS lookup_value,
|
||
COUNT(*) AS match_count,
|
||
GROUP_CONCAT(CAST(user_id AS CHAR) ORDER BY user_id SEPARATOR ',') AS candidate_user_ids
|
||
FROM sys_user
|
||
WHERE del_flag = '0'
|
||
AND user_name IS NOT NULL
|
||
AND user_name != ''
|
||
GROUP BY BINARY TRIM(user_name)
|
||
) name_match ON name_match.lookup_value = BINARY TRIM(c.sales_user_id)
|
||
WHERE c.del_flag = '0'
|
||
AND c.source_sales_code IS NULL;
|
||
|
||
-- 唯一工号映射到系统用户ID；不唯一、不匹配或存在ID/工号冲突的项不猜测，清空关联并保留源值。
|
||
UPDATE ctr_customer c
|
||
JOIN ctr_customer_sales_user_migration_backup b
|
||
ON b.customer_id = c.customer_id
|
||
JOIN ctr_customer_sales_user_migration_control migration
|
||
ON migration.migration_key = '20261028_customer_sales_user'
|
||
AND migration.status = 'PENDING'
|
||
SET c.source_sales_code = b.original_sales_user_id,
|
||
c.sales_user_id = CASE
|
||
WHEN b.classification = 'UNIQUE_USERNAME'
|
||
THEN b.user_name_candidates
|
||
ELSE NULL
|
||
END
|
||
WHERE c.del_flag = '0'
|
||
AND c.source_sales_code IS NULL
|
||
AND b.classification IN (
|
||
'UNIQUE_USERNAME',
|
||
'AMBIGUOUS_USERNAME',
|
||
'NO_MATCH',
|
||
'ID_USERNAME_COLLISION'
|
||
);
|
||
|
||
-- 将旧空字符串归一为 NULL，已确认是系统用户ID的行保持不变。
|
||
UPDATE ctr_customer c
|
||
JOIN ctr_customer_sales_user_migration_control migration
|
||
ON migration.migration_key = '20261028_customer_sales_user'
|
||
AND migration.status = 'PENDING'
|
||
SET c.sales_user_id = NULL
|
||
WHERE c.del_flag = '0'
|
||
AND c.sales_user_id IS NOT NULL
|
||
AND TRIM(c.sales_user_id) = '';
|
||
|
||
UPDATE ctr_customer_sales_user_migration_control
|
||
SET status = 'DONE', completed_at = NOW()
|
||
WHERE migration_key = '20261028_customer_sales_user'
|
||
AND status = 'PENDING';
|
||
|
||
COMMIT;
|
||
|
||
-- 执行后核对人工处理清单；原值和候选均保存在备份表中。
|
||
SELECT customer_id, customer_code, original_sales_user_id, classification,
|
||
user_id_candidates, user_name_candidates
|
||
FROM ctr_customer_sales_user_migration_backup
|
||
WHERE classification IN ('ID_USERNAME_COLLISION', 'AMBIGUOUS_USERNAME', 'NO_MATCH')
|
||
ORDER BY classification, customer_id;
|