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(#id#,#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,先执行插入,再调用该函数,返回的就是此次插入数据,所用的主键。
为什么不能使用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做主键
<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>
2022年8月14日15:30:35
IBTATIS/MYBATIS如何在插入数据后返回主键?
前提:
要想IBTATIS或MYBATIS在成功插入数据后返回主键,我们需要在插入数据之前,确定主键。
使用场景:
当一个业务需要插入多张表,且表与表之间使用主键进行关联。
不管是Oracle还是MySQL都能实现,下面以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属性值。
写在最后
哪位大佬如若发现文章存在纰漏之处或需要补充更多内容,欢迎留言!!!
作者:Marydon