Comparison between MySQL and Oracle table field definitions

Comparison between MySQL and Oracle table field definitions

 

	Description	MySQL	Oracle
Varchar	Variable-length string	Varchar <0-65535] The definition length is calculated by the character length by default.GBKThe Encoded chinese characters will occupy2Bytes	Varchar2 [2-4000] VarcharYesVarchar2Synonym 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 use Number) replace smallint can be used with Number (5, 0) replace meduimint can be used with Number) replace int can use Number) replace bigint can use Number (20, 0) replace Oracle has the smallint, Int, integer type, however, this is a synonym for Number ()
decimal numeric	Value Type	decimal [1-65 [, 0-30] numeric is synonym for decimal	Number indicates the number range: 1*10 ^-130 to 1*10 ^ 126 Number ([1-38] [,-84-127]) decimal , numeric , dec is a synonym for Number
float	floating point	float (D, m)	Oracle10g begins to add binary_float type 10 Gb is not a dedicated type. You can use Number instead Oracle contains float and real type, but this is a synonym for Number
double	double precision floating point	double (D, m)	Oracle10g begins to add binary_double type 10 Gb is not a dedicated type. You can use Number instead Oracle has the double precision type, but this is synonym for number
Bit	Bit type	Bit (1-64)	None
Datetime	Date type	date , 3 byte storage. Only the date is stored and there is no time. The supported range is [1000-01-01] to [9999-12-31] time , 3 , only storage time, no date, supported range: [-838: 59: 59] to [838: 59: 59] datetime , which occupies 8 bytes, which can represent the date and time, the supported range is [1000-01-01 00:00:00] to [9999-12-31 23:59:59] timestamp , accounting for 4 byte storage, which can represent the date and time in the range of [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	high-precision date	5.6.4 decimal second precision is not supported before 5.6.4 Start time, datetime, timestamp supports up to 6 decimal seconds, that is, the microsecond level	timestamp [0-9] 7-11 bytes, when the decimal second precision is 0 ; "> date is of the same type, and the precision of decimal seconds can reach the 9 bits, that is, the accuracy
year	year	year , 1 bytes. Only the year is stored. The supported range is [1901] to [2155]	no corresponding type. You can use Number (3, 0) instead
Char	Fixed Length string	Char [0-255], The definition length is calculated by the character length by default, and the maximum value is saved.255Character	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.	TinytextMaximum supported255Bytes TextMaximum supported65535Bytes MediumtextMaximum supported16 MBBytes LongtextMaximum supported4 GBBytes The field does not support default values.	Supported(Clob) Oracle10gPreviously maximum support4 GBBytes Oracle10gStart with maximum support4 GBData blocks. The data block size is2kb-32kb OracleThere is anotherLongType, which is an early storage of large string type, maximum supported2 GBBytes,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 maximum supported 65535 bytes mediumblob maximum supported 16 MB bytes longblob supports up to 4 GB bytes the field does not support the default value	(BLOB) Oracle10g previously supported 4 GB bytes Oracle10g supports up to 4G data blocks, the data block size is 2kb-32kb Oracle also has a long raw type, it is an early binary storage type and supports up to 2 GB bytes , 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 ,...),Maximum65535Elements	Not Supported
Set	Set Type	Set (V1, V2, V3 ,...), Maximum64Elements	Not Supported
National char	International character set type, rarely used	None,MySQLYou can specify character encoding for each field	Supported Nchar (1-2000) Nvarchar (1-4000) Nclob
Bfile	External file pointer type	Not Supported	Supported Maximum File Size4 GB Maximum File Name255Character
Custom Data Type		Not Supported	Supported
XMLType		Not Supported	Supported
Auto-increment type	Automatic growth type	Supported Easy to use	Not Supported Generally usedSequenceSolution: The usage is significantly different from the auto-increment type, which is complicated to use, but can be used flexibly, including the auto-incrementing primary key and global primary key.
default field expression		Functions and expressions are not supported text and blob field type does not support default values	Functions and expressions
field order modification		supported, for example, set EMP id is placed in 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 and its result value is usually an expression, the physical values are stored in the table, which does not occupy space and 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		InnoDBMax1000Fields The total length of all fields cannot exceed65535Bytes The total length of all fixed-length fields cannot exceed half the size of data blocks.(The data block size is generally16 K)	Max1000Fields

 

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