Golang MysqlDB.Query方法代码示例

// 检查 mUserId 与 sUserId 的关系，
// return:
//      @isFollower: sUserId是否关注mUserId
//      @isFollowed: mUserId是否关注sUserId
//      @isFriend: 是否互相关注
func User_CheckRelationship(mUserId, sUserId int64) (isFollower, isFollowed, isFriend bool) {
	var db *goku.MysqlDB = GetDB()
	defer db.Close()

	rows, err := db.Query("select * from `user_follow` where `user_id`=? and `follow_id`=? limit 1",
		mUserId, sUserId)
	if err != nil {
		goku.Logger().Errorln(err.Error())
		return
	}
	defer rows.Close()
	if rows.Next() {
		isFollowed = true
	}

	rows1, err1 := db.Query("select * from `user_follow` where `user_id`=? and `follow_id`=? limit 1",
		sUserId, mUserId)
	if err1 != nil {
		goku.Logger().Errorln(err1.Error())
		return
	}
	defer rows1.Close()
	if rows1.Next() {
		isFollower = true
	}

	if isFollowed && isFollower {
		isFriend = true
	}

	return
}

func thirdPartyUser_SearchOneBy(criteria string, values ...interface{}) (u *ThirdPartyUser) {
	var db *goku.MysqlDB = GetDB()
	defer db.Close()

	sql := "SELECT `user_id`, `third_party`, `third_party_user_id`, `third_party_email`, `access_token`, `refresh_token`, `token_expire_time`, `create_time`, `last_active_time`, `avatar_url`, `link` FROM `third_party_user` WHERE " + criteria + " limit 1"
	thirdPartyUserRow, err := db.Query(sql, values...)
	if err != nil {
		return
	}
	if thirdPartyUserRow == nil {
		return
	}

	if thirdPartyUserRow.Next() {
		u = &ThirdPartyUser{}
		err = thirdPartyUserRow.Scan(
			&u.UserId, &u.ThirdParty, &u.ThirdPartyUserId, &u.ThirdPartyEmail,
			&u.AccessToken, &u.RefreshToken, &u.TokenExpireTime, &u.CreateTime, &u.LastActiveTime, &u.AvatarUrl, &u.Link)
	}

	if err != nil {
		u = nil
	}

	return
}

/**
 * 删除tui_link_for_handle已经处理的数据
 */
func delete_tui_link_for_handle(handleTime time.Time, db *goku.MysqlDB) error {

	sql := "DELETE FROM tui_link_for_handle WHERE `insert_time`<=? "
	_, err := db.Query(sql, handleTime)

	return err
}

func Del_link_for_topic_all(db *goku.MysqlDB) error {

	err := del_link_for_topic_later_top("tui_link_for_topic_top", "reddit_score DESC,link_id DESC", db)
	if err == nil {
		err = del_link_for_topic_later_top("tui_link_for_topic_later", "link_id DESC", db)
	}
	if err == nil {
		_, err = db.Query(`DELETE FROM tui_link_for_topic_hot WHERE (time_type=2 AND create_time<?) OR (time_type=3 AND create_time<?) OR 
							(time_type=4 AND create_time<?) OR (time_type=5 AND create_time<?) OR 
			(time_type=6 AND create_time<?)`, utils.ThisHour(), utils.ThisDate(), utils.ThisWeek(), utils.ThisMonth(), utils.ThisYear())
		if err == nil {
			err = del_link_for_topic_hot_vote("tui_link_for_topic_hot", "dispute_score DESC,link_id DESC", db)
		}
	}
	if err == nil {
		_, err = db.Query(`DELETE FROM tui_link_for_topic_vote WHERE (time_type=2 AND create_time<?) OR (time_type=3 AND create_time<?) OR 
							(time_type=4 AND create_time<?) OR (time_type=5 AND create_time<?) OR 
			(time_type=6 AND create_time<?)`, utils.ThisHour(), utils.ThisDate(), utils.ThisWeek(), utils.ThisMonth(), utils.ThisYear())
		if err == nil {
			err = del_link_for_topic_hot_vote("tui_link_for_topic_vote", "vote DESC,link_id DESC", db)
		}
	}

	return err
}

/**
 * 链接推送给话题(热议) 2:这个小时；3:今天；4:这周；5:这个月；6:今年
 */
