insert select写法报错Incorrect integer value的坑

Myth丶恋晨 2024-02-22 09:01 73阅读 0赞

## 一、问题 ##

最近项目的mysql数据库迁移到了oceanbase数据库，同样的sql，mysql可以执行，但是oceanbase里执行就报错：

[Err] 1366 - Incorrect integer value

错误sql样例如下：

INSERT into student_sign_statistics 
    SELECT
    t.student_id,
    t.class_id,
    t.riqi,
    SXW,
    COUNT(1),
    1,
    NOW()
    FROM
    (
    SELECT
    s.class_id class_id,
    s.user_id student_id,
    DATE_FORMAT(s.sign_time, '%Y-%m-%d') riqi,
    CASE WHEN 
    (
    DATE_FORMAT(s.sign_time, '%H') >= 0
    AND DATE_FORMAT(s.sign_time, '%H') <= 11
    ) THEN
    0
    ELSE
    1
    END SXW
    FROM
    signin s  JOIN subject_student c
     ON s.subject_id=c.class_subject_id AND s.user_id=c.user_id AND s.sign_type='2' 
     AND IFNULL(c.isdel, 0) != 1
     JOIN classinfo cf ON s.class_id=cf.class_id 
     AND cf.fact_startdate > DATE_SUB(DATE_FORMAT(CURRENT_TIME (),'%Y-%m-%d'),INTERVAL 31 DAY)
     AND cf.status='1' GROUP BY
    s.class_id,s.user_id,
    riqi,
    SXW
    ) t GROUP BY t.student_id ,t.class_id ON DUPLICATE KEY UPDATE student_id=VALUES(student_id), classid=VALUES(classid), sign_date=VALUES(sign_date),afternoon=VALUES(afternoon),sign_num=VALUES(sign_num) ,type=VALUES(type) ,update_time=VALUES(update_time) ;

**总之就是这么一大堆sql，mysql数据库执行没事，但是oceanbase数据库执行就报错：**

`[Err] 1366 - Incorrect integer value`

**并且如果单独把select的sql拿出来，那么在oceanbase数据库也是可以正常执行的，如下：**

SELECT
    t.student_id,
    t.class_id,
    t.riqi,
    SXW,
    COUNT(1),
    1,
    NOW()
    FROM
    (
    SELECT
    s.class_id class_id,
    s.user_id student_id,
    DATE_FORMAT(s.sign_time, '%Y-%m-%d') riqi,
    CASE WHEN 
    (
    DATE_FORMAT(s.sign_time, '%H') >= 0
    AND DATE_FORMAT(s.sign_time, '%H') <= 11
    ) THEN
    0
    ELSE
    1
    END SXW
    FROM
    signin s  JOIN subject_student c
     ON s.subject_id=c.class_subject_id AND s.user_id=c.user_id AND s.sign_type='2' 
     AND IFNULL(c.isdel, 0) != 1
     JOIN classinfo cf ON s.class_id=cf.class_id 
     AND cf.fact_startdate > DATE_SUB(DATE_FORMAT(CURRENT_TIME (),'%Y-%m-%d'),INTERVAL 31 DAY)
     AND cf.status='1' GROUP BY
    s.class_id,s.user_id,
    riqi,
    SXW
    ) t GROUP BY t.student_id ,t.class_id

**这样单独执行select，也没有问题；**  
**把结果集的数据复制出insert来执行，也没有问题，就很奇怪。**

## 二、解决方法 ##

后来发现，把`AND IFNULL(c.isdel, 0) != 1`换成`AND IFNULL(c.isdel, 0) != '1'`，就可以了。如下：

INSERT into student_sign_statistics 
    SELECT
    t.student_id,
    t.class_id,
    t.riqi,
    SXW,
    COUNT(1),
    1,
    NOW()
    FROM
    (
    SELECT
    s.class_id class_id,
    s.user_id student_id,
    DATE_FORMAT(s.sign_time, '%Y-%m-%d') riqi,
    CASE WHEN 
    (
    DATE_FORMAT(s.sign_time, '%H') >= 0
    AND DATE_FORMAT(s.sign_time, '%H') <= 11
    ) THEN
    0
    ELSE
    1
    END SXW
    FROM
    signin s  JOIN subject_student c
     ON s.subject_id=c.class_subject_id AND s.user_id=c.user_id AND s.sign_type='2' 
     AND IFNULL(c.isdel, 0) != '1'
     JOIN classinfo cf ON s.class_id=cf.class_id 
     AND cf.fact_startdate > DATE_SUB(DATE_FORMAT(CURRENT_TIME (),'%Y-%m-%d'),INTERVAL 31 DAY)
     AND cf.status='1' GROUP BY
    s.class_id,s.user_id,
    riqi,
    SXW
    ) t GROUP BY t.student_id ,t.class_id ON DUPLICATE KEY UPDATE student_id=VALUES(student_id), classid=VALUES(classid), sign_date=VALUES(sign_date),afternoon=VALUES(afternoon),sign_num=VALUES(sign_num) ,type=VALUES(type) ,update_time=VALUES(update_time) ;

不知道为什么，总之把`AND IFNULL(c.isdel, 0) != 1`换成`AND IFNULL(c.isdel, 0) != '1'`，就可以正常执行了。

## 三、备注 ##

可能是之前数据库是mysql5，语法要求不严格；换成oceanbase数据库后（是基于mysql8的），语法严格了，导致报错；具体原因还不清楚。