2015-04-20 09:06
浏览 111


I have two tables - a post table Posts and a post collection table Collections. When a user creates a post, I will insert it to the database and update the corresponding collection (for sorting-based-on-update-time purpose). To avoid duplicate posts (by either user operation or network churn), I will check if a same post (based on content and author) exists. However, I still get duplicate posts (with exactly same create time and no user duplicate operations) occasionally. What could be the cause - CodeIgniter, transaction, or weird network status? Since I check existing same posts explicitly, I guess the duplication is caused by the code between trans_start() and trans_complete().

    # check if a same post exists already within 1 hour
    $sql="SELECT id FROM Posts WHERE content=? AND author_id=(SELECT id FROM Users WHERE username=? LIMIT 1) AND create_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) LIMIT 1";
    $query=$this->db->query($sql, array($content, $username));
        return 0;
    # start post        
    $sql="INSERT INTO Posts (content, author_id) VALUES (?, (SELECT id FROM Users WHERE username=? LIMIT 1))";
    $this->db->query($sql, array($content, $username));
        # update post collection update_time
        $sql="UPDATE Collections SET update_time=now() WHERE id=$collection_id";
    return $ret;

图片转代码服务由CSDN问答提供 功能建议

我有两个表 - 一个帖子表帖子和一个帖子集合表 集合。 当用户创建帖子时,我会将其插入数据库并更新相应的集合(用于基于排序的更新时间目的)。 为避免重复发帖(通过用户操作或网络流失),我将检查是否存在相同的帖子(基于内容和作者)。 但是,我偶尔会收到重复的帖子(创建时间完全相同,没有用户重复操作)。 可能是什么原因 - CodeIgniter ,交易或奇怪的网络状态? 由于我明确检查了现有的相同帖子,我想重复是由 trans_start() trans_complete()之间的代码引起的。

 $ sql =“SELECT id FROM Posts WHERE content =?AND author_id =(SELECT id FROM Users WHERE username =?LIMIT 1)AND create_time> = DATE_SUB  (NOW(),INTERVAL 1 HOUR)LIMIT 1“; 
 $ query = $ this-> db-> query($ sql,array($ content,$ username)); 
 if($ query-&gt  ; num_rows()> = 1){
 #start post 
 $ this-> db-> trans_start(); 
 $ sql =“INSERT INTO帖子(内容,  author_id)VALUES(?,(SELECT id FROM Users WHERE username =?LIMIT 1))“; 
 $ this-> db-> query($ sql,array($ content,$ username)); 
 if  ($ this-> db-> affected_rows()> 0){
 #upup post collection update_time 
 $ insert_id = $ this-> db-> insert_id(); 
 $ sql =“UPDATE 集合SET update_time = now()WHERE id = $ collect  ion_id“; 
 $ query = $ this-> db-> query($ sql); 
 if($ this-> db-> affected_rows()> 0){
 $ ret = $  insert_id; 
} else {
 $ ret = 0; 
} else {
 $ ret = 0; 
 $ this-> db-> trans_complete(); 
 return  $ ret; 
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanrang3357 2015-11-06 03:13

    A temporary workaround is to add a unique index on the (content, author_id, create_time) columns, enabling a check on the mysql level. But still, I don't know why the duplication appears...

    点赞 打赏 评论

相关推荐 更多相似问题