编程知识 cdmana.com

Compatibility of Oracle and MySQL

List of articles

compatible Oracle And MySQL Those things
compatible Oracle And MySQL Those things ( Paging problem )
compatible Oracle And MySQL Some things about


Preface

As the main database used by the company at present is Oracle, And then partially compatible MySQL, All support will be considered later Oracle and MySQL. Because of the differences between them , We must have a workable plan to reduce the workload . stay compatible Oracle And MySQL Those things We have discussed in detail the support for multiple databases in the data layer , The next goal is to combine this support with other means to achieve the goal . This article talks about compatibility considerations from the following points : Database field type differences 、 The difference between functions and operators 、SQL Syntactic differences and lock differences

The following official document address :
oracle Official document home address :https://www.oracle.com/technetwork/cn/indexes/documentation/index.html

Oracle11g Official document address :https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
MySQL5.6 Official document address :https://dev.mysql.com/doc/refman/5.6/en/

Blog demo project case address :https://download.csdn.net/download/m0_37607945/13102919


One 、 Field type differences

Oracle And MySQL Many of the field types in are the same , It can directly correspond to , such as CHAR,INTEGER,DECIMAL, There are also some that can correspond to , But consider size , such as Oracle Medium BLOB Field , And MySQL Medium BLOB Fields cannot correspond to , It should be LONGBLOB, Why? ?Oracle Medium BLOB The maximum length of the field is 4G,MySQL Medium BLOB Only 64M,LONGBLOB It's just 4G. Similar to that CLOB Corresponding LONGTEXT, instead of TEXT. To sum up, the common type conversions are as follows :

oracle mysql mapping java type remarks
CHAR CHAR String Fixed length string
VARCHAR2 VARCHAR String Variable length string
INTEGER INT Integer plastic
BLOB LONGBLOB String Binary string type
CLOB LONGTEXT String Text string type
NUMBER(P,S) DECIMAL(M,D) BigDecimal Fixed point number type
DECIMAL(P,S) DECIMAL(M,D) BigDecimal ORACLE in DECIMAL The internal is NUMBER

It should be noted that , We don't use it in our system DATE perhaps TIMESTAMP These field types , Because in the financial business, the vast majority of the use of standard format date or time , So we usually use the function provided by the database to get the time and convert it to the standard format . There are other types of , Or belong to ORACLE specific , such as ROWID, or MySQL specific , such as SET, We try to avoid using these .

When using the above field types , One of the possible problems is NUMBER One type dominates the world , You bet ,Number The type is powerful , Supports all digital types . In our system, we can see that many fields are defined as follows

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

The mapping of java The type is

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

In fact, it may be surprising at first , If you don't think about precision , Why is the field type not directly used Integer Well ? Actually Oracle Not at first Integer Type of , Later, it was offered for compatibility Integer, The following is an excerpt from official documents .

Official documents :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.
 Insert picture description here
So here comes the question , Is it in the above entity class termDay Field as Integer That's it ? If you think so , It's a little dangerous . because Number(31) It means to be able to save 31 A digit long number ,Java Inside Long The maximum of (‘9223372036854775807’) There is only a 19 position , In other words, there is a serious mismatch between the two sides . One may find that Oracle There's a Long Field type , But this Long Not only with Java Medium Long Dissimilarity , It's totally different from what you think , It's not even a number type .

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 Medium Long It's actually a variable length string type . That's about it ,termDay Only use BigDecimal Type it . Let's put this aside ,partyId by Long type , and party_id The longest is 31 Isn't that a big mismatch ? It seems to be appropriate to change it to the following

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

So is the above trade-off really appropriate ? take termDay As BigDecimal Is it really suitable ? If we give termDay Assign a decimal , And then insert it into the database ,Oracle The database will not report errors , It's just that the fraction is discarded ( rounding ).

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.

 Insert picture description here
