兼容Oracle与MySQL的一些事

最新推荐文章于 2022-08-15 10:48:09 发布
置顶 最新推荐文章于 2022-08-15 10:48:09 发布
阅读量1.8k 收藏 4
点赞数 2
分类专栏： mysql mybatis oracle 文章标签： mysql java 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_37607945/article/details/109503665
版权
mysql 同时被 3 个专栏收录
61 篇文章 2 订阅
订阅专栏
mybatis
28 篇文章 5 订阅
订阅专栏
oracle
12 篇文章 0 订阅
订阅专栏

系列文章目录

系列文章目录（兼容Oracle与MySQL）

文章目录

前言

由于公司目前主要使用的数据库为Oracle，然后部分兼容MySQL，后期会考虑全部支持Oracle和MySQL。由于二者的各种差异，我们必须有一套可行的方案减少工作量。在兼容Oracle与MySQL的那些事中我们已经仔细讨论过在数据层对多数据库的支持了，接下来的目标就是结合这种支持同时考虑其他手段达到目标了。本文从以下几点来谈一下对兼容考虑：数据库字段类型差异、函数和操作符差异、SQL语句语法差异和锁的差异

以下相关官方文档地址：
oracle官方文档首页地址：https://www.oracle.com/technetwork/cn/indexes/documentation/index.html

Oracle11g官方文档地址：https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
MySQL5.6官方文档地址：https://dev.mysql.com/doc/refman/5.6/en/

博客演示项目案例地址：https://download.csdn.net/download/m0_37607945/13102919

一、字段类型差异

Oracle与MySQL中不少字段类型是相同的，可以直接对应，比如CHAR，INTEGER，DECIMAL，还有一些可以对应，但是要考虑大小问题，比如Oracle中的BLOB字段，与MySQL中的BLOB字段是不能对应的，而应该是LONGBLOB，为什么？Oracle中的BLOB字段最大长度为4G，MySQL中的BLOB只有64M，LONGBLOB才是4G。类似的还有CLOB对应LONGTEXT，而不是TEXT。总结一下常见的类型转换如下：

oraclemysql映射java类型备注
CHARCHARString定长字符串
VARCHAR2VARCHARString变长字符串
INTEGERINTInteger整形
BLOBLONGBLOBString二进制字符串类型
CLOBLONGTEXTString文本字符串类型
NUMBER(P,S)DECIMAL(M,D)BigDecimal定点数类型
DECIMAL(P,S)DECIMAL(M,D)BigDecimalORACLE中DECIMAL内部就是NUMBER

需要说明的是，在我们的系统中基本不会用到DATE或者TIMESTAMP这些字段类型，因为在金融业务中绝大多数使用的是标准格式的日期或时间，所以一般都是使用数据库提供的函数获取时间并转换为标准格式。还有一些其他的类型，要么属于ORACLE特有，比如ROWID，要么MySQL特有，比如SET，这些我们都尽量避免使用。

在使用以上字段类型的时候，可能出现的一个问题就是NUMBER一个类型打天下，的确，Number类型很强大，支持所有数字类型。在我们系统中可以看到很多的字段定义如下

party_id                  NUMBER(31),
term_day                  NUMBER(31),
par_value                 NUMBER(31,8),

映射的java类型为

private Long partyId;
private BigDecimal termDay;
private BigDecimal parValue;

其实一开始可能会让人诧异，如果不考虑精度的话，为啥字段类型不直接用Integer呢？其实Oracle一开始是没有Integer类型的，后来为了兼容才提供Integer，以下摘自官方文档。

官方文档：https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i156865

SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type based on the conversions shown in the tables that follow.
在这里插入图片描述
那么问题来了，是不是以上实体类中termDay字段作为Integer就可以了呢？如果这么想，就有些危险了。因为Number（31）是指能存31位长的数字，Java里面的Long的最大值（‘9223372036854775807’）也只有19位，也就是说两边是严重的不匹配。有人可能发现Oracle里面有个Long字段类型，但是这个Long不但跟Java中的Long不一样，甚至跟你想的完全不一样，它连数字类型都不是。

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility.

LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer.

Oracle中的Long其实是变长字符串类型。那说来说去，termDay只能用BigDecimal类型了吧。这个暂且放一边，partyId为Long类型，而party_id最长为31那是不是也很不匹配了？好像改为以下才合适

party_id                  NUMBER(19),
term_day                  NUMBER(31),
par_value                 NUMBER(31,8),

那么以上取舍真的合适吗？将termDay作为BigDecimal真的合适吗？如果我们给termDay赋值一个小数，然后插入到数据库的时候，Oracle数据库是不会报错的，只是将小数部分舍弃掉（四舍五入）。

It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it.

在这里插入图片描述
以上的考虑还只是开始，因为还要考虑在MySQL中的字段类型，按照一开始提供的类型转换的表格，是很简单的，直接将NUMBER映射为DECIMAL就行了。其实我们不应该这样随意。如果从Oracle->Java->MySQL 或者 Oracle->MySQL->Java都不太好考虑，我们的思路应该是这样的，先定义好这个字段在Java中的类型，然后再分别映射到Oracle和MySQL当中，也就是说我们不应该以哪个数据库为中心，而是以代码为中心。比如以上的字段我们首先根据业务定义Java中的类型

private Long partyId;
private Integer termDay;
private BigDecimal parValue;

这样没有任何问题了吧？可能有人会问为啥parValue不用Double类型，这个比较基础啊，在金融业务系统中，使用Double会有精度问题，比较基础，就不讨论了。然后分别考虑Oracle中的类型

-- Long的最大值'9223372036854775807'长度为19
party_id                  NUMBER(19),
-- Integer的最大值'2147483647'长度为10
term_day                  NUMBER(10),
-- 涉及小数的统一使用NUMBER 并按照业务规定好精度
par_value                 NUMBER(31,8),

MySQL中的类型

-- Long的最大值'9223372036854775807'长度为19
party_id                  BIGINT,
-- Integer的最大值'2147483647'长度为10
term_day                  INT,
-- 涉及小数的统一使用NUMBER 并按照业务规定好精度
par_value                 DECIMAL(31,8),

以上par_value的有效位和精度具体还是得看业务的，不必过大。要知道有效位为31位也是非常大非常大的一个数字，以上的par_value可以存一千亿，单位千亿了… 不知道全球有没有如此多的资产

如果真有一个整形数字非常的大，那么Java类型可以用BigInteger，数据库中用NUMBER(D)和DECIMAL(D)类型。综合以上考虑，解决兼容多个数据库类型的问题的思路应该是先规定好业务代码中字段的类型（目前为Java类型），然后再考虑数据库字段类型。

Java类型OracleMySQL备注
StringCHARCHAR定长模式 比如一些固定日期格式数据
StringVARCHARVARCHAR非定长模式
StringCLOB/BLOBLONGTEXT/LONGBLOB特长字符串 字符or字节 通常建议按其他方式存储
IntegerNUMBER(10)INT整型
LongNUMBER(19)BIGINT长整型
BigIntegerNUMBER(38)DECIMAL(38)超长整型，实际业务中应该比较少
BigDecimalNUMBER(P,S)DECIMAL(M,D)定点数类型 必须保证精度 不能使用浮点型

如果实际使用过程中数字没那么大，Oracle中使用NUMBER存储，有效位可以设置更小，MySQL中可以使用SMALLINT（65535）或者 TINYINT（255），关键还是根据业务需求选择合适的字段类型。

字段类型的映射非常重要，错误的类型映射会埋下地雷，总有一天会出现问题。下面的一个例子是真实遇到的，在Oracle中一切正常，在MySQL中出现数字类型转换错误：

java.lang.NumberFormatException: For input string: "1.00000000"

假设设计的Java对象类型为

package com.example.durid.demo.entity;

import java.io.Serializable;
import java.math.BigDecimal;

public class TtrdTestInstrument implements Serializable {
    /**
     * 金融工具代码
     */
    private String iCode;
    /**
     * 资产类型
     */
    private String aType;
    /**
     * 市场类型
     */
    private String mType;
    /**
     * 到期日期
     */
    private String mtrDate;
    /**
     * 付息频率
     */
    private String term;
    /**
     * 发行机构id
     */
    private Long partyId;
    /**
     * 发行数量
     */
    private String volume;
    /**
     * 是否非标
     */
    private Integer isNonstd;
    /**
     * 发行人编码
     */
    private BigDecimal financerId;

    // setter getter省略
}

对应oracle脚本

-- ----------------------------
-- Table structure for TTRD_TEST_INSTRUMENT
-- ----------------------------
DROP TABLE TTRD_TEST_INSTRUMENT;
CREATE TABLE TTRD_TEST_INSTRUMENT (
  I_CODE VARCHAR2(50 BYTE) NOT NULL ,
  A_TYPE VARCHAR2(20 BYTE) NOT NULL ,
  M_TYPE VARCHAR2(20 BYTE) NOT NULL ,
  MTR_DATE CHAR(10 BYTE) NULL ,
  TERM VARCHAR2(6 BYTE) NULL ,
  PARTY_ID NUMBER(31) NULL ,
  VOLUME NUMBER(31,8) DEFAULT 1  NULL ,
  IS_NONSTD NUMBER(1) NULL ,
  FINANCER_ID NUMBER(31) NULL
)
    LOGGING
    NOCOMPRESS
    NOCACHE;

COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.I_CODE IS '金融工具代码';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.A_TYPE IS '资产类型';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.M_TYPE IS '市场类型';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.MTR_DATE IS '到期日';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.TERM IS '如 1Y，6M，7D';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.PARTY_ID IS '发行机构id';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.VOLUME IS '发行数量';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.IS_NONSTD IS '是否非标';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.FINANCER_ID IS '融资人';

-- ----------------------------
-- Primary Key structure for table TTRD_TEST_INSTRUMENT
-- ----------------------------
ALTER TABLE TTRD_TEST_INSTRUMENT ADD PRIMARY KEY (I_CODE, A_TYPE, M_TYPE);

INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('SYXZGJH01', 'SPT_LBS', 'X_CNBD', '2020-08-03', '7D', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('CFTYTEST01', 'SPT_LBS', 'X_CNBD', '2020-08-03', '7', '59868', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('LLXXMTEST01', 'SPT_LBS', 'X_CNBD', '2020-08-03', '7D', '56838', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('glrllxzc0817', 'SPT_LBS', 'X_CNBD', '2020-07-29', '1D', '29222', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('glrllxzc081702', 'SPT_LBS', 'X_CNBD', '2020-07-30', '1', '60144', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('glrtylzc0817(temp)', 'SPT_LBS', 'X_CNBD', '2020-07-31', '1D', '60144', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('glrtylzc0817', 'SPT_LBS', 'X_CNBD', '2020-07-31', '1D', '60144', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty005', 'SPT_LBS', 'X_CNBD', '2020-10-21', '79', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty001', 'SPT_LBS', 'X_CNBD', '2020-09-29', '57', '60085', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty004', 'SPT_LBS', 'X_CNBD', '2020-11-25', '114', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty003', 'SPT_LBS', 'X_CNBD', '2020-09-30', '58', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty002', 'SPT_LBS', 'X_CNBD', '2020-09-30', '58', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gaegaeg(temp)', 'SPT_LBS', 'X_CNBD', '2020-08-29', '19D', '60245', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('LYtest001', 'SPT_LBS', 'X_CNBD', '2020-08-27', '23D', '60245', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('829300HUSHSKJA', 'SPT_LBS', 'X_CNBD', '2021-06-30', '330D', '57884', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('LYtest001(temp)', 'SPT_LBS', 'X_CNBD', '2020-08-27', '23D', '60245', '1', '1', '6024559232602456024560144602446');

对应mysql脚本

-- ----------------------------
-- Table structure for TTRD_TEST_INSTRUMENT
-- ----------------------------
DROP TABLE IF EXISTS TTRD_TEST_INSTRUMENT;
CREATE TABLE TTRD_TEST_INSTRUMENT (
  I_CODE VARCHAR(50) NOT NULL COMMENT '金融工具代码',
  A_TYPE VARCHAR(20) NOT NULL COMMENT '资产类型',
  M_TYPE VARCHAR(20) NOT NULL COMMENT '市场类型',
  MTR_DATE CHAR(10) COMMENT '到期日',
  TERM VARCHAR(6) COMMENT '如 1Y，6M，7D',
--   PARTY_ID BIGINT COMMENT '发行机构id',   -- 2147483647
  PARTY_ID Long COMMENT '发行机构id',   -- 2147483647
  VOLUME DECIMAL(31,8) DEFAULT 1  NULL COMMENT '发行数量', -- 精度31 标度 8
  IS_NONSTD TINYINT COMMENT '是否非标',  -- 1个字节
  FINANCER_ID DECIMAL(31) COMMENT '融资人' -- 4个字节
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Primary Key structure for table TTRD_TEST_INSTRUMENT
-- ----------------------------
ALTER TABLE TTRD_TEST_INSTRUMENT ADD PRIMARY KEY (I_CODE, A_TYPE, M_TYPE);

INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('SYXZGJH01', 'SPT_LBS', 'X_CNBD', '2020-08-03', '7D', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('CFTYTEST01', 'SPT_LBS', 'X_CNBD', '2020-08-03', '7', '59868', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('LLXXMTEST01', 'SPT_LBS', 'X_CNBD', '2020-08-03', '7D', '56838', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('glrllxzc0817', 'SPT_LBS', 'X_CNBD', '2020-07-29', '1D', '29222', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('glrllxzc081702', 'SPT_LBS', 'X_CNBD', '2020-07-30', '1', '60144', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('glrtylzc0817(temp)', 'SPT_LBS', 'X_CNBD', '2020-07-31', '1D', '60144', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('glrtylzc0817', 'SPT_LBS', 'X_CNBD', '2020-07-31', '1D', '60144', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty005', 'SPT_LBS', 'X_CNBD', '2020-10-21', '79', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty001', 'SPT_LBS', 'X_CNBD', '2020-09-29', '57', '60085', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty004', 'SPT_LBS', 'X_CNBD', '2020-11-25', '114', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty003', 'SPT_LBS', 'X_CNBD', '2020-09-30', '58', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gyxty002', 'SPT_LBS', 'X_CNBD', '2020-09-30', '58', '60245', '1', '0', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('gaegaeg(temp)', 'SPT_LBS', 'X_CNBD', '2020-08-29', '19D', '60245', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('LYtest001', 'SPT_LBS', 'X_CNBD', '2020-08-27', '23D', '60245', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('829300HUSHSKJA', 'SPT_LBS', 'X_CNBD', '2021-06-30', '330D', '57884', '1', '1', '6024559232602456024560144602446');
INSERT INTO TTRD_TEST_INSTRUMENT (I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID) VALUES ('LYtest001(temp)', 'SPT_LBS', 'X_CNBD', '2020-08-27', '23D', '60245', '1', '1', '6024559232602456024560144602446');

现在假设从数据库中取一个数据，将其中的volume进行加1操作并修改主键字段，保存到数据库。如下所示

@Override
public boolean add() {
    String iCode = "SYXZGJH01";
    String aType = "SPT_LBS";
    String mType = "X_CNBD";
    TtrdTestInstrument ttrdTestInstrument = ttrdInstrumentMapper.selectByPrimaryKey(iCode, aType, mType);
    ttrdTestInstrument.setiCode(UUID.randomUUID().toString());
    Long aLong = Long.parseLong(ttrdTestInstrument.getVolume()) + 1;
    ttrdTestInstrument.setVolume(String.valueOf(aLong));
    return ttrdInstrumentMapper.insert(ttrdTestInstrument) == 1;
}

这个代码在oracle中是没有任何问题的
在这里插入图片描述
但是在mysql中就会报错。

java.lang.NumberFormatException: For input string: "1.00000000"
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[na:1.8.0_121]
	at java.lang.Long.parseLong(Long.java:589) ~[na:1.8.0_121]
	at java.lang.Long.parseLong(Long.java:631) ~[na:1.8.0_121]
	at com.example.durid.demo.service.impl.InstrumentServiceImpl.add(InstrumentServiceImpl.java:29) ~[classes/:na]

为什么会出现这种问题呢？
当我们去mysql数据库客户端查看数据时，有没有发现什么奇怪的地方，就是这里的volume后面很多的零（Oracle并不会）。
在这里插入图片描述
然后在映射成java对象的时候映射成了String类型，这里是为了模拟方法，实际项目中其实是通过JdbcTemplate查询出Map对象，或者面向map编程，然后将这个字段多处传递最后变成了String，再次需要计算时，业务人员从业务出发（或者直接参照Oracle数据库中的值），这个字段的值只可能是整形，就强制转型，导致了bug。

String iCode = "SYXZGJH01";
String aType = "SPT_LBS";
String mType = "X_CNBD";
Map<String, Object> objectMap = ttrdInstrumentMapper.selectMapByPrimaryKey(iCode, aType, mType);
// 业务中 这个字段不可能为空
String volume = objectMap.get("VOLUME").toString();
// ... 各种业务代码
// 由于业务中这个字段只能是数字类型
Long newVolume = Long.parseLong(volume);

<select id="selectMapByPrimaryKey" parameterType="map" resultType="hashmap">
  select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
  from TTRD_TEST_INSTRUMENT
  where I_CODE = #{iCode,jdbcType=VARCHAR}
    and A_TYPE = #{aType,jdbcType=VARCHAR}
    and M_TYPE = #{mType,jdbcType=VARCHAR}
</select>

这里面既有Java类型与数据库类型不匹配导致的问题，同样也有面向Map而不是面向对象编程的问题。以上的bug有两种方式可以解决：

  1. 既然mysql多了一堆零，那就修改MYSQL数据库字段类型
-- VOLUME DECIMAL(31) DEFAULT 1  NULL COMMENT '发行数量',
ALTER TABLE TTRD_TEST_INSTRUMENT MODIFY VOLUME DECIMAL(31) DEFAULT 1  NULL COMMENT '发行数量';

再次操作，问题得到解决。
在这里插入图片描述
感觉很完美…

  1. 修改Java字段类型
private BigDecimal volume;

对应代码修改

@Override
public boolean add() {
    String iCode = "SYXZGJH01";
    String aType = "SPT_LBS";
    String mType = "X_CNBD";
    TtrdTestInstrument ttrdTestInstrument = ttrdInstrumentMapper.selectByPrimaryKey(iCode, aType, mType);
    ttrdTestInstrument.setiCode(UUID.randomUUID().toString());
    ttrdTestInstrument.setVolume(ttrdTestInstrument.getVolume().add(BigDecimal.ONE));
    return ttrdInstrumentMapper.insert(ttrdTestInstrument) == 1;
}

bug也解决了，其实如果现实中如果是面向map编程的话，由于不能利用编译器的帮助，代码又过于分散的话，成本高，最后放弃修改代码选用了上面改数据库字段的方法。

  1. 修改Java字段类型并同时修改数据库字段类型 与业务匹配

其实最完美的也应该做的不仅仅是修改Java对象字段类型，而且将Oracle和MySQL中的数据库字段类型都修改。

二、函数和操作符差异

Oracle中的函数与MySQL中的操作符和函数差别还是蛮大的。
比如现在存在以下这样的一条SQL语句

SELECT I_CODE||'-'||A_TYPE||'-'||M_TYPE AS INSTRUMET_KEY FROM TTRD_TEST_INSTRUMENT WHERE I_CODE = 'SYXZGJH01' AND A_TYPE = 'SPT_LBS' AND M_TYPE = 'X_CNBD';

在Oracle数据库中是没有问题的
在这里插入图片描述
但是MySQL中不会报错，但是结果为0，不是我们想要的结果（字符串拼接功能）
在这里插入图片描述
因为在默认情况下，MySQL当中是不支持通过||进行拼接的，因为在mysql中 || 是逻辑或操作符。

1. 修改数据库默认配置

但可以通过修改配置满足

By default, || is a logical OR operator. With PIPES_AS_CONCAT enabled, || is string concatenation,with a precedence between ^ and the unary operators.

-- 修改模式
SET GLOBAL sql_mode='ANSI';
-- 查询模式
SELECT @@global.sql_mode;
-- 默认值
SET GLOBAL sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

可以通过修改模式，让MySQL将||作为拼接符。

2. 利用Mybatis的特性

如果不修改数据库的默认配置，也可以，应该MySQL提供了CONCAT函数用于数据库的拼接
在这里插入图片描述
可以利用MyBatis配置文件中我们可以按照如下方式处理。如果对以下代码不熟悉，参考这里：（兼容Oracle与MySQL的那些事）

<select id="concatByPrimaryKey" parameterType="map" resultType="string">
  select
  <if test="_databaseId == 'mysql'">
    CONCAT(I_CODE,'-',A_TYPE,'-',M_TYPE)
  </if>
  <if test="_databaseId == 'oracle'">
    I_CODE||'-'||A_TYPE||'-'||M_TYPE
  </if>
  AS INSTRUMET_KEY
  from TTRD_TEST_INSTRUMENT
  where I_CODE = #{iCode,jdbcType=VARCHAR}
    and A_TYPE = #{aType,jdbcType=VARCHAR}
    and M_TYPE = #{mType,jdbcType=VARCHAR}
</select>

请求Oracle数据库
在这里插入图片描述
请求MySQL数据库
在这里插入图片描述

3. 是否存在相同的函数

对于CONCAT这个函数，其实Oracle也是有的，但是只能接收两个参数，MySQL是任意多个，如果是两个参数的情况下，通过CONCAT函数其实就能满足要求了，Oracle不需要使用||来拼接了。

SELECT CONCAT(I_CODE, MTR_DATE) AS IDATE FROM TTRD_TEST_INSTRUMENT 
WHERE I_CODE = 'SYXZGJH01' AND A_TYPE = 'SPT_LBS' AND M_TYPE = 'X_CNBD';

在这里插入图片描述

4. 自定义同名函数

如果不想通过以上各种方式解决，也可以通过创建同名函数的方式解决问题。比如在Oracle中存在一个位与的函数bitadd.用于计算数字的位与操作

SELECT BITAND(6,3) FROM DUAL;

结果返回

2

在这里插入图片描述
但是MySQL当中没有这样的函数，但是可以找到一个同样功能的操作符&。

SELECT 6 & 3 FROM DUAL;

结果返回

2

在这里插入图片描述

在MYSQL中创建一个同名同功能的函数

DELIMITER //
CREATE OR REPLACE FUNCTION BITAND(v1 NUMERIC,v2 NUMERIC) RETURNS NUMERIC
RETURN v1 & v2;
//
DELIMITER ;

SELECT BITAND(6,3) FROM DUAL;

结果如下：
在这里插入图片描述
这样在数据库层做到了统一，在代码数据层直接使用BITAND函数就好了。

三、SQL语句语法差异

有兴趣可以参考这个博客：Oracle和MySQL语法区别

1. 数据层代码兼容

如果说到数据库语法的差异，首先不得不谈分页语句的差别了。在兼容Oracle与MySQL的那些事（分页问题）中我们详细介绍了MyBatis、MyBatis-PageHelper、MyBatis-Plus中针对分页的解决方案。其实后者都是异曲同工的，这里给我们对数据库语句语法差异的最关键思路其实是通过数据层拦截器来解决，屏蔽复杂性。

2. 使用相同的语法

其实SQL也是有规范的，无论Oracle、MySQL都会遵循，比如SQL-92、SQL:1999、SQL:2003，SQL:2008，但是都在标准的基础上加入了自己的元素，另一方面，这些标准越到后面内容越来越多，几乎没人能全部掌握，大家可以参考这个博客：SQL标准简介了解一下。如果说一个标准比较简单，只有一两百页的话，个人觉得遵循这个标准将是非常不错的，可惜，我们没有精力去研究这个标准（专业DBA除外吧）。那么我们该如何呢？其实就是尽最大努力使用简单的语法。比如针对both as a target for 'UPDATE'(参考博客)的解决方案，使用相同的语法对数据库兼容非常有用。

四、锁的差异

之所以要把锁的差异单独来谈，就是这个差异与上面不一样，无论是函数不一样，语法不一样错误很容易就会被发现（在SQL客户端执行一下就好了）。但是针对锁而言，就没那么容易了。

另一方面，针对锁的使用，还涉及到事务，如果不存在事务，首先锁其实是没有效果的，另外事务还有隔离性区别。Oracle默认的隔离级别为读已提交READ COMMITED，MySQL默认的隔离级别为可重复读REPEATABLE READ，在可重复读的情况下，可能两个事务同时去获取锁，一个事务获取锁成功修改了数据，第二个事务再获取锁，但因为可重复读，不能读到第一个事务修改的值，第二个事务读到原来的值再修改数据库，导致第一个事务其实没有起作用，通常我们首先要修改数据库隔离界别为读已提交。以下探讨的都是在读已提交的隔离级别下的。

假设存在一对父子表TTRD_TEST_PARENT和TTRD_TEST_CHILD，第一个表存储父交易信息，而子交易存储父交易对应的多个子交易信息。

Oracle脚本如下

-- ----------------------------
-- Table structure for TTRD_TEST_PARENT
-- ----------------------------
DROP TABLE TTRD_TEST_PARENT;
CREATE TABLE TTRD_TEST_PARENT
(
    SYSORDID    NUMBER     NOT NULL,
    IS_NONSTD   NUMBER(1)  NULL,
    FINANCER_ID NUMBER(31) NULL
) LOGGING NOCOMPRESS NOCACHE;

COMMENT ON COLUMN TTRD_TEST_PARENT.SYSORDID IS '主交易号';

COMMENT ON COLUMN TTRD_TEST_PARENT.IS_NONSTD IS '是否非标';

COMMENT ON COLUMN TTRD_TEST_PARENT.FINANCER_ID IS '融资人';

-- ----------------------------
-- Primary Key structure for table TTRD_TEST_PARENT
-- ----------------------------
ALTER TABLE TTRD_TEST_PARENT
    ADD PRIMARY KEY (SYSORDID);

INSERT INTO TTRD_TEST_PARENT (SYSORDID, IS_NONSTD, FINANCER_ID)
VALUES ('200001', '0', '60245');
INSERT INTO TTRD_TEST_PARENT (SYSORDID, IS_NONSTD, FINANCER_ID)
VALUES ('200002', '1', '60144');
INSERT INTO TTRD_TEST_PARENT (SYSORDID, IS_NONSTD, FINANCER_ID)
VALUES ('200003', '0', '60245');
-- ----------------------------
-- Table structure for TTRD_TEST_CHILD
-- ----------------------------
DROP TABLE TTRD_TEST_CHILD;
CREATE TABLE TTRD_TEST_CHILD (
    SYSORDID NUMBER NOT NULL,
    PARENT_SYSORDID NUMBER NOT NULL,
    I_CODE VARCHAR2 (50 BYTE) NOT NULL,
    A_TYPE VARCHAR2 (20 BYTE) NOT NULL,
    M_TYPE VARCHAR2 (20 BYTE) NOT NULL,
    PARTY_ID NUMBER NULL,
    VOLUME NUMBER (31, 8) DEFAULT 1 NULL
) LOGGING NOCOMPRESS NOCACHE;

COMMENT ON COLUMN TTRD_TEST_CHILD.SYSORDID IS '交易号';

COMMENT ON COLUMN TTRD_TEST_CHILD.PARENT_SYSORDID IS '父交易号';

COMMENT ON COLUMN TTRD_TEST_CHILD.I_CODE IS '金融工具代码';

COMMENT ON COLUMN TTRD_TEST_CHILD.A_TYPE IS '资产类型';

COMMENT ON COLUMN TTRD_TEST_CHILD.M_TYPE IS '市场类型';

COMMENT ON COLUMN TTRD_TEST_CHILD.PARTY_ID IS '发行机构id';

COMMENT ON COLUMN TTRD_TEST_CHILD.VOLUME IS '数量';

-- ----------------------------
-- Primary Key structure for table TTRD_TEST_CHILD
-- ----------------------------
ALTER TABLE TTRD_TEST_CHILD ADD PRIMARY KEY (SYSORDID);

CREATE INDEX PARENT_SYSORDID_IDX ON TTRD_TEST_CHILD (PARENT_SYSORDID);

INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000023', '200001', 'SYXZGJH01', 'SPT_LBS', 'X_CNBD', '60245', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000024', '200001', 'CFTYTEST01', 'SPT_LBS', 'X_CNBD', '59868', '1500');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000025', '200001', 'LLXXMTEST01', 'SPT_LBS', 'X_CNBD', '56838', '1200');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000026', '200001', 'glrllxzc0817', 'SPT_LBS', 'X_CNBD', '29222', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000027', '200002', 'glrllxzc081702', 'SPT_LBS', 'X_CNBD', '60144', '1200');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000028', '200002', 'glrtylzc0817(temp)', 'SPT_LBS', 'X_CNBD', '60144', '1100');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000029', '200002', 'glrtylzc0817', 'SPT_LBS', 'X_CNBD', '60144', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000030', '200002', 'gyxty005', 'SPT_LBS', 'X_CNBD', '60245', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000031', '200003', 'gyxty001', 'SPT_LBS', 'X_CNBD', '60085', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000032', '200003', 'gyxty004', 'SPT_LBS', 'X_CNBD', '60245', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000033', '200003', 'gyxty003', 'SPT_LBS', 'X_CNBD', '60245', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000034', '200003', 'gyxty002', 'SPT_LBS', 'X_CNBD', '60245', '1000');

MySQL脚本如下

-- ----------------------------
-- Table structure for TTRD_TEST_PARENT
-- ----------------------------
DROP TABLE IF EXISTS TTRD_TEST_PARENT;
CREATE TABLE TTRD_TEST_PARENT
(
    SYSORDID    DECIMAL     NOT NULL COMMENT '主交易号',
    IS_NONSTD   DECIMAL(1)  NULL COMMENT '是否非标',
    FINANCER_ID DECIMAL(31) NULL COMMENT '融资人'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Primary Key structure for table TTRD_TEST_PARENT
-- ----------------------------
ALTER TABLE TTRD_TEST_PARENT ADD PRIMARY KEY (SYSORDID);

INSERT INTO TTRD_TEST_PARENT (SYSORDID, IS_NONSTD, FINANCER_ID)
VALUES ('200001', '0', '60245');
INSERT INTO TTRD_TEST_PARENT (SYSORDID, IS_NONSTD, FINANCER_ID)
VALUES ('200002', '1', '60144');
INSERT INTO TTRD_TEST_PARENT (SYSORDID, IS_NONSTD, FINANCER_ID)
VALUES ('200003', '0', '60245');

-- ----------------------------
-- Table structure for TTRD_TEST_CHILD
-- ----------------------------
DROP TABLE IF EXISTS TTRD_TEST_CHILD;
CREATE TABLE TTRD_TEST_CHILD (
    SYSORDID DECIMAL NOT NULL COMMENT '交易号',
    PARENT_SYSORDID DECIMAL NOT NULL COMMENT '父交易号',
    I_CODE VARCHAR (50) NOT NULL COMMENT '金融工具代码',
    A_TYPE VARCHAR (20) NOT NULL COMMENT '资产类型',
    M_TYPE VARCHAR (20) NOT NULL COMMENT '市场类型',
    PARTY_ID DECIMAL NULL COMMENT '数量',
    VOLUME DECIMAL (31, 8) DEFAULT 1 NULL COMMENT '主交易号'
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Primary Key structure for table TTRD_TEST_CHILD
-- ----------------------------
ALTER TABLE TTRD_TEST_CHILD ADD PRIMARY KEY (SYSORDID);
ALTER TABLE TTRD_TEST_CHILD ADD INDEX  PARENT_SYSORDID_IDX(PARENT_SYSORDID);

INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000023', '200001', 'SYXZGJH01', 'SPT_LBS', 'X_CNBD', '60245', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000024', '200001', 'CFTYTEST01', 'SPT_LBS', 'X_CNBD', '59868', '1500');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000025', '200001', 'LLXXMTEST01', 'SPT_LBS', 'X_CNBD', '56838', '1200');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000026', '200001', 'glrllxzc0817', 'SPT_LBS', 'X_CNBD', '29222', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000027', '200002', 'glrllxzc081702', 'SPT_LBS', 'X_CNBD', '60144', '1200');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000028', '200002', 'glrtylzc0817(temp)', 'SPT_LBS', 'X_CNBD', '60144', '1100');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000029', '200002', 'glrtylzc0817', 'SPT_LBS', 'X_CNBD', '60144', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000030', '200002', 'gyxty005', 'SPT_LBS', 'X_CNBD', '60245', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000031', '200003', 'gyxty001', 'SPT_LBS', 'X_CNBD', '60085', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000032', '200003', 'gyxty004', 'SPT_LBS', 'X_CNBD', '60245', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000033', '200003', 'gyxty003', 'SPT_LBS', 'X_CNBD', '60245', '1000');
INSERT INTO TTRD_TEST_CHILD (SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME) VALUES ('10000034', '200003', 'gyxty002', 'SPT_LBS', 'X_CNBD', '60245', '1000');

对应实体类

public class TtrdTestParent {
    /**
     * 父交易号
     */
    private Long sysordid;
    /**
     * 是否非标
     */
    private BigDecimal isNonstd;
    /**
     * 投资人信息
     */
    private BigDecimal financerId;

    // setter getter省略 
}

public class TtrdTestChild {
    /**
     * 子交易单号
     */
    private Long sysordid;
    /**
     * 父交易单号
     */
    private Long parentSysordid;
    /**
     * 金融工具代码
     */
    private String iCode;
    /**
     * 金融工具资产类型
     */
    private String aType;
    /**
     * 金融工具市场类型
     */
    private String mType;
    /**
     * 发行机构ID
     */
    private BigDecimal partyId;
    /**
     * 持有数量
     */
    private BigDecimal volume;

    // setter getter省略
}

现在假设存在这样的一个业务，需要对一个父交易均分一定数量的金融工具到子交易当中，对应的接口如下

package com.example.durid.demo.service;

import com.example.durid.demo.entity.TtrdTestChild;

import java.math.BigDecimal;
import java.util.List;

public interface OrderService {

    /**
     * 给一个父交易分配数量 均分到所有子交易
     *
     * @param parentSysOrdId 父交易编号
     * @param allocatVol     分配额度
     * @return 子交易列表
     */
    List<TtrdTestChild> allocate(Long parentSysOrdId, BigDecimal allocatVol);

}

定义好接口之后，我们就需要实现这个接口了，这个时候我们可能会想到并发问题，如果两个线程同时操作了一个父交易怎么办？所以必须通过加锁来完成。因为需要对子表中同一个父交易所有的子交易进行加锁。很容易写出如下的数据层代码

/**
 * 根据父交易单号查询所有的子交易同时上锁
 * @param parentSysordid 父交易单号
 * @return 父交易对应的素有子交易列表
 */
List<TtrdTestChild> selectByParentIdByLock(Long parentSysordid);

<select id="selectByParentIdByLock" parameterType="java.lang.Long" resultMap="BaseResultMap">
  select SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME
  from TTRD_TEST_CHILD where PARENT_SYSORDID = #{parentSysordid,jdbcType=DECIMAL} for update
</select>

然后再分配额度并更新数据库，对应业务代码如下

package com.example.durid.demo.service.impl;

import com.example.durid.demo.entity.TtrdTestChild;
import com.example.durid.demo.mapper.TtrdTestChildMapper;
import com.example.durid.demo.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    private TtrdTestChildMapper ttrdTestChildMapper;

    @Transactional
    @Override
    public List<TtrdTestChild> allocate(Long parentSysOrdId,BigDecimal allocatVol) {
        List<TtrdTestChild> childList = ttrdTestChildMapper.selectByParentIdByLock(parentSysOrdId);
        try {
            // 模拟具体业务时间
            Thread.sleep(500);
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
        if (CollectionUtils.isEmpty(childList)) {
            return new ArrayList<>();
        }
        // 分配额度
        BigDecimal avgAllocat = allocatVol.divide(BigDecimal.valueOf(childList.size()), 2, BigDecimal.ROUND_HALF_UP);
        int i = ttrdTestChildMapper.updateByParentId(parentSysOrdId, allocatVol);
        if (i != childList.size()) {
            throw new RuntimeException("更新失败");
        }

        return ttrdTestChildMapper.selectByParentIdNoLock(parentSysOrdId);
    }
}

另外两个数据层接口如下

/**
* 无锁根据父交易单号查询所有的子交易
*
* @param parentSysordid 父交易单号
* @return 父交易对应的素有子交易列表
*/
List<TtrdTestChild> selectByParentIdNoLock(Long parentSysordid);

/**
* 更新父交易对应的所有子交易的数量
*
* @param parentSysordid 父交易单号
* @param addVolume      增加的数量
* @return 更新的数据条数
*/
int updateByParentId(@Param("parentSysordid") Long parentSysordid, @Param("addVolume") BigDecimal addVolume);

<select id="selectByParentIdNoLock" parameterType="java.lang.Long" resultMap="BaseResultMap">
  select SYSORDID, PARENT_SYSORDID, I_CODE, A_TYPE, M_TYPE, PARTY_ID, VOLUME
  from TTRD_TEST_CHILD where PARENT_SYSORDID = #{parentSysordid,jdbcType=DECIMAL}
</select>
<!--  updateByParentId-->
<update id="updateByParentId">
  UPDATE TTRD_TEST_CHILD SET VOLUME = VOLUME + #{addVolume,jdbcType=DECIMAL} WHERE PARENT_SYSORDID = #{parentSysordid,jdbcType=DECIMAL}
</update>

在控制层模拟并发

/**
 * 数量分配 http://localhost:8083/order/allocate?db=oracle
 *
 * @return 包含的子交易列表
 */
@RequestMapping("/allocate")
public List<TtrdTestChild> allocate() {
    DataSourceTypeEnum dataSourceTypeEnum = DataSouceTypeContext.get();
    List<TtrdTestChild> all = new ArrayList<>();
    Long[] parentSysOrdIds = new Long[]{200001L, 200002L};
    return Arrays.asList(parentSysOrdIds).parallelStream().map(parentSysOrdId -> {
                // 再重启线程 与Servlet线程非同一个线程
                DataSouceTypeContext.set(dataSourceTypeEnum);
                return orderService.allocate(parentSysOrdId, new BigDecimal(300));
            }
    ).flatMap(Collection::stream).collect(Collectors.toList());
}

如果我们在浏览器请求：http://localhost:8083/order/allocate?db=oracle，不会有任何异常。
在这里插入图片描述
但如果请求：http://localhost:8083/order/allocate?db=mysql
在这里插入图片描述
出现了异常

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in file [D:\20200702\simpe-demo-diffdb\target\classes\sqlmapper\TtrdTestChildMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE TTRD_TEST_CHILD SET VOLUME = VOLUME + ? WHERE PARENT_SYSORDID = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_121]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_121]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_121]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_121]

这是怎么回事呢？我们模拟的是两个不同的父交易，就算上锁，也不可能互相影响呀。MySQL也是有行锁的。 如果没有遇到过类似的问题，可能需要花费很多的时间来查找。首先是索引问题，如果没有索引会导致MySQL的行锁升级为表锁导致问题。在这里并不是索引的问题，而是行锁升级表锁的问题。问题就出在我们针对同一个父交易的子交易上锁时，其实锁定了多行数据。在MySQL当中，如果锁定的数据条数在一个表中占有一定的比例，最终会从行锁升级为表锁，比如上面的案例当中这个比例在33%。如果来修改这个bug呢？其实很简单，我们去锁父表TTRD_TEST_PARENT中对应的那一条数据，而不是锁定多条数据。Oracle中倒并不会升级为表锁，所以不会出现任何问题，MySQL应该是出于效率问题才有这样的考虑吧。但是我们在今后，还是应该通过锁定单行数据的方式来锁表，如果要锁多行，通过冗余表、汇总表转变为锁定单行数据的方式。

参考博客：MySQL死锁问题

总结

在上面我们通过几个方法来谈了兼容Oracle与MySQL的一些事，但是在真实项目中问题远比以上要多很多，其实我们最关键的还是要养成良好的思维和规范，字段类型的不同可以从代码层出发，而不是数据库出发，我们现在使用的为Java，Java是一个面向对象的语言，不是面向Map，所以定义好对象是重中之重，而不能为了一时简单，否则将来花的精力会更多，另外在函数不同方面，我们也提供了四种方案：

  • 修改数据库配置满足功能（不推荐）
  • 利用MyBaits的特性（推荐）
  • 使用相同功能的函数（推荐）
  • 自定义同名函数（推荐，但要求函数编写能力）

在遇到SQL语法差异，也可以通过业务层代码来兼容，比如MyBatis-PageHelper针对分页的兼容就是值得学习的典范。另外就是努力使用相同的语法，简单的语法。

最后关于锁这一块，可以说就是数据库思维方面的问题了，需要我们从特定数据库的思维来思考锁的本质，相比以上问题会更有难度。

lang20150928
关注
  • 2
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 7
    评论
专栏目录
兼容mysql和oracle的数据库_Web项目--------原Oracle数据库的项目同时兼容MySql
weixin_30070663的博客
01-29 655
原Oracle数据库的项目同时兼容MySql步骤:(一)修改资源配置文件applicationContext-dataSource.xml的数据库连接Oracle数据库中加上from dual的原因:Oracle数据库中自带了一个虚拟表dual，这个的作用是测试数据库是否正常使用。(二)添加一个类用于获取资源配置文件中当前使用的数据库类型以及该方法继承SqlMapClientDaoSupportE...
Oracle GoldenGate 21.3 ，oracle to oracle and mysql to mysql
最新发布
12-17
Oracle GoldenGate 21.3 是一款强大的数据复制和实时数据集成工具，支持在Oracle数据库与MySQL数据库之间进行数据同步。在21.3版本中，GoldenGate引入了微服务架构，这使得部署和管理更加灵活，提高了系统的可扩展性...
mysql数据迁移至oracle中的几个问题（2）
weixin_43575357的博客
05-29 378
oracle数据类型：varchar2,clob 在迁移数据的过程中，发现报长度错误。经排查原来有个字段在mysql中设置为text长文本类型，而在oracle设置的varchar2，长度设置的2000。 这时候出现了长度不够的情况，重新设置varchar2长度为4000。长度还是不够，可是varchar2最大长度就是4000，怎么办呢，使用clob吧。 在调整varchar2为clob的时候发现调整不了，经查字段不能被修改为clob类型，只能一开始新建设置为clob。吐了，只有重新建表了（真想把这些写小作
mysql和Oracle数据库兼容
Eat_a_meal的博客
12-24 983
1.需求说明 注：此文档并不是程序运行过程中动态的切换数据源，而是需要手动修改yml文件，选择所要使用的数据库。 2.实现步骤 1):修改配置文件 每个项目的application.yml文件中只能有一个数据源，否则运行报错，这里采用的是新建两个yml文件(application-mysql.yml和application-oracle.yml),如下图 然后将各自的数据源信息编写进各自的yml...
mysql兼容oracle_jfinal2.2之mysql兼容oracle浅谈
weixin_39612499的博客
01-19 191
《xxxx系统》v2.3数据库mysql兼容oracle方案升级概述对《xxxx系统》v2.3进行重构，使之兼容oracle数据库，oracle版本12cR2。升级步骤系统可行性分析可以升级。确定升级方案重构代码，对mysql于oracle存在兼容性问题的地方进行重构。实施升级修改不兼容的代码、sql语句、个性化函数采用兼容或替代方案。升级后测试测试oracle环境所有系统功能。兼容点分页查询关键...
【LightDB】mysql和oracle函数兼容
C45117的博客
08-15 608
LightDB,SQL兼容，MySQL,Oracle
MYSQL与ORACLE数据类型对照
qq_41482600的博客
06-13 6320
mysql与oracle字段类型对照
oracle 到mysql转换工具
01-17
Oracle到MySQL转换工具是一款专为数据库迁移设计的实用软件，主要功能是将Oracle数据库中的数据结构和SQL语句转换成MySQL兼容的格式，方便用户在不同的数据库系统间进行数据迁移。这款工具通常适用于那些需要从...
MySQL与Oracle差异比较之三 函数
09-09
在数据库管理领域，MySQL和Oracle都是广泛使用的数据库系统，但它们在语法和功能上存在一些差异。本篇文章将深入探讨这两个数据库系统在函数使用上的区别，帮助开发者更好地理解和选择适合的工具。 1. 数字函数： ...
oracle迁移mysql自增序列问题
12-14
本问题主要涉及从Oracle迁移到MySQL时，如何处理自增序列（Identity或Sequence）的兼容性问题。Oracle数据库中，自增序列通常通过Sequence对象实现，而MySQL则通过在表定义中设置`AUTO_INCREMENT`属性来实现自增主键...
mysql和Oracle在对clob和blob字段的处理
04-10
NULL 博文链接：https://shihuan830619.iteye.com/blog/1662937
MySQL与Oracle SQL语言差异比较一览
09-09
MySQL和Oracle都是广泛使用的SQL数据库管理系统，它们在SQL语法上存在一些显著的差异。下面将详细探讨这些差异，以便于理解和适应这两种系统的不同。 1. 数据类型： - 在Oracle中，数字类型通常使用`NUMBER`，而在...
mysql与oracle数据类型转换
ofofw的专栏
03-19 3001
BIGINT NUMBER(19, 0) BIT RAW BLOB BLOB, RAW CHAR CHAR DATE DATE DATETIME DATE DECIMAL FLOAT (24) DOUBLE FLOAT (24) DOUBL
java连接数据库（oracle and mysql）
路漫漫其修远兮，吾将上下而求索。
04-25 608
1. 确认oracle服务打开 在任务管理器—服务中找到下面两个服务OracleOraDb11g_home1TNSListener和OracleServiceORCL正在运行，否则右键启动服务。 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql...
oracle数据库mysql代码,Java代码链接mysql数据库和oracle数据库
weixin_36037417的博客
04-04 143
非常基础的代码，导入相应数据库的驱动包，代码如下：package com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit....
java.lang.NumberFormatException: For input string:
黑色头发
08-12 4493
java.lang.NumberFormatException: For input string: "" 这个错误是因为用到了类型转换 的包装类，有时转换会出现这种异常的，所以最好要用try/catch包起来。 jdk1.5下 [code="java"] java.text.DecimalFormat df = new DecimalForma...
mysql 大对象存取_mysql,sqlserver,oracle三种数据库的大对象存取 （转）
weixin_42527401的博客
02-05 141
mysql 大对象存取:类型一般应该用mediumblod,blob只能存2的16次方个byte,mediumblod是24次方,一般来说够用了.longblob是32次方有些大.MYSQL默认配置只能存1M大小的文件,要修改配置,WIN版本的在mysql.ini文件中修改max_allowed_packet,net_buffer_length等几个参数,或直接SET GLOBAL varName...
评论 7
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

lang20150928

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值