你好,我有一个表,有两个唯一的键profile_id和日期。我不知道问题出在哪里,但是我的查询不工作。
表:CREATE TABLE `profile_views`
(n `id` int(11) NOT NULL AUTO_INCREMENT,
n `profile_id` varchar(45) DEFAULT NULL,
n `counter` varchar(45) DEFAULT NULL,
n `date` date DEFAULT NULL,
n PRIMARY KEY (`id`),
n UNIQUE KEY `date_UNIQUE` (`date`),
n UNIQUE KEY `profile_id_UNIQUE` (`profile_id`)n
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=latin1'
Data Right Now:
# id , profile_id, counter, date
113, 2 , 36 , 2015-08-27
我发出这个命令:
INSERT INTO profile_views (profile_id, counter, date)
VALUES (2, 1, '2015-08-28')
ON DUPLICATE KEY UPDATE counter = counter+1;
和
INSERT INTO profile_views (profile_id, counter, date)
VALUES (2, 1, '2015-08-27')
ON DUPLICATE KEY UPDATE counter = counter+1;
在这个查询中,我只是更改了日期,所以它应该插入新行。
My Desired Results:
如果我改变日期,它仍然改变相同的配置文件id计数器。我想为每个配置文件id存储每天的配置文件视图。因此,如果日期和配置文件id相同,则增加计数器,否则插入新行。
帮忙吗?谢谢。
Schema:
CREATE TABLE `profile_views`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`profile_id` varchar(45) DEFAULT NULL,
`counter` varchar(45) DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `date_UNIQUE` (`date`),
UNIQUE KEY `profile_id_UNIQUE` (`profile_id`)
) ENGINE=InnoDB auto_increment=150;
insert profile_views (id,profile_id,counter,date) values (113,2,36,'2015-08-27');
……
select * from profile_views;
+-----+------------+---------+------------+
| id | profile_id | counter | date |
+-----+------------+---------+------------+
| 113 | 2 | 36 | 2015-08-27 |
+-----+------------+---------+------------+
INSERT INTO profile_views (profile_id, counter, date)
VALUES (2, 1, '2015-08-28')
ON DUPLICATE KEY UPDATE counter = counter+1;
-- 2 row(s) affected
select * from profile_views;
+-----+------------+---------+------------+
| id | profile_id | counter | date |
+-----+------------+---------+------------+
| 113 | 2 | 37 | 2015-08-27 |
+-----+------------+---------+------------+
INSERT INTO profile_views (profile_id, counter, date)
VALUES (2, 1, '2015-08-27')
ON DUPLICATE KEY UPDATE counter = counter+1;
-- 2 row(s) affected
select * from profile_views;
+-----+------------+---------+------------+
| id | profile_id | counter | date |
+-----+------------+---------+------------+
| 113 | 2 | 38 | 2015-08-27 |
+-----+------------+---------+------------+
我觉得不错。对重复更新的每次插入都有一个唯一的键冲突,从而允许更新发生。冲突是什么?profile_id上的唯一键可以
我错过了什么?
如果你一步一步地列出,人们可以更好地想象:>
编辑:(OP改变主意)
CREATE TABLE `profile_views`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`profile_id` varchar(45) DEFAULT NULL,
`counter` varchar(45) DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `combo_thingie1` (profile_id,`date`) -- unique composite
) ENGINE=InnoDB auto_increment=150;
insert profile_views (id,profile_id,counter,date) values (113,2,36,'2015-08-27');
... ...
select * from profile_views;
+-----+------------+---------+------------+
| id | profile_id | counter | date |
+-----+------------+---------+------------+
| 113 | 2 | 36 | 2015-08-27 |
+-----+------------+---------+------------+
INSERT INTO profile_views (profile_id, counter, date)
VALUES (2, 1, '2015-08-28')
ON DUPLICATE KEY UPDATE counter = counter+1;
select * from profile_views;
+-----+------------+---------+------------+
| id | profile_id | counter | date |
+-----+------------+---------+------------+
| 113 | 2 | 36 | 2015-08-27 |
| 150 | 2 | 1 | 2015-08-28 |
+-----+------------+---------+------------+
INSERT INTO profile_views (profile_id, counter, date)
VALUES (2, 1, '2015-08-27')
ON DUPLICATE KEY UPDATE counter = counter+1;
select * from profile_views;
+-----+------------+---------+------------+
| id | profile_id | counter | date |
+-----+------------+---------+------------+
| 113 | 2 | 37 | 2015-08-27 |
| 150 | 2 | 1 | 2015-08-28 |
+-----+------------+---------+------------+
我想出了这个疯狂的结构——它为新的日期插入新的记录,然后对连续的插入语句进行更新——从而增加计数器。
CREATE TABLE `profile_views` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`profile_id` VARCHAR(45) NOT NULL,
`counter` VARCHAR(45) NOT NULL,
`date` DATE NOT NULL,
PRIMARY KEY (`id`, `profile_id`, `date`),
UNIQUE INDEX `profile_id_date` (`profile_id`, `date`),
UNIQUE INDEX `id_profile_id_date` (`id`, `profile_id`, `date`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=267;