14

I am inserting into the following table using LuaSQL with PtokaX API.

CREATE TABLE `requests` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',
    `dated` DATETIME NOT NULL,
    `filldate` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_msg` (`nick`, `msg`),
    UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)
)
COMMENT='Requests from users in any of the categories.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is, when a user(represented by nick) tries to insert same request again, the UNIQUE index is checked and the script returns a false. This causes my script to fail and I have to restart the script.

Is there something I can do in the INSERT ... ON DUPLICATE KEY command so that it does nothing or at-least does NOT return an error in case of DUPLICATE KEY?

Otherwise I'd have to go for updating my dated field with the new DATETIME value.

23

Three ways. Either IGNORE duplicate errors:

INSERT IGNORE 
  ... ;                   -- without ON DUPLICATE KEY

or try to do a redundant update when there is a duplicate:

INSERT 
  ... 
ON DUPLICATE KEY UPDATE
  id = id ;

or check for duplicates before inserting:

INSERT INTO requests
  (id, ctg, msg, nick, filled, dated, filldate)
SELECT
  NULL, 'urgent', 'Help!', 'Hermione', 'Y', NOW(), NOW()
FROM
  dual
WHERE NOT EXISTS
      ( SELECT *  FROM requests  WHERE (nick, msg) = ('Hermione', 'Help!') )
  AND NOT EXISTS
      ( SELECT *  FROM requests  WHERE (ctg, msg) = ('urgent', 'Help!') ) ;

A difference between the 3rd way and the first two is that when there are duplicates, the id will not be incremented. With INSERT IGNORE and INSERT ... ON DUPLICATE KEY, it will be auto incremented and since the insert will not be done, you'll have gaps in the values of id.

I should also add that your scripts should always check for errors anyway and not fail when there is one. Any query or statement can fail and return error occasionally, for various reasons. The tricks above will only save you from one kind of error.

1

The options are great, I just know a fourth one. You can Create a procedure as the following (MySQL 5.5 or higher).

DELIMITER ;;
CREATE PRODECURE...
...necessary parameters...
BEGIN

DECLARE v_dups BIGINT;
DECLARE CONTINUE HANDLER FOR 1062 SET v_dups = v_dups + 1;

-- RUN SIMPLE INSERT, IF IT TAKES DUPLICATE KEY NOTHING HAPPENS
END;;
  • Nice, I haven't been using MySQL lately so I didn't know that. Can you update and elaborate on: what happens if other (not duplicate key violations) errors occur? And how does this work exactly (what 1062 stand for)? Does the query need to be a singe insert or can it work with inserting multiple rows? – ypercubeᵀᴹ Apr 17 '17 at 23:10
  • 1062 is Duplicated row. Only this SQL error triggers the: SET v_dups = v_dups + 1; – billmask Nov 8 '17 at 0:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.