MYSQL插入数据时原始数据的null无法识别

Adore_rui

已于 2023-09-12 10:44:22 修改

阅读量594

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2023-09-12 10:38:04 首次发布

本文链接：https://blog.csdn.net/Adore_rui/article/details/132825822

文章讲述了在MySQL中遇到的错误，因尝试将字符串null插入int类型的字段，解决方法是使用IF函数判断并转换。原由是数据库中的JSON数据导致null被识别为字符串。

1.问题描述

我有一个status的字段，建表时候数据类型为int(4)，在插入数据的时候报了如下错误，原因： SQL 错误 [1366] [HY000]: Incorrect integer value: 'null' for column 'status' at row 13’

2.问题分析

这个错误提示表明在插入数据时，尝试将字符串值 'null' 插入到一个数据类型为int的字段 'status' 中，这是不允许的，因为 'null' 不是一个有效的整数值。

all in all，在mysql里NULL才是关键字空值，null是字符串，所以问题的关键是应该在status里数据位null时候转换为NULL

3.问题背景：

1.原始库是json格式

2.结构化的数据

可以看到问题出在null被识别成了字符串

3.错误的语句是：

INSERT INTO an_feb_ods_hetong.ht_attachment (
    status
)
SELECT
    
   JSON_UNQUOTE(JSON_EXTRACT(json, '$.status'))) AS status
   
FROM an_hetong.ht_attachment;

4.问题解决方法

用一个if语句即可解决这个问题，在SQL语句中，使用了IF函数来判断是否将字符串 'null' 转换为NULL值，并在插入时将 'status' 列的值直接放入列中。

INSERT INTO an_feb_ods_hetong.ht_attachment (
  
    status
)
SELECT
    
    IF(JSON_UNQUOTE(JSON_EXTRACT(json, '$.status')) = 'null', NULL, JSON_UNQUOTE(JSON_EXTRACT(json, '$.status'))) AS status
   
FROM an_hetong.ht_attachment;

使用以上sql后结构化数据：