The above considerations are just the beginning , Because you have to think about MySQL Field type in , Follow the type conversion table provided at the beginning , It's very simple , Direct will NUMBER It maps to DECIMAL That's it . In fact, we should not be so casual . If from Oracle->Java->MySQL perhaps Oracle->MySQL->Java It's not very easy to think about , Our thinking should be like this , First define this field in Java The type of , And then map them to Oracle and MySQL among , That is to say, we Which database should not be the center , It's code centric . For example, the above fields are defined according to the business Java The type of

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

There's no problem with that ? One might ask why parValue no need Double type , This is more basic , In the financial business system , Use Double There will be accuracy problems , More basic , Let's not discuss . Then consider separately Oracle The type of

-- Long The maximum of '9223372036854775807' The length is 19
party_id                  NUMBER(19),
-- Integer The maximum of '2147483647' The length is 10
term_day                  NUMBER(10),
--  Involving the uniform use of decimals NUMBER  And in accordance with the business requirements of good precision 
par_value                 NUMBER(31,8),

MySQL The type of

-- Long The maximum of '9223372036854775807' The length is 19
party_id                  BIGINT,
-- Integer The maximum of '2147483647' The length is 10
term_day                  INT,
--  Involving the uniform use of decimals NUMBER  And in accordance with the business requirements of good precision 
par_value                 DECIMAL(31,8),

above par_value It depends on the business , It doesn't have to be too big . You know that the significant bit is 31 The bit is also a very large number , The above par_value You can save 100 billion , It's a hundred billion units … I wonder if there are so many assets in the world

If a number is really big , that Java Type can be used BigInteger, In the database NUMBER(D) and DECIMAL(D) type . Take all the above into consideration , The solution to the problem of compatibility of multiple database types should be First specify the type of the field in the business code ( At present for Java type ), Then consider the database field type .

Java type Oracle MySQL remarks
String CHAR CHAR Fixed length mode For example, some fixed date format data
String VARCHAR VARCHAR Non fixed length mode
String CLOB/BLOB LONGTEXT/LONGBLOB Special string character or byte Other storage methods are generally recommended
Integer NUMBER(10) INT integer
Long NUMBER(19) BIGINT Long integer
BigInteger NUMBER(38) DECIMAL(38) Super long integers , There should be less in actual business
BigDecimal NUMBER(P,S) DECIMAL(M,D) Fixed point number type Accuracy must be guaranteed You can't use floating point

If the number is not so big in actual use ,Oracle Use in NUMBER Storage , Significant bits can be set smaller ,MySQL Can be used in SMALLINT(65535) perhaps TINYINT(255), The key is to choose the appropriate field type according to the business requirements .

The mapping of field types is very important , Wrong type mapping can lay a mine , There will be problems one day . The following example is a real encounter , stay Oracle Everything is normal in , stay MySQL A number type conversion error has occurred in :

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

Suppose it's designed Java The object type is

package com.example.durid.demo.entity;

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

public class TtrdTestInstrument implements Serializable {
   
     
    /**
     *  Financial instrument code 
     */
    private String iCode;
    /**
     *  Asset type 
     */
    private String aType;
    /**
     *  Market type 
     */
    private String mType;
    /**
     *  Due date 
     */
    private String mtrDate;
    /**
     *  Interest payment frequency 
     */
    private String term;
    /**
     *  The issuer id
     */
    private Long partyId;
    /**
     *  Issue number 
     */
    private String volume;
    /**
     *  Whether it is non-standard or not 
     */
    private Integer isNonstd;
    /**
     *  Publisher code 
     */
    private BigDecimal financerId;

    // setter getter Omit 
}

Corresponding oracle Script

-- ----------------------------
-- 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 ' Financial instrument code ';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.A_TYPE IS ' Asset type ';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.M_TYPE IS ' Market type ';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.MTR_DATE IS ' due date ';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.TERM IS ' Such as  1Y,6M,7D';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.PARTY_ID IS ' The issuer id';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.VOLUME IS ' Issue number ';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.IS_NONSTD IS ' Whether it is non-standard or not ';
COMMENT ON COLUMN TTRD_TEST_INSTRUMENT.FINANCER_ID IS ' Financier ';

