SoFunction
Updated on 2025-04-07

Correlationship between MySQL and Oracle data types (table form)

Both MySQL and Oracle databases use more databases in their work. Because there are some differences in data types between MySQL and Oracle, we will encounter some trouble when migrating the database. The following introduces the correspondence between MySQL and Oracle database data types.

1. The manifestation of common data types in MySQL and Oracle databases

illustrate

mysql

oracle

Variable length string

VARCHAR[0-65535]

The definition length is calculated by character length by default. If it is a GBK-encoded Chinese character, it will occupy 2 bytes.

VARCHAR2[1-4000]
VARCHAR is synonymous with VARCHAR2

Definition by default calculated by byte length

Integer

TINYINT(-128-127)

SMALLINT(-32768-32767)

MEDIUMINT(-8388608-8388607)

INT(-2147483648-2147483647)
BIGINT(-9223372036854775808-9223372036854775807) 

No dedicated type,

TINYINT can be replaced by NUMBER(3,0)

SMALLINT can be replaced by NUMBER(5,0)

MEDUIMINT can be replaced by NUMBER(7,0)
 

INT can be replaced by NUMBER(10,0)
 

BIGINT can be replaced by NUMBER(20,0)


 

There are types of SMALLINT, INT, INTEGER in ORACLE, but this is synonym for NUMBER(38,0)

Numerical type

DECIMAL[1-65[,0-30]]
NUMERIC is synonym for DECIMAL

NUMBER can represent the number range: 1*10^-130 to 1*10^126

NUMBER([1-38][,-84-127])

 

DECIMAL, NUMERIC, DEC are synonyms of NUMBER

Floating point type

FLOAT(D,M)

oracle10g starts to increase BINARY_FLOAT type

There was no special type before 10g, so NUMBER can be used instead.

There are FLOAT and REAL types in ORACLE, but this is synonym for NUMBER

Double precision floating point type

DOUBLE(D,M)

oracle10g starts to increase BINARY_DOUBLE type

There was no special type before 10g, so NUMBER can be used instead.

There is DOUBLE PRECISION type in ORACLE, but this is synonym for NUMBER

Bit type

BIT(1-64)

none

Date Type

DATE, 3-byte storage, only storage date, no time, support range is [1000-01-01] to [9999-12-31]
TIME, 3-byte storage, only storage time, no date, support range is [-838:59:59] to [838:59:59]
DATETIME, which accounts for 8 bytes of storage, can represent date and time, and the support range is [1000-01-01 00:00:00] to [9999-12-31 23:59:59]
TIMESTAMP, which accounts for 4 bytes of storage, can represent date and time, and the range is [1970-01-01 00:00:00] to [2038-01-19 03:14:07]

DATE type
7 byte storage, which can represent date and time, and the support range is [-4712-01-01 00:00:00] to [9999-12-31 23:59:59]

High precision date

5.6.4 did not support decimal seconds accuracy before
5.6.4 Starting with TIME, DATETIME, and TIMESTAMP support, it can be up to 6 decimal seconds, that is, microsecond level

TIMESTAMP[0-9]
Takes up 7-11 bytes of space. When the accuracy of the fractional second is 0, the same as the DATE type, the maximum accuracy of the fractional second can reach 9 bits, that is, the nanoprecision

years

YEAR, 1 byte storage, only the year is stored, the support range is [1901] to [2155]

No corresponding type, NUMBER(3,0) can be used instead.

Fixed-length string

CHAR[0-255], the definition length is calculated by character length by default, and the maximum saving is 255 characters.

CHAR[1-2000]

Definition by default calculated by byte length

Unsigned description

Supported for numeric types

Not supported

Large strings, generally used to store text files or oversized descriptions and remarks information

TINYTEXT supports up to 255 bytes
TEXT supports up to 65535 bytes
MEDIUMTEXT supports up to 16MB bytes
LONGTEXT supports up to 4GB bytes

The default value is not supported in fields

Support (CLOB)
Oracle10g used to support up to 4GB bytes

Oracle10g starts to support up to 4GB data blocks, with a data block size of 2KB-32KB

Oracle also has a LONG type, which is an early storage large string type, with a maximum support of 2GB bytes. It is not recommended to use it now.

Binary objects, generally used to store file or picture data

TINYBLOB supports up to 255 bytes
BLOB supports up to 65535 bytes
MEDIUMBLOB supports up to 16MB bytes

LONGBLOB supports up to 4GB bytes

 

The default value is not supported in fields

Support (BLOB)

Oracle10g used to support up to 4GB bytes
 

Oracle10g starts to support up to 4G data blocks, with a data block size of 2KB-32KB

Oracle also has a LONG RAW type, which is an early storage binary type, with a maximum support of 2GB bytes, and is no longer recommended to use it.

