主页 / 问题 / 978045
Accepted
Victor Gorban
Asked:2020-05-05 02:50:22 +0800 CST2020-05-05 02:50:22 +0800 CST

如果通过 user_variables 设置参数，mysql select 不起作用

  • 772

我将简要介绍：

作品

SELECT p.id,
    p.name,
    cat.name `category`,
    prod.name `producer`,
    p.images,
    p.price,
    p.flag_avaliable,
    p.amount,
    p.description,
    p.options
FROM product p
    INNER JOIN product_category cat ON cat.id = p.category_id
    INNER JOIN product_producer prod ON prod.id = p.producer_id
ORDER BY @asc_or_desc
    limit 5 offset 6;

不工作

set @asc_or_desc = 'id desc ';
set @limit_number = 5;
set @offset_number = 6;
SELECT p.id,
   p.name,
   cat.name  `category`,
   prod.name `producer`,
   p.images,
   p.price,
   p.flag_avaliable,
   p.amount,
   p.description,
   p.options
FROM product p
         INNER JOIN product_category cat ON cat.id = p.category_id
         INNER JOIN product_producer prod ON prod.id = p.producer_id
ORDER BY @asc_or_desc
limit @limit_number offset @offset_number;

我希望“不起作用”的版本能够工作。我该怎么做？

我收到此错误：'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 '@limit_number offset @offset_number; END''。

JetBrains DataGrip，MySQL 8.0.15。

**更新：** Mysql 不响应@asc_or_desc. 至少排序为asc.

mysql

1 个回答

  1. Best Answer

    准备好的语句解决了这个问题。编码：

    set @statement =
concat('SELECT p.id,
   p.name,
   cat.name  `category`,
   prod.name `producer`,
   p.images,
   p.price,
   p.flag_avaliable,
   p.amount,
   p.description,
   p.options
FROM product p
         INNER JOIN product_category cat ON cat.id = ?
         INNER JOIN product_producer prod ON prod.id = p.producer_id
ORDER BY id ',@asc_or_desc,'
limit ? offset ?');
prepare prepared from @statement;
EXECUTE prepared USING @category_id, @limit_number, @offset_number;
DEALLOCATE PREPARE prepared;
    • 1

相关问题