Mysql、Oracle插表关于主键的处理（insert后返回主键）

1.情景展示

在实际开发过程中，在新增表数据时，对于表字段主键，Mysql和Oracle的处理方式是不一样的。

2.Oracle

Oracle的主键通过序列来实现；

要想使用序列，需要先给主键创建序列，通常以"seq_"+表名的格式进行命名，方便识别；

获取主键的方式：需要提前获取，也就是在插入表记录之前，就可以提前知晓该行记录的主键ID；

即：通过查询获取主键ID；

SELECT SEQ_TABLE_NAME.NEXTVAL FROM DUAL

在IBATIS或MYBATIS中的用法为：（ibatis+oracle）

<!-- 接入授权表 插入sql -->
<insert id="insertACCESS_GRANT" parameterClass="map">
 <selectKey keyProperty="ID" resultClass="java.lang.String">
       SELECT SEQ_ACCESS_GRANT.NEXTVAL FROM DUAL
   </selectKey>
    INSERT INTO ACCESS_GRANT(ID,GRANTJK,SECRETKEY,ACCESSID)
    VALUES(#ID#,#GRANTJK#,#SECRETKEY#,#ACCESSID#)
</insert>

这样，查询出来的ID将会和行数据一同插入到表中。

Oracle使用序列的方式的好处在于：

序列的数据具有唯一性，每次查询都会+1，不用考虑并发问题，只要是使用序列值当做主键，基本不会出现序列值（ID）重复导致插入失败的问题；

另外，基于这个特性，当我们需要提前获取ID而进行一些基于当前行ID做些处理时，将会非常简单。

缺点在于：序列一直增加，因为其它字段导致插入失败时，难免会造成序列号不连续。

3.Mysql

Mysql的主键可以设置自增属性；

在插入表数据的时候，不需要获取主键，不需要给主键填充数值，Mysql会自动给该字段赋值；

在IBATIS或MYBATIS中的用法为：（ibatis+mysql）

<insert id="insertMETA_THEME" parameterClass="map">
    INSERT INTO META_THEME(THEMENAME,THEMECODE,THEMELEVEL,PARENTTHEMEID,STATUS,ZJM,CREATETIME,REMARK1,REMARK2,REMARK3)
    VALUES(#THEMENAME#,#THEMECODE#,#THEMELEVEL#,#PARENTTHEMEID#,#STATUS#,#ZJM#,DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s'),#REMARK1#,#REMARK2#,#REMARK3#)
</insert>

或者，携带主键，不赋值：

Mysql自增主键的好处在于：

可以只管填数据，不需考虑主键的问题；

插入数据失败，不会触发自增，确保ID的连续性（刨除删除表数据造成的主键ID缺失的情形）。

坏处在于：在插入数据之前无法提前获得主键ID。

4.拓展

Mysql，如何在插入数据之前获取（确定）主键？

首先，这种需求肯定是普遍存在的，举个例子：

某表结构有字段：主键ID和编码，编码根据主键等固定规则生成，这就需要我们在插入数据之前提前获取并确定该行记录的主键ID；

这里提供两种解决办法：

办法一：不推荐使用

在插入数据的时候编码字段先不管，也就是该字段值插入的是null，待插入成功将主键返回后（假设能根据其它查询条件能拿到主键），再生成编码并更新字段编码的值。

缺点是：繁琐，需要对表操作两次，才能完成一条记录；

好处是：提高成功率，不需要考虑因主键ID重复造成插入失败问题。

办法二：

查询当前最大的ID，MAX_ID+1当做即将插入字段的ID。

考虑到并发问题，在查询的时候增加了排他锁，但预计并不能解决并发造成主键ID重复的问题。

好处在于：可以在插入数据之前就能确定ID；

坏处在于：不支持并发，一旦并发，就会造成因主键ID重复造成数据插入失败。

办法三：推荐使用

2022年5月31日18:15:29

使用函数LAST_INSERT_ID()并指定order="AFTER"。

<insert id="insertMETA_THEME" parameterClass="map">
    <selectKey keyProperty="id" resultType="Long" order="AFTER">
        SELECT LAST_INSERT_ID()
    </selectKey>
    INSERT INTO META_THEME(THEMEID,THEMENAME,THEMECODE,THEMELEVEL,PARENTTHEMEID,STATUS,ZJM,CREATETIME,REMARK1,REMARK2,REMARK3)
    VALUES(null,#THEMENAME#,#THEMECODE#,#THEMELEVEL#,#PARENTTHEMEID#,#STATUS#,#ZJM#,DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s'),#REMARK1#,#REMARK2#,#REMARK3#)
</insert>

先执行的是INSERT，后执行SELECT；

因为LAST_INSERT_ID()返回的是：上一次插入语句的主键ID，先执行插入，再调用该函数，返回的就是此次插入数据，所用的主键。

说明：

返回的主键值，会被塞到Map对象当中，因为keyProperty指定的属性是id，所以，这个insert最终表达的意思是：

执行插入语句后，拿到该行数据对应的主键值（id=主键值），并将其塞到Map对象当中。

这样，我们就能从Map当中取到key=id对应的值（也就是主键值）。

为什么不能使用order="BEFORE"？

说明：order属性的默认值是：BEFORE。

网上百度出来，可以使用这种方法，经测试无效；

LAST_INSERT_ID()返回的是上一次插入的ID值，不能在插入数据前，只能用在插入数据后，那这样，对于我们来说，必须反其道而行之，才能获取到正确的主键。

第1次执行插入，虽然last_insert_id()函数返回的是0，控制台显示的也是插入的是0，但实际上，插入的数据库的主键确实48；

第2次执行插入，last_insert_id()函数返回的是48，因为主键ID=48的数据记录已经存在，所以，就会造成主键重复，导致插入失败；

那是不是last_insert_id()+1就可以解决这个问题了呢？

重启tomcat，再次尝试，主键ID计数将从0重新开始：

更多关于last_insert_id()使用弊端的问题见文末推荐。

办法四：使用UUID/GUID做主键

<insert id="insertMETA_THEME" parameterClass="map">
    <selectKey keyProperty="id" resultType="Long" order="BEFORE">
        SELECT UUID()
    </selectKey>
    INSERT INTO META_THEME(THEMEID,THEMENAME,THEMECODE,THEMELEVEL,PARENTTHEMEID,STATUS,ZJM,CREATETIME,REMARK1,REMARK2,REMARK3)
    VALUES(#id#,#THEMENAME#,#THEMECODE#,#THEMELEVEL#,#PARENTTHEMEID#,#STATUS#,#ZJM#,DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s'),#REMARK1#,#REMARK2#,#REMARK3#)
</insert>

说明：

mysql调用UUID()，Oracle调用SYS_GUID()。

在插入前还是插入后，拿到主键值都是可以的。

2022年8月14日15:30:35

IBTATIS如何在插入数据后返回主键？

前提：

要想IBTATIS或MYBATIS在成功插入数据后返回主键，我们需要在插入数据之前，确定主键。

使用场景：

当一个业务需要插入多张表，且表与表之间使用主键进行关联。

不管是Oracle还是MySQL都能实现，如果是将guid作为主键的话，下面以ibatis为例；

XML

<insert id="insertBL_PATIENT_DETAILINFO" parameterClass="map">
    <selectKey keyProperty="id" resultClass="java.lang.String">
        SELECT SYS_GUID() FROM DUAL
    </selectKey>
    INSERT INTO BL_PATIENT_DETAILINFO(DETAILINFO_ID, ORG_CODE,PATIENT_TYPE,PATIENT_ID,PATHOLOGY_NO,CHECK_POINT,FROZEN_NO,SJCL,SECTION_ID,SECTION_NAME,PATHOLOGY_TYPE,CREATETIME)
    VALUES(#id#, #ORG_CODE#,#PATIENT_TYPE#,#PATIENT_ID#,#PATHOLOGY_NO#,#CHECK_POINT#,#FROZEN_NO#,#SJCL#,#SECTION_ID#,#SECTION_NAME#,#PATHOLOGY_TYPE#,TO_DATE(#CREATETIME#,'YYYY-MM-DD'))
</insert>

JAVA

// 返回插入行的主键
String DETAILINFO_ID = (String) daoBL_PATIENT_DETAILINFO.insertBL_PATIENT_DETAILINFO(paramsMap);

插入成功之后，返回既定的主键值。

如果入参使用的是Map的话，ibatis会自定将主键字段及其值塞到Map当中。

这里的字段名称对应的是<SelectKey>标签当中的keyProperty属性值。

2023年9月24日17:09:52

MYBATIS如何在插入数据后返回主键？

以MYSQL为例。

方式一：

<!--插入数据，返回主键-->
<insert id="addFlinkDataPipeline" parameterType="com.mi.link.api.domain.FlinkDataPipeline" useGeneratedKeys="true" keyProperty="pipelineid">
    insert into flink_data_pipeline
        (pipelineid, pipelinename, databaseidSource, readtype, synctype, synctable, databaseidTarget, databasnameTarget, dataDelete, syncTimestamp, syncTableupdate, dirtyDatanum, failTry, resuleNote, status, createtime)
    values
        (null,#{pipelinename},#{databaseidSource},#{readtype},#{synctype},#{synctable},#{databaseidTarget},#{databasnameTarget},#{dataDelete},#{syncTimestamp},#{syncTableupdate},#{dirtyDatanum},#{failTry}, #{resuleNote}, #{status} ,now())
</insert>

方式二：

<insert id="addFlinkDataPipeline" parameterType="com.mi.link.api.domain.FlinkDataPipeline">
	<selectKey keyProperty="pipelineid" order="AFTER">
        select last_insert_id()
    </selectKey>
    insert into flink_data_pipeline
        (pipelineid, pipelinename, databaseidSource, readtype, synctype, synctable, databaseidTarget, databasnameTarget, dataDelete, syncTimestamp, syncTableupdate, dirtyDatanum, failTry, resuleNote, status, createtime)
    values
        (null,#{pipelinename},#{databaseidSource},#{readtype},#{synctype},#{synctable},#{databaseidTarget},#{databasnameTarget},#{dataDelete},#{syncTimestamp},#{syncTableupdate},#{dirtyDatanum},#{failTry}, #{resuleNote}, #{status} ,now())
</insert>

插入成功后，主键值会被赋给FlinkDataPipeline对象当中的pipelineid属性。

java片段

Boolean addFlinkDataPipeline(FlinkDataPipeline FlinkDataPipeline);

最终调用

posted @ 2021-11-23 16:11 Marydon 阅读(838) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部