实现数据库批量更新与回滚
create database awktest;
use awktest
create table user(
id int unsigned not null unique auto_increment primary key,
name varchar(40) not null,
value int unsigned not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8 comment='user表';
insert into user(id, name, value) values ('1','xiao1','18'),
('2','xiao2','19'),
('3','xiao3','20'),
('4','xiao4','21');
exit
mysql -uroot -p -e 'use awktest;select * from user;' > datadump.txt
nl datadump.txt | sed '1d' | awk -F ' ' '{printf ("update user set value = 0 where name = \047%s\047;\n", $3)}' > sql.txt
nl datadump.txt | sed '1d' | awk -F ' ' '{printf ("update user set value = %s where name = \047%s\047;\n",$4,$3)}' > rollback.txt
db -Dawktest < ~/sql.txt
db -Dawktest < ~/rollback.txt
其中sql.txt:
update user set value = 0 where name = 'xiao1';
update user set value = 0 where name = 'xiao2';
update user set value = 0 where name = 'xiao3';
update user set value = 0 where name = 'xiao4';
rollback.txt:
update user set value = 18 where name = 'xiao1';
update user set value = 19 where name = 'xiao2';
update user set value = 20 where name = 'xiao3';
update user set value = 21 where name = 'xiao4';