当unique列在一个UNIQUE键上插入包含重复值的记录时,我们可以控制MySQL如何处理这种情况:使用IGNORE关键字或者ON DUPLICATE KEY UPDATE子句跳过INSERT、中断操作或者更新旧记录为新值。
增量文件导入到mysql,如果一条增量文件里有N条为1类型的数据操作,在一个insert语句里就可以实现了,使用的语法是:
insert into table(a,b,c) values(1,2,3),(2,3,4)....on duplicate key update b = values(b)。
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;
如果不会导致唯一值列重复的问题,则插入新行。
首先,我们都知道insert 的语法为insert into table (id,value) values (1,2),那么当我们有成千上万条数据要插入的时候可以这样写:
insert into table (id,value) values(1,2),(2,3),(3,4).....
这样就可以一次性插入多条数据。
INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);
执行后, c 的值会变为 7 (第二条与第一条重复, c 在直接取重复的值7).
注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!
这个语法和适合用在需要 判断记录是否存在,不存在则插入存在则更新的场景.
例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果:
INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
等同于:如果行作为新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2
UPDATE TABLE SET c=c+1 WHERE a=1;
那么对于update语句由于往往是因为条件不同而更新的值不同,所以只能分开做。对于下面两种情况可以这样处理:
1. 更新条件中有主键或者是唯一的键。
2. 更新条件是等于而不是范围。
对于第一点可以用replace
或者insert into table(a,b,c) values(1,2,3),(2,3,4)....on duplicate key update b = values(b)。
具体请参考 http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert Mysql insert 语法。 对于第二条可以建立一个临时的表来存储要更新的数据 create temporary table temp (id int primary key, name varchar(20), address text) insert into temp (id,name,address) values(?,?) update usr,temp set usr.name = temp.name,usr.address = temp.address where usr.id=temp.id 切记建立临时表的时候不可以断开数据库连接,一旦断开临时表就不存在了。