mysql - 更改唯一键

Question

我是新手，所以我会尽力解释。我在一个表中有一个 UNIQUE 键，我希望通过添加另一列来改变它。

所以，我有一个 UNIQUE 键 - UNIQUE_tablename_column1_column2。我的目标是删除此键并创建一个名为 - UNIQUE_tablename_column1_column2_column3 的新键。此外，我希望确保它可以在需要时一遍又一遍地运行。

我有基本的 sql，ALTER TABLE tablename DROP index UNIQUE_tablename_column1_column2，将 DROP 原始键。然后 ALTER TABLE tablename ADD CONSTRAINT UNIQUE_tablename_column1_column2_column3 UNIQUE (column1,column2,column3) 将添加我需要的新内容。

我想做的是将它放在某种 IF/IF EXISTS THEN 语句中，其中 IF EXISTS UNIQUE_tablename_column1_column2 THEN DROP UNIQUE_tablename_column1_column2 AND ADD UNIQUE_tablename_column1_column2_column3。

非常感谢任何想法、帮助和协助。谢谢!

Answer 1

简短的回答是，您需要在存储过程中编写此类逻辑，因为 IF/ELSE 结构仅适用于存储程序。

这是我经常用来删除和创建索引的两个示例脚本，首先检查是否存在。它支持任何类型的索引(唯一或非唯一)。

-- Use to create indexes via a CALL statement. Be sure to use DELIMITER when creating these
-- procedures on your system:

    CREATE PROCEDURE `admin_create_index`(
        index_name VARCHAR(64),    
        column_list VARCHAR(64),   
        table_name VARCHAR(64),
        is_unique TINYINT(1),
        db_name  VARCHAR(64)
    )
    BEGIN
    SET @sqlexec := 
    CONCAT
    (
        'SELECT @i := COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_NAME = \'',
        index_name, 
        '\' AND TABLE_NAME = \'', 
        table_name,
        '\' AND TABLE_SCHEMA = \'',
        db_name,
        '\''
    );

    SELECT CONCAT('Executing: "', @sqlexec, '"') AS 'Info';
    PREPARE lookup FROM @sqlexec;
    EXECUTE lookup;
    DEALLOCATE PREPARE lookup;

    IF @i = 0 THEN
        SET @sqlexec := CONCAT('CREATE ', IF(is_unique > 0, 'UNIQUE ', ''), 'INDEX ', index_name, ' ON `', db_name, '`.`', table_name, '`(', column_list, ')');
        SELECT CONCAT('Executing: "', @sqlexec, '"') AS 'Info';
        PREPARE statement FROM @sqlexec;
        EXECUTE statement;
        DEALLOCATE PREPARE statement;
        SELECT CONCAT('Successful execution of: "', @sqlexec, '"') AS 'Info';
    ELSE
        SELECT CONCAT('Warning: Index `', index_name, '` already exists on `', db_name, '`.`', table_name, '`') AS 'Warning';
    END IF;
    END;

-- Use to drop an index (if it exists):

CREATE PROCEDURE `admin_drop_index`(
    index_name VARCHAR(64),    
    table_name VARCHAR(64),
    db_name  VARCHAR(64)
)
BEGIN
SET @sqlexec := 
CONCAT
(
    'SELECT @i := COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_NAME = \'',
    index_name, 
    '\' AND TABLE_NAME = \'', 
    table_name,
    '\' AND TABLE_SCHEMA = \'',
    db_name,
    '\''
);

SELECT CONCAT('Executing: "', @sqlexec, '"') AS 'Info';
PREPARE lookup FROM @sqlexec;
EXECUTE lookup;
DEALLOCATE PREPARE lookup;

IF @i > 0 THEN
    SET @sqlexec := CONCAT('ALTER TABLE `', db_name, '`.`', table_name, '` DROP INDEX `', index_name, '`');
    SELECT CONCAT('Executing: "', @sqlexec, '"') AS 'Info';
    PREPARE statement FROM @sqlexec;
    EXECUTE statement;
    DEALLOCATE PREPARE statement;
    SELECT CONCAT('Successful execution of: "', @sqlexec, '"') AS 'Info';
ELSE
    SELECT CONCAT('Warning: Index `', index_name, '` does not exist on `', db_name, '`.`', table_name, '`') AS 'Warning';
END IF;

END;

下面是示例 CALL 语句:

CALL admin_create_index('my_index_name', 'col_name_a, col_name_b', 'table_name', TRUE, DATABASE());

CALL admin_drop_index('my_index_name', 'table_name', DATABASE());