-- ----------------------------
-- 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');

Corresponding mysql Script

-- ----------------------------
-- 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 ' Financial instrument code ',
  A_TYPE VARCHAR(20) NOT NULL COMMENT ' Asset type ',
  M_TYPE VARCHAR(20) NOT NULL COMMENT ' Market type ',
  MTR_DATE CHAR(10) COMMENT ' due date ',
  TERM VARCHAR(6) COMMENT ' Such as  1Y,6M,7D',
--   PARTY_ID BIGINT COMMENT ' The issuer id',   -- 2147483647
  PARTY_ID Long COMMENT ' The issuer id',   -- 2147483647
  VOLUME DECIMAL(31,8) DEFAULT 1  NULL COMMENT ' Issue number ', --  precision 31  scale  8
  IS_NONSTD TINYINT COMMENT ' Whether it is non-standard or not ',  -- 1 Bytes 
  FINANCER_ID DECIMAL(31) COMMENT ' Financier ' -- 4 Bytes 
) 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');

Now suppose you take a piece of data from the database , Will be one of the volume add 1 Operate and modify the primary key field , Save to database . As shown below

@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;
}

This code is in oracle There is no problem in
 Insert picture description here
But in mysql You'll get a wrong report .

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]

Why is this problem ?
When we go mysql When the database client looks at the data , Did you find anything strange , It's here volume A lot of zeros in the back (Oracle It's not ).
 Insert picture description here
And then it maps to java Objects are mapped to String type , Here's the simulation method , The actual project is actually through JdbcTemplate Query out Map object , Or face map Programming , Then the field is passed in multiple places and finally becomes String, When you need to calculate again , Business people start from business ( Or refer directly to Oracle Values in the database ), The value of this field can only be reshaped , Just force the transformation , Led to bug.


String iCode = "SYXZGJH01";
String aType = "SPT_LBS";
String mType = "X_CNBD";
Map<String, Object> objectMap = ttrdInstrumentMapper.selectMapByPrimaryKey(iCode, aType, mType);
//  In business   This field cannot be empty 
String volume = objectMap.get("VOLUME").toString();
// ...  Various business codes 
//  Because this field can only be of numeric type in business 
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>

There are both Java Problems caused by type mismatch with database type , There is also an orientation to Map It's not an object-oriented programming problem . The above bug There are two ways to solve this :

  1. since mysql There's a bunch more zeros , Then modify MYSQL Database field type
-- VOLUME DECIMAL(31) DEFAULT 1  NULL COMMENT ' Issue number ',
ALTER TABLE TTRD_TEST_INSTRUMENT MODIFY VOLUME DECIMAL(31) DEFAULT 1  NULL COMMENT ' Issue number ';

Do it again , Problem solved .
 Insert picture description here
It feels perfect …

  1. modify Java Field type
private BigDecimal volume;

Corresponding code modification

@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 It also solved , In fact, if the reality is facing map Programming words , Because you can't use the compiler's help , If the code is too scattered , The high cost , Finally, abandon the modification code and choose the method above to change the database field .

  1. modify Java Field type and modify database field type at the same time Match the business

In fact, the most perfect thing to do is not just modify Java Object field type , And will Oracle and MySQL The database field types in are modified .

Two 、 The difference between functions and operators

Oracle The function in and MySQL There is a big difference between the operator and the function in .
For example, there is now one of the following SQL sentence

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';

stay Oracle There is no problem in the database
 Insert picture description here
however MySQL You can't make a mistake , But the result is 0, It's not what we want ( String splicing function )
 Insert picture description here
Because by default ,MySQL There is no support for passing || To splice , Because in mysql in || It's logic or operators .



1. Modify the database default configuration

But it can be satisfied by modifying the configuration

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

--  Modify mode 
SET GLOBAL sql_mode='ANSI';
--  Query mode 
SELECT @@global.sql_mode;
--  The default value is 
SET GLOBAL sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

