您不能在一个查询中执行所描述的操作，因为LIMIT和OFFSET的参数不能是表达式或子查询。
所以你必须在运行查询之前计算位置。你说过你已经知道怎么做了。
允许使用整型参数或变量作为LIMIT的参数。

SELECT * FROM tbl_messages 
WHERE _user = '2' AND _instance = '5' 
ORDER BY _date ASC LIMIT 10 OFFSET ?

（注意我去掉了OFFSET前面的逗号）

好吧，对不起那些说这不可能同时又放弃的人。对于那些来这个问题寻找答案的人，我是这样理解的：

SELECT @a = row_num - 1 FROM (SELECT row_number() over (ORDER BY `_date` DESC) row_num, `_message_id` AS "m_id" FROM `tbl_messages` WHERE `_contact` = '2' AND `_instance` = '5' ORDER BY `_date` DESC) AS tab WHERE tab.`m_id` = 'BAE58BEA44DD7972';
PREPARE PREP FROM "SELECT * FROM `tbl_messages` WHERE `_contact` = '2' AND `_instance` = '5' ORDER BY `_date` DESC LIMIT 10 OFFSET ?";
EXECUTE PREP USING @a;

这可以用PHP写成这样：

$sql = $conn->prepare('SELECT row_num - 1 AS num FROM (SELECT row_number() over (ORDER BY _date DESC) row_num, _message_id AS "m_id" FROM tbl_messages WHERE _contact = :contact AND _instance = :instance ORDER BY _date DESC) AS tab WHERE tab.m_id = "BAE58BEA44DD7972"');
$sql->execute(array(':contact' => 2, ':instance' => 5));
$result = $sql->fetch(PDO::FETCH_ASSOC);
$a = $result['num'];

$stmt = $conn->prepare("SELECT * FROM tbl_messages WHERE _contact = :contact AND _instance = :instance ORDER BY _date DESC LIMIT 10 OFFSET :offset");
$contact = 2;
$instance = 5;
$offset = intval($a);
$stmt->bindParam(':contact', $contact, PDO::PARAM_INT);
$stmt->bindParam(':instance', $instance, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();

echo '<pre>';
var_dump($result);