使用INNER JOIN关联gifts与users_gifts表查询用户礼物图片问题求助

现有实现的问题及修复方案 #

1. 表结构缺陷导致查询效率低/异常

两个表均未设置主键、关联索引，数据量稍大就会出现查询超时、无结果的问题，且MyISAM引擎可靠性差，不支持事务和外键约束，容易出现数据不一致。
修复表结构的SQL如下：

-- 修正gifts表
ALTER TABLE `gifts` 
  ADD PRIMARY KEY (`gift_id`),
  MODIFY `gift_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ENGINE = InnoDB;

-- 修正users_gifts表
ALTER TABLE `users_gifts` 
  ADD PRIMARY KEY (`id`),
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD INDEX `idx_to_user` (`to_user_id`),
  ADD INDEX `idx_gift_id` (`gift_id`),
  ENGINE = InnoDB;

2. JOIN逻辑缺陷

当前用INNER JOIN关联两张表，如果users_gifts里的gift_id对应的礼物记录已被删除，这条用户收礼记录会被直接过滤，导致查询结果少数据。建议改成LEFT JOIN，同时给空图片加默认值避免前端渲染异常。

3. 参数格式化不严谨

用%s格式化整数类型的用户ID，即使做了安全转义也存在类型转换隐患，建议直接用%d作为整数占位符。

4. 入参校验缺失

没有对传入的$user_id做合法性校验，传入异常值时会返回不符合预期的结果。

修复后的完整代码 #

public function get_my_gifts($user_id) {
    global $db;
    $my_gifts = [];
    // 入参合法性校验
    if (!is_numeric($user_id) || intval($user_id) <= 0) {
        return $my_gifts;
    }
    // 调整查询逻辑，返回更多常用字段方便前端渲染
    $sql = sprintf(
        "SELECT users_gifts.from_user_id, gifts.gift_id, gifts.image, gifts.gift_price 
         FROM users_gifts 
         LEFT JOIN gifts ON users_gifts.gift_id = gifts.gift_id 
         WHERE users_gifts.to_user_id = %d",
        secure($user_id, 'int')
    );
    $get_my_gifts = $db->query($sql) or _error("SQL_ERROR_THROWEN");

    if ($get_my_gifts->num_rows > 0) {
        while ($my_gift = $get_my_gifts->fetch_assoc()) {
            // 空图片设置默认值，可根据实际业务替换路径
            $my_gift['image'] = $my_gift['image'] ?? '/static/default_gift.png';
            $my_gifts[] = $my_gift;
        }
    }
    return $my_gifts;
}

内容的提问来源于stack exchange，提问作者Wpgn