升级至MariaDB 10.6.12后SQL查询报错求助

错误根源 #

升级MariaDB后，原SQL的语法不符合新版本规范：当使用UNION ALL组合查询时，INTO OUTFILE必须放置在整个联合查询的末尾，而非附加在第二个SELECT语句之后。旧版本MariaDB/MySQL可能兼容这种不规范写法，但新版本严格执行SQL标准，因此触发语法错误。

修正后的SQL语句 #

SELECT 'Label', 'Catalog #', 'UPC', 'CFG', 'Artist', 'Title', 'Price 1', 'QTY', 'Genre', 'Release Date'
UNION ALL
SELECT
    rc.record_company_name as Label,
    p.products_sku as 'Catalog #',
    p.products_model as UPC,
    cd.categories_name as CFG,
    ra.artists_name as Artist,
    pd.products_name as Title,
    p.products_group_a_price as 'Price 1',
    p.products_quantity as QTY,
    mg.music_genre_name as Genre,
    DATE_FORMAT(p.products_date_available,'%m/%d/%Y') as 'Release Date'
FROM products p,
     products_description pd,
     product_music_extra pme,
     record_artists ra,
     music_genre mg,
     record_company rc,
     products_to_categories p2c,
     categories c,
     categories_description cd
WHERE p.products_id = pd.products_id
  and p.products_id = pme.products_id
  and p.products_id = p2c.products_id
  and pme.artists_id = ra.artists_id
  and pme.record_company_id = rc.record_company_id
  and pme.music_genre_id = mg.music_genre_id
  and p2c.categories_id = c.categories_id
  and c.categories_id = cd.categories_id
  and p2c.categories_id NOT IN ('31','21','22','23','24','25','41')
  and p.products_status = '1'
INTO OUTFILE '/temp/soh_catalog.csv'
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

额外优化说明 #

• 将多个p2c.categories_id != 'XX'合并为NOT IN语句，简化条件逻辑，提升可读性
• 保留了你原有的字段别名（包括带空格和#的名称），无需修改这些别名即可正常运行

内容的提问来源于stack exchange，提问作者Kevin Moody