You can modify the mode , Give Way MySQL take || As a tiler .

2. utilize Mybatis Characteristics of

If you don't change the default configuration of the database , It's fine too , should MySQL Provides CONCAT Function is used for database splicing
 Insert picture description here
You can use MyBatis In the configuration file, we can handle it as follows . If you are not familiar with the following code , Reference here :( compatible Oracle And MySQL Those things

<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>

request Oracle database
 Insert picture description here
request MySQL database
 Insert picture description here


3. Whether there is the same function

about CONCAT This function , Actually Oracle There are , But only two parameters can be received ,MySQL It's any number of , If it's two parameters , adopt CONCAT Function can actually meet the requirements ,Oracle No need to use || Here we go .

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';

 Insert picture description here

4. Custom function with the same name

If you don't want to solve it in any of the above ways , You can also solve the problem by creating a function with the same name . For example Oracle There is a bitwise and function in bitadd. The bits and operations used to calculate numbers

SELECT BITAND(6,3) FROM DUAL;

The result returned to

2

 Insert picture description here
however MySQL There is no such function , But you can find an operator with the same function &.

SELECT 6 & 3 FROM DUAL;

The result returned to

2

 Insert picture description here

stay MYSQL Create a function with the same name and function

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

SELECT BITAND(6,3) FROM DUAL;

give the result as follows :
 Insert picture description here
In this way, the database layer is unified , Use directly in the code data layer BITAND Function is good .

3、 ... and 、SQL Sentence grammar differences

If you are interested, please refer to this blog :Oracle and MySQL Grammatical differences

1. Data layer code compatible

When it comes to differences in database Syntax , First of all, I have to talk about the difference between pagination statements . stay compatible Oracle And MySQL Those things ( Paging problem ) We introduced in detail MyBatis、MyBatis-PageHelper、MyBatis-Plus Solutions for paging in . In fact, the latter are the same , Here we give us the most critical idea of syntax differences in database statements, which is actually solved through the data layer interceptor , Masking complexity .

2. Use the same syntax

Actually SQL There are also norms , No matter what Oracle、MySQL Will follow , such as SQL-92SQL:1999SQL:2003,SQL:2008, But they all add their own elements to the standard , On the other hand , The more these standards are, the more they are , Almost no one can master it all , You can refer to this blog :SQL Introduction to the standard Get to know . If a standard is relatively simple , If it's only one or two hundred pages , I think it would be very nice to follow this standard , unfortunately , We don't have the energy to study this standard ( major DBA Except ). So what should we do ? It's all about trying to use simple grammar . For example both as a target for 'UPDATE'( Reference blog ) Solutions for , Using the same syntax is very useful for database compatibility .

Four 、 The difference between locks

The reason why we should talk about the difference of locks separately , It's this difference that's different from the above , Whether the function is different , If the grammar is different, mistakes can be easily found ( stay SQL The client will execute it once ). But for locks , It's not that easy .

On the other hand , For the use of locks , It's also about business , If there is no transaction , First of all, locks don't work , In addition, there are also isolated differences between transactions .Oracle The default isolation level is read committed READ COMMITED,MySQL The default isolation level is repeatable read REPEATABLE READ, In the case of repeatable reading , It is possible that two transactions acquire locks at the same time , A transaction acquired lock successfully modified the data , The second transaction acquires the lock again , But because it's repeatable , Cannot read the value of the first transaction modification , The second transaction reads the original value and modifies the database , So the first thing doesn't work , Usually we first change the database isolation field to read committed . The following discussion is under the isolation level of read committed .

Suppose there is a parent-child table TTRD_TEST_PARENT and TTRD_TEST_CHILD, The first table stores the parent transaction information , And the child transaction stores multiple child transaction information corresponding to the parent transaction .

Oracle The script is as follows

-- ----------------------------
-- 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 ' Main deal number ';

COMMENT ON COLUMN TTRD_TEST_PARENT.IS_NONSTD IS ' Whether it is non-standard or not ';

COMMENT ON COLUMN TTRD_TEST_PARENT.FINANCER_ID IS ' Financier ';

-- ----------------------------
-- 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 ' Transaction No ';

COMMENT ON COLUMN TTRD_TEST_CHILD.PARENT_SYSORDID IS ' Trading parent number ';

COMMENT ON COLUMN TTRD_TEST_CHILD.I_CODE IS ' Financial instrument code ';

COMMENT ON COLUMN TTRD_TEST_CHILD.A_TYPE IS ' Asset type ';

COMMENT ON COLUMN TTRD_TEST_CHILD.M_TYPE IS ' Market type ';

COMMENT ON COLUMN TTRD_TEST_CHILD.PARTY_ID IS ' The issuer id';

COMMENT ON COLUMN TTRD_TEST_CHILD.VOLUME IS ' Number ';

-- ----------------------------
-- 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 The script is as follows

-- ----------------------------
-- Table structure for TTRD_TEST_PARENT
-- ----------------------------
DROP TABLE IF EXISTS TTRD_TEST_PARENT;
CREATE TABLE TTRD_TEST_PARENT
(
    SYSORDID    DECIMAL     NOT NULL COMMENT ' Main deal number ',
    IS_NONSTD   DECIMAL(1)  NULL COMMENT ' Whether it is non-standard or not ',
    FINANCER_ID DECIMAL(31) NULL COMMENT ' Financier '
) 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 ' Transaction No ',
    PARENT_SYSORDID DECIMAL NOT NULL COMMENT ' Trading parent number ',
    I_CODE VARCHAR (50) NOT NULL COMMENT ' Financial instrument code ',
    A_TYPE VARCHAR (20) NOT NULL COMMENT ' Asset type ',
    M_TYPE VARCHAR (20) NOT NULL COMMENT ' Market type ',
    PARTY_ID DECIMAL NULL COMMENT ' Number ',
    VOLUME DECIMAL (31, 8) DEFAULT 1 NULL COMMENT ' Main deal number '
) 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');

