MySQL中详细update (on duplicate key update id=values(id),value=values(value)......)

最新推荐文章于 2024-04-27 10:42:16 发布

mysqldbd

最新推荐文章于 2024-04-27 10:42:16 发布

阅读量8.9k

点赞数 1

分类专栏： MySQL 开发文章标签： mysql insert table null

本文链接：https://blog.csdn.net/mysqldbd/article/details/6137509

版权

MySQL 开发专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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'
*/