Comparison between mysql and oracle table field definitions

Mysql and oracle table field definitions comparison description mysql oracle VARCHAR www.2cto.com variable-length string VARCHAR [0-65535] definition length is calculated by default, if it is GBK-Encoded chinese characters will occupy 2 bytes, if the total byte length of the field definition exceeds 65535, it will be automatically converted to the MEDIUMTEXT type definition VARCHAR2 [1-4000] VARCHAR is a synonym definition of VARCHAR2. By default, tinyint smallint www.2cto.com is calculated by byte length (-128-127) SMALLINT (-32768-32767) MEDIUMINT (-8388608-8388607) INT (-2147483648-2147483647) BIGINT (-9223372036854775808-9223372036854775807) Use type. www.2cto.com TINYINT can use NUMBER () instead of SMALLINT. Instead of SMALLINT, use NUMBER () instead of MEDUIMINT, and use NUMBER () instead of INT) instead of BIGINT, you can use NUMBER (20, 0) to replace SMALLINT, INT, and INTEGER types in ORACLE. However, this is a synonym for NUMBER (), DECIMALNUMERIC, numerical type DECIMAL [1-65 [, 0-30] NUMERIC is a synonym for DECIMAL. The NUMBER range is 1*10 ^-130 to 1*10 ^ 126 NUMBER ([1-38] [, -84-127]) DECIMAL, NUMERIC, and DEC are synonyms of number float floating point type www.2cto.com FLOAT (D, M) oracle10g and the BINARY_FLOAT type is increased. 10 Gb ago, there was no dedicated type. You can use NUMBER to replace the FLOAT and REAL types in ORACLE. However, this is the synonym for NUMBER, DOUBLE (D, M) there was no special type before oracle10g began to add BINARY_DOUBLE type 10g. You can use NUMBER to replace the double precision type in ORACLE. However, this is a synonym for the number bit type BIT (1-64) no datetime date type www.2cto.com 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, 8-byte storage, it 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-byte storage and can represent the DATE and time. The value ranges from [00:00:00] to [03:14:07] DATE 7-byte storage, which can represent the DATE and time, supported range: [-4712-01-01 00:00:00] to [9999-12-31 23:59:59] TIMESTAMP high-precision date before 5.6.4 does not support decimal second precision 5.6.4 start TIME, DATETIME, TIMESTAMP support, A maximum of six decimal seconds, that is, the microsecond-level TIMESTAMP [0-9] occupies 7-11 bytes. When the decimal second precision is 0, it is of the same type as DATE, the maximum precision of decimal seconds can reach 9 bits, that is, YEAR of YEAR www.2cto.com, which is 1-byte storage. Only the YEAR is stored. The supported range is [1901] to [2155] and there is no corresponding type, you can use NUMBER (255) to replace the CHAR fixed-length string CHAR [0-] and define the length. The default value is based on the length of characters. The maximum value is 255 characters. CHAR [1-2000] is defined. The default value is based on the byte length. UNSIGNED description is supported by www.2cto.com. It is used for numeric values that do not support CLOB strings, it is generally used to store TEXT files, large descriptions, and remarks. TINYTEXT supports up to 255 bytes. TEXT supports up to 65535 bytes. MEDIUMTEXT supports up to 16 Mb. LONGTEXT supports up to 4 GB bytes. fields do not support default values. (CLOB) previously, oracle10g supported a maximum of 4 GB of data blocks starting with oracle10g and a maximum of 4 GB of data blocks. The data block size is 2KB-32KB. oracle also has a LONG type, which is an early type of large string and supports a maximum of 2 GB of data, BLOB large binary objects are currently not recommended. It is generally used to store files or image data. www.2cto.com TINYBLOB supports up to 255 bytes. BLOB supports up to 65535 bytes. MEDIUMBL OB supports a maximum of 16 MB bytes. LONGBLOB supports a maximum of 4 GB bytes. The default value is not supported. (BLOB) a maximum of 4 GB bytes before oracle10g supports a maximum of 4 GB Data blocks, 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. BINARY (0-255) is not recommended now ), fixed Length VARBINARY (0-65535), variable length RAW (1-2000) ENUM Enumeration type ENUM (v1, v2, v3 ,...), up to 65535 elements www.2cto.com do not support SET (v1, v2, v3 ,...), up to 64 elements do not support the international character set type of national char, which is rarely used. MYSQL supports NCHAR (1-2000) NVARCHAR (1-4000) N for the specified character encoding for each field Clob bfile external file pointer type does not support file size up to 4 GB file name up to 255 characters Custom Data Type www.2cto.com does not support XML type not support auto-increment Type Automatic growth type support simple use SEQUENCE is generally not supported, the usage differs greatly from the auto-increment type and is complex to use, but it can be used flexibly, the default field expressions, including the Character Auto-incrementing primary key and global primary key, do not support functions and expressions. TEXT and BLOB field types do not support default values. Support for function and expression field order modification. For example, put the id field order of the emp table behind the name field: alter table emp modify column id varchar (20) after name; not supported, only a table or field www.2cto.com virtual field can be rebuilt. A virtual field is a logical field definition, and the result value is usually an expression. The physical value is stored 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 for the amount. The expression = unit price * the number does not support 11 GB. For example: create table sales (id number, quantity number, price number, amount GENERATED always as (quantity * price) virtual ); table field quantity limit INNODB maximum 1000 fields total definition length of all fields cannot exceed 65535 bytes total length of all fixed length fields cannot exceed half data block size (data block size is generally 16 K) up to 1000 Fields