MySQL生成的整型计算可以为空吗

缺少的JSON键提取为SQL NULL.

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

JSON null值不会提取到SQL NULL.它提取为标量值'null'的JSON文档.

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

JSON_UNQUOTE()不会将JSON值'null'转换为SQL NULL.它将其转换为SQL字符串值'null'.

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

因此，您生成的列try 解析字符串值'null'以将其转换为整数，这在严格模式下失败(默认设置):

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'

您可以通过将该字符串值与'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字符串值'null'区分开来:

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

基本上，JSON在与SQL结合时是一团乱七八糟的东西.有如此多的边缘情况以违反直觉的方式运行，我不得不说这是多年来添加到SQL中的最糟糕的功能.