1

I have a table just like the following one. It's a price talbe name price_data and it contains a unique key DateTime and a value price.

 +----------+-------+
 | DateTime | price |
 +----------+-------+
 |2018-01-01| 100.2 | 
 |2018-01-02| 101.5 | 
 +----------+-------+

I want to insert multiple price data into the table simultaneously. Thus, I can use the following SQL to accomplish that.

Insert two rows just for example. 

INSERT INTO `price_data` (`DateTime`, `price`) VALUES
('2018-01-03', 100.0),
('2018-01-04', 101.2);

P.S. In the real case, I will insert over 100 rows simultaneously.

The above SQL is total OK for the task.

However, if the data that will be inserted had been in the table. For example: ('2018-01-02', 101.5). There is an error since the key had existed.

I find the two ways to solve the problems.

  1. INSERT IGNORE
  2. INSERT ... ON DUPLICATE KEY UPDATE

In the first case, it works and doesn't print any error message. However, according to the post, INSERT IGNOREwill hide all errors rather than the only key duplicated.

INSERT IGNORE INTO `price_data` (`DateTime`, `price`) VALUES
('2018-01-02', 101.5),
('2018-01-03', 100.0);

In the second case, it works and doesn't print any error message too. However, there is a side effect that the SQL will update at least one column. Because I don't want to update any column if the row existed, I will let the key DateTime be updated to the new DateTime. Since the DateTime will equal the new DateTime, there isn't any change in the table.

INSERT INTO `price_data` (`DateTime`, `price`) VALUES
('2018-01-02', 101.5),
('2018-01-03', 100.0);
ON DUPLICATE KEY UPDATE DateTime=VALUES(DateTime);

Is the second way better?

Are there other ways to accomplish that and waste less time?

Thank you very much.

fishballLin
  • 45
  • 1
  • 9
  • `INSERT...ON DUPLICATE KEY UPDATE` should be fine. Only side effect is that if you have an Auto-increment column in your table; it will also get unnecessarily incremented; however it will not be used in current query. – Madhur Bhaiya Nov 22 '18 at 09:48
  • Can you clarify what exactly the problem is in using the first way? If you don't want to update existing columns, it looks pretty good to me – Nico Haase Nov 22 '18 at 09:48
  • One option: Unique key on both columns, and insert one record at a time. – Tim Biegeleisen Nov 22 '18 at 09:48
  • @NicoHaase according to the [post](https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update/548570#548570) There are three problems. 1. Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints. 2. Inserting a NULL into a column with a NOT NULL constraint. 3. Inserting a row to a partitioned table, but the values you insert don't map to a partition. – fishballLin Nov 22 '18 at 10:38
  • @TimBiegeleisen Because I have lots of rows needed to be inserted, inserting one record at a time will be too slow. Thus, I want to use the batch insert. – fishballLin Nov 22 '18 at 10:40

0 Answers0