mysql> create table t_jiab( -> mp int, -> tdate date, -> cnt int default 1 -> ); Query OK, 0 rows affected (0.09 sec)mysql> create UNIQUE index uk_t_jiab on t_jiab (mp,tdate); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> insert into t_jiab(mp,tdate) -> select 100, ifnull((select tdate from t_jiab where mp=100 and date(tdate)=date('2009-06-29 06:41:14')),'2009-06-29 06:41:14') -> ON DUPLICATE KEY UPDATE cnt=cnt+1; Query OK, 1 row affected, 1 warning (0.06 sec) Records: 1 Duplicates: 0 Warnings: 1mysql> select * from t_jiab; +------+------------+------+ | mp | tdate | cnt | +------+------------+------+ | 100 | 2009-06-29 | 1 | +------+------------+------+ 1 row in set (0.00 sec)mysql> insert into t_jiab(mp,tdate) -> select 100, ifnull((select tdate from t_jiab where mp=100 and date(tdate)=date('2009-06-29 02:31:13')),'2009-06-29 02:31:13') -> ON DUPLICATE KEY UPDATE cnt=cnt+1; Query OK, 2 rows affected (0.06 sec) Records: 1 Duplicates: 1 Warnings: 0mysql> select * from t_jiab; +------+------------+------+ | mp | tdate | cnt | +------+------------+------+ | 100 | 2009-06-29 | 2 | +------+------------+------+ 1 row in set (0.00 sec)mysql> insert into t_jiab(mp,tdate) -> select 100, ifnull((select tdate from t_jiab where mp=100 and date(tdate)=date('2009-06-30 02:31:13')),'2009-06-30 02:31:13') -> ON DUPLICATE KEY UPDATE cnt=cnt+1; Query OK, 1 row affected, 1 warning (0.06 sec) Records: 1 Duplicates: 0 Warnings: 1mysql> select * from t_jiab; +------+------------+------+ | mp | tdate | cnt | +------+------------+------+ | 100 | 2009-06-29 | 2 | | 100 | 2009-06-30 | 1 | +------+------------+------+ 2 rows in set (0.00 sec)mysql>
oracle的实现: merge into tb_user_session us using (select 1 from dual) ustemp on (us.account='10102177') when matched then update set us.sessionid = '123456789' when not matched then insert (us.account, us.sessionid) values ('10102177', '888888888');本来merge into是用于检索两张表,进行匹配然后处理update还是insert的 现在结合dual来做单表处理,也照样行 tb_user_session的字段有: account,sessionid
ON DUPLICATE KEY UPDATE cnt=cnt+1;mysql> create table t_jiab(
-> mp int primary key,
-> cnt int default 1
-> );
Query OK, 0 rows affected (0.14 sec)mysql> insert into t_jiab(mp) values (100)
-> ON DUPLICATE KEY UPDATE cnt=cnt+1;
Query OK, 1 row affected (0.06 sec)mysql> select * from t_jiab;
+-----+------+
| mp | cnt |
+-----+------+
| 100 | 1 |
+-----+------+
1 row in set (0.00 sec)mysql> insert into t_jiab(mp) values (100)
-> ON DUPLICATE KEY UPDATE cnt=cnt+1;
Query OK, 2 rows affected (0.06 sec)mysql> select * from t_jiab;
+-----+------+
| mp | cnt |
+-----+------+
| 100 | 2 |
+-----+------+
1 row in set (0.00 sec)mysql>
你的表中的主键或唯一键有冲突的时候,不再执行insert 替代为 update具体可参见
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert
13.2.4. INSERT语法
很简单,你直接把 (手机号,日期) 做一个复合的唯一键不就行了?
mysql> create table t_jiab(
-> mp int,
-> tdate date,
-> cnt int default 1
-> );
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> create UNIQUE index uk_t_jiab on t_jiab (mp,tdate);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> insert into t_jiab(mp,tdate) values (100,'2009-06-29')
-> ON DUPLICATE KEY UPDATE cnt=cnt+1;
Query OK, 1 row affected (0.06 sec)mysql> select * from t_jiab;
+------+------------+------+
| mp | tdate | cnt |
+------+------------+------+
| 100 | 2009-06-29 | 1 |
+------+------------+------+
1 row in set (0.00 sec)mysql> insert into t_jiab(mp,tdate) values (100,'2009-06-29')
-> ON DUPLICATE KEY UPDATE cnt=cnt+1;
Query OK, 2 rows affected (0.06 sec)mysql> select * from t_jiab;
+------+------------+------+
| mp | tdate | cnt |
+------+------------+------+
| 100 | 2009-06-29 | 2 |
+------+------------+------+
1 row in set (0.00 sec)mysql>
-> mp int,
-> tdate date,
-> cnt int default 1
-> );
Query OK, 0 rows affected (0.09 sec)mysql> create UNIQUE index uk_t_jiab on t_jiab (mp,tdate);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> insert into t_jiab(mp,tdate)
-> select 100, ifnull((select tdate from t_jiab where mp=100 and date(tdate)=date('2009-06-29 06:41:14')),'2009-06-29 06:41:14')
-> ON DUPLICATE KEY UPDATE cnt=cnt+1;
Query OK, 1 row affected, 1 warning (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql> select * from t_jiab;
+------+------------+------+
| mp | tdate | cnt |
+------+------------+------+
| 100 | 2009-06-29 | 1 |
+------+------------+------+
1 row in set (0.00 sec)mysql> insert into t_jiab(mp,tdate)
-> select 100, ifnull((select tdate from t_jiab where mp=100 and date(tdate)=date('2009-06-29 02:31:13')),'2009-06-29 02:31:13')
-> ON DUPLICATE KEY UPDATE cnt=cnt+1;
Query OK, 2 rows affected (0.06 sec)
Records: 1 Duplicates: 1 Warnings: 0mysql> select * from t_jiab;
+------+------------+------+
| mp | tdate | cnt |
+------+------------+------+
| 100 | 2009-06-29 | 2 |
+------+------------+------+
1 row in set (0.00 sec)mysql> insert into t_jiab(mp,tdate)
-> select 100, ifnull((select tdate from t_jiab where mp=100 and date(tdate)=date('2009-06-30 02:31:13')),'2009-06-30 02:31:13')
-> ON DUPLICATE KEY UPDATE cnt=cnt+1;
Query OK, 1 row affected, 1 warning (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql> select * from t_jiab;
+------+------------+------+
| mp | tdate | cnt |
+------+------------+------+
| 100 | 2009-06-29 | 2 |
| 100 | 2009-06-30 | 1 |
+------+------------+------+
2 rows in set (0.00 sec)mysql>
merge into tb_user_session us
using (select 1 from dual) ustemp
on (us.account='10102177')
when matched then
update set us.sessionid = '123456789'
when not matched then
insert (us.account, us.sessionid) values ('10102177', '888888888');本来merge into是用于检索两张表,进行匹配然后处理update还是insert的
现在结合dual来做单表处理,也照样行
tb_user_session的字段有: account,sessionid