已经有下表test
+-----+------+------+
| id | name | type |
+-----+------+------+
| 101 | aaa | 1 |
| 102 | bbb | 2 |
| 103 | ccc | 3 |
+-----+------+------+
现在有新的记录,
104 ddd 4
101 aaa 1
入写写insert语句,可以插入
104 ddd 4
不能插入
101 aaa 1
+-----+------+------+
| id | name | type |
+-----+------+------+
| 101 | aaa | 1 |
| 102 | bbb | 2 |
| 103 | ccc | 3 |
+-----+------+------+
现在有新的记录,
104 ddd 4
101 aaa 1
入写写insert语句,可以插入
104 ddd 4
不能插入
101 aaa 1
104 ddd 4
不能插入
101 aaa 1
不存在就插入,存在就不做任何动作。
已经有数据
1\2008\3.5
2\2009\3.8
3\2010\4
有新数据
1\2008\3.45
2\2009\3.8
这个时候,我要做到
原来的
1\2008\3.5删除,变成1\2008\3.45,第二个2\2009\3.8不予插入
请问,如何实现?
+------+------+-------+
| code | date | price |
+------+------+-------+
| 1 | 2008 | 3.50 |
| 2 | 2009 | 3.80 |
| 3 | 2010 | 4.00 |
+------+------+-------+
3 rows in set (0.00 sec)mysql> insert into table_a values (1,2008,3.45) on DUPLICATE key update price=values(price);
Query OK, 2 rows affected (0.03 sec)mysql> select * from table_a;
+------+------+-------+
| code | date | price |
+------+------+-------+
| 1 | 2008 | 3.45 |
| 2 | 2009 | 3.80 |
| 3 | 2010 | 4.00 |
+------+------+-------+
3 rows in set (0.00 sec)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
use test;
create table `quote` (
`code` char(8),
`date` int(8),
`open` decimal(8,3),
`high` decimal(8,3),
`low` decimal(8,3),
`close` decimal(8,3));
INSERT INTO quote (code,date,open,high,low,close) VALUES ("sh000001","20101001","3","3.2","2.98","3.01");
INSERT INTO quote (code,date,open,high,low,close) VALUES ("sh000001","20101002","3.02","3.22","3","3.03");
INSERT INTO quote (code,date,open,high,low,close) VALUES ("sh000001","20101003","3.02","3.2","2.98","3.01");
INSERT INTO quote (code,date,open,high,low,close) VALUES ("sh000002","20101001","4","4.2","3.98","4.05");
INSERT INTO quote (code,date,open,high,low,close) VALUES ("sh000002","20101002","4.02","4.15","4","4.01");
INSERT INTO quote (code,date,open,high,low,close) VALUES ("sh000002","20101003","4.03","4.2","3.98","4.01");现在来了新的数据,三条
item1 ("sh000001","20101001","3","3.2","2.98","3.01")
item2 ("sh000001","20101003","3.04","3.25","2.98","3.03")
item3 ("sh000002","20101004","4.05","4.2","3.99","4.06")
我要求,item1,执行插入时,不予插入,因为数据库中已经有了完全一样的一条记录。
item2,执行插入时,先删除原有的数据项
("sh000001","20101003","3.02","3.2","2.98","3.01");然后,插入
("sh000001","20101003","3.04","3.25","2.98","3.03")
这个动作的意图,就是,凡是字段code\date完全一样,但是其他字段不同的记录,被系统认为是对以前记录的更正,以前的包含相同code\date的整条记录需要删除;
item3,是一条全新的记录,可以插入。
请问,如何设计主键、索引,来满足我的需求?
+----------+----------+-------+-------+-------+-------+
| code | date | open | high | low | close |
+----------+----------+-------+-------+-------+-------+
| sh000001 | 20101001 | 3.000 | 3.200 | 2.980 | 3.010 |
| sh000001 | 20101002 | 3.020 | 3.220 | 3.000 | 3.030 |
| sh000001 | 20101003 | 3.020 | 3.200 | 2.980 | 3.010 |
| sh000002 | 20101001 | 4.000 | 4.200 | 3.980 | 4.050 |
| sh000002 | 20101002 | 4.020 | 4.150 | 4.000 | 4.010 |
| sh000002 | 20101003 | 4.030 | 4.200 | 3.980 | 4.010 |
+----------+----------+-------+-------+-------+-------+
6 rows in set (0.03 sec)mysql> alter table quote add primary key (code,date);
Query OK, 6 rows affected (0.12 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql>-- item1,执行插入时,不予插入,因为数据库中已经有了完全一样的一条记录。 Query OK, 0 rows affected (0.00 sec)
mysql> insert into quote values ("sh000001","20101001","3","3.2","2.98","3.01")
-> ON DUPLICATE KEY UPDATE open=values(open),high=values(high),low=values(low),close=values(close);
Query OK, 0 rows affected (0.00 sec)-- item2,执行插入时,先删除原有的数据项 ("sh000001","20101003","3.02","3.2","2.98","3.01");然后,插入"sh000001","20101003","3.04","3.25","2.98","3.03")
mysql> insert into quote values ("sh000001","20101003","3.04","3.25","2.98","3.03")
-> ON DUPLICATE KEY UPDATE open=values(open),high=values(high),low=values(low),close=values(close);
Query OK, 2 rows affected (0.03 sec)mysql> insert into quote values ("sh000002","20101004","4.05","4.2","3.99","4.06")
-> ON DUPLICATE KEY UPDATE open=values(open),high=values(high),low=values(low),close=values(close);
Query OK, 1 row affected (0.00 sec)mysql>