有一个表其中一个字段是手机号,另一个字段是次数,如果是判断手机号表里没有则insert,如果手机号表里已经存在则update次数列加1,如何用一个sql语句完成呢谢谢!
解决方案 »
- linux系统下面,mysql数据库服务器要换磁盘阵列了,原有的ibd文件以及数据怎么迁移呢?
- 我的WEB程序访问MYSQL是不要密码的,可是MYSQL有密码?
- myisam btree 索引的弊端?
- mysql 俺写了个创建数据库以及建3个表的语句 存在了 create_news.sql文件中,请问怎么执行这个脚本文件呢 ?
- 数据库导入出错的问题
- MYSQL中文存储的问题
- 用installshield 打包mysql + odbc 的问题?
- Err 1064求解决
- 新手linux 下mysql内存占用过高
- 求帮忙
- 怎么显示 数据库count统计的值. $rs->count(*) 这样不行.会出错
- mysql 可以编译安装覆盖原mysql吗
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