Mysql单条SQL解决数据的插入或者更新

以下是建表语句

CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL,
`author` varchar(100) NOT NULL,
PRIMARY KEY (`id`) );

插入时判断是否存在，存在则不做任何操作:

insert into test select * from (select 1, 'aa') as tmp where not exists (select * from test where id = 1);

或者

insert ignore into test set id = 1, author = "aa";

上面一种灵活性更大一些

插入时判断是否存在，存在则更新数据：

insert into test values (1, 'aa') on duplicate key update author = 'aa';