InnoDB Transaction Help - Select then if Not Found Add Unique Row

Question

Hi all!

I have a mysql table that contains IPAddress parts

TABLE `EndPoints`(
    `EndPointId` BIGINT(19) UNSIGNED NOT NULL AUTO_INCREMENT ,
    `IpPart1` TINYINT(3) UNSIGNED NOT NULL ,
    `IpPart2` TINYINT(3) UNSIGNED NOT NULL ,
    `IpPart3` TINYINT(3) UNSIGNED NOT NULL ,
    `IpPart4` TINYINT(3) UNSIGNED NOT NULL ,
    PRIMARY KEY (`EndPointId`))
ENGINE = InnoDB

---

Each IpPart contains the byte of an IPAddress byte array. e.g. address 100.101.102.103
IpPart1 = 101;
IpPart2 = 102;
IpPart3 = 103;
IpPart4 = 104;

---

Anyway!

I want to do the following :

SELECT EndPointId FROM EndPoints WHERE
    IpPart1 = @a AND
    IpPart2 = @b AND
    IpPart3 = @c AND
    IpPart4 = @d;

Then, if the address is not found, add it

INSERT INTO EndPoints (IpPart1,IpPart2,IpPart3,IpPart4)
    Values(@a,@b,@c,@d);

---

Using MySql transactions how will I ensure that only one row is added for each IPAddress?

Thanks!

Answer 1

Well you could BEGIN a transaction, do the two queries you quoted, then COMMIT. But wouldn't it be easier just to put a UNIQUE key over (IpPart1, IpPart2, IpPart3, IpPart4)?

(Is there really anything to be gained by separating out the octets in this way? It seems unlikely you'd want to eg. search on and index IpPart3 alone. IPv4 addresses are often stored as a string or simple integer converted by inet_aton. IPv6 addresses are a bit more work, using a canonicalised string or a BINARY char(16).)