【Mybatis】INSERT INTO 遇到NULL怎么处理？

背景-使用Mybatis手写批量插入Insert方法

表中设置都为非空字段，author默认值为sys。

测试核心代码，author字段为null，插入条件怎么写？

List<Article> ls = new ArrayList<>();
Article article = new Article();
article.setTitle("123");
article.setContent("666");
ls.add(article);
try {
    articleService.insertBatch1(ls);
} catch (Exception e) {
    e.printStackTrace();
}

MybatisPlus解决方案

自动填充字段

https://baomidou.com/guides/auto-fill-field/#1-定义实体类

• FieldFill.DEFAULT 完全依赖数据库默认值
• FieldFill.INSERT 插入时填充
• FieldFill.UPDATE 更新时填充

FieldFill.DEFAULT 完全依赖数据库默认值

不做任何处理，author为空，报错了

<!-- 批量插入Article -->
<insert id="insertBatch1" parameterType="java.util.List">
    INSERT INTO article(title, content, author, create_time, update_time)
    VALUES
    <foreach item="article" index="index" collection="list"
             open="(" separator=")," close=")">
        #{article.title}, #{article.content},#{article.author},
        NOW(), NOW()
    </foreach>
</insert>

org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'author' cannot be null
### The error may exist in file [/Users/SpringBootDemo/SpringBootDemo-web/target/classes/mapper/ArticleMapper.xml]
### The error may involve com.example.sbb.SpringBootDemo.demos.mapper.ArticleMapper.insertBatch1-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO article(title, content, author, create_time, update_time)         VALUES          (               ?, ?,?,             NOW(), NOW()          )
### Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'author' cannot be null
; Column 'author' cannot be null

FieldFill.INSERT 插入时填充

xml的insertBatch1方法没改，插入成功，原理应该是拦截器处理

Mybatis解决方案

if标签处理

<!-- 批量插入Article -->
<insert id="insertBatch2" parameterType="java.util.List">
    INSERT INTO article(title, content, author, create_time, update_time)
    VALUES
    <foreach item="article" index="index" collection="list"
             open="(" separator=")," close=")">
        #{article.title}, #{article.content},
        <if test="article.author != null">#{article.author}</if>
        <if test="article.author == null">0</if>,
        NOW(), NOW()
    </foreach>
</insert>

本方法也能成功，依赖动态标签，动态拼接SQL

问题：如果不在工程里面设置默认值？如何直接使用数据库的默认值？

<!-- 批量插入Article -->
<insert id="insertBatch1" parameterType="java.util.List">
     INSERT INTO article(title, content, author, create_time, update_time)
     VALUES
     <foreach item="article" index="index" collection="list"
              open="(" separator=")," close=")">
         #{article.title}, #{article.content},
         IFNULL(#{article.author}, DEFAULT(author)),
         NOW(), NOW()
     </foreach>
 </insert>