Mysql：删除一个唯一的密钥，如果存在 - 但不知道密钥的名称或它是否存在

Question

不要问为什么（因为答案是'我们完全奇怪和bepsoke设置...'），但我需要在不知道密钥名称的情况下删除表中的唯一密钥（如果存在） - 仅列出组成密钥的列。

例如我有这个表

CREATE TABLE `my_table` (
          `id` binary(36) NOT NULL, 
          `username` char(12) NOT NULL DEFAULT '', 
          `password` char(32) NOT NULL DEFAULT '', 
          `role` char(1) NOT NULL, 
          PRIMARY KEY (`id`), 
          UNIQUE KEY `username_2` (`username`,`role`), 
          UNIQUE KEY `username` (`username`), 
          UNIQUE KEY `username_3` (`username`), 
          UNIQUE KEY `username_4` (`username`), 
          ) 

，我想放弃了(username,role)关键，但不是别人（虽然只有当它的存在！ - 的时候，我得到了它，它可能已经被删除）

现在我知道如何删除一个关键的时候，我知道了约束名

ALTER TABLE `my_table` DROP KEY `name_of_my_key` 

，我知道如何检查一个唯一的密钥存在

SELECT EXISTS (SELECT constraint_name 
       FROM INFORMATION_SCHEMA.table_constraints 
       WHERE table_name = 'my_table' AND constraint_type='UNIQUE'); 

但是，当我只知道构成唯一密钥的列时，这仍然无法帮助我。

感谢您的帮助......当我知道一个键存在，我怎么能仅知道哪些列推导constraint_name命令（S）组成的约束，让我放弃呢？注意约束名称，因为mySQL允许您重复添加相同的密钥，如上面3个相同的(username)密钥。

我发现的唯一的东西是文章，如Drop Foreign Key without knowing the name of the constraint? 它似乎只显示如何获取约束名称，不能以编程方式标识要删除的约束。

最后，如果有人知道为什么地狱的MySQL将允许你反复添加相同的键（非主），以表我会洗耳恭听。 MySQL，你太疯狂了。

Answer 1

在@ shadow的关于key_column_usage和序号位置的提示的帮助下，我现在创建了这个漂亮的存储过程，它接受一个表，键定义（逗号分隔的列列表）和数据库。然后它检查该键定义是否存在唯一键并生成SQL以删除它们。

我只生产了SQL这里，以避免人们越来越运行此过程的破坏性操作，只是删除初始从@sqlstmt“选择”，如果你真的想要它做的业务。

也可以修改这个在密钥类型采取作为一个参数，而不是仅仅依靠硬编码UNIQUE。

CREATE PROCEDURE sp_drop_unique_key_if_exists 
(
given_table VARCHAR(64), 
given_key  TEXT, -- In comma sep form '(col1, col2, ...)' note the brackets are important! 
db    VARCHAR(64) 
) 
BEGIN 

DECLARE drop_constraints TEXT; 
DECLARE dbase    VARCHAR(64); 

SET dbase = IFNULL(db, 'my_db'); 

SELECT  group_concat('DROP KEY `',unique_constraints, '`') 
      INTO drop_constraints 
    FROM    
    (SELECT  IF(REPLACE(given_key, ' ', '') 
         = CONCAT('(', GROUP_CONCAT(kcu.column_name ORDER BY ordinal_position), ')'), 
         tc.constraint_name, null) 
        AS unique_constraints 
     FROM  INFORMATION_SCHEMA.table_constraints tc 
     INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
        ON kcu.constraint_name = tc.constraint_name 
        AND kcu.constraint_schema = tc.constraint_schema 
     WHERE  tc.table_name = given_table AND tc.constraint_schema = dbase 
        AND tc.constraint_type='UNIQUE' 
     GROUP BY tc.constraint_name 
    ); 

-- drop the key or keys 
IF drop_constraints IS NOT NULL THEN 
    SET @sqlstmt = CONCAT('SELECT \"ALTER TABLE ',dbase,'.', given_table, ' ',drop_constraints,'\"'); 
    PREPARE st FROM @sqlstmt; 
    EXECUTE st; 
    DEALLOCATE PREPARE st; 
ELSE 
    SELECT CONCAT('Cannot find key ', given_key, ' on ', given_table) DropUniqueKeyErrorMessage; 
END IF; 

END $$ 

Answer 2

的INFORMATION_SCHEMA.KEY_COLUMN_USAGE表保存其中的字段组成的索引的信息。

可以返回，涉及到给定表与下面的查询指定的字段指数（或指数）的名称。存在子查询确保索引具有两个字段，而不存在则确保索引没有任何其他字段。进一步的限制，例如顺序位置也可以包含在查询中。

select CONSTRAINT_NAME 
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 
where TABLE_NAME='my_table' 
    and CONSTRAINT_SCHEMA='myshema' 
    and COLUMN_NAME='username' 
    and exists (select 1 
       from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2 
       where TABLE_NAME='my_table' 
        and CONSTRAINT_SCHEMA='myshema' 
        and COLUMN_NAME='role' 
        and t2.CONSTRAINT_NAME=t1.CONSTRAINT_NAME 
        and t2.CONSTRAINT_SCHEMA=t1.CONSTRAINT_SCHEMA) 
    and not exists (select 1 
        from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2 
        where TABLE_NAME='my_table' 
         and CONSTRAINT_SCHEMA='myshema' 
         and COLUMN_NAME NOT IN ('username','role') 
         and t2.CONSTRAINT_NAME=t1.CONSTRAINT_NAME 
         and t2.CONSTRAINT_SCHEMA=t1.CONSTRAINT_SCHEMA) 

但是，上述查询只返回要删除的索引的名称（如果有的话）。您需要动态连接sql命令以使用prepared statement删除索引。