MySQL更新查询数据不一致：深入解析MD5与类型绑定的陷阱

$sql = "UPDATE `users` SET $column = :value WHERE md5(userId) = :id OR userId =:id LIMIT 1";
$stmt = $db->prepare($sql);
$stmt->bindParam(":id", $id, PDO::PARAM_INT); // 关键问题所在
$stmt->bindParam(":value", $value);
if (!$stmt->execute()) {
  print_r($stmt->errorInfo());
}

// 假设 $id = $_POST['id'];
$is_int_id = filter_var($id, FILTER_VALIDATE_INT) !== false;
$is_md5_hash = (bool) preg_match('/^[a-f0-9]{32}$/i', $id);

include_once("../connections/db.inc.php");

if (isset($_POST['id'])) {
    $value = $_POST['value'];
    $column = $_POST['column'];
    $id = $_POST['id'];

    $sql = "UPDATE `users` SET `$column` = :value WHERE "; // 注意列名也应安全处理，这里假设$column是安全的
    $param_name = ":id_param"; // 定义一个通用的参数占位符名称

    try {
        $stmt = $db->prepare($sql);
        $stmt->bindParam(":value", $value);

        // 根据ID类型动态构建WHERE子句和绑定参数
        if (filter_var($id, FILTER_VALIDATE_INT) !== false) {
            // ID是一个整数，匹配userId
            $sql_where = "userId = " . $param_name . " LIMIT 1";
            $param_type = PDO::PARAM_INT;
            $stmt = $db->prepare($sql . $sql_where); // 重新准备SQL语句
            $stmt->bindParam(":value", $value);
            $stmt->bindParam($param_name, $id, $param_type);
        } elseif (preg_match('/^[a-f0-9]{32}$/i', $id)) {
            // ID是一个MD5哈希，匹配md5(userId)
            $sql_where = "md5(userId) = " . $param_name . " LIMIT 1";
            $param_type = PDO::PARAM_STR;
            $stmt = $db->prepare($sql . $sql_where); // 重新准备SQL语句
            $stmt->bindParam(":value", $value);
            $stmt->bindParam($param_name, $id, $param_type);
        } else {
            // ID格式无效，可以抛出异常或记录错误
            echo "无效的ID格式，更新失败。";
            exit;
        }

        if (!$stmt->execute()) {
            print_r($stmt->errorInfo());
        } else {
            echo "y";
        }
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

// ... (之前的初始化代码)

if (isset($_POST['id'])) {
    $value = $_POST['value'];
    $column = $_POST['column'];
    $id = $_POST['id'];

    // 假设 $column 已经过安全验证
    $sql = "UPDATE `users` SET `$column` = :value WHERE md5(userId) = :id_md5 OR userId = :id_int LIMIT 1";

    try {
        $stmt = $db->prepare($sql);
        $stmt->bindParam(":value", $value);
        $stmt->bindParam(":id_md5", $id, PDO::PARAM_STR); // 绑定为字符串类型用于MD5比较
        $stmt->bindParam(":id_int", $id, PDO::PARAM_INT); // 绑定为整数类型用于userId比较

        if (!$stmt->execute()) {
            print_r($stmt->errorInfo());
        } else {
            echo "y";
        }
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}