Synia Synia - 6 months ago 17
SQL Question

How to know if MySql's conditional INSERT worked?

With a conditional Insert, I want to know if a row was inserted or if overlap was found.

I have a query that inserts a request into the database if it doesn't overlap with any existing entry. The query works, but how do I know if there's been an collision? The query just executes successfully. Here is my query:

INSERT INTO requests(id, start_time, end_time)
SELECT NULL, 1463631640671,1463636731000,
FROM Dual
WHERE NOT EXISTS (
SELECT * FROM requests
WHERE
start_time < 1463638531000 AND
end_time > 1463636731000
)


For your information. I'm doing this inside Node.js with the mysql package.

and the code looks like this, and it returns 'SUCCESS' every time.

var queryString = "INSERT INTO requests(id, start_time, end_time..."

connection.query(queryString, function(error, results, fields) {
if (!error) {
console.log('SUCCESS!');
}
else {
console.log('Insert into requests unsuccessful.');
console.log('ERROR: ' + error);
}
});

Answer

you can inspect results.insertId and see if you have something in it.