Member Avatar for jstfsklh211

my insert statement seems to be ignoring my unique index and adding duplicates
what'd i mess up?

thanks in advance

Table

CREATE TABLE `address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `address_street1` varchar(100) NOT NULL,
  `address_street2` varchar(100) DEFAULT NULL,
  `address_street3` varchar(100) DEFAULT NULL,
  `address_city` varchar(45) NOT NULL,
  `address_state` char(2) DEFAULT NULL,
  `address_zip` varchar(10) NOT NULL,
  `address_country` varchar(100) NOT NULL,
  `address_latitude` double DEFAULT NULL,
  `address_longitude` double DEFAULT NULL,
  PRIMARY KEY (`address_id`),
  UNIQUE KEY `a_dupe` (`address_street1`,`address_street2`,`address_street3`,`address_city`,`address_state`,`address_zip`,`address_country`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=latin1;

insert query

set @address_street1 = '1 street name';
set @address_street2 = null;
set @address_street3 = null;
set @address_city = 'some city';
set @address_state = 'SS';
set @address_zip = '00000';
set @address_country = 'US';
INSERT INTO address (address_street1, address_street2, address_street3, address_city, address_state, address_zip, address_country)
VALUES (@address_street1, @address_street2, @address_street3, @address_city, @address_state, @address_zip, @address_country)
ON DUPLICATE KEY UPDATE address_id = LAST_INSERT_ID(address_id);
Edited by jstfsklh211
  • 3 Contributors
  • 3 Replies
  • 1K Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Teddy112

Dani AI

Generated

is right: this is NULL semantics, not a broken UNIQUE. In MySQL, a UNIQUE index allows multiple NULLs, so two rows that are identical except for nullable parts of the key are not considered duplicates. Because no duplicate-key error is raised, ON DUPLICATE KEY UPDATE never fires, which is why saw new rows instead of updates. (dev.mysql.com)

If you want your upsert to treat NULL like an empty value, index a normalized form that replaces NULLs. On MySQL 5.7+, add virtual generated columns and put a UNIQUE index on them (InnoDB can index virtual generated columns):

ALTER TABLE address
  ADD address_street2_n VARCHAR(100) GENERATED ALWAYS AS (COALESCE(address_street2,'')) VIRTUAL,
  ADD address_street3_n VARCHAR(100) GENERATED ALWAYS AS (COALESCE(address_street3,'')) VIRTUAL,
  ADD address_state_n  CHAR(2)      GENERATED ALWAYS AS (COALESCE(address_state,''))  VIRTUAL,
  ADD UNIQUE KEY a_dupe_norm (
    address_street1, address_street2_n, address_street3_n,
    address_city, address_state_n, address_zip, address_country
  );

With that in place, a row where address_street2 is NULL normalizes to '' and collides as intended, so your existing INSERT ... ON DUPLICATE KEY UPDATE pattern will update instead of inserting. (dev.mysql.com)

On MySQL 8.0.13+ you can skip the generated columns and create a functional unique index directly, for example: CREATE UNIQUE INDEX a_dupe_norm ON address (address_street1,(COALESCE(address_street2,'')),(COALESCE(address_street3,'')),address_city,(COALESCE(address_state,'')),address_zip,address_country); Consider also normalizing in the expression (e.g., TRIM(...), UPPER(...)) if your business rules require it. (dev.mysql.com)

Recommended Answers

All 3 Replies

Member Avatar for cereal

It happens because you're using NULL values, for the series it's not a bug, it's a feature:

Edited by cereal
Member Avatar for jstfsklh211

yep that would do it thanks

Member Avatar for Teddy112

cereal is right ...it is happening due to null values which are being assigned.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.