mysql-error-1064 - mariadb 10.3.15 中出现 1064 错误，但 mysql 版本 5.5.62 上没有

Question

我希望有人能够指出 mysql 查询的哪一部分可能导致语法错误。

此查询在我们以前运行 mysql 5.5.62 的服务器上运行，但现在在 mariadb 版本 10.3.15 上显示错误

SELECT p.product_id
    ,p.product_name
    ,p.product_thumb_image
FROM jos_vm_product AS p
WHERE p.product_sku = ' w '
    AND p.product_publish = 'Y'
LIMIT 0,10

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_name LIKE ' w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE (p.product_name LIKE '% w %')
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_sku LIKE 'w%'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_desc LIKE '% w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_s_desc LIKE '% w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE ((p.product_name LIKE '%w%'))
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

我希望查询能够成功运行，但它会输出错误 1064 - 您的 SQL 语法有错误...

标签： mysql-error-1064mariadb-10.3

Answer 1

您LIMIT只能在 a 的最后一个子查询中使用UNION。也许 MySQL [错误地] 允许您运行此查询，但 MariaDB 拒绝它。

解决方案？只需将第一个查询括在括号中，如下所示：

( -- enclosing parenthesis
SELECT p.product_id
    ,p.product_name
    ,p.product_thumb_image
FROM jos_vm_product AS p
WHERE p.product_sku = ' w '
    AND p.product_publish = 'Y'
LIMIT 0,10
) -- enclosing parenthesis

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_name LIKE ' w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE (p.product_name LIKE '% w %')
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_sku LIKE 'w%'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_desc LIKE '% w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE p.product_s_desc LIKE '% w %'
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

UNION

(
    SELECT p.product_id
        ,p.product_name
        ,p.product_thumb_image
    FROM jos_vm_product AS p
    WHERE ((p.product_name LIKE '%w%'))
        AND p.product_publish = 'Y'
    LIMIT 0,10
)

注意：请考虑LIMIT-ing 行而不ORDER-ing 它们可能会产生随机过滤。那是你要的吗？