設想
我希望從具有簡單 varchar 和整數列的表中選擇并回傳整個表的 JSON 表示。這樣以后我可以使用 PhP 的 curl() 將“表”發送到另一臺服務器并重建它。我在我的 ISP 托管的遠程共享服務器上使用 MySQL 版本 8.0.28-cll-lve。我沒有對服務器本身的管理員訪問權限，只有使用和維護資料庫的所有訪問權限。
我試過的
下面的代碼和這個SQLfiddle中的代碼是為了回應Stack Exchange 的“資料庫管理員”上的這篇文章以及關于完全相同主題的 SO 上的其他幾篇文章（我不會將它們全部列出，因為我希望第一個回復會告訴我這個問題復制它們）
應該作業的示例代碼
DROP TABLE IF EXISTS contact;
CREATE TABLE contact
(
name_field VARCHAR (5) NOT NULL,
address_field VARCHAR (20) NOT NULL,
contact_age INTEGER NOT NULL
);
INSERT INTO contact
VALUES
('Mary', 'address one', 25),
('Fred', 'address two', 35),
('Bill', 'address three', 47);
SELECT
CONCAT
('[', REPLACE
(
REPLACE
(
GROUP_CONCAT
(
JSON_ARRAY
(
'name_field:', name_field,
'address_field:', address_field,
'age_field:', contact_age
) SEPARATOR ','
), '[', '{'
), ']', '}'
), ']'
)
AS best_result2
FROM contact
Result of running this code in SQL fiddle under MySQL 8.0
[{"name_field:", "Mary", "address_field:", "address one", "age_field:", 25},{"name_field:", "Fred", "address_field:", "address two", "age_field:", 35},{"name_field:", "Bill", "address_field:", "address three", "age_field:", 47}]
Problem
When I run this code in SQL fiddle using mySQL ver 8.0 it produces the correct result above. However if I copy/paste the code from SQLfiddle into SQLyog 12.4.3 and run it against a MySQL database version 8.0.28-cll-lve I get the following syntax error
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR ','
The code looks OK to me. Can anyone see a reason for this please?
Edit - more strangeness
Thanks to forpas but I couldn't see any difference between his code that worked and mine that gave a syntax error As an experiment I put all my code on one line - still got the error Then I removed all the spaces apart from after SELECT, AS and FROM and it worked OK . I can only assume that having spaces in the wrong place was spoiling the syntax, although that seems unlikely.
Edit 2 - Solved, reason for the syntax error
Thanks to everyone who pointed out that the code I was using did not produce valid JSON. That is not really the point, it produced something, not a syntax error, so is slightly irrelevant to my question. The code came from an accepted answer on Database Administrators which, as it worked in SDQLfiddle without syntax errors, I assumed would work in my DBMS (I didn't notice the slight issue with the JSON at the time but I could easily fix that later).
However user forpas came up with the correct answer to my question on why I was getting a syntax error, which is the copy/paste action put a space between GROUP_CONCAT and its following bracket. Remove that space and the code runs without syntax errors.
uj5u.com熱心網友回復：
您的問題實際上記錄在Function Name Parsing and Resolution/Built-In Function Name Parsing 中：
決議器使用默認規則來決議內置函式的名稱。可以通過啟用 IGNORE_SPACE SQL 模式來更改這些規則。
當決議器遇到作為內置函式名稱的單詞時，它必須確定該名稱是表示函式呼叫還是對識別符號（如表或列名）的非運算式參考......
一些內置函式具有特殊的決議或實作注意事項，因此決議器默認使用以下規則來區分它們的名稱是用作函式呼叫還是用作非運算式背景關系中的識別符號：
要將名稱用作運算式中的函式呼叫，名稱和后面的 ( 括號字符之間不能有空格。
相反，要使用函式名作為識別符號，它后面一定不能緊跟括號
該函式GROUP_CONCAT()屬于受 IGNORE_SPACE 設定影響的函式串列。
GROUP_CONCAT因此，洗掉和之間的任何間距(。
請參閱演示。
uj5u.com熱心網友回復：
在 MySQL 8.0 下的 SQL fiddle 中運行此代碼的結果
[{"name_field:", "Mary", "address_field:", "address one", "age_field:", 25},{"name_field:", "Fred", "address_field:", "address two", "age_field:", 35},{"name_field:", "Bill", "address_field:", "address three", "age_field:", 47}]
這不是有效的 JSON。這是一些類似 JSON 的字串。
測驗這對您是否安全：
SELECT JSON_ARRAYAGG(JSON_OBJECT('name_field', name_field,
'address_field', address_field,
'contact_age', contact_age)) all_contacts
FROM contact;
準確提供所需輸出的代碼：
SELECT CONCAT('[',
GROUP_CONCAT(
CONCAT(
'{',
CONCAT_WS(', ',
'"name_field:"', JSON_QUOTE(name_field),
'"address_field:"', JSON_QUOTE(address_field),
'"age_field:"', contact_age
),
'}'
)
),
']'
) one_value
FROM contact
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=568f2f8f0ef7e6dfb10941b7469fa0db
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/438537.html
標籤：mysql group-concat sql-parser
上一篇：如何修復“應使用逗號或右括號”？