mybatis不重复列插入例子详细说明

之前有做过不重复列插入的需求，当时是 在插入时判断 对应的列在数据库中有没有对应的数据 有则返回false 无则插入，但是这加大了数据库的查询负担 也增加了插入的时间，故今天研究一下 使用sql来简化了这一点

使用的知识点是 daul表

insert into tb_account(rname) select '非管理员' 
WHERE not exists 
(SELECT id FROM sys_role WHERE rname='非管理员');

1.表结构

-- newTechnologyTest.tb_account definition
CREATE TABLE `tb_account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `age` int NOT NULL,
  `birthday` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` int NOT NULL DEFAULT '1' COMMENT '0非正常 1 正常',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO newTechnologyTest.tb_account (user_name,age,birthday,status) VALUES
	 ('张三',18,'2020-01-11 00:00:00',1),
	 ('李四',19,'2021-03-21 00:00:00',1),
	 ('李六10',23,'2024-03-05 12:26:35',1),
	 ('李六11',23,'2024-03-05 12:28:43',1);

使用mybatis 自定义的sql来实现 功能

    @Insert("<script>" +
    "insert into tb_account(" +
    "<if test=\"userName != null and userName != '' \">user_name,</if>" +
    "<if test=\"age != null and age != '' \">age,</if>" +
    "<if test=\"birthday != null and birthday != '' \">birthday,</if>" +
    "<if test=\"status != null and status != '' \">status,</if>" +
    "id) " +
    "select " +
    "<if test=\"userName != null and userName != '' \">#{userName},</if>" +
    "<if test=\"age != null and age != '' \">#{age},</if>" +
    "<if test=\"birthday != null and birthday != '' \">#{birthday},</if>" +
    "<if test=\"status != null and status != '' \">#{status},</if>" +
    " #{id} " +
    "WHERE not exists " +
//             重复列查找
    "(SELECT id FROM tb_account WHERE user_name=#{userName} and age = #{age})" +
    "</script>")
    boolean insertNoRepeat(Account account);
    // 注意一定要判断是否为null “”  因为在输入数据时不一定是全列的而且一般表都有默认值列

调用此方法实现功能(当前本人使用的框架是 mybatisFlex)

Account account2 = new Account();
        account2.setUserName("李六11");
        account2.setAge(23);
        System.out.println(account2);
        System.out.println(accountMapper.insertNoRepeat(account2));