Binary information

BINARY(0-255), fixed length
VARBINARY(0-65535), change length

RAW(1-2000)

Enumeration type

ENUM(v1,v2,v3,...), up to 65,535 elements

Not supported

Collection Type

SET(v1,v2,v3,...), up to 64 elements

Not supported

Internationalized character set types, less used

None, MYSQL can specify character encoding for each field

support
NCHAR(1-2000)
NVARCHAR(1-4000)
NCLOB

External file pointer type

Not supported

support
File size up to 4GB
File name up to 255 characters long

 

Not supported

support

 

Not supported

support

Automatic growth type

support
Easy to use

Not supported
Generally, SEQUENCE is used to solve the problem. The usage is quite different from the self-increment type and is more complicated to use, but it can achieve very flexible applications, including character self-increment primary key, global primary key, etc.

 

Functions and expressions are not supported
TEXT and BLOB field types do not support default values

Support functions and expressions

 

Support, for example, put the id fields of the emp table after the name field:
alter table emp modify column id varchar(20) after name;

Not supported, only tables or fields can be rebuilt

A virtual field is a logical field definition, and its result value is usually an expression, and it stores physical values ​​in the table without taking up space. It is mainly used to simplify query logic. For example, if there is a product sales table with two fields: unit price and quantity, you can create a virtual field amount, and its expression = unit price * quantity

Not supported

11g support, example:
create table sales
(
  id       number,
  quantity number,
  price    number,
  amount   GENERATED always as (quantity*price) virtual
);

 

INNODB Maximum 1000 fields
The total definition length of all fields cannot exceed 65535 bytes
The total length of all fixed-length fields does not exceed half a data block size (the data block size is generally 16K)

Maximum 1000 fields

2. Correlation between common data types of MySQL and Oracle database

serial number ORACLE MYSQL Comments
1 NUMBER int / DECIMAL DECIMAL is NUMBER(10,2) and the structure INT is NUMBER(10), representing an integer;
MYSQL has many int types, tinyint medium bigint, etc. Different int widths are different
2 Varchar2（n） varchar(n) 　
3 Date DATATIME Processing of date fields
MYSQL date fields are divided into DATE and TIME. The ORACLE date field only has DATE, which contains information about year, month, day, hour, minute and second. The system time of the current database is SYSDATE, which is accurate to seconds, or convert a string into a date-type function TO_DATE('2001-08-01','YYYY-MM-DD') Year-month-day 24 hours: minute: seconds format YYYY-MM-DD HH24:MI:SS TO_DATE() There are many date formats, you can refer to ORACLE DOC. Convert a date-type field into a string function TO_CHAR('2001-08-01','YYYY-MM-DD HH24:MI:SS')

The mathematical formulas for date fields vary greatly. MYSQL find DATE_FIELD_NAME 7 days away from the current time DATE_FIELD_NAME ＞ SUBDATE (NOW(), INTERVAL 7 DAY) ORACLE find DATE_FIELD_NAME ＞SYSDATE - 7;