func link_for_topic_hop_time(timeType int, handleTime time.Time, db *goku.MysqlDB) error {

	var t time.Time
	switch {
	case timeType == 2:
		t = utils.ThisHour()
	case timeType == 3:
		t = utils.ThisDate()
	case timeType == 4:
		t = utils.ThisWeek()
	case timeType == 5:
		t = utils.ThisMonth()
	case timeType == 6:
		t = utils.ThisYear()
	}

	sql := `INSERT ignore INTO tui_link_for_topic_hot(topic_id,link_id,create_time,dispute_score,time_type) 
		( 
		SELECT TL.topic_id,H.link_id,H.create_time,L.dispute_score,? AS time_type FROM tui_link_for_handle H 
		INNER JOIN topic_link TL ON H.insert_time<=? AND H.create_time>=? AND H.link_id=TL.link_id
		INNER JOIN link L ON L.id=H.link_id
		);`
	_, err := db.Query(sql, timeType, handleTime, t)

	return err
}

//更新邀请码
func UpdateIsRegister(invite *RegisterInvite) {
	if !golink.Invite_Enabled {
		return
	}
	var db *goku.MysqlDB = GetDB()
	defer db.Close()

	db.Query("UPDATE `register_invite` SET `is_register`=1 WHERE `Guid`=?", invite.Guid)

}

//根据用户关注的话题给它推荐相关的用户
func User_RecommendFromTopic(userId int64) ([]User, error) {
	iRecommendCount := 10
	var db *goku.MysqlDB = GetDB()
	db.Debug = true
	defer db.Close()

	sql := "SELECT `topic_id` FROM `topic_follow` WHERE `user_id`=? ORDER BY `create_time` DESC limit ?"
	topicRows, topicErr := db.Query(sql, userId, iRecommendCount)
	if topicErr != nil {
		return nil, topicErr
	}
	topicIds := make([]int64, 0)
	var topicId int64
	for topicRows.Next() {
		topicErr = topicRows.Scan(&topicId)
		if topicErr == nil {
			topicIds = append(topicIds, topicId)
		}
	}

	hashUsers := map[int64]int64{}
	users := make([]User, 0)
	tLen := len(topicIds)
	var uCount int
	uCount = iRecommendCount / tLen
	strUserIds := fmt.Sprintf("%d", userId)
	hashUsers[userId] = userId
	if tLen > 0 {
		sql = "SELECT u.`id`,u.`name`,u.`email`,u.`description`,u.`user_pic`,u.`friend_count`,u.`topic_count`,u.`ftopic_count`,u.`status`," +
			"u.`follower_count`,u.`link_count`,u.`create_time` FROM `tui_link_for_topic_top` tl INNER JOIN `link` l ON " +
			"tl.`topic_id`=? AND tl.`link_id`=l.`id` AND l.`user_id` NOT IN(?) " +
			"AND NOT EXISTS(SELECT 1 FROM `user_follow` uf WHERE uf.`user_id`=? AND uf.`follow_id`=l.`user_id`) " +
			"INNER JOIN `user` u ON u.`id`=l.`user_id` " +
			"ORDER BY tl.`reddit_score` DESC limit ?"

		for _, tId := range topicIds {
			userRows, userErr := db.Query(sql, tId, strUserIds, userId, uCount)
			if userErr == nil {
				for userRows.Next() {
					user := User{}
					userErr = userRows.Scan(&user.Id, &user.Name, &user.Email, &user.Description, &user.UserPic, &user.FriendCount, &user.TopicCount, &user.FtopicCount, &user.Status, &user.FollowerCount, &user.LinkCount, &user.CreateTime)
					if userErr == nil && hashUsers[userId] <= 0 {
						users = append(users, user)
						strUserIds += fmt.Sprintf(",%d", user.Id)
						hashUsers[user.Id] = user.Id
					}

				}
			}
		}
	}
	//fmt.Print(users)
	return users, nil
}

/**
 * 链接推送给话题(最新)
 */
func Link_for_topic_later(handleTime time.Time, db *goku.MysqlDB) error {

	sql := `INSERT ignore INTO tui_link_for_topic_later(topic_id,link_id,create_time) 
		( 
		SELECT TL.topic_id,H.link_id,H.create_time FROM tui_link_for_handle H 
		INNER JOIN topic_link TL ON H.data_type=1 AND H.insert_time<=? AND H.link_id=TL.link_id 
		);`
	_, err := db.Query(sql, handleTime)

	return err
}

/**
 * 链接推送给网站首页(热门)
 */
