Syntax
帶有“INTO OUTFILE”的 SQL 查詢不適用於 MariaDB 10.4,但適用於 10.0
我有一個在 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' );