MySQL 笔记

MySQL中常见的坑

404 - Not Found · 语雀

将 A 表查询的数据插入到 B 表中

INSERT INTO user_new(id,username,pwd) SELECT id,NAME,pwd FROM user;

不存在则插入，存在则不插入

参考链接

需求： 对于普通的 INSERT 插入，如果想要保证不插入重复记录，我们只有对某个字段创建唯一约束实现（比如：id 不能重复），那么没有不创建唯一约束，仅通过一条 INSERT INTO 语句实现的方案呢？

答案：有的，使用 _**INSERT INTO IF EXISTS**_ 即可实现该需求。
具体语法如下：

INSERT INTO table(field1, field2, fieldn) SELECT 'field1Value', 'field2Value', 'fieldnValue' FROM DUAL WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)

其中的 DUAL 是一个临时表，不需要物理创建，这么用即可。

趁热打铁

需求：已知有 user 表，判断表中是否有张飞，没有则新增，有则不新增。

user 表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `alias` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user
-- ----------------------------
SET FOREIGN_KEY_CHECKS = 1;
-- 新增两条基础数据
INSERT INTO `user`(`name`, `alias`, `last_login_time`) VALUES ('刘备', '阿蛮', NOW());
INSERT INTO `user`( `name`, `alias`, `last_login_time`) VALUES ('关羽', '云长', NOW());

实现：

INSERT INTO user(name, alias, last_login_time) SELECT "张飞","翼德",NOW() FROM DUAL WHERE NOT EXISTS(SELECT NAME FROM user WHERE NAME = "张飞")

多次执行上面的 SQL 语句可以发现，第一次执行 user 表新增一条 id = 3 的数据，再次执行 user 表无任何变化。

不存在则插入，存在则更新

方法1：使用 replace 关键字
replace是 insert 的增强版，可以实现插入的数据和已存在的数据发生主键或者唯一键重复，则删除已存在的数据，再实现插入，如果不重复，则直接插入数据。
结合 Mybatis 批量处理，用法如下：

<update id="updateSchoolTable" useGeneratedKeys="true" parameterType="java.util.List">
<!-- 存在则更新，不存在则插入 -->
replace into
schoolTable(Id,describeDataForm,childrenID,relevanceID,showType)
values
<foreach collection="list" item="schoolDataForm" separator=",">
(
#{schoolDataForm.Id},
#{schoolDataForm.describeDataForm},
#{schoolDataForm.childrenID},
#{schoolDataForm.relevanceID},
#{schoolDataForm.showType}
)
</foreach>
</update>

方法2：使用 ON DUPLICATE KEY UPDATE
该方法能够在主键或者唯一键重复时，修改原记录中某字段的数据。
结合Mybatis批量处理，用法如下：

<update id="updateSchoolTable" useGeneratedKeys="true" parameterType="java.util.List">
insert into
schoolTable(Id,describeDataForm,childrenID,relevanceID,showType)
values
<foreach collection="list" item="schoolDataForm" separator=",">
(
#{schoolDataForm.Id},
#{schoolDataForm.describeDataForm},
#{schoolDataForm.childrenID},
#{schoolDataForm.relevanceID},
#{schoolDataForm.showType}
)
</foreach>
<!--存在即可修改下述字段的数据，注意values()中的内容是数据表中相应的字段名-->
ON DUPLICATE KEY UPDATE
describeDataForm=values(describeDataForm),
childrenID=values(childrenID),
relevanceID=values(relevanceID),
showType=values(showType)
</update>

性能区别：
正如 replace 底层实现所示，如果主键重复会先删除数据库中原来的记录，插入新纪录。但是数据库删除操作需要维护主键索引，这无疑需要消耗性能。
ON DUPLICATE KET QPDATE 只是在主键重复时修改所需字段的值，所以不影响主键。维护成本自然相对于replace 低。
注意：在数据量较小时，两者效率大致相同，都很快，但是出现大量数据（百万级别）时，差异就显示出来了。