背景
使用sql实现MySQL 记录不存在时插入 记录存在则不执行插入。
初始化表结构
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL COMMENT '名字',
`age` int(11) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('2', 'lisi', '19');
INSERT INTO `student` VALUES ('3', 'wangwu', '20');
INSERT INTO `student` VALUES ('5', 'zhangsan', '18');
INSERT INTO `student` VALUES ('6', 'zhangsan1', '18');
实现sql
INSERT into student (id,name,age) select null,"zhangsan1",18 from dual where not exists (select name from student where `name`="zhangsan1");
经过测试成功,存在name为zhangsan1的记录则不插入新记录。