MySQL中详细update (on duplicate key update id=values(id),value=values(value)......)
use test;
drop table if exists test.test;
CREATE TABLE `test` (
`id` tinyint(4) NOT NULL,
`value` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into test.test(id,value) values(1,"first"),(2,"second"),(3,"third");
select * from test.test;
/**
'1', 'first'
'2', 'second'
'3', 'third'
*/
-- 方案1
insert into test(id,value) values(1,"11first"),(2,"22second"),(3,"33third") on duplicate key update id=values(id),value=values(value);
-- 方案2
select * from test.test;
replace into test(id,value) values(1,"11first"),(2,"22second"),(3,"33third");
select * from test.test;
/**
-- 看结果已经改变了
'1', '11first'
'2', '22second'
'3', '33third'
*/