Mysql
來自json的Mysql臨時表插入無法處理空值
MySql-8
JSON_TABLE函式允許您像處理表一樣處理 JSON 數據。我正在嘗試使用它來使用生成的表填充臨時表，但我相信我遇到了一個錯誤。
將空值插入
INT NULL列時，將引發以下錯誤：Invalid JSON value for CAST to INTEGER from column quantity at row 1這是有問題的程式碼
DROP TABLE IF EXISTS json_temp_table; CREATE TEMPORARY TABLE json_temp_table ( item_id int NOT NULL PRIMARY KEY, model_number varchar(100), quantity int NULL ) ENGINE = INNODB SELECT json_tb.item_id, json_tb.model_number, json_tb.quantity FROM JSON_TABLE ( '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]' , "$[*]" COLUMNS ( item_id int PATH "$.item_id", model_number varchar(100) PATH "$.model_number", quantity int PATH "$.quantity" ) ) json_tb; SELECT * FROM json_temp_table有沒有辦法讓 json 生成的表將空值插入臨時表？
奇怪的部分是選擇語句在沒有插入的情況下工作正常。此外，臨時表本身絕對可以處理 null int 值。我不明白為什麼這兩個不混合。
解決方案是省略您想要為空的屬性。
在搜尋了一段時間後，我發現了一年多前的這個錯誤報告，它解釋了一個包含值 NULL 的 json 字元串實際上是一個值而不是一個空值。
{"item":1,"quantity":4}–> 項目 = 1，數量 = 4
{"item":1,"quantity":null}–> item = 1, quantity = ’null’ （某種不友好的 json null ）
{"item":1}–> item = 1, quantity = NULL (你的好舊 sql null ！)看到這個之後，我想“必須有一種方法可以將這個不友好的 json null 轉換為一個不錯的 null”。我嘗試了許多有效的方法，但沒有一個可以插入到臨時表中。
這是我最後一次嘗試，根據我上面所說的，它應該有效。我設法使用內聯
IF語句檢測值是否為空，然後嘗試將實際值SQL NULL轉換為SIGNEDint。我展示瞭如何選擇值，但由於某種原因無法插入它們。程式碼本身：
DROP TABLE IF EXISTS json_temp_table; CREATE TEMPORARY TABLE json_temp_table ( item_id int NOT NULL PRIMARY KEY, model_number varchar(100), quantity int NULL ) ENGINE = INNODB; INSERT INTO json_temp_table SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM ( SELECT json_tb.item_id, json_tb.model_number, json_tb.quantity, if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null' FROM JSON_TABLE ( '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]' , "$[*]" COLUMNS ( item_id int PATH "$.item_id", model_number varchar(100) PATH "$.model_number", quantity int PATH "$.quantity" ) ) json_tb) x;在 CLI mysql 客戶端上執行
mysql> DROP TABLE IF EXISTS json_temp_table; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TEMPORARY TABLE json_temp_table ( -> item_id int NOT NULL PRIMARY KEY, -> model_number varchar(100), -> quantity int NULL -> ) -> ENGINE = INNODB; Query OK, 0 rows affected (0.00 sec) mysql> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM ( -> SELECT -> json_tb.item_id, -> json_tb.model_number, -> json_tb.quantity, -> if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null' -> FROM JSON_TABLE -> ( -> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]' -> , "$[*]" -> COLUMNS -> ( -> item_id int PATH "$.item_id", -> model_number varchar(100) PATH "$.model_number", -> quantity int PATH "$.quantity" -> ) -> ) json_tb) x; +---------+--------------+----------+ | item_id | model_number | quantity | +---------+--------------+----------+ | 1 | MFJA53 | 4 | | 2 | HSRHJN5 | NULL | | 3 | FAFAF1 | 345 | +---------+--------------+----------+ 3 rows in set, 1 warning (0.00 sec) mysql> INSERT INTO json_temp_table -> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM ( -> SELECT -> json_tb.item_id, -> json_tb.model_number, -> json_tb.quantity, -> if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null' -> FROM JSON_TABLE -> ( -> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]' -> , "$[*]" -> COLUMNS -> ( -> item_id int PATH "$.item_id", -> model_number varchar(100) PATH "$.model_number", -> quantity int PATH "$.quantity" -> ) -> ) json_tb) x; ERROR 3156 (22018): Invalid JSON value for CAST to INTEGER from column quantity at row 1 mysql>
我相信這是其中一種方式
DROP TABLE IF EXISTS json_temp_table; CREATE TEMPORARY TABLE json_temp_table ( item_id int NOT NULL PRIMARY KEY, model_number varchar(100), quantity int NULL ) ENGINE = INNODB INSERT INTO json_temp_table SELECT json_tb.item_id, json_tb.model_number, CAST( CASE true WHEN json_tb.quantity = 'null' -- handle string null THEN NULL ELSE json_tb.quantity END as signed) as quantity FROM JSON_TABLE ( '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]' , "$[*]" COLUMNS ( item_id int PATH "$.item_id", model_number varchar(100) PATH "$.model_number", quantity varchar(100) PATH "$.quantity" -- changed to varchar, converting to signed/unsigned above ) ) json_tb;