Mysql查询在qt中不起作用

Question

我写了一个相当复杂的mysql查询并在mysql工作台中测试它，它运行得很好。但是当我在我的qt程序中使用这个查询时，它不起作用。

为了获得更好的分辨率，我有 3 个表(产品、类别、products_categories)，我想获取 3 个parentId = 5 的类别和每个类别的 2 个产品。这是我的功能:

void DbQueries::getFullCategories(int parent_id, int offset, int limit, int plimit)
{
    ...

    QSqlQuery q;
    q.prepare("set @num := 0, @cid := '';"
              "SELECT e.ProductId, e.title, e.price, e.rate, e.rateAvg, e.imgUrl, d.cid, d.ctitle"
              "FROM products e,"
                  "(SELECT ProductId, @num := if(@cid = categoryId, @num + 1, 1) as qty, @cid := categoryId as cid, title as ctitle"
                  "FROM ("
                      "SELECT b.ProductId, b.categoryId, a.title"
                      "FROM products_categories b, (SELECT catId, title FROM categories WHERE parentId = :parent_id LIMIT :limit OFFSET :offset) as a"
                      "WHERE b.categoryId = a.catId"
                      "ORDER BY b.categoryId"
                      ") as c"
                  ") as d"
              "WHERE e.ProductId = d.ProductId AND d.qty <= :plimit"
              "ORDER BY d.cid;");
    q.bindValue(":parent_id", parent_id);
    q.bindValue(":limit", limit);
    q.bindValue(":offset", offset);
    q.bindValue(":plimit", plimit);
    if(!q.exec())
        qDebug() << q.lastError().text();

    const QSqlRecord &r = q.record();
    qDebug() << r.count();

    ...
}

通过此查询，在 exec() 之后，我没有收到任何错误，但在我的 QSqlRecord 中没有获取任何记录。我尝试将 \ 放在 := 之前，但得到了 未知转义序列 '\:'。

当我将查询减少为:

QSqlQuery q;
q.prepare("SELECT b.ProductId, b.categoryId, a.title"
          "FROM products_categories b, (SELECT catId, title FROM categories WHERE parentId = :parent_id LIMIT :limit OFFSET :offset) as a"
          "WHERE b.categoryId = a.catId"
          "ORDER BY b.categoryId");

我收到 mysql 错误，如下所示:“您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，了解在 'b 附近使用的正确语法，(SELECT catId, title FROMcategories WHERE parentId = 5 LIMIT 3 OFFSE'位于第1行QMYSQL:无法执行查询”

Answer 1

此查询缺少一些重要的空格:

      "SELECT b.ProductId, b.categoryId, a.title"
      "FROM products_categories b, (SELECT catId, title FROM categories WHERE parentId = :parent_id LIMIT :limit OFFSET :offset) as a"
      "WHERE b.categoryId = a.catId"
      "ORDER BY b.categoryId"

修复非常简单:

      "SELECT b.ProductId, b.categoryId, a.title"
      " FROM products_categories b, (SELECT catId, title FROM categories WHERE parentId = :parent_id LIMIT :limit OFFSET :offset) as a"
      " WHERE b.categoryId = a.catId"
      " ORDER BY b.categoryId"
      -^-