zzh
2b0158acb4
All checks were successful
Build and Deploy / deploy (push) Successful in 1m0s
Co-authored-by: Cursor <cursoragent@cursor.com>
214 lines
7.0 KiB
SQL
214 lines
7.0 KiB
SQL
-- =============================================================================
|
||
-- drop_all_unique_indexes.sql
|
||
-- 去掉当前库所有非 PRIMARY 唯一索引的「唯一性」（改为同名普通 INDEX）
|
||
-- 流程：备份并删除外键 → UNIQUE 改 KEY → 恢复外键
|
||
-- 解决 Error 1553：Cannot drop index ... needed in a foreign key constraint
|
||
-- 可重复执行；唯一性请在业务代码中校验。
|
||
-- 请在 Navicat / DBeaver / MySQL Workbench 中整段执行（需要 DELIMITER）。
|
||
-- =============================================================================
|
||
|
||
SET @db := DATABASE();
|
||
|
||
-- ---------- 预览：将处理的唯一索引 ----------
|
||
SELECT
|
||
TABLE_NAME,
|
||
INDEX_NAME,
|
||
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = @db
|
||
AND NON_UNIQUE = 0
|
||
AND INDEX_NAME <> 'PRIMARY'
|
||
GROUP BY TABLE_NAME, INDEX_NAME
|
||
ORDER BY TABLE_NAME, INDEX_NAME;
|
||
|
||
-- ---------- 预览：库中物理外键（会临时删除再重建）----------
|
||
SELECT
|
||
rc.CONSTRAINT_NAME,
|
||
rc.TABLE_NAME,
|
||
rc.REFERENCED_TABLE_NAME,
|
||
rc.UPDATE_RULE,
|
||
rc.DELETE_RULE
|
||
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
|
||
WHERE rc.CONSTRAINT_SCHEMA = @db
|
||
ORDER BY rc.TABLE_NAME, rc.CONSTRAINT_NAME;
|
||
|
||
DROP PROCEDURE IF EXISTS `sp_drop_all_unique_indexes`;
|
||
DELIMITER $$
|
||
CREATE PROCEDURE `sp_drop_all_unique_indexes`()
|
||
BEGIN
|
||
DECLARE done INT DEFAULT 0;
|
||
DECLARE v_fk_name VARCHAR(128);
|
||
DECLARE v_fk_table VARCHAR(128);
|
||
DECLARE v_ref_table VARCHAR(128);
|
||
DECLARE v_update VARCHAR(16);
|
||
DECLARE v_delete VARCHAR(16);
|
||
DECLARE v_cols TEXT;
|
||
DECLARE v_ref_cols TEXT;
|
||
DECLARE v_table VARCHAR(128);
|
||
DECLARE v_index VARCHAR(128);
|
||
DECLARE v_index_cols TEXT;
|
||
DECLARE v_sql TEXT;
|
||
|
||
-- 1) 备份当前库全部外键定义
|
||
DROP TEMPORARY TABLE IF EXISTS `_tmp_fk_backup`;
|
||
CREATE TEMPORARY TABLE `_tmp_fk_backup` (
|
||
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
`constraint_name` VARCHAR(128) NOT NULL,
|
||
`table_name` VARCHAR(128) NOT NULL,
|
||
`column_names` TEXT NOT NULL,
|
||
`ref_table_name` VARCHAR(128) NOT NULL,
|
||
`ref_column_names` TEXT NOT NULL,
|
||
`update_rule` VARCHAR(16) NOT NULL,
|
||
`delete_rule` VARCHAR(16) NOT NULL
|
||
);
|
||
|
||
INSERT INTO `_tmp_fk_backup` (
|
||
`constraint_name`, `table_name`, `column_names`,
|
||
`ref_table_name`, `ref_column_names`, `update_rule`, `delete_rule`
|
||
)
|
||
SELECT
|
||
kcu.CONSTRAINT_NAME,
|
||
kcu.TABLE_NAME,
|
||
GROUP_CONCAT(CONCAT('`', kcu.COLUMN_NAME, '`') ORDER BY kcu.ORDINAL_POSITION SEPARATOR ','),
|
||
kcu.REFERENCED_TABLE_NAME,
|
||
GROUP_CONCAT(CONCAT('`', kcu.REFERENCED_COLUMN_NAME, '`') ORDER BY kcu.ORDINAL_POSITION SEPARATOR ','),
|
||
rc.UPDATE_RULE,
|
||
rc.DELETE_RULE
|
||
FROM information_schema.KEY_COLUMN_USAGE kcu
|
||
INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
|
||
ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
|
||
AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
|
||
AND rc.TABLE_NAME = kcu.TABLE_NAME
|
||
WHERE kcu.TABLE_SCHEMA = DATABASE()
|
||
AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
|
||
GROUP BY
|
||
kcu.CONSTRAINT_NAME,
|
||
kcu.TABLE_NAME,
|
||
kcu.REFERENCED_TABLE_NAME,
|
||
rc.UPDATE_RULE,
|
||
rc.DELETE_RULE;
|
||
|
||
-- 2) 删除全部外键（解除对唯一索引的占用）
|
||
BEGIN
|
||
DECLARE cur_fk_drop CURSOR FOR
|
||
SELECT `constraint_name`, `table_name` FROM `_tmp_fk_backup` ORDER BY `id`;
|
||
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
|
||
SET done = 0;
|
||
OPEN cur_fk_drop;
|
||
fk_drop_loop: LOOP
|
||
FETCH cur_fk_drop INTO v_fk_name, v_fk_table;
|
||
IF done = 1 THEN
|
||
LEAVE fk_drop_loop;
|
||
END IF;
|
||
SET v_sql = CONCAT('ALTER TABLE `', v_fk_table, '` DROP FOREIGN KEY `', v_fk_name, '`');
|
||
SET @ddl := v_sql;
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
END LOOP;
|
||
CLOSE cur_fk_drop;
|
||
END;
|
||
|
||
-- 3) 唯一索引 → 同名普通 INDEX（去掉唯一性，保留列与查询能力，便于外键重建）
|
||
BEGIN
|
||
DECLARE cur_uk CURSOR FOR
|
||
SELECT
|
||
s.TABLE_NAME,
|
||
s.INDEX_NAME,
|
||
GROUP_CONCAT(CONCAT('`', s.COLUMN_NAME, '`') ORDER BY s.SEQ_IN_INDEX SEPARATOR ',')
|
||
FROM information_schema.STATISTICS s
|
||
WHERE s.TABLE_SCHEMA = DATABASE()
|
||
AND s.NON_UNIQUE = 0
|
||
AND s.INDEX_NAME <> 'PRIMARY'
|
||
GROUP BY s.TABLE_NAME, s.INDEX_NAME
|
||
ORDER BY s.TABLE_NAME, s.INDEX_NAME;
|
||
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
|
||
SET done = 0;
|
||
OPEN cur_uk;
|
||
uk_loop: LOOP
|
||
FETCH cur_uk INTO v_table, v_index, v_index_cols;
|
||
IF done = 1 THEN
|
||
LEAVE uk_loop;
|
||
END IF;
|
||
SET v_sql = CONCAT(
|
||
'ALTER TABLE `', v_table, '` DROP INDEX `', v_index,
|
||
'`, ADD INDEX `', v_index, '` (', v_index_cols, ')'
|
||
);
|
||
SET @ddl := v_sql;
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
END LOOP;
|
||
CLOSE cur_uk;
|
||
END;
|
||
|
||
-- 4) 按备份重建外键
|
||
-- 若原外键引用的是「非主键唯一键」，去掉唯一性后 InnoDB 可能拒绝重建，将跳过并写入警告表
|
||
DROP TEMPORARY TABLE IF EXISTS `_tmp_fk_restore_fail`;
|
||
CREATE TEMPORARY TABLE `_tmp_fk_restore_fail` (
|
||
`constraint_name` VARCHAR(128) NOT NULL,
|
||
`table_name` VARCHAR(128) NOT NULL,
|
||
`error_msg` TEXT
|
||
);
|
||
|
||
BEGIN
|
||
DECLARE cur_fk_add CURSOR FOR
|
||
SELECT
|
||
`constraint_name`, `table_name`, `column_names`,
|
||
`ref_table_name`, `ref_column_names`, `update_rule`, `delete_rule`
|
||
FROM `_tmp_fk_backup`
|
||
ORDER BY `id`;
|
||
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
|
||
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
|
||
BEGIN
|
||
GET DIAGNOSTICS CONDITION 1 @fk_err = MESSAGE_TEXT;
|
||
INSERT INTO `_tmp_fk_restore_fail` (`constraint_name`, `table_name`, `error_msg`)
|
||
VALUES (v_fk_name, v_fk_table, @fk_err);
|
||
END;
|
||
SET done = 0;
|
||
OPEN cur_fk_add;
|
||
fk_add_loop: LOOP
|
||
FETCH cur_fk_add INTO
|
||
v_fk_name, v_fk_table, v_cols, v_ref_table, v_ref_cols, v_update, v_delete;
|
||
IF done = 1 THEN
|
||
LEAVE fk_add_loop;
|
||
END IF;
|
||
SET v_sql = CONCAT(
|
||
'ALTER TABLE `', v_fk_table,
|
||
'` ADD CONSTRAINT `', v_fk_name,
|
||
'` FOREIGN KEY (', v_cols,
|
||
') REFERENCES `', v_ref_table, '` (', v_ref_cols,
|
||
') ON UPDATE ', v_update,
|
||
' ON DELETE ', v_delete
|
||
);
|
||
SET @ddl := v_sql;
|
||
PREPARE stmt FROM @ddl;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
END LOOP;
|
||
CLOSE cur_fk_add;
|
||
END;
|
||
|
||
-- 输出重建失败的外键（通常因引用侧不再是 UNIQUE/PRIMARY）
|
||
SELECT * FROM `_tmp_fk_restore_fail`;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS `_tmp_fk_backup`;
|
||
DROP TEMPORARY TABLE IF EXISTS `_tmp_fk_restore_fail`;
|
||
END$$
|
||
DELIMITER ;
|
||
|
||
CALL `sp_drop_all_unique_indexes`();
|
||
DROP PROCEDURE IF EXISTS `sp_drop_all_unique_indexes`;
|
||
|
||
-- ---------- 校验：唯一索引应无结果（PRIMARY 除外）----------
|
||
SELECT
|
||
TABLE_NAME,
|
||
INDEX_NAME,
|
||
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = @db
|
||
AND NON_UNIQUE = 0
|
||
AND INDEX_NAME <> 'PRIMARY'
|
||
GROUP BY TABLE_NAME, INDEX_NAME
|
||
ORDER BY TABLE_NAME, INDEX_NAME;
|