php How to check existed before insert data to mysql

Question

I have a table (id, location, timestamp, type, value)

location / timestamp / type can duplicate,
but location + timestamp + type is unique

1, "Paris", 1474986000, "a", "100"
2, "London", 1474986000, "a", "90"
3, "Paris", 1474986000, "b", "12"
4, "London", 1474986000, "b", "13"
5, "Paris", 1474990000, "a", "100"
6, "London", 1474990000, "a", "100"
7, "Paris", 1474990000, "a", "100"
8, "London", 1474990000, "a", "100"

I tried to check existed before insert using select statement, but i have >100000 record, it spend a lot of time for checking

any other solution can solve this problem? thanks~

Or write the INSERT query and process the duplicate error as an OK — RiggsFolly
– RiggsFolly, Commented Sep 28, 2016 at 15:21
Please check this docs: dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html — Petko Kostov
– Petko Kostov, Commented Sep 28, 2016 at 15:22

spencer7593 · Accepted Answer · 2016-09-28 15:45:02Z

If there is a unique constraint on that combination of columns, assuming those columns are all declared NOT NULL...

You could create a unique index. That index would speed up a "search" for existing rows, with an appropriate SELECT statement.

 CREATE UNIQUE INDEX mytable_UX1 ON mytable (location, timestamp, type)

But if you have a UNIQUE INDEX defined, and the goal is to just to add rows that are missing, you could use INSERT IGNORE

 INSERT IGNORE INTO mytable ( ... ) VALUES ( ... ), ( ... )

An attempt to insert a row that violates a unique constraint will not throw an error. The IGNORE keyword causes MySQL to ignore more than just duplicate key violations, it turns other errors into warnings.

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

Another technique I have used is to perform a check within the INSERT itself, using an INSERT ... SELECT ... with the SELECT statement including an anti-join to eliminate rows that already exist. This approach only checks for rows that already exist in the table, not "duplicates" that might be produced by the SELECT statement.

Amitsouko · Accepted Answer · 2016-09-28 15:43:14Z

1

You can use INSERT IGNORE that keeps the script running and does not insert duplicate keys. Or you can use INSERT ... ON DUPLICATE KEY...

answered Sep 28, 2016 at 15:43

Amitsouko

1755 bronze badges

Collectives™ on Stack Overflow

php How to check existed before insert data to mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related