由於“null”而導致 CAST(meta->>’$.field’) 更新失敗

此問題影響 MySQL 5.7.25。

因此，在遷移中，我將表中的遺留欄位移動到 JSON 列中。該過程的這一部分工作正常。但是，回滾失敗得很慘！此時，我正在考慮將 JSON 欄位邏輯從 SQL 移到 PHP 中。

您可以按如下方式重現問題。

數據庫設置

CREATE TABLE `test_table` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `meta` json DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_table` VALUES
(null, '{"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"}'),
(null, '{"test_dt": "2019-03-28 15:44:15", "test_enum": null}'),
(null, '{"test_dt": null, "test_enum": "bar"}');

ALTER TABLE `test_table`
 ADD COLUMN `test_enum` ENUM('foo', 'bar') NOT NULL DEFAULT 'foo',
 ADD COLUMN `test_dt` DATETIME NULL;

問題

以下查詢是否有解決方法？如果可能的話，我寧願只使用 1 個更新查詢……

-- copy from meta back into legacy fields
UPDATE `test_table` SET
 `test_enum` = `meta`->>"$.test_enum",
 `test_dt` = CAST(`meta`->>"$.test_dt" AS DATETIME);

第 2 行將導致錯誤：Data truncated for column 'test_enum'. 這似乎是因為meta->>"$.test_enum"和JSON_EXTRACT(meta, "$.test_enum")都返回"null"而不是NULL.

現在在不更新列舉欄位的情況下重試：

-- copy from meta back into legacy fields
UPDATE `test_table` SET
 `test_dt` = CAST(`meta`->>"$.test_dt" AS DATETIME);

第 3 行現在導致錯誤：Incorrect datetime value: 'null'

我知道我可以通過將查詢分成兩部分來解決這些錯誤，例如：

UPDATE test_table 
SET test_enum = `meta`->>"$.test_enum" 
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_enum")) != 'NULL';

UPDATE test_table 
SET test_dt = `meta`->>"$.test_dt" 
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_dt")) != 'NULL';

困惑

通過 CAST 選擇數據可以正常工作，即使與 ENUM 相關的列由於"null"值而無效。為什麼不能JSON_EXTRACT()正確返回 NULL？

SELECT
 `meta`->>"$.test_enum" AS `test_enum`,
 CAST(`meta`->>"$.test_dt" AS DATETIME) AS `test_dt`
 FROM test_table

"id","test_enum","test_dt"
1,"foo","2019-03-28 15:43:59"
2,"null","2019-03-28 15:44:15"
3,"bar",NULL

現在，我只是想知道為什麼 MySQL 在 JSON 和 CAST/CONVERT 方法中都以這種方式處理 NULL。我覺得我錯過了一些明顯的東西。

我認為這是一個錯誤。我在 MySQL 開發者專區找不到任何這樣的錯誤報告，所以你應該報告它。

• 展示 1
• 展示 2

字元串 的"2019-03-28 15:43:59"外觀和感覺和行為都像DATETIME. 不要打擾使用CAST().

不是值，而是4 個字母的字元串NULL？也就是說，您在空單元格上工作太努力了？NULL``"NULL"

引用自：https://dba.stackexchange.com/questions/233438