Erreur de gestion de clé dupliquée dans MySQL lors d'une insertion dans une table avec plusieurs index uniques ; pas d'index unique multi-colonnes

Question

Comment trouver l'index unique qui a échoué à partir d'une procédure stockée ?

J'ai créé une table avec une clé primaire à une colonne et trois index uniques à une colonne :

CREATE TABLE tableName (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  col1 VARCHAR(50) NOT NULL,
  col2 VARCHAR(50) NOT NULL,
  col3 VARCHAR(50) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX id_UNIQUE (id ASC),
  UNIQUE INDEX col1_UNIQUE (col1 ASC),
  UNIQUE INDEX col2_UNIQUE (col2 ASC),
  UNIQUE INDEX col3_UNIQUE (col3 ASC)
) ENGINE = InnoDB;

J'ai créé une procédure stockée qui traite l'erreur '1062' : 'Duplicate entry %s for key %d' :

DELIMITER $$
CREATE PROCEDURE insertRecord (
        col1Value   VARCHAR(50), 
        col2Value   VARCHAR(50), 
        col3Value   VARCHAR(50), 
    OUT idValue     INT,
    OUT errColName  VARCHAR(50)
) BEGIN
    DECLARE EXIT HANDLER FOR 1062 BEGIN
        SET errColName = 'Insert Column Name Here';
    END;

    INSERT tableName SET
        col1 = col1Value, 
        col2 = col2Value, 
        col3 = col3Value;

    SET idValue = LAST_INSERT_ID();
END;
$$
DELIMITER ;

J'ai appelé la procédure stockée 5 fois avec des données différentes pour obtenir des résultats variés :

1ère tentative d'INSERTION réussie

-- Initialize parameters
SET @col1Val = 'MySQL', 
    @col2Val = 'Is', 
    @col3Val = 'Cool', 
    @Id = NULL, 
    @ErrColName = NULL;
-- 1st Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = 1, @ErrColName = NULL
SELECT @Id '@Id', @ErrColName '@ErrColName';

La deuxième tentative d'INSERT échoue car col1 n'est pas unique.

-- Re-initialize parameters
SET @col1Val = 'MySQL', -- Intended to generate an error
    @col2Val = 'Is', 
    @col3Val = 'Cool', 
    @Id = NULL, 
    @ErrColName = NULL;
-- 2nd Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = NULL, @ErrColName = 'col1'
SELECT @Id '@Id', @ErrColName '@ErrColName';

La troisième tentative d'INSERT échoue car col2 n'est pas unique.

-- Re-initialize parameters
SET @col1Val = 'SQL', 
    @col2Val = 'Is', -- Intended to generate an error
    @col3Val = 'Cool', 
    @Id = NULL, 
    @ErrColName = NULL;
-- 3rd Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = NULL, @ErrColName = 'col2'
SELECT @Id '@Id', @ErrColName '@ErrColName';

La 4ème tentative d'INSERT échoue car col3 n'est pas unique.

-- Re-initialize parameters
SET @col1Val = 'SQL', 
    @col2Val = 'For', 
    @col3Val = 'Cool', -- Intended to generate an error
    @Id = NULL, 
    @ErrColName = NULL;
-- 4th Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = NULL, @ErrColName = 'col3'
SELECT @Id '@Id', @ErrColName '@ErrColName';

5e tentative d'INSERTION réussie

-- Re-initialize parameters
SET @col1Val = 'SQL', 
    @col2Val = 'For', 
    @col3Val = 'Life', 
    @Id = NULL, 
    @ErrColName = NULL;
-- 5th Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = 5, @ErrColName = NULL
SELECT @Id '@Id', @ErrColName '@ErrColName';

Que dois-je faire dans la procédure stockée pour trouver l'index unique qui a échoué à partir d'une procédure stockée ?

Merci d'avance.

Answer 1

J'ai trouvé la réponse sous Quelle est la clé unique qui est frappée avec mon insert ? . Ce n'est pas la solution idéale, mais elle résout le problème.

La solution appliquée est la suivante :

    ...
    DECLARE EXIT HANDLER FOR 1062 BEGIN
        -- Check if col1Value is already in use
        IF EXISTS (
            SELECT 1
            FROM tableName
            WHERE col1 = col1value
            LIMIT 1
        ) THEN
            SET errColName = 'Col1';

        -- Check if col2Value is already in use
        ELSEIF EXISTS (
            SELECT 1
            FROM tableName
            WHERE col2 = col2value
            LIMIT 1
        ) THEN
            SET errColName = 'Col2';

        -- Check if col3Value is already in use
        ELSEIF EXISTS (
            SELECT 1
            FROM tableName
            WHERE col3 = col3value
            LIMIT 1
        ) THEN
            SET errColName = 'Col3';

        END IF;
    END;
    ...