1

I try to do INSERT IF NOT EXISTS

So I search for this and I found:How to 'insert if not exists' in MySQL?

So I try to use IGNORE , this is my table:

CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
`mail` VARCHAR(255),
PRIMARY KEY (`id`)
) CHARACTER SET utf8 COLLATE utf8_general_ci;

And this is my query:

INSERT IGNORE INTO `users` SET `mail` = ?, `name` = ?

But its insert even if exists So I search for that and i found: mySQL INSERT IGNORE doesn't "ignore"

I read there that only with PRIMARY KEY its dosent insert duplicates

So as you can see in my table I have PRIMARY KEY
If the problem is that I need to insert into my query the id field its problem because I dont know what it is when I do the query
So how to insert information to db without creating duplicates ?

Community
  • 1
  • 1
sdfds dffdsf
  • 51
  • 1
  • 6

3 Answers3

1

Insert ignore only works when you try to insert a value to a column which is primary key (id), but you haven't include id. On the other hand you don't even need it. You did just right. When you insert a new user, it'll get the next available I'd, because it's auto_increment.

Maybe you want to make the email field uniq, so two users can't have the same email. In that case the insert ignore would ignore adding a new line if you used an email that's already in the table.

Gavriel
  • 18,088
  • 12
  • 63
  • 98
  • The OP stated that he knows this issue and looking for alternate solutions, and also he doesn't know the `id` as it is most probably `auto-increment` field and unknown most of the time. – Mahmoud Tantawy Jan 27 '16 at 18:21
0

You will have to select first using the info you are trying to insert and if the result contains one or more rows then don't insert.

For example, run the query SELECT count(*) from users where mail = ? and name = ? Then check the returned result object, and according to it, insert or skip.

Mahmoud Tantawy
  • 683
  • 5
  • 12
0

INSERT IGNORE only skip records when some keys are violated. Create a unique key in your case:

CREATE UNIQUE INDEX uniq_users ON users (name, mail);
slbteam08
  • 611
  • 3
  • 11