Томас Петр Томас Петр - 3 months ago 11
MySQL Question

SQL - Verification of existing data

I have a script always verifies that the row exists or not, depending on adjusts to the event.

For example:

if(exists($row)){
// update
}else{
// insert
}


Insert query:

INSERT INTO table (name, value) VALUES
('...', '1'),
('...', '2'),
('...', '3'),
('...', '4'),
('...', '5'),
//...100x


UPDATE QUERY:

UPDATE table
SET value = CASE uuid
WHEN 'x' THEN 1
WHEN 'y' THEN 2
END,
SET value1 = CASE uuid
WHEN 'x' THEN 1
WHEN 'y' THEN 2
END
WHERE uuid IN ('x','y')


Data is quite a lot, and verify that the record exists long enough. Because performs necessary 500x or more.

Exists method:

SELECT id FROM table WHERE uuid=Y;


There is a possibility somehow speed up the script? It is possible to verify existing data added directly to the query?

Can I have just one query CREATE + UPDATE + CHECK EXISTING?

Thanks for help!

Updating and adding data with verification : 27-40 SEC
Updating and adding data WITHOUT verification: 1-5 SEC


EDIT: I'm getting data from another server like json example script:

$data = json_decode($_POST["data"]);
foreach($data as value){
// value is an array
if(exists($value["uuid"])){
$this->appendUpdate($value); // building the update query
}else{
$this->appendInsert($value); // building the insert query
}
}

$this->insert(); // for example: 30 inserts in 1 query
$this->update(); // for example: 500 updates in 1 query


In this table is just 300-2000 rows (I do not understand why it takes so long.)

EDIT2:

This is probably my solution:

INSERT INTO test (uuid, value) VALUES
('aaa', 1),
('bbb', 2),
('ccc', 3)
ON DUPLICATE KEY UPDATE
value = (
IF(uuid='aaa', 4, IF(uuid='bbb', 5, IF(uuid='ccc', 6, value)))
);


I'm going to try to run the brisk server and then touch.

Answer

Make sure the column uuid has a unique index and you can do the following query

INSERT INTO table (a,b) VALUES (1,2)
   ON DUPLICATE KEY UPDATE b=4;
Comments