Corresponding entity class

public class TtrdTestParent {
   
     
    /**
     *  Trading parent number 
     */
    private Long sysordid;
    /**
     *  Whether it is non-standard or not 
     */
    private BigDecimal isNonstd;
    /**
     *  Investor information 
     */
    private BigDecimal financerId;

    // setter getter Omit  
}
public class TtrdTestChild {
   
     
    /**
     *  Sub transaction No 
     */
    private Long sysordid;
    /**
     *  Parent transaction No 
     */
    private Long parentSysordid;
    /**
     *  Financial instrument code 
     */
    private String iCode;
    /**
     *  Asset types of financial instruments 
     */
    private String aType;
    /**
     *  Types of financial instrument markets 
     */
    private String mType;
    /**
     *  The issuer ID
     */
    private BigDecimal partyId;
    /**
     *  Number of Holdings 
     */
    private BigDecimal volume;

    // setter getter Omit 
}

Now suppose there is such a business , It is necessary to divide a certain number of financial instruments into the sub transactions of a parent transaction , The corresponding interfaces are as follows

package com.example.durid.demo.service;

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

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

public interface OrderService {
   
     

    /**
     *  Assign a quantity to a parent transaction   Divide all sub transactions equally 
     *
     * @param parentSysOrdId  Parent transaction number 
     * @param allocatVol      Allocation amount 
     * @return  Sub transaction list 
     */
    List<TtrdTestChild> allocate(Long parentSysOrdId, BigDecimal allocatVol);

}

After defining the interface , We need to implement this interface , At this point, we may think of concurrency , What if two threads operate a parent transaction at the same time ? So it has to be done by locking . Because you need to lock all the child transactions of the same parent transaction in the child table . It's easy to write the following data layer code

/**
 *  Query all child transactions according to the parent transaction number and lock them at the same time 
 * @param parentSysordid  Parent transaction No 
 * @return  List of prime transactions corresponding to parent transactions 
 */
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>

