insert into test select 0,'123456' from test where sign=1 and phone='123456' union all select distinct 1,'123456' from test where not exists(select 1 from test where sign=1 and phone='123456');
mysql> select * from t_floodow; Empty set (0.00 sec)mysql> insert into t_floodow -> select count(*)=0,'123456' -> from t_floodow -> where col1=1 and phone='123456'; Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0-- 第一次,不存在 1,123456, 所以插入 | 1 | 123456 | mysql> select * from t_floodow; +------+--------+ | col1 | phone | +------+--------+ | 1 | 123456 | +------+--------+ 1 row in set (0.00 sec)mysql> insert into t_floodow -> select count(*)=0,'123456' -> from t_floodow -> where col1=1 and phone='123456'; Query OK, 1 row affected (0.08 sec) Records: 1 Duplicates: 0 Warnings: 0-- 第二次,存在 1,123456, 所以插入 | 0 | 123456 | mysql> select * from t_floodow; +------+--------+ | col1 | phone | +------+--------+ | 1 | 123456 | | 0 | 123456 | +------+--------+ 2 rows in set (0.00 sec)mysql>
insert into test
select 0,'123456' from test
where sign=1 and phone='123456'
union all
select distinct 1,'123456' from test
where not exists(select 1 from test where sign=1 and phone='123456');
mysql> select * from t_floodow;
Empty set (0.00 sec)mysql> insert into t_floodow
-> select count(*)=0,'123456'
-> from t_floodow
-> where col1=1 and phone='123456';
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0-- 第一次,不存在 1,123456, 所以插入 | 1 | 123456 |
mysql> select * from t_floodow;
+------+--------+
| col1 | phone |
+------+--------+
| 1 | 123456 |
+------+--------+
1 row in set (0.00 sec)mysql> insert into t_floodow
-> select count(*)=0,'123456'
-> from t_floodow
-> where col1=1 and phone='123456';
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0-- 第二次,存在 1,123456, 所以插入 | 0 | 123456 |
mysql> select * from t_floodow;
+------+--------+
| col1 | phone |
+------+--------+
| 1 | 123456 |
| 0 | 123456 |
+------+--------+
2 rows in set (0.00 sec)mysql>
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html