数据库数据类型对比、数据类型大全

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

数据类型对比（各个数据库）

Other Vendor Type	MySQL Type	Oracle
BOOL	TINYINT
BOOLEAN	TINYINT
CHARACTER VARYING(M)	VARCHAR(M)
FIXED	DECIMAL
FLOAT4	FLOAT
FLOAT8	DOUBLE
INT1	TINYINT
INT2	SMALLINT
INT3	MEDIUMINT
INT4	INT
INT8	BIGINT
LONG VARBINARY	MEDIUMBLOB
LONG VARCHAR	MEDIUMTEXT
LONG	MEDIUMTEXT
MIDDLEINT	MEDIUMINT
NUMERIC	DECIMAL

三、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)