1

I have a MySQL table defined as so:

create table MyTable2 (
   ID bigint unsigned NOT NULL AUTO_INCREMENT, 
   Field2 varchar(4096) CHARACTER SET utf8 NOT NULL, 
   Field3 varchar(256) CHARACTER SET utf8 NOT NULL,
   Field4 varchar(64) NOT NULL, 
   ForeignID bigint unsigned NOT NULL, 
   DateAdded TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY(ID), 
FOREIGN KEY fk_ForeignID(ForeignID) REFERENCES MyTable1(ID)
ON UPDATE CASCADE ON DELETE RESTRICT);

I want to insert a bunch of new records. However, if a record exists where Field2, Field3, Field4 and ForeignID match I want to ignore. IOW, if a record matches but not the DateAdded field or the ID (which is auto added) then I want to ignore the insert. But I want to add any where these 4 fields do not match already.

Is this possible with a MYSQL INSERT command alone or do I need to do some magic where I compare the values in existing records against these 4 fields?

I tried with INSERT IGNORE INTO setting a record matching these 4 fields and the new record was added with a new ID and DateAdded field which is NOT what I want.

kaya3
  • 47,440
  • 4
  • 68
  • 97
  • You should have an unique key on those 4 cols. – tkausl Dec 08 '16 at 16:19
  • Possible duplicate of: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – xQbert Dec 08 '16 at 16:24
  • Not sure changing Field2, Field3, Field4 and ForeignID to UNIQUE KEYs will work as any of those values can occur individually in other records. I just don' want to add a new record if the 4 exist already with an older DateAdded value. I hope this clarifies. –  Dec 08 '16 at 16:31
  • Not sure if you misunderstand the suggestions or if something is still unclear: if you have one 4-column unique key (not 4 1-column unique keys), `ignore` will ignore rows with that exact (unique) combination, and I think that is what you are looking for. – Solarflare Dec 08 '16 at 16:48
  • Ah. Gotcha. UNIQUE KEY (Field2, Field3, Field4, ForeignID). Alas, my Field2 is a varchar(4096) and creating the UNIQUE KEY gives the error ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes". Any way around this? –  Dec 08 '16 at 17:39
  • Without additional comments out there I think I can resolve this by modifying the size of my fields to bring under 767 bytes. After evaluation those max sizes (UTF8 on top of that thus doubling the byte count) weren't hard values so I could safely bring those fields smaller to fit into a UNIQUE KEY(set). Thanks for the clarification on UNIQUE KEY(multiple columns). Turns out I had only every declared single columns UNIQUE. –  Dec 08 '16 at 20:14

0 Answers0