查询中的mysql语法错误 [英] mysql syntax error in query

问题描述

我收到一个错误查询失败:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 5 行的5,7,6,9,13 ORDER BY n.date DESC"附近使用的正确语法.有人能指出什么问题吗?谢谢

I'm getting an error Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5,7,6,9,13 ORDER BY n.date DESC' at line 5. Can anyone point out what's wrong? Thanks

$news_query = 'SELECT u.id as userId, u.username, n.id as newsId, n.action, n.date 
            FROM newsfeed as n 
            JOIN users as u on n.userId = u.id
            WHERE
            userId in '.implode(',', array_map('intval', $myfriends)).' &&   
            userId == :myId             // posts by me
            ORDER BY n.date DESC
        ';

推荐答案

比较运算符:

 userId == :myId

在 SQL 中，相等运算符是 =，而不是 ==.

In SQL the equality operator is =, not ==.

从错误消息看来，当值被插入到查询中时，上面的代码片段看起来像这样:

From the error message it appears that the above snippet, when the value is inserted into the query, looks like this:

userId == 5,7,6,9,13

即使您使用了正确的运算符，这也不是有效的比较.如果要将 userId 与值列表进行比较，则应使用 IN 运算符和元组(一组值):

That is not a valid comparison, even if you used the right operator. If you want to compare userId against a list of values then you should use the IN operator and a tuple (a set of values):

userId IN (5,7,6,9,13)

回复评论

您可以将代码更改为:

$tuple = implode(',', array_map('intval', $myfriends));
$news_query = <<< SQL
    SELECT
        u.id AS userId,
        u.username,
        n.id AS newsId,
        n.action,
        n.date 
    FROM
        newsfeed AS n 
    JOIN
        users AS u ON n.userId = u.id
    WHERE
        userId IN ($tuple) AND
        userId = :myId
    ORDER BY
        n.date DESC
SQL;

您还应该限定 WHERE 子句中的 userId 字段；即把 u. 或 n. 放在它前面，这样就没有歧义了.

You should also qualify the userId field in the WHERE clause; i.e. put u. or n. in-front of it, so that there is no ambiguity.

这篇关于查询中的mysql语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！