Several functions that insert the current time in MYSQL are: The NOW() function returns the current date and time with `'YYY-MM-DD HH:MM:SS', and can be stored directly in the DATETIME field. CURDATE() returns today's date in the format 'YYYY-MM-DD' and can be saved directly into the DATE field. CURTIME() returns the current time in the format of 'HH:MM:SS' and can be stored directly in the TIME field. Example: insert into tablename (fieldname) values ​​(now())

The current time in oracle is sysdate
4 INTEGER int / INTEGER In Mysql INTEGER is equivalent to int
5 EXCEPTION SQLEXCEPTION  See <<>> for details on Mysql exception handling
6 CONSTANT VARCHAR2(1) There is no CONSTANT keyword in mysql Migrating from ORACLE to MYSQL, all CONSTANT constants can only be defined as variables
7 TYPE g_grp_cur IS REF CURSOR; Cursor: There is an alternative in mysql See <<>> for details. Cursor processing
8 TYPE unpacklist_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; Array: Temporary table processing in mysql
Or write the logic directly into the corresponding code,
Directly process each value in the set correspondingly		 See 2.4 Array Processing in <<>> for details
9 Automatically growing sequence Automatically growing data types MYSQL has an automatic data type. If you do not need to operate this field when inserting records, the data value will be automatically obtained. ORACLE does not have an automatic growth data type, and it is necessary to establish an automatic growth serial number. When inserting the record, the next value of the serial number must be assigned to this field.
10 NULL NULL Processing of empty characters
The non-empty fields of MYSQL also have empty content. If a non-empty field is defined in ORACLE, there is no empty content. Press NOT NULL of MYSQL to define the ORACLE table structure, an error will occur when transducing data. Therefore, when deriving data, you must judge the null character. If it is a NULL or a null character, you need to change it to a space string.

Basic syntax for comparing the differences between MySQL and Oracle

serial number category ORACLE MYSQL Comments
1 Variables are declared in different ways li_index NUMBER := 0 DECLARE li_index INTEGER DEFAULT 0 1. mysql uses DECLARE to define local variables.
The syntax for defining variables is: DECLARE var_name[,...] type [DEFAULT value] To provide a default value to a variable, you need to include a DEFAULT clause. The value can be specified as an expression and does not need to be a constant. If there is no DEFAULT clause, the initial value is NULL.
2 Different ways of assignment of variables lv_inputstr := iv_inputstr SET lv_inputstr = iv_inputstr 1. Use oracle variable assignment:=
mysql uses the set keyword using assignment. Use "=" when assigning a value to a variable.
3 The jump (exit) statement is different EXIT; LEAVE procedure name; 1. oracle: If the exit statement is in the loop, it exits the current loop. If the exit statement is no longer looped, it exits the current process or method.
Mysql: If the leave statement is followed by the stored procedure name, then the current stored procedure is exited. If the leave statement is followed by the lable name, then the current lable is exited.
   
while conditions loop
exit;
end loop;		 label_name:while condition do
    leave label_name;
end while label_name;
4 Define cursor TYPE g_grp_cur IS REF CURSOR;
     		 DECLARE cursor_name CURSOR FOR SELECT_statement; Oracle can first define the cursor and then assign a value to the cursor.
When mysql defines a cursor, it is necessary to assign a value to the cursor. The Mysql defines a cursor from Mysql 5.1 Reference Manual 20.2.11.1. Declare the cursor.
5 Define an array TYPE unpacklist_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; You can use temporary tables instead of oracle arrays, or you can loop split characters instead of oracle arrays. Currently, temporary tables can be used instead of oracle arrays.
See the 2.4 Mysql array processing section in <<2009002-OTMPPS-Difficult >> for details
6 Different comments "-- message"  or "/** ….  */" or "/* ….  */" "-- message" or "/* ….  */" or "#" mysql annotation comes from MySQL 5.1 Reference Manual 9.5. Comment syntax, it is recommended to use single line--, multiple lines/* */
7 The format of the date and time function is different Oracle time format: yyyy-MM-dd hh:mi:ss Mysql time format: %Y-%m-%d %H:%i:%s 1. The MYSQL date fields are divided into DATE and TIME.
The ORACLE date field has only DATE and contains information about year, month, day, hour, minute and second.
2. Take the current system time in mysql as the now() function, and it is accurate to seconds.
The system time of the current database is taken from oracle to SYSDATE, which is accurate to seconds.
8 Date addition and subtraction Current time plus N days: sysdate+N
Current time is reduced by N days: sysdate-N		 Date addition: date_add(now(), INTERVAL 180 DAY)
Date subtraction: date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second)		 　
9 Different string concatenators result  := v_int1||v_int2; set result =concat(v_int1,v_int2);
1. Oracle uses || to connect strings, and you can also use the concat function. However, Oracle's concat function can only concatenate two strings.
Mysql uses the concat method to connect strings. MySQL's concat function can concatenate one or more strings, such as
mysql> select concat('10');   The result is: 10.
mysql> select concat('11','22','33','aa'); The result is: 112233aa
2. "||" in Mysql is with operations
10 Define cursors differently CURSOR l_bk_cur IS
   SELECT B.BK_HDR_INT_KEY, B.BK_NUM
   FROM ES_SR_DTL_VRB A, ES_BK_HDR B
   WHERE A.BK_HDR_INT_KEY = B.BK_HDR_INT_KEY
         AND b.BK_STATUS != ES_BK_PKG.g_status_can
         AND A.SR_HDR_INT_KEY = ii_sr_hdr_int_key;		 DECLARE l_bk_cur CURSOR
    FOR SELECT B.BK_HDR_INT_KEY, B.BK_NUM
   FROM ES_SR_DTL_VRB A, ES_BK_HDR B
   WHERE A.BK_HDR_INT_KEY = B.BK_HDR_INT_KEY
         AND b.BK_STATUS != ES_BK_PKG.g_status_can
         AND A.SR_HDR_INT_KEY = ii_sr_hdr_int_key;
 See the 2.2 Mysql cursor processing section in <<2009002-OTMPPS-Difficult >> for details
11 Transaction rollback ROLLBACK; ROLLBACK; The same method is used in oracle and mysql
12 GOTO statement GOTO check_date; GOTO check_date; The same method is used in oracle and mysql

The above is all the corresponding relationship between MySQL and Oracle data types