数据库:Test
表:voteItem(id,title,ticketNumber),主键:id,自增长整型。title:投票项标题。ticketNumber:获得投票数。
表:ticket(id,voteItemID,ip),主键:id,自增长整型。voteItemID:外键到表voteItem的主键。ip:进行投票的IP地址。
需求:在插入数据到ticket表的时候,插入数据项关联的外键项的'ticketNumber'值能自增。
也就是出现一张投票时,投票项的获得投票数自己能发现这个事件,并进行投票数自增。
不知道描述是否清楚,请大家帮忙。
11 个解决方案
#1
贴数据吧,就是在插入ticket表的时候,同时插入voteItem表?
#2
不完全是,我希望比如
voteItem表中有数据
id,title,ticketNumber
1,"title",0
在向表ticket插入数据
voteItemID,ip
1,"127.0.0.1"
时,由于插入的数据的外键是1,此时,voteItem表的ticketNumber能自增1.
#3
也就是ticketNumber+1?
TRIGGER:
UPDATE voteItem SET ticketNumber=ticketNumber+1 WHERE ID=NEW.voteItemID
TRIGGER:
UPDATE voteItem SET ticketNumber=ticketNumber+1 WHERE ID=NEW.voteItemID
#4
创建触发器如下即可。 帮助中有例子 http://dev.mysql.com/doc/refman/5.1/zh/triggers.html
DELIMITER |
CREATE TRIGGER testref AFTER INSERT ON ticket
FOR EACH ROW BEGIN
update voteItem set ticketNumber=ticketNumber+1 where id = new.voteItemID;
END;
|
DELIMITER ;
#5
mysql> create table voteItem
-> (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> title varchar(30),
-> ticketNumber int
-> );
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> create table ticket(
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> voteItemID INT references voteItem(id) ,
-> ip varchar(30)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> insert into voteItem values (null,'aaaaa',0);
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> select * from voteItem;
+----+-------+--------------+
| id | title | ticketNumber |
+----+-------+--------------+
| 1 | aaaaa | 0 |
+----+-------+--------------+
1 row in set (0.00 sec)
mysql>
mysql> DELIMITER |
mysql>
mysql> CREATE TRIGGER testref AFTER INSERT ON ticket
-> FOR EACH ROW BEGIN
-> update voteItem set ticketNumber=ticketNumber+1 where id = new.v
oteItemID;
-> END;
-> |
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> insert into ticket values (null,1,'127.0.0.1');
Query OK, 1 row affected (0.08 sec)
mysql> select * from ticket;
+----+------------+-----------+
| id | voteItemID | ip |
+----+------------+-----------+
| 1 | 1 | 127.0.0.1 |
+----+------------+-----------+
1 row in set (0.00 sec)
mysql> select * from voteItem;
+----+-------+--------------+
| id | title | ticketNumber |
+----+-------+--------------+
| 1 | aaaaa | 1 |
+----+-------+--------------+
1 row in set (0.00 sec)
#6
虽然可以实现,但不太建议这样实现这种统计功能。
不如把表voteItem中的这个计数ticketNumber取消,直接通过查询来得到投票数。
mysql> insert into voteItem values (null,'bbbbb',0);
Query OK, 1 row affected (0.06 sec)
mysql> select v.id,v.title,count(t.id)
-> from voteItem v left join ticket t on v.id=t.voteItemID
-> group by v.id,v.title;
+----+-------+-------------+
| id | title | count(t.id) |
+----+-------+-------------+
| 1 | aaaaa | 1 |
| 2 | bbbbb | 0 |
+----+-------+-------------+
2 rows in set (0.00 sec)
#7
不建议在TRIGGER中做统计,用连接即可得到,TRIGGER少用,会影响性能
#8
谢谢两位回复,正在尝试中,成功后马上散分。
#9
这位朋友如果换成连接的话,应该怎么做统计呢?
#10
这位朋友如果换成连接的话,应该怎么做统计呢?
6楼不是已经答复你了吗?
#11
直接用 count 查询会很慢
#1
贴数据吧,就是在插入ticket表的时候,同时插入voteItem表?
#2
不完全是,我希望比如
voteItem表中有数据
id,title,ticketNumber
1,"title",0
在向表ticket插入数据
voteItemID,ip
1,"127.0.0.1"
时,由于插入的数据的外键是1,此时,voteItem表的ticketNumber能自增1.
#3
也就是ticketNumber+1?
TRIGGER:
UPDATE voteItem SET ticketNumber=ticketNumber+1 WHERE ID=NEW.voteItemID
TRIGGER:
UPDATE voteItem SET ticketNumber=ticketNumber+1 WHERE ID=NEW.voteItemID
#4
创建触发器如下即可。 帮助中有例子 http://dev.mysql.com/doc/refman/5.1/zh/triggers.html
DELIMITER |
CREATE TRIGGER testref AFTER INSERT ON ticket
FOR EACH ROW BEGIN
update voteItem set ticketNumber=ticketNumber+1 where id = new.voteItemID;
END;
|
DELIMITER ;
#5
mysql> create table voteItem
-> (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> title varchar(30),
-> ticketNumber int
-> );
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> create table ticket(
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> voteItemID INT references voteItem(id) ,
-> ip varchar(30)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> insert into voteItem values (null,'aaaaa',0);
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> select * from voteItem;
+----+-------+--------------+
| id | title | ticketNumber |
+----+-------+--------------+
| 1 | aaaaa | 0 |
+----+-------+--------------+
1 row in set (0.00 sec)
mysql>
mysql> DELIMITER |
mysql>
mysql> CREATE TRIGGER testref AFTER INSERT ON ticket
-> FOR EACH ROW BEGIN
-> update voteItem set ticketNumber=ticketNumber+1 where id = new.v
oteItemID;
-> END;
-> |
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> insert into ticket values (null,1,'127.0.0.1');
Query OK, 1 row affected (0.08 sec)
mysql> select * from ticket;
+----+------------+-----------+
| id | voteItemID | ip |
+----+------------+-----------+
| 1 | 1 | 127.0.0.1 |
+----+------------+-----------+
1 row in set (0.00 sec)
mysql> select * from voteItem;
+----+-------+--------------+
| id | title | ticketNumber |
+----+-------+--------------+
| 1 | aaaaa | 1 |
+----+-------+--------------+
1 row in set (0.00 sec)
#6
虽然可以实现,但不太建议这样实现这种统计功能。
不如把表voteItem中的这个计数ticketNumber取消,直接通过查询来得到投票数。
mysql> insert into voteItem values (null,'bbbbb',0);
Query OK, 1 row affected (0.06 sec)
mysql> select v.id,v.title,count(t.id)
-> from voteItem v left join ticket t on v.id=t.voteItemID
-> group by v.id,v.title;
+----+-------+-------------+
| id | title | count(t.id) |
+----+-------+-------------+
| 1 | aaaaa | 1 |
| 2 | bbbbb | 0 |
+----+-------+-------------+
2 rows in set (0.00 sec)
#7
不建议在TRIGGER中做统计,用连接即可得到,TRIGGER少用,会影响性能
#8
谢谢两位回复,正在尝试中,成功后马上散分。
#9
这位朋友如果换成连接的话,应该怎么做统计呢?
#10
这位朋友如果换成连接的话,应该怎么做统计呢?
6楼不是已经答复你了吗?
#11
直接用 count 查询会很慢