mysql insert on duplicate key, update, ignore

insert 语句中不能使用where，所以如果需要根据插入的数据在已有的数据库表是否重复做一些操作可以使用下面三种方法：

1. 使用insert，捕获duplicate错误

2. insert into ... on duplicate key update. 如果重复，执行update

3. insert ignore, 抛出警告而不是错误

 

eg. for insert into ... on duplicate key update

mysql 插入数据，如果存在，更新

INSERT INTO subs
  (subs_name, subs_email, subs_birthday)
VALUES
  (?, ?, ?)
ON DUPLICATE KEY UPDATE
  subs_name     = VALUES(subs_name),
  subs_birthday = VALUES(subs_birthday)

如果批量操作，使用python:

sql = "insert into <table_name> (start_time, name) values(%(start_time)s, %(name)s) on DUPLICATE KEY UPDATE stat_time = values(start_time), name = values(name)"

mysql_conn.executemany(sql, data)  # data is a list of dict, for method to insert batch of tuple, google it.

eg. for insert ignore

插入数据，如果存在，使用insert ignore忽略：

INSERT IGNORE INTO <table_name> (subs_name, subs_email, subs_birthday) values ("Tom", "Tom@jerry.com", "1900");

另一个方案：

IF NOT EXISTS(SELECT * FROM myTable WHERE Email=@Email) THEN INSERT INTO blah blah

 

insert_stmt = mysql.insert(TestTable).values(**data)
on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
       {"start_time": data.get("start_time"), "ts": data.get("ts")})
session.execute(on_duplicate_key_stmt)

 

 

ref: https://stackoverflow.com/questions/15383852/sql-if-exists-update-else-insert-into

https://stackoverflow.com/questions/285937/is-it-possible-to-insert-a-row-but-only-if-a-value-does-not-already-exist/285980#285980