Pano Pano - 2 months ago 17
MySQL Question

node.js update mysql database

I am using the node.js mysql package where I can create a connection and after connection.query(..) .., close the connection.

I have a collection of rows that I want to update in mysql database, and I don't know if the rows exist or not. Here I have two solutions but I don't know which one is better:


  1. delete all the rows I want to update in mysql database and insert all rows using one connection.query(..)..

    sudo code: 1. delete from table where id = ..[],
    2. insert into table set ...

  2. check each row that I want to update/insert, if the id exists in mysql database, then send query to update, otherwise insert.

    sudo code: 1. for each row in list
    2. check if the row exists in mysql
    3. if exists, then send query to update;
    if not exists, then send query to insert



for the second solution, I concerned that might be too time-consuming. Any one have some suggestions? What is the best way to do this?
I'm new to these stuff so I don't know if it is better to send multiple queries inside a for loop, especially in node each connection.query is a function with callback. Which one will have better overall performance?, or any other solutions?

Answer

Use insert ... on duplicate key update

Example:

insert into `tableName` (col1, col2, col3) values ( 'v1', 'v2', 'v3') on dupicate key update `col3`='v3'

*This wouldn't work if the key in this case is an auto_increment counter.

Source: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html