mysql查看唯一索引语句

SHOW INDEX FROM table_name WHERE Key_name = 'unique_index_name';

SHOW CREATE TABLE table_name;

CREATE UNIQUE INDEX unique_email ON users(email);

SHOW INDEX FROM users WHERE Key_name = 'unique_email';

SHOW CREATE TABLE users;

-- 删除重复值
DELETE FROM users WHERE email IN (
    SELECT email FROM (
        SELECT email, COUNT(*) as cnt FROM users GROUP BY email HAVING cnt > 1
    ) as subquery
) AND id NOT IN (
    SELECT id FROM (
        SELECT id, MIN(id) as min_id FROM users GROUP BY email HAVING COUNT(*) > 1
    ) as subquery
);

-- 更改索引名称
ALTER TABLE users DROP INDEX unique_email;
CREATE UNIQUE INDEX new_unique_email ON users(email);