I have a following
Table1 : userid, who, share, date :: id is auto increment and primary key
I would like to build a query to check if record exist and insert new record if is empty. How to build this in a single query and insert multiple records using a single query
data to inser ('a1','a2','a3','a4'), ('b1','b2','b3','b4'), ('c1','c2','c3','c4'), .......
UNIQUE index on column that you want to be uniqueINSERT IGNORE to insert unique data and ignore not uniqueYou could use REPLACE statemement that works just like INSERT only it doesn't do anything if the row already exists:
REPLACE INTO table ...
You should take careful look at mysql INSERT INTO documentation.
How to insert "on no exists" is simple. Let's say you want combination of user,who,share to be unique and use latest date. Update your table and create UNIQUE KEY on those fields:
ALTER TABLE Table1 ADD UNIQUE KEY (user, who, share);
Normally inserting the same combination would cause error but using INSERT IGNORE (link above) would silently ignore error:
INSERT IGNORE INTO Table1 (user,who,share,date) VALUES ( 1, 2, 3, NOW());
You may also force key to update on insert:
INSERT IGNORE INTO Table1 (user,who,share,date) VALUES ( 1, 2, 3, NOW())
ON DUPLICATE KEY UPDATE date = VALUES(date);
And inserting multiple values at once, again the first link:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
insert ignore into <table> values(),(),();