Then allocate the quota and update the database , The corresponding business code is as follows

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 {
   
     
            //  Simulate specific business hours 
            Thread.sleep(500);
        } catch (InterruptedException e) {
   
     
            throw new RuntimeException(e);
        }
        if (CollectionUtils.isEmpty(childList)) {
   
     
            return new ArrayList<>();
        }
        //  Allocation amount 
        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(" Update failed ");
        }

        return ttrdTestChildMapper.selectByParentIdNoLock(parentSysOrdId);
    }
}

The other two data layer interfaces are as follows

/**
*  Query all the child transactions according to the parent transaction number 
*
* @param parentSysordid  Parent transaction No 
* @return  List of prime transactions corresponding to parent transactions 
*/
List<TtrdTestChild> selectByParentIdNoLock(Long parentSysordid);

/**
*  Update the number of all child transactions corresponding to the parent transaction 
*
* @param parentSysordid  Parent transaction No 
* @param addVolume       The amount of increase 
* @return  Number of updated data 
*/
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>

Simulate concurrency in the control layer

/**
 *  Quantity allocation  http://localhost:8083/order/allocate?db=oracle
 *
 * @return  List of included sub transactions 
 */
@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 -> {
   
     
                //  Restart the thread again   And Servlet Thread is not the same thread 
                DataSouceTypeContext.set(dataSourceTypeEnum);
                return orderService.allocate(parentSysOrdId, new BigDecimal(300));
            }
    ).flatMap(Collection::stream).collect(Collectors.toList());
}

If we request in the browser :http://localhost:8083/order/allocate?db=oracle, There won't be any anomalies .
 Insert picture description here
But if you ask :http://localhost:8083/order/allocate?db=mysql
 Insert picture description here
Something is wrong



### 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]

What's going on here ? We're simulating two different parent transactions , Even if it's locked , It's impossible to influence each other .MySQL There is also a row lock . If you don't have a similar problem , It may take a lot of time to find . The first is the index problem , If there is no index, it will lead to MySQL Upgrading row lock to table lock caused a problem . It's not about indexing here , It's about row locks upgrading table locks . The problem is when we lock the child transactions of the same parent transaction , Actually, multiple rows of data are locked . stay MySQL among , If the number of locked data accounts for a certain proportion in a table , Eventually, it will be upgraded from row lock to table lock , For example, in the above case, this ratio is in 33%. If you want to modify this bug Well ? It's very simple , Let's lock the parent watch TTRD_TEST_PARENT The corresponding piece of data in , Instead of locking multiple pieces of data .Oracle Middle does not upgrade to table lock , So there won't be any problems ,MySQL It should be for the sake of efficiency . But in the future , We should lock the table by locking single row data , If you want to lock multiple lines , Through redundant tables 、 The summary table is transformed into a way to lock single row data .

Reference blog :MySQL The deadlock problem

summary

We talked about compatibility in several ways above Oracle And MySQL Some things about , But in real projects, there are more problems than that , In fact, the most important thing is to develop good thinking and norms , The difference of field type can start from the code layer , Not the database , What we are using now is Java,Java It's an object-oriented language , Not oriented Map, So defining objects is the most important thing , Not for the sake of simplicity , Otherwise, more energy will be spent in the future , In addition, in different aspects of functions , We also offer four options :

  • Modify the database configuration to meet the function ( Not recommended )
  • utilize MyBaits Characteristics of ( recommend )
  • Functions that use the same function ( recommend )
  • Custom function with the same name ( recommend , But it requires the ability to write functions )

In case of SQL Grammatical differences , It can also be compatible through business layer code , such as MyBatis-PageHelper Compatibility for paging is a good example to learn from . The other is to try to use the same grammar , Simple grammar .

Finally, about the lock , It can be said that the problem of database thinking , We need to think about the nature of locks from a database specific perspective , It will be more difficult than the above questions .

版权声明
本文为[2gaqqnhg]所创,转载请带上原文链接,感谢

Scroll to Top