insert into 插入多条数据可以用values(),(),()....使用insert into ... values() ,(),() on duplicate key ...也能插入多条记录
但是on duplicate key是对这插入的记录作同样的操作的,不能针对每个值作修改例子如下:
CREATE TABLE `test` (
`id` tinyint(4) NOT NULL,
`value` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;insert into test(id,value) values(1,"first"),(2,"second"),(3,"third")insert into test(id,value) values(1,"11first"),(2,"22second"),(3,"33third") on duplicate key update value="11first"我想要的结果是将每个值都改成对应的新值,即插入多条时,key存在的话,把value都换成新值
1,11first
2,22second
3,33third
但是on duplicate key是对这插入的记录作同样的操作的,不能针对每个值作修改例子如下:
CREATE TABLE `test` (
`id` tinyint(4) NOT NULL,
`value` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;insert into test(id,value) values(1,"first"),(2,"second"),(3,"third")insert into test(id,value) values(1,"11first"),(2,"22second"),(3,"33third") on duplicate key update value="11first"我想要的结果是将每个值都改成对应的新值,即插入多条时,key存在的话,把value都换成新值
1,11first
2,22second
3,33third
replace into test(id,value) values(1,"11first"),(2,"22second"),(3,"33third")
这个?
replace into test(id,value) values(1,"11first"),(2,"22second"),(3,"33third")
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'
*/
insert into test.test(id,value) values(1,"11first"),(2,"22second"),(3,"33third") on duplicate key update value="11first";
select * from test.test;
/**
-- 看结果已经改变了
'1', '11first'
'2', '11first'
'3', '11first'
*/
select * from test.test;
是改变所有的值都为11first了。试试replace吧,所有sql如下: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'
*/
replace into test.test(id,value) values(1,"11first"),(2,"22second"),(3,"33third") ;
select * from test.test;
/**
-- 看结果已经改变了
'1', '11first'
'2', '22second'
'3', '33third'*/
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'
*/
insert into test(id,value) values(1,"11first"),(2,"22second"),(3,"33third") on duplicate key update id=values(id),value=values(value);
select * from test.test;
/**
-- 看结果已经改变了
'1', '11first'
'2', '22second'
'3', '33third'
*/
+----+--------+
| id | value |
+----+--------+
| 1 | first |
| 2 | second |
| 3 | third |
+----+--------+
3 rows in set (0.01 sec)mysql> insert into test(id,value) values(1,"11first"),(2,"22second"),(3,"33third") on duplicate key update value=values(value);
Query OK, 6 rows affected (0.00 sec)
Records: 3 Duplicates: 3 Warnings: 0mysql> select * from test;
+----+----------+
| id | value |
+----+----------+
| 1 | 11first |
| 2 | 22second |
| 3 | 33third |
+----+----------+
3 rows in set (0.00 sec)mysql>
我给的例子不详细了 应该说有3个字段 value value2
插入的时候我只想改变value 而value2保持不变不使用新值
id value value2
1 first bad
2 second bad--------------------
1 11first good
2 22second bad即插入的时候value2默认是bad,但是会有其他进程读同一张表,将value2的值改变,我们这里面的插入只修改value的值
你动作太快了吧!FU L