Laravel5.7执行Oracle适配SQL在MariaDB报1064语法错误

1064语法错误由Oracle到MariaDB的语法不兼容问题导致，核心问题有两个：

• 原SQL使用Oracle专属的TO_CHAR()函数做日期格式化，MariaDB不支持该函数，需替换为MariaDB原生的DATE_FORMAT()函数，同时日期格式占位符要对应调整：Oracle的MM/DD/YYYY HH12:MI:SS AM格式对应MariaDB的格式串为%m/%d/%Y %h:%i:%s %p
• MariaDB强制要求，当子查询作为派生表出现在FROM子句中时必须定义别名，原SQL外层嵌套的子查询没有设置别名，会直接触发语法报错
• 额外优化：原SQL中s_varrient_id IS NOT NULL条件未指定表别名，多表关联时可能出现字段歧义，补充表别名避免潜在报错。

SELECT
    local_upc,
    shopify_id,
    sku,
    description1,
    description2,
    SIZ,
    ATTR,
    base_price,
    retail_price,
    SUM(oh_qty) oh_qty,
    SUM(available_qty) available_qty,
    SUM(so_qty) so_qty,
    MAX(qty_update) qty_update,
    MAX(price_update) price_update
FROM
    (
    SELECT
        i.local_upc,
        c.s_varrient_id AS shopify_id,
        c.sku,
        i.description1,
        i.description2,
        i.SIZ,
        i.ATTR,
        cp.pl_1 AS base_price,
        cp.pl_2 AS retail_price,
        SUM(cq.oh_qty) oh_qty,
        SUM(cq.available_qty) available_qty,
        SUM(cq.so_qty) so_qty,
        DATE_FORMAT(cq.modified_date, '%m/%d/%Y %h:%i:%s %p') AS qty_update,
        DATE_FORMAT(cp.modified_date, '%m/%d/%Y %h:%i:%s %p') AS price_update
    FROM
        inventory_c c,
        invn_sbs i,
        invn_price_c cp,
        invn_qty_c cq
    WHERE
        i.item_sid = c.item_sid
        AND i.item_sid = cp.item_sid
        AND c.s_varrient_id IS NOT NULL
        AND i.item_sid = cq.item_sid
    GROUP BY
        i.local_upc,
        c.s_varrient_id,
        c.sku,
        i.description1,
        i.description2,
        i.SIZ,
        i.ATTR,
        cp.pl_1,
        cp.pl_2,
        DATE_FORMAT(cq.modified_date, '%m/%d/%Y %h:%i:%s %p'),
        DATE_FORMAT(cp.modified_date, '%m/%d/%Y %h:%i:%s %p')
) t
GROUP BY
    local_upc,
    shopify_id,
    sku,
    description1,
    description2,
    SIZ,
    ATTR,
    base_price,
    retail_price

直接通过DB门面执行原生SQL即可，无参数场景示例：

$list = DB::select(/* 放入上面修正后的完整SQL即可 */);

如果后续需要追加动态查询条件，不要直接拼接变量到SQL字符串中，使用?占位符做参数绑定，避免SQL注入风险。

内容的提问来源于stack exchange，提问作者Usman Khan