Comparison between mysql and oracle table field definitions

The Comparison Between mysql and oracle table field definitions indicates that the length of mysql1_levarchar variable-length string VARCHAR [0-65535] is calculated by character length by default, for GBK-Encoded chinese characters, VARCHAR2 [1-4000] VARCHAR is a synonym for VARCHAR2. By default, TINYINTSMALLINTMEDIU is calculated by byte length.

Mysql and oracle table field definitions comparison description mysql oracle VARCHAR variable-length string VARCHAR [0-65535] definition length is calculated by character length by default, for GBK-Encoded chinese characters, VARCHAR2 [1-4000] VARCHAR is a synonym definition of VARCHAR2. By default, tinyint smallint mediu is calculated by byte length.

Comparison between mysql and oracle table field definitions

	Description	Mysql	Oracle
VARCHAR	Variable-length string	VARCHAR <0-65535] The defined length is calculated by the character length by default. If it is a GBK-Encoded chinese character, it occupies 2 bytes.	VARCHAR2 [2-4000] VARCHAR is a synonym for VARCHAR2 Definition is calculated by byte length by default
TINYINT SMALLINT MEDIUMINT INT BIGINT	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 (). BIGINT can be replaced by NUMBER (20, 0 ). ORACLE has the SMALLINT, INT, and INTEGER types, but this is a synonym for NUMBER ().
DECIMAL NUMERIC	Value Type	DECIMAL [1-65 [, 0-30] NUMERIC is a synonym for DECIMAL.	NUMBER indicates the NUMBER range: 1x10 ^-130 to 1x10 ^ 126 NUMBER ([1-38] [,-84-127]) DECIMAL, NUMERIC, and DEC are synonyms of NUMBER.
FLOAT	Floating Point Type	FLOAT (D, M)	The BINARY_FLOAT type is added to oracle10g. No dedicated type before 10g, can be replaced by NUMBER There are FLOAT and REAL types in ORACLE, but this is a synonym for NUMBER.
DOUBLE	Dual-precision floating point	DOUBLE (D, M)	The BINARY_DOUBLE type is added to oracle10g. No dedicated type before 10g, can be replaced by NUMBER ORACLE has the double precision type, but this is a synonym for NUMBER.
BIT	Bit type	BIT (1-64)	None
DATETIME	Date type	DATE, 3-byte storage, only store DATE, no time, supported range: [1000-01-01] to [9999-12-31] TIME, 3-byte storage, only storage TIME, no date, supported range: [-838: 59: 59] to [838: 59: 59] DATETIME, which occupies 8 bytes of storage and can represent the date and time. The supported range is [1000-01-01 00:00:00] to [9999-12-31 23:59:59] TIMESTAMP, which occupies 4 bytes of storage, can represent the date and time, range: [00:00:00] to [03:14:07]	DATE type 7-byte storage, which can represent the date and time. The supported range is [-4712-01-01 00:00:00] to [9999-12-31 23:59:59]
TIMESTAMP	Precision date	Earlier than 5.6.4, decimal second precision is not supported 5.6.4 start TIME, DATETIME, TIMESTAMP supported, up to 6 decimal seconds, that is, microsecond level	TIMESTAMP [0-9] The storage space is 7-11 bytes. When the decimal second precision is 0, it is the same as the DATE type. The decimal second precision can reach 9 bits, that is, the accuracy.
YEAR	Year	YEAR, 1-byte storage, only for the YEAR. The supported range is [1901] to [2155].	No corresponding type, which can be replaced by NUMBER (3, 0)
CHAR	Fixed Length string	CHAR [0-255]. The default length is calculated based on the Character length. The maximum length is 255 characters.	CHAR [1-2000] Definition is calculated by byte length by default
UNSIGNED	Unsigned description	Supported, used for numeric type	Not Supported
CLOB	A large string, which is generally used to store text files or extremely large descriptions and remarks.	TINYTEXT supports a maximum of 255 bytes. TEXT supports a maximum of 65535 bytes. MEDIUMTEXT supports a maximum of 16 MB bytes LONGTEXT supports a maximum of 4 GB bytes. The field does not support default values.	Supported (CLOB) Up to 4 GB bytes are supported before oracle10g At the beginning of oracle10g, a maximum of 4 GB Data blocks are supported. The data block size is 2KB-32KB. Oracle also has a LONG type, which is an early type of large string storage and supports a maximum of 2 GB bytes. It is not recommended now.
BLOB	A large binary object. It is generally used to store files or image data.	TINYBLOB supports up to 255 bytes BLOB supports a maximum of 65535 bytes. MEDIUMBLOB supports a maximum of 16 MB bytes LONGBLOB supports a maximum of 4 GB bytes The field does not support default values.	Supported (BLOB) Up to 4 GB bytes are supported before oracle10g At the beginning of oracle10g, a maximum of 4 GB Data blocks are supported. The data block size is 2KB-32KB. Oracle also has a long raw type, which is an early binary storage type and supports a maximum of 2 GB bytes. It is not recommended now.
BINARY	Binary information	BINARY (0-255), fixed length VARBINARY (0-65535), variable length	RAW (1-2000)
ENUM	Enumeration type	ENUM (v1, v2, v3,...), up to 65535 Elements	Not Supported
SET	Set Type	SET (v1, v2, v3,...), up to 64 elements	Not Supported
NATIONAL CHAR	International character set type, rarely used	None. MYSQL can specify character encoding for each field.	Supported NCHAR (1-2000) NVARCHAR (1-4000) NCLOB
BFILE	External file pointer type	Not Supported	Supported File size up to 4 GB The file name cannot exceed 255 characters
Custom Data Type		Not Supported	Supported
XML type		Not Supported	Supported
Auto-increment type	Automatic growth type	Supported Easy to use	Not Supported SEQUENCE is generally used to solve the problem. Its usage differs greatly from the auto-incrementing type and is complicated to use. However, it can be used flexibly, including the auto-incrementing primary key and global primary key.
Default field expression		Functions and expressions are not supported. The TEXT and BLOB fields do not support default values.	Supported functions and expressions
Field order modification		Supported. For example, place the id field order 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.
Virtual Field	A virtual field is a logical field definition. Its Result value is usually an expression that stores physical values in the table without occupying space. It is mainly used to simplify the query logic. For example, if a commodity sales table has two fields: unit price and quantity, you can create a virtual field with the expression = unit price * quantity.	Not Supported	11g supported, for example: Create table sales ( Id number, Quantity number, Price number, Amount GENERATED always as (quantity * price) virtual );
Table field count limit		Up to 1000 INNODB Fields The total length of all fields cannot exceed 65535 bytes. The total length of all fixed-length fields cannot exceed half the size of data blocks (the size of data blocks is generally 16 K)	Up to 1000 Fields

My Sina Weibo: http://weibo.com/yzsind