创建存储过程以在截断所有数据和恢复数据之前删除所有约束，而不删除数据库

DELIMITER //

CREATE PROCEDURE TruncateAndRestoreData()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(100);
    DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 删除所有约束
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE ', tableName, ' DROP FOREIGN KEY ', tableName, '_fk');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;

    -- 截断所有表的数据
    SET FOREIGN_KEY_CHECKS = 0;
    SELECT CONCAT('TRUNCATE TABLE ', table_name, ';') INTO @sql FROM information_schema.tables WHERE table_schema = DATABASE();
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET FOREIGN_KEY_CHECKS = 1;

    -- 恢复所有约束
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE ', tableName, ' ADD CONSTRAINT ', tableName, '_fk FOREIGN KEY (column_name) REFERENCES other_table (column_name)');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //

DELIMITER ;