MYSQL if not exists insert data

Question

Guys I'm getting duplicate records on insert into my database for some reason with this code

$qry = "INSERT IGNORE INTO reports (". implode(", ",array_keys($reports)) .") VALUES (". implode(", ",array_values($reports)) .");";
if(!mysql_query(trim($qry), $link)) { die('Error: ' . mysql_error()); }

The actual statement is this

INSERT IGNORE INTO reports (`inspection_number`, `report_date`, `customer`) VALUES ('996', '10-21-2012', 'Jd Daniel');

DB now looks like

19  NULL    NULL    NULL    996     NULL    0000-00-00  NULL    Jd Daniel   NULL    NULL    NULL    NULL
20  NULL    NULL    NULL    996     NULL    0000-00-00  NULL    Jd Daniel   NULL    NULL    NULL    NULL
21  NULL    NULL    NULL    996     NULL    0000-00-00  NULL    Jd Daniel   NULL    NULL    NULL    NULL
22  NULL    NULL    NULL    996     NULL    0000-00-00  NULL    Jd Daniel   NULL    NULL    NULL    NULL

I thought that INSERT IGNORE was supposed to ignore duplicates? What's up?

EDIT Here's my table structure, I was trying to use inspection_number as my unique index to compare against.

--
-- Table structure for table `reports`
--

DROP TABLE IF EXISTS `reports`;
CREATE TABLE `reports` (

    `key`                   INT UNSIGNED AUTO_INCREMENT, 
    `role`                  VARCHAR(70), 
    `region`                VARCHAR(70),
    `inspection_type`       VARCHAR(70),
    `inspection_number`     VARCHAR(70),
    `customer_number`       VARCHAR(70),

    `report_date`           DATE DEFAULT NULL,  -- Date field? Needs DATETIME instead? Needs DEFAULT NULL?
                                                -- Does this need to be created on upload,
                                                -- or is it uploaded from tablet?

    `order_date`            DATE DEFAULT NULL,  -- Date field? Needs DATETIME instead? Needs DEFAULT NULL?
                                                -- Ditto

    `customer`              VARCHAR(70),
    `customer_division`     VARCHAR(70),
    `location`              VARCHAR(70),
    `memo`                  VARCHAR(255),       -- Same as _comments? VARCHAR(255)??
    `billing_key`           VARCHAR(70),

    PRIMARY KEY(`key`)

) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

This may also be helpful: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — Dan, Feb 16 '12 at 18:19
It among other things causes Primary Key Violation to be treated as a warning. So if 19, 22 happens to be an auto inc for instance, there are no duplicates.... — Tony Hopkinson, Feb 16 '12 at 18:24
But the structure says `inspection_number` is *not* a unique index? — JJJ, Feb 16 '12 at 18:39

Justin · Accepted Answer · 2012-02-16T18:33:36.123

The INSERT IGNORE will try to insert the record into the table, and ignores the duplicate error from the database engine so that your script won't fail to continue.

In order to avoid having duplicate data in your table. you need to create a PRIMARY KEY on your table. This example below will not allow more than 1 row with the same inspection_number number

EXAMPLE:

CREATE TABLE reports (
  inspection_number int(10) NOT NULL,
  report_date       timestamp,
  customer          VARCHAR(50),
  PRIMARY KEY(inspection_number)
);

score 1 · Answer 2 · answered Feb 16 '12 at 18:19

1

If you do not want any duplicate you can use the Replace command instead of Insert .

http://dev.mysql.com/doc/refman/5.0/en/replace.html

This will try to add the value, if detect a duplicate (from primary key) will delete the old one and insert it again.

answered Feb 16 '12 at 18:19

Patrick Desjardins

136,852
88
292
341

This doesn't seem to insert? `REPLACE INTO reports ('inspection_number', 'report_date', 'customer') VALUES ('996', '10-21-2012', 'Jd Daniel');` Something wrong? – ehime Feb 16 '12 at 18:24
You need a primary key somewhere other wise it will simply add the value. See doc "Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another." – Patrick Desjardins Feb 16 '12 at 18:26
Probably a stupid question, but can I use `UNIQUE INDEX('inspection_number')` when I create the table? – ehime Feb 16 '12 at 18:38
An index is not a Primary key but yes you could do this. – Patrick Desjardins Feb 16 '12 at 18:45

score 1 · Answer 3 · answered Feb 16 '12 at 18:21

1

INSERT IGNORE considers a row duplicate if it has the same primary key or unique index. Most likely none of the three fields (inspection_number, report_date or customer) are primary keys or unique indexes.

answered Feb 16 '12 at 18:21

JJJ

32,902
20
89
102

MYSQL if not exists insert data

3 Answers3