数据库之数据类型参考资料
2018年02月18日


一、Oracle表结构移植到Mysql中需要的类型转换

MYSQL                    ORACLE
BLOB(220)          RAW(220)
BLOB (20)           RAW(20)
BLOG(1024)            RAW(1024)
VARCHAR(n)           VARCHAR2(n)
CHAR                       CHAR
FLOAT(22,6)            NUMBER(22,6)
DOUBLE(44,12)       NUMBER(44,12)
TINYINT (3)              NUMBER(3)
SMALLINT(5)            NUMBER(5)
MEDIUMINT(8)         NUMBER(8)
INT(10)                     NUMBER(10)
BIGINT(20)               NUMBER(20)

DATATIME                DATA


二、mysql和oracle数据类型对应关系参照表
MySQL Data Type
Oracle Data Type
NUMERIC
NUMBER
BIGINT
NUMBER(19, 0)
BIT
RAW
BLOB
BLOB, RAW
CHAR
CHAR
DATE
DATE
DATETIME
DATE
DECIMAL
FLOAT (24)
DOUBLE
FLOAT (24)
DOUBLE PRECISION
FLOAT (24)
ENUM
VARCHAR2
FLOAT
FLOAT
INT
NUMBER(10, 0)
INTEGER
NUMBER(10, 0)
LONGBLOB
BLOB, RAW
LONGTEXT
CLOB, RAW
MEDIUMBLOB
BLOB, RAW
MEDIUMINT
NUMBER(7, 0)
MEDIUMTEXT
CLOB, RAW
REAL
FLOAT (24)
SET
VARCHAR2
SMALLINT
NUMBER(5, 0)
TEXT
VARCHAR2, CLOB
TIME
DATE
TIMESTAMP
DATE
TINYBLOB
RAW
TINYINT
NUMBER(3, 0)
TINYTEXT
VARCHAR2
VARCHAR
VARCHAR2, CLOB
YEAR
NUMBER
数据类型对比(各个数据库)


三、MySQL数据类型:

参见:

https://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html


NUMERIC[(M[,D])] 
These types are synonyms for DECIMAL
DECIMAL[(M[,D])] 
 If D is omitted, the default is 0. If M is omitted, the default is 10. 
Type
Storage
Minimum Value
Maximum Value

(Bytes)
(Signed/Unsigned)
(Signed/Unsigned)
TINYINT
1
-128
127


0
255
SMALLINT
2
-32768
32767


0
65535
MEDIUMINT
3
-8388608
8388607


0
16777215
INT
4
-2147483648
2147483647


0
4294967295
BIGINT
8
-9223372036854775808
9223372036854775807


0
18446744073709551615
TINYINT相当于Java的  Byte (-2^7 ~~~ 2^7-1)
SMALLINT                    Short  (-2^15 ~~~ 2^15-1)
INT                                Integer (-2^31 ~~~ 2^31-1)
BIGINT                          Long    (-2^63 ~~~ 2^63-1)


Numeric
Integer
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
Decimal
Single precision
Double precision
Character
Char
Varchar
Text
Binary
Varbinary
BLOB
Date & Time
Date
Time
Datetime
Year
Timestamp
Miscellaneous
ENUM
SET

BIT


四、Oracle数据类型

参见:http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT113

Character Data Types
VARCHAR2 and CHAR Data Types
NCHAR and NVARCHAR2 Data Types
Numeric Data Types
NUMBER Data Type
Floating-Point Numbers (BINARY_FLOAT and BINARY_DOUBLE)
Datetime Data Types
      DATE Data Type
      TIMESTAMP Data Type (TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE are time-zone aware)