Syntax

帶有“INTO OUTFILE”的 SQL 查詢不適用於 MariaDB 10.4,但適用於 10.0

  • March 13, 2020

我有一個在 MariaDB 10.0.35 上完美執行的 SQL 查詢,但在 MariaDB 10.4.12 上因語法錯誤而失敗。我試圖聯繫他們,但他們告訴我查看論壇並比較從 10.0 到 10.4 的變更日誌。我真的沒有時間瀏覽幾十個變更日誌文件,試圖找出有什麼不同,特別是因為它們的文件似乎與我正在做的事情兼容。這是查詢:

(SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company') 
UNION (
SELECT name, address1, address2, city, state, zip, phone, ssn, credit_card, county, comment, company 
FROM testcust  
WHERE type = '0'  
ORDER BY name 
INTO OUTFILE '/tmp/sellers.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
LINES TERMINATED BY '\r\n'
);

我什至嘗試重新安排它以將 INTO OUTFILE 子句放在 FROM 之前(如範例中所示):

(SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company') 
UNION (
SELECT name, address1, address2, city, state, zip, phone, ssn, credit_card, county, comment, company 
INTO OUTFILE '/tmp/allcustomers.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
LINES TERMINATED BY '\r\n'
FROM testcust  
ORDER BY name 
);

但這沒關係。(順便說一下,這兩個版本都適用於 10.0。)

這是來自 MariaDB 10.4.12 的錯誤:

ERROR 1064 (42000):您的 SQL 語法有錯誤;檢查與您的 MariaDB 伺服器版本相對應的手冊,以了解在“INTO OUTFILE ‘/tmp/allcustomers.csv’ 附近使用的正確語法”

這是 MariaDB 的“INTO OUTFILE”文件: MariaDB 文件頁面 ,其中有以下範例:

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 FROM customers;

我在 /etc/my.cnf.d/secure_file_priv.cnf 中禁用了secure_file_priv:

[server]
# secure_file_priv = /var/lib/mysql-files
secure_file_priv = ""

我找不到為任何想要導入 SQL 文件以使用上述查詢進行測試的人附加 SQL 文件的方法,但很高興根據請求發送它(除非我只是錯過瞭如何附加文件)。

我對此完全不知所措,似乎找不到他們文件的不同版本來查看是否真的存在語法差異——我只能假設他們的線上文件是最新版本的。

我真的很感激任何人能夠給予我的任何啟示……

採用

SELECT *
FROM ( SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 
             'SSN', 'Credit Card', 'County', 'Comment', 'Company'
      UNION ALL       
      SELECT name, address1, address2, city, state, zip, phone, 
             ssn, credit_card, county, comment, company 
      FROM testcust  
      WHERE type = '0'         
    ) subquery
ORDER BY name = 'Name' DESC, name
INTO OUTFILE '/tmp/sellers.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ',' 
LINES TERMINATED BY '\r\n'
);

引用自:https://dba.stackexchange.com/questions/261843