func Link_for_home_top(handleTime time.Time, db *goku.MysqlDB) error {

	sql := `INSERT ignore INTO tui_link_for_home(link_id,create_time,data_type,score) 
		( 
		SELECT H.link_id,H.create_time,2,L.reddit_score FROM tui_link_for_handle H 
		INNER JOIN link L ON H.insert_time<=? AND L.id=H.link_id 
		); `

	_, err := db.Query(sql, handleTime)

	return err
}

func Comment_DelById(id int64) error {
	var db *goku.MysqlDB = GetDB()
	defer db.Close()

	_, err := db.Query("UPDATE `comment` SET status=2 WHERE id=?", id)
	if err != nil {
		goku.Logger().Errorln(err.Error())
		return err
	}

	return nil
}

//更新发送的邀请email状态
func UpdateInviteEmailStatus(emails []*EmailInvite) {

	var db *goku.MysqlDB = GetDB()
	defer db.Close()

	for _, email := range emails {
		if email.SendSuccess == true {
			db.Query("UPDATE register_invite SET is_send=1 WHERE guid=?", email.Guid)
		} else {
			db.Query("UPDATE register_invite SET fail_count=fail_count+1 WHERE guid=?", email.Guid)
		}
	}
}

/** 删除`tui_link_for_home`
 * 热门, whereDataType:data_type=2    orderName:score DESC,link_id DESC
 * 热议, whereDataType:data_type IN(3,10,11,12,13,14)    orderName:score desc,link_id DESC
 * 投票, whereDataType:data_type IN(4,5,6,7,8,9)    orderName:score DESC,link_id DESC
 */
func del_link_for_home(whereDataType string, orderName string, db *goku.MysqlDB) error {

	sql := fmt.Sprintf(`SELECT data_type, tcount - %d AS del_count FROM 
		(SELECT link_id,data_type,COUNT(1) AS tcount FROM tui_link_for_home WHERE %s GROUP BY data_type) T
		WHERE T.tcount>%d;`, LinkMaxCount, whereDataType, LinkMaxCount)

	delSqlCreate := `INSERT ignore INTO tui_link_temporary_delete(id)
		( 
		SELECT link_id FROM tui_link_for_home WHERE data_type=%d ORDER BY ` + orderName + ` LIMIT %d,%d 
		); `
	delSqlDelete := `DELETE T FROM tui_link_temporary_delete D INNER JOIN tui_link_for_home T ON T.data_type=%d
		AND T.link_id=D.id; `

	var delCount int64
	var dataType int
	rows, err := db.Query(sql)
	if err == nil {
		for rows.Next() {
			rows.Scan(&dataType, &delCount)
			db.Query("DELETE FROM tui_link_temporary_delete;")
			db.Query(fmt.Sprintf(delSqlCreate, dataType, LinkMaxCount, delCount))
			db.Query(fmt.Sprintf(delSqlDelete, dataType))
		}
	}

	return err
}

// 检查用户是否已经关注话题，
// @isFollowed: 是否已经关注话题
func Topic_CheckFollowByDb(db *goku.MysqlDB, userId, topicId int64) (isFollowed bool) {

	rows, err := db.Query("select * from `topic_follow` where `user_id`=? and `topic_id`=? limit 1",
		userId, topicId)
	if err != nil {
		goku.Logger().Errorln(err.Error())
		return
	}
	defer rows.Close()
	if rows.Next() {
		isFollowed = true
	}

	return
}

/**
 * 链接推送给网站首页(更新现有数据 )
 */
func Link_for_home_update(handleTime time.Time, db *goku.MysqlDB) error {

	sql := `UPDATE tui_link_for_handle H 
		INNER JOIN tui_link_for_home T ON H.insert_time<=? AND H.data_type=2 AND T.link_id=H.link_id 
		INNER JOIN link L ON L.id=H.link_id 
		SET T.score=CASE WHEN T.data_type=2 THEN L.reddit_score -- 热门 
		WHEN T.data_type=3 THEN L.dispute_score -- 热议
		WHEN T.data_type BETWEEN 10 AND 14 THEN L.dispute_score -- 热议
		ELSE L.vote_up-L.vote_down -- 投票 
		END;`

	_, err := db.Query(sql, handleTime)

	return err
}

// 检查email地址是否存在。
// 任何出错都认为email地址存在，防止注册
func User_IsEmailExist(email string) bool {
	var db *goku.MysqlDB = GetDB()
	defer db.Close()

	rows, err := db.Query("select id from `user` where `email_lower`=? limit 1", strings.ToLower(email))
	if err != nil {
		goku.Logger().Errorln(err.Error())
		// 出错直接认为email存在
		return true
	}
	defer rows.Close()
	if rows.Next() {
		return true
	}
	return false
}