代码之家 › 专栏 › 技术社区 › epeleg

mysql生成的int culoms不能为null吗?

mysql-json mysql-workbench mysql

epeleg · 技术社区 · 11 月前

将Mysql与下表一起使用:

CREATE TABLE participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    puuid CHAR(36) NOT NULL,
    data JSON,
    project INT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.project'))) STORED NULL,
    schoolCode VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.schoolCode'))) STORED,
    UNIQUE KEY (puuid)
);

当我尝试跑步时:

insert into participants(puuid,data) values('aasd','{"project":1}');

它解决了一个问题但是当我尝试的时候

insert into participants(puuid,data) values('aasd1','{"project":null}');

我收到一个错误,说:

Error Code: 1366. Incorrect integer value: 'null' for column 'project' at row 1

我使用进行了双重检查

 show columns from participants;

为什么它不允许JSON对项目具有null值,即使它声明项目列允许null?

除了这样的解决方案之外,没有其他解决方案了吗

project INT GENERATED ALWAYS AS (COALESCE(JSON_UNQUOTE(JSON_EXTRACT(data, '$.project')), 0)) STORED NULL,

更新: 实际上似乎两者都没有

 project INT GENERATED ALWAYS AS (COALESCE(JSON_UNQUOTE(JSON_EXTRACT(data, '$.project')), 0)) STORED NULL,

也没有

 project INT GENERATED ALWAYS AS (IFNULL(JSON_UNQUOTE(JSON_EXTRACT(data, '$.project')), 0)) STORED NULL,

工作我即将放弃“生成柱”的想法。

2 回复 | 直到 11 月前

Bill Karwin 11 月前

丢失的JSON密钥提取为SQL NULL .

mysql> select json_extract('{"project":null}', '$.xyzzy') is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          1 |
+------------+

JSON null 值未提取到SQL 无效的 。它将作为标量值的JSON文档提取 'null' .

mysql> select json_extract('{"project":null}', '$.project') is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          0 |
+------------+

JSON_UNQUOTE() 不转换JSON值 无效的 转换为SQL 无效的 。它将其转换为SQL字符串值 无效的 .

mysql> select json_unquote(json_extract('{"project":null}', '$.project')) is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          0 |
+------------+

因此,您生成的列正在尝试解析字符串值 无效的 将其强制转换为整数,在严格模式(默认模式)下失败:

mysql> select cast('null' as signed) as bad_integer;
+-------------+
| bad_integer |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect INTEGER value: 'null'

您可以通过将该字符串值与 无效的 并以这种方式替换SQL NULL:

mysql> select nullif(json_unquote(json_extract('{"project":null}', '$.project')), 'null') is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          1 |
+------------+

但这与JSON字符串值无法区分 无效的 :

mysql> select nullif(json_unquote(json_extract('{"project":"null"}', '$.project')), 'null') is null as is_it_null;
+------------+
| is_it_null |
+------------+
|          1 |
+------------+

基本上,当JSON与SQL结合在一起时,它就是一团乱麻。有太多的边缘案例以反直觉的方式表现,我不得不说这是多年来添加到SQL中最糟糕的功能。

Luuk WHIP 11 月前

阅读后 answer 来自Bill,

你可以/应该做:

CREATE TABLE participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    puuid CHAR(36) NOT NULL,
    data JSON,
    project INT GENERATED ALWAYS AS (CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(data, '$.project')) = 'null' 
                                          then null 
                                          else JSON_UNQUOTE(JSON_EXTRACT(data, '$.project')) end) STORED NULL,
    schoolCode VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.schoolCode'))) STORED,
    UNIQUE KEY (puuid)
);

看见 DBFIDDLE