系列文章目錄

兼容Oracle與MySQL的那些事
兼容Oracle與MySQL的那些事（分頁問題）
兼容Oracle與MySQL的一些事

文章目錄

系列文章目錄
前言
一、欄位型別差異
二、函式和運算子差異
- 1. 修改資料庫默認配置
- 2. 利用Mybatis的特性
- 3. 是否存在相同的函式
- 4. 自定義同名函式
三、SQL陳述句語法差異
- 1. 資料層代碼兼容
- 2. 使用相同的語法
四、鎖的差異
總結

前言

由于公司目前主要使用的資料庫為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，總結一下常見的型別轉換如下：

oracle	mysql	映射java型別	備注
CHAR	CHAR	String	定長字串
VARCHAR2	VARCHAR	String	變長字串
INTEGER	INT	Integer	整形
BLOB	LONGBLOB	String	二進制字串型別
CLOB	LONGTEXT	String	文本字串型別
NUMBER(P,S)	DECIMAL(M,D)	BigDecimal	定點數型別
DECIMAL(P,S)	DECIMAL(M,D)	BigDecimal	ORACLE中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型別	Oracle	MySQL	備注
String	CHAR	CHAR	定長模式比如一些固定日期格式資料
String	VARCHAR	VARCHAR	非定長模式
String	CLOB/BLOB	LONGTEXT/LONGBLOB	特長字串字符or位元組通常建議按其他方式存盤
Integer	NUMBER(10)	INT	整型
Long	NUMBER(19)	BIGINT	長整型
BigInteger	NUMBER(38)	DECIMAL(38)	超長整型，實際業務中應該比較少
BigDecimal	NUMBER(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有兩種方式可以解決：

既然mysql多了一堆零，那就修改MYSQL資料庫欄位型別

-- VOLUME DECIMAL(31) DEFAULT 1  NULL COMMENT '發行數量',
ALTER TABLE TTRD_TEST_INSTRUMENT MODIFY VOLUME DECIMAL(31) DEFAULT 1  NULL COMMENT '發行數量';

再次操作，問題得到解決，
在這里插入圖片描述
感覺很完美…

修改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編程的話，由于不能利用編譯器的幫助，代碼又過于分散的話，成本高，最后放棄修改代碼選用了上面改資料庫欄位的方法，

修改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;

結果回傳

在這里插入圖片描述
但是MySQL當中沒有這樣的函式，但是可以找到一個同樣功能的運算子&，

SELECT 6 & 3 FROM DUAL;

結果回傳

在這里插入圖片描述

在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針對分頁的兼容就是值得學習的典范，另外就是努力使用相同的語法，簡單的語法，

最后關于鎖這一塊，可以說就是資料庫思維方面的問題了，需要我們從特定資料庫的思維來思考鎖的本質，相比以上問題會更有難度，

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/210904.html

標籤：區塊鏈

上一篇：常見的RuntimeException

下一篇：MySQL之SQL陳述句的基本使用