我需要如何更正这个SQL语句？

Question

我有以下表格：

1. products - 包含库存中的产品。
2. suppliers - 包含供应商详细信息。
3. product_category - 包含产品类别名称。
4. product_suppliers - 包含产品和供应商之间的关系。

在这里最重要的可能是 product_suppliers，因此列如下：

id //(auto-increment)  
prod_id //(id of product)  
sup_id //(id of supplier)  
preferred //(1 if it's the preferred supplier for that product - 0 if not)  
cost_per_outer //(this suppliers price for this item)  
qty_in_outer //(the number in a box)  

product表不仅存储产品详细信息，还有stock_level和reorder_level字段。当前者小于后者时，该产品应包含在重新订购列表中。

我正在尝试构建列表查询，但它并没有完全正确地工作。

我有4个符合重新订购条件的项目，涉及两个供应商。每个供应商应有两个项目，但目前只检索到第一个供应商的结果。

我正在使用MySQL和PHP。

我运行了以下查询，以便更好地了解数据库中的实际数据：

mysql> SELECT
    ->         p.prod_id,
    ->         AES_DECRYPT(p.alt_id, 'MW4KQLg1Irfo3Xz7Nxht')                         AS sku,
    ->         AES_DECRYPT(p.prod_name, 'MW4KQLg1Irfo3Xz7Nxht')                      AS prod_name,
    ->         AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht')                    AS prod_type,
    ->         AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht')                    AS stock_level,
    ->         AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht')            AS reorder_level,
    ->         AES_DECRYPT(c.category_name, 'MW4KQLg1Irfo3Xz7Nxht')            AS category_name,
    ->         ps.sup_id,
    ->         ps.preferred
    -> FROM
    ->         products p
    -> INNER JOIN
    ->         product_category c
    ->   ON
    ->         p.category_id = c.category_id
    -> INNER JOIN
    ->         product_supplier ps
    ->   ON
    ->         p.prod_id = ps.prod_id
    -> INNER JOIN
    ->         suppliers s
    ->   ON
    ->         ps.sup_id = s.supplier_id
    -> ORDER BY
    ->         ps.sup_id;
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+
| prod_id | sku  | prod_name       | prod_type | stock_level | reorder_level | category_name | sup_id | preferred |
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+
|       7 | 7    | Term Block      | 1         | 3           | 5             | Electrical    |      2 |         1 |
|       5 | 5    | Electrical Tape | 1         | 12          | 20            | Electrical    |      2 |         1 |
|       6 | 6    | BlowGas         | 1         | 6           | 15            | Plumbing      |     12 |         1 |
|       1 | 1    | PTFE Tape       | 1         | 9           | 10            | Plumbing      |     12 |         1 |
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+

这是查询列表的语句，但结果只显示了我预期中四项中的两项：

mysql> SELECT
    ->         p.prod_id,
    ->         AES_DECRYPT(p.alt_id, 'MW4KQLg1Irfo3Xz7Nxht')                         AS sku,
    ->         AES_DECRYPT(p.prod_name, 'MW4KQLg1Irfo3Xz7Nxht')                AS prod_name,
    ->         AES_DECRYPT(p.prod_desc, 'MW4KQLg1Irfo3Xz7Nxht')                 AS prod_desc,
    ->         AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht')                AS stock_level,
    ->         AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht')             AS reorder_level,
    ->         AES_DECRYPT(p.reorder_qty, 'MW4KQLg1Irfo3Xz7Nxht')               AS reorder_qty,
    ->         p.vat_exempt,
    ->         AES_DECRYPT(p.lastorderdate, 'MW4KQLg1Irfo3Xz7Nxht')            AS lastorderdate,
    ->         AES_DECRYPT(p.lastorderqty, 'MW4KQLg1Irfo3Xz7Nxht')               AS lastorderqty,
    ->         AES_DECRYPT(c.category_name, 'MW4KQLg1Irfo3Xz7Nxht')          AS category_name,
    ->         ps.sup_id
    -> FROM
    ->         products p
    -> INNER JOIN
    ->         product_category c
    ->   ON
    ->         p.category_id = c.category_id
    -> INNER JOIN
    ->         product_supplier ps
    ->   ON
    ->         p.prod_id = ps.prod_id
    -> INNER JOIN
    ->         suppliers s
    ->   ON
    ->         ps.sup_id = s.supplier_id
    -> WHERE
    ->         AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht') = 1
    ->   AND
    ->           AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht') <= AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht')
    ->   AND
    ->           ps.preferred = 1
    -> ORDER BY
    ->         ps.sup_id;
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+
| prod_id | sku  | prod_name       | prod_desc              | stock_level | reorder_level | reorder_qty | vat_exempt | lastorderdate | lastorderqty | category_name | sup_id |
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+
|       7 | 7    | Term Block      | Nylon connector block. | 3           | 5             | 20          |          0 | NULL          | NULL         | Electrical    |      2 |
|       5 | 5    | Electrical Tape | Black                  | 12          | 20            | 100         |          0 | NULL          | NULL         | Electrical    |      2 |
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+

- Peter White

---

为什么你要对每一列使用AES_DECRYPT()函数？如果所有内容都被加密，索引将无法正常工作（如果能工作的话）。 - mvp

因为这是会计系统的一部分，其中大多数字段需要加密。如果您有更好的解决方案，我很乐意听取建议。 - Peter White

您的查询看起来是正确的，能否提供符合重新订购条件的两个产品的样本数据？同时请检查第二个未出现在结果中的产品是否为prod_type = 1，并且该产品有一个“首选供应商”。 - Meherzad

我相信我的id列并没有加密（虽然alt_id是加密的，但实际上它被错误地标记为包含自由格式的虚荣文本字段的产品sku）。 - Peter White

在这个阶段，检查ps.preferred = 1并将所有内容设置为1。另外，如果级别相等，您仍然需要重新排序，对吗？ - Tony Shih

Answer 1

问题在于来自AES_DECRYPT()的结果是字符串类型，因此比较库存水平和重新订购水平值时，3-5和12-20会显示，而6-15和9-20不会显示，因为如果将6和15作为字符串进行比较，则6将大于15，9和20也是如此。

希望您已经理解了问题...

在比较之前将AES_DECRYPT()的结果转换为数字。

将您的where子句更改为以下内容：

WHERE
     (AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht') + 0) = 1 AND
     (AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht') + 0) <= (AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht') + 0)

在加上+0后，会将结果转换为数字类型。

希望这能有所帮助。

- Meherzad

Answer 2

WHERE
    ->         AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht',varchar) = 1
    ->   AND
    ->           AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht',varchar) <= AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht',varchar)

或者：

WHERE
    ->         AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht',int) = 1
    ->   AND
    ->           AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht',varchar) <= AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht',varchar)

来自：http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01015.1510/html/iqsecure/Functions_s_4132405.htm

如果您在使用AES_ENCRYPT函数插入数据时不使用CAST语句，则可以通过将VARCHAR作为数据类型传递给AES_DECRYPT函数来查看相同的数据。如果您没有向AES_DECRYPT传递数据类型，则返回VARBINARY数据类型。

- Amir

---

谢谢您的尝试，但我已经尝试过此方法，然而出现了以下错误。语法错误或访问违规：1064您的SQL语法有误；请检查相应于您的MySQL服务器版本的手册，以了解正确使用语法的方法。（'int'）=1。 - Peter White