-- sp_generic_shift_dates：按指定时间窗口平移库内日期类字段
|
-- 修复点：
|
-- 1) 唯一索引包含日期列的表（如 hrm_attendance_record.uk_user_date）改为整表统一平移，
|
-- 常量偏移不会破坏唯一键，避免 1062 Duplicate entry
|
-- 2) 单表执行失败不再中断整轮，错误记入预览表后继续处理后续表
|
|
DROP PROCEDURE IF EXISTS `sp_generic_shift_dates`;
|
|
DELIMITER $$
|
|
CREATE DEFINER = `root`@`%` PROCEDURE `sp_generic_shift_dates`(
|
IN p_schema VARCHAR(64),
|
IN p_start DATETIME,
|
IN p_end DATETIME,
|
IN p_execute BOOLEAN,
|
IN p_exclude_regex VARCHAR(1000),
|
IN p_include_regex VARCHAR(1000)
|
)
|
BEGIN
|
DECLARE v_done BOOL DEFAULT FALSE;
|
DECLARE v_table VARCHAR(64);
|
DECLARE v_pk VARCHAR(64);
|
DECLARE v_pk_count INT;
|
DECLARE v_cols LONGTEXT;
|
DECLARE v_anchor VARCHAR(64);
|
DECLARE v_sql LONGTEXT;
|
DECLARE v_seconds BIGINT;
|
DECLARE v_count BIGINT DEFAULT 0;
|
DECLARE v_tables BIGINT DEFAULT 0;
|
DECLARE v_fields INT;
|
DECLARE v_uniq_hit INT;
|
DECLARE v_uniq_cols VARCHAR(1000);
|
DECLARE v_min_expr LONGTEXT;
|
DECLARE v_max_expr LONGTEXT;
|
DECLARE v_regex VARCHAR(1000);
|
DECLARE v_err TEXT;
|
DECLARE cur CURSOR FOR
|
SELECT t.TABLE_NAME
|
FROM information_schema.TABLES t
|
WHERE t.TABLE_SCHEMA = p_schema
|
AND t.TABLE_TYPE = 'BASE TABLE'
|
AND (p_exclude_regex IS NULL OR TRIM(p_exclude_regex) = ''
|
OR NOT REGEXP_LIKE(t.TABLE_NAME, p_exclude_regex, 'i'))
|
ORDER BY t.TABLE_NAME;
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
|
|
IF p_schema IS NULL OR p_schema = '' OR p_schema IN ('mysql','information_schema','performance_schema','sys') THEN
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid or system schema';
|
END IF;
|
IF p_start IS NULL OR p_end IS NULL OR p_end < p_start THEN
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid date range';
|
END IF;
|
IF NOT EXISTS (SELECT 1 FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = p_schema) THEN
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Schema not found';
|
END IF;
|
|
-- 列清单可能很长，避免 GROUP_CONCAT 被默认 1024 字节截断
|
SET SESSION group_concat_max_len = 1048576;
|
|
SET v_seconds = TIMESTAMPDIFF(SECOND, p_start, p_end);
|
-- 空串不是 NULL，必须显式归一化：MySQL 8 的 REGEXP_LIKE 遇到空模式会抛 3685
|
IF p_include_regex IS NULL OR TRIM(p_include_regex) = '' THEN
|
SET v_regex = '(^|_)(create|created|update|updated|modify|modified|order|production|produce|receipt|receive|delivery|ship|stock|inbound|outbound|start|end|finish|complete|plan|actual|inspect|check|approve|audit|pay|payment|due|expire|expiry|effective|business|biz|date|time|timestamp)(_|$)';
|
ELSE
|
SET v_regex = p_include_regex;
|
END IF;
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_date_shift_preview;
|
CREATE TEMPORARY TABLE tmp_date_shift_preview (
|
table_name VARCHAR(64),
|
anchor_column VARCHAR(64),
|
shift_mode VARCHAR(16),
|
field_count INT,
|
affected_rows BIGINT,
|
statement_text LONGTEXT
|
);
|
|
OPEN cur;
|
loop_tables: LOOP
|
FETCH cur INTO v_table;
|
IF v_done THEN LEAVE loop_tables; END IF;
|
|
SELECT COUNT(*), MAX(k.COLUMN_NAME) INTO v_pk_count, v_pk
|
FROM information_schema.KEY_COLUMN_USAGE k
|
WHERE k.TABLE_SCHEMA = p_schema AND k.TABLE_NAME = v_table AND k.CONSTRAINT_NAME = 'PRIMARY';
|
IF v_pk_count <> 1 THEN ITERATE loop_tables; END IF;
|
|
SELECT COUNT(*) INTO v_fields
|
FROM information_schema.COLUMNS c
|
WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
|
AND c.DATA_TYPE IN ('date','datetime','timestamp')
|
AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
|
AND c.EXTRA NOT LIKE '%on update%'
|
AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');
|
IF v_fields = 0 THEN ITERATE loop_tables; END IF;
|
|
SELECT c.COLUMN_NAME INTO v_anchor
|
FROM information_schema.COLUMNS c
|
WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
|
AND c.DATA_TYPE IN ('date','datetime','timestamp')
|
AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
|
AND c.EXTRA NOT LIKE '%on update%'
|
AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i')
|
ORDER BY CASE WHEN c.COLUMN_NAME IN ('create_time','created_at','creation_time','created_time') THEN 0 ELSE 1 END,
|
c.ORDINAL_POSITION
|
LIMIT 1;
|
|
-- 待平移的日期列中是否有落在唯一索引上的
|
SELECT COUNT(*) INTO v_uniq_hit
|
FROM information_schema.STATISTICS s
|
JOIN information_schema.COLUMNS c
|
ON c.TABLE_SCHEMA = s.TABLE_SCHEMA
|
AND c.TABLE_NAME = s.TABLE_NAME
|
AND c.COLUMN_NAME = s.COLUMN_NAME
|
WHERE s.TABLE_SCHEMA = p_schema AND s.TABLE_NAME = v_table AND s.NON_UNIQUE = 0
|
AND c.DATA_TYPE IN ('date','datetime','timestamp')
|
AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
|
AND c.EXTRA NOT LIKE '%on update%'
|
AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');
|
|
SET @tbl = CONCAT('`', REPLACE(p_schema,'`','``'), '`.`', REPLACE(v_table,'`','``'), '`');
|
SET @anchor = CONCAT('`', REPLACE(v_anchor,'`','``'), '`');
|
|
inner_block: BEGIN
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
BEGIN
|
GET DIAGNOSTICS CONDITION 1 v_err = MESSAGE_TEXT;
|
INSERT INTO tmp_date_shift_preview
|
VALUES (v_table, v_anchor, 'failed', v_fields, 0, CONCAT('[error] ', v_err));
|
END;
|
|
IF v_uniq_hit > 0 THEN
|
-- 统一平移：整表使用同一偏移，保留唯一键。
|
-- 偏移量按所有待平移列的整体跨度计算，保证每一列都落在目标窗口内。
|
-- COALESCE 保护：整列为 NULL 时 MIN/MAX 返回 NULL，会让 LEAST/GREATEST 整体变 NULL
|
SELECT GROUP_CONCAT(CONCAT('COALESCE(MIN(`', REPLACE(c.COLUMN_NAME,'`','``'), '`), ''9999-12-31 23:59:59'')')
|
ORDER BY c.ORDINAL_POSITION SEPARATOR ', '),
|
GROUP_CONCAT(CONCAT('COALESCE(MAX(`', REPLACE(c.COLUMN_NAME,'`','``'), '`), ''1000-01-01 00:00:00'')')
|
ORDER BY c.ORDINAL_POSITION SEPARATOR ', ')
|
INTO v_min_expr, v_max_expr
|
FROM information_schema.COLUMNS c
|
WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
|
AND c.DATA_TYPE IN ('date','datetime','timestamp')
|
AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
|
AND c.EXTRA NOT LIKE '%on update%'
|
AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');
|
|
SET @sql = CONCAT('SELECT LEAST(', v_min_expr, '), GREATEST(', v_max_expr,
|
') INTO @min_all, @max_all FROM ', @tbl);
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @span_days = TIMESTAMPDIFF(DAY, DATE(@min_all), DATE(@max_all));
|
SET @range_days = TIMESTAMPDIFF(DAY, DATE(p_start), DATE(p_end));
|
|
IF @min_all IS NULL OR @min_all >= '9999-01-01' THEN
|
INSERT INTO tmp_date_shift_preview
|
VALUES (v_table, v_anchor, 'skipped', v_fields, 0, 'no non-null date value');
|
ELSEIF @span_days > @range_days THEN
|
INSERT INTO tmp_date_shift_preview
|
VALUES (v_table, v_anchor, 'skipped', v_fields, 0,
|
CONCAT('date span ', @span_days, ' days exceeds target range ', @range_days, ' days'));
|
ELSE
|
-- 按整日平移，保留时分秒与相对顺序
|
SET @shift_delta = (TIMESTAMPDIFF(DAY, DATE(@min_all), DATE(p_start))
|
+ FLOOR(RAND() * (@range_days - @span_days + 1))) * 86400;
|
-- 随机到 0 天等于没改，强制至少平移 1 天
|
IF @shift_delta = 0 AND @span_days < @range_days THEN
|
SET @shift_delta = 86400;
|
END IF;
|
|
SELECT GROUP_CONCAT(
|
CONCAT('`', REPLACE(c.COLUMN_NAME,'`','``'), '` = DATE_ADD(`',
|
REPLACE(c.COLUMN_NAME,'`','``'), '`, INTERVAL @shift_delta SECOND)')
|
ORDER BY c.ORDINAL_POSITION SEPARATOR ', ')
|
INTO v_cols
|
FROM information_schema.COLUMNS c
|
WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
|
AND c.DATA_TYPE IN ('date','datetime','timestamp')
|
AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
|
AND c.EXTRA NOT LIKE '%on update%'
|
AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');
|
|
-- InnoDB 逐行更新唯一键会瞬时冲突，按偏移方向决定扫描顺序：
|
-- 正偏移先动较大的值，负偏移先动较小的值
|
SELECT GROUP_CONCAT(CONCAT('`', REPLACE(d.COLUMN_NAME,'`','``'), '`')
|
ORDER BY d.ORDINAL_POSITION SEPARATOR ', ')
|
INTO v_uniq_cols
|
FROM (
|
SELECT DISTINCT c.COLUMN_NAME, c.ORDINAL_POSITION
|
FROM information_schema.STATISTICS s
|
JOIN information_schema.COLUMNS c
|
ON c.TABLE_SCHEMA = s.TABLE_SCHEMA
|
AND c.TABLE_NAME = s.TABLE_NAME
|
AND c.COLUMN_NAME = s.COLUMN_NAME
|
WHERE s.TABLE_SCHEMA = p_schema AND s.TABLE_NAME = v_table AND s.NON_UNIQUE = 0
|
AND c.DATA_TYPE IN ('date','datetime','timestamp')
|
AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
|
AND c.EXTRA NOT LIKE '%on update%'
|
AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i')
|
) d;
|
|
SET @update_sql = CONCAT('UPDATE ', @tbl, ' SET ', v_cols,
|
' ORDER BY ', v_uniq_cols, ' ',
|
IF(@shift_delta >= 0, 'DESC', 'ASC'));
|
SET @count_sql = CONCAT('SELECT COUNT(*) INTO @affected FROM ', @tbl);
|
PREPARE stmt FROM @count_sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
IF p_execute THEN
|
PREPARE stmt FROM @update_sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
|
SET v_count = v_count + @affected;
|
INSERT INTO tmp_date_shift_preview
|
VALUES (v_table, v_anchor, IF(p_execute, 'uniform-executed', 'uniform-preview'),
|
v_fields, @affected, @update_sql);
|
SET v_tables = v_tables + 1;
|
END IF;
|
ELSE
|
-- 逐行随机：每行一个随机目标时间，以锚点整体平移
|
SELECT GROUP_CONCAT(
|
CONCAT('`', REPLACE(c.COLUMN_NAME,'`','``'), '` = CASE WHEN `',
|
REPLACE(c.COLUMN_NAME,'`','``'), '` IS NULL THEN NULL ELSE ',
|
CASE WHEN c.DATA_TYPE = 'date' THEN 'DATE(' ELSE '' END,
|
'DATE_ADD(`', REPLACE(c.COLUMN_NAME,'`','``'), '`, INTERVAL TIMESTAMPDIFF(SECOND, `',
|
REPLACE(v_anchor,'`','``'), '`, __new_anchor) SECOND)',
|
CASE WHEN c.DATA_TYPE = 'date' THEN ')' ELSE '' END, ' END')
|
ORDER BY c.ORDINAL_POSITION SEPARATOR ', ')
|
INTO v_cols
|
FROM information_schema.COLUMNS c
|
WHERE c.TABLE_SCHEMA = p_schema AND c.TABLE_NAME = v_table
|
AND c.DATA_TYPE IN ('date','datetime','timestamp')
|
AND c.EXTRA NOT LIKE '%VIRTUAL GENERATED%' AND c.EXTRA NOT LIKE '%STORED GENERATED%'
|
AND c.EXTRA NOT LIKE '%on update%'
|
AND REGEXP_LIKE(c.COLUMN_NAME, v_regex, 'i');
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_shift_row_dates;
|
SET @sql = CONCAT('CREATE TEMPORARY TABLE tmp_shift_row_dates AS SELECT `',
|
REPLACE(v_pk,'`','``'), '` AS __pk, DATE_ADD(?, INTERVAL FLOOR(RAND() * ',
|
v_seconds + 1, ') SECOND) AS __new_anchor FROM ', @tbl,
|
' WHERE ', @anchor, ' IS NOT NULL');
|
|
IF p_execute THEN
|
PREPARE stmt FROM @sql;
|
SET @start_date = p_start;
|
EXECUTE stmt USING @start_date;
|
DEALLOCATE PREPARE stmt;
|
|
SET @update_sql = CONCAT('UPDATE ', @tbl, ' t JOIN tmp_shift_row_dates s ON t.`',
|
REPLACE(v_pk,'`','``'), '` = s.__pk SET ',
|
REPLACE(v_cols, '__new_anchor', 's.__new_anchor'));
|
SET @count_sql = 'SELECT COUNT(*) INTO @affected FROM tmp_shift_row_dates';
|
PREPARE stmt FROM @count_sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
PREPARE stmt FROM @update_sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET v_count = v_count + @affected;
|
INSERT INTO tmp_date_shift_preview
|
VALUES (v_table, v_anchor, 'random-executed', v_fields, @affected, @update_sql);
|
DROP TEMPORARY TABLE tmp_shift_row_dates;
|
ELSE
|
SET @count_sql = CONCAT('SELECT COUNT(*) INTO @affected FROM ', @tbl, ' WHERE ', @anchor, ' IS NOT NULL');
|
PREPARE stmt FROM @count_sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
INSERT INTO tmp_date_shift_preview
|
VALUES (v_table, v_anchor, 'random-preview', v_fields, @affected,
|
CONCAT('Will shift ', v_fields, ' date columns per row; anchor=', v_anchor));
|
END IF;
|
SET v_tables = v_tables + 1;
|
END IF;
|
END inner_block;
|
END LOOP;
|
CLOSE cur;
|
|
SELECT * FROM tmp_date_shift_preview ORDER BY shift_mode, table_name;
|
SELECT p_schema AS database_name, p_execute AS executed,
|
v_tables AS tables_processed, v_count AS rows_targeted;
|
END$$
|
|
DELIMITER ;
|
