Ing between MySQL and Oracle Data Types (table form), mysqloracle

Ing between MySQL and Oracle Data Types (table form), mysqloracle

MySQL and Oracle databases are usually used at work. Because of some differences in data types between MySQL and Oracle, the following describes the relationship between the data types of MySQL and Oracle databases.

I. Common Data Types in MySQL and Oracle databases

Description	Mysql	Oracle
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
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 ().
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.
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.
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 type	BIT (1-64)	None
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]
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, 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)
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 description	Supported, used for numeric type	Not Supported
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.
Binary object, which 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 information	BINARY (0-255), fixed length VARBINARY (0-65535), variable length	RAW (1-2000)
Enumeration type	ENUM (v1, v2, v3,...), up to 65535 Elements	Not Supported
Set Type	SET (v1, v2, v3,...), up to 64 elements	Not Supported
International character set type, rarely used	None. MYSQL can specify character encoding for each field.	Supported NCHAR (1-2000) NVARCHAR (1-4000) NCLOB
External file pointer type	Not Supported	Supported File size up to 4 GB The file name cannot exceed 255 characters
	Not Supported	Supported
	Not Supported	Supported
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.
	Functions and expressions are not supported. The TEXT and BLOB fields do not support default values.	Supported functions and expressions
	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.
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 );
	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

2. ing between MySQL and common data types in Oracle databases

No.	ORACLE	MYSQL	Note
1	NUMBER	Int/DECIMAL	DECIMAL is a structure such as NUMBER (). INT Is NUMBER (10), which indicates an integer; MYSQL has many int-type classes, such as tinyint mediumint bigint. Different int widths are different.
2	Varchar2 (n)	Varchar (n)
3	Date	DATATIME	Processing of date fields Mysql date fields are divided into DATE and TIME. The oracle date field is only DATE, which contains information about the year, month, day, hour, minute, and second. the system TIME of the current database is SYSDATE, accurate to seconds, or use a string to convert to the date type function TO_DATE ('1970-08-01 ', 'yyyy-MM-DD') year-month-day 24 hours: minute: Second format YYYY-MM-DD HH24: MI: SS TO_DATE () has many date formats. For more information, see oracle doc. convert a datetime field to a string function TO_CHAR ('2017-08-01 ', 'yyyy-MM-DD HH24: MI: ss ') The mathematical formulas for date fields vary greatly. MYSQL uses DATE_FIELD_NAME> SUBDATE (NOW (), INTERVAL 7 DAY) to locate the current time seven days from the current time and uses DATE_FIELD_NAME> SYSDATE-7; The following functions Insert the current time in MYSQL are: NOW () function returns the current date time with ''yyyy-MM-DD HH: MM: s'', which can be saved directly to the DATETIME field. CURDATE () returns today's DATE in 'yyyy-MM-DD 'format and can be saved directly to the DATE field. CURTIME () returns the current TIME in 'hh: MM: ss' format and can be saved directly to the TIME field. Example: insert into tablename (fieldname) values (now ()) In oracle, the current time is sysdate.
4	INTEGER	Int/INTEGER	In Mysql, INTEGER is equivalent to int.
5	EXCEPTION	SQLEXCEPTION	For more information, see <2009001-eService-O2MG.doc> 2.5 Mysql Exception Handling
6	CONSTANT VARCHAR2 (1)	Mysql does not have the CONSTANT keyword	From ORACLE to MYSQL, all CONSTANT constants can only be defined as variables
7	TYPE g_grp_cur is ref cursor;	Cursor: there are alternative solutions in mysql	For more information, see <2009001-eService-O2MG.doc> 2.2 cursor Processing
8	TYPE unpacklist_type is table of VARCHAR2 (2000) index by BINARY_INTEGER;	Array: using temporary tables in mysql Or directly write the logic to the corresponding code, Directly process each value in the Set	For details, see "2.4 Array Processing" in <2009001-eService-O2MG.doc>.
9	Auto-increasing sequence	Automatically increasing data types	MYSQL has a data type that increases automatically. When you insert a record, you do not need to operate on this field. The data value is automatically obtained. ORACLE does not have an auto-increasing data type. You need to create an auto-increasing serial number. When inserting a record, you need to assign the next value of the serial number to this field.
10	NULL	NULL	Processing of null characters Non-empty fields in MYSQL are also empty. empty fields are not allowed in ORACLE. The ORACLE table structure is defined based on the not null value of MYSQL. errors may occur when data is imported. Therefore, when importing data, you need to judge the NULL character. If it is NULL or NULL, you need to change it to a space string.

Basic syntax for comparison between MySQL and Oracle

No.	Category	ORACLE	MYSQL	Note
1	Variables are declared differently.	Li_index NUMBER: = 0	DECLARE li_index integer default 0	1. mysql uses DECLARE to define local variables. Syntax for defining variables: DECLARE var_name [,...] type [DEFAULT value] to provide a DEFAULT value for the variable, a DEFAULT clause must be included. The value can be specified as an expression and does not need to be a constant. If no DEFAULT clause exists, the initial value is NULL.
2	Variable assignment methods are different.	Lv_inputstr: = iv_inputstr	SET lv_inputstr = iv_inputstr	1. oracle variable assignment: = Mysql uses the set keyword to assign values. "=" is used to assign a value to a variable ".
3	The exit statement is different.	EXIT;	LEAVE procedure name;	1. oracle: exit the current loop if the exit statement is in the loop. If the exit statement is no longer in the loop, exit the current process or method. Mysql: if the leave statement is followed by the stored procedure name, the current stored procedure is exited. If the leave statement is followed by the lable name, the current lable is exited.
		While condition loop Exit; End loop;	Label_name: while condition do Leave label_name; End while label_name;
4	Define a cursor	TYPE g_grp_cur is ref cursor;	DECLARE cursor_name cursor for SELECT_statement;	Oracle can define the cursor first, and then assign a value to the cursor. When defining a cursor in mysql, You need to assign a value to the cursor. Mysql defines the cursor from Mysql 5.1 reference manual 20172.11.1. Declare the cursor.
5	Define an array	TYPE unpacklist_type is table of VARCHAR2 (2000) index by BINARY_INTEGER;	Temporary tables can be used to replace oracle arrays, or oracle Arrays can be replaced by cyclic splitting characters.	Currently, temporary tables can be used to replace oracle arrays. See section 2.4 Mysql Array Processing in <2009002-OTMPPS-Difficult Questions-0001.doc>
6	Different annotation Methods	"-- Message" or "/**.... */"Or "/*.... */"	"-- Message" or "/*.... */"Or "#"	Mysql annotation comes from MySQL 5.1 reference manual 9.5. annotation syntax. It is recommended to use single-row --, multi-row/**/like oracle /**/
7	Formats of built-in date and time functions are different	Oracle time format: yyyy-MM-dd hh: mi: ss	Mysql time format: % Y-% m-% d % H: % I: % s	1. mysql date fields include DATE and TIME. The oracle date Field only contains DATE, which contains the year, month, day, hour, minute, and second information. 2. Take the current system time in mysql as the now () function, accurate to seconds. In oracle, the system time of the current database is SYSDATE, accurate to seconds.
8	Date addition/Subtraction	Current Time plus N days: sysdate + N Current Time minus N days: sysdate-N	Date addition: date_add (now (), INTERVAL 180 DAY) Date subtraction: date_sub ('2017-01-01 00:00:00 ', interval '1' day_second)
9	Different string connectors	Result: = v_int1 | v_int2;	Set result = concat (v_int1, v_int2 );	1. oracle uses | connection string or concat function. However, Oracle's concat function can only connect two strings. Mysql uses the concat method to connect a string. MySQL's concat function can connect one or more strings, as shown in Mysql> select concat ('10'); Result: 10. Mysql> select concat ('11', '22', '33', 'aa'); Result: 112233aa 2. "|" in Mysql
10	Different definitions of cursors	CURSOR l_bk_cur IS Select B. BK_HDR_INT_KEY, B. BK_NUM FROM ES_SR_DTL_VRB A, ES_BK_HDR B Where a. BK_HDR_INT_KEY = B. BK_HDR_INT_KEY AND B. BK_STATUS! = ES_BK_PKG.g_status_can And a. SR_HDR_INT_KEY = ii_sr_hdr_int_key;	DECLARE l_bk_cur CURSOR For select B. BK_HDR_INT_KEY, B. BK_NUM FROM ES_SR_DTL_VRB A, ES_BK_HDR B Where a. BK_HDR_INT_KEY = B. BK_HDR_INT_KEY AND B. BK_STATUS! = ES_BK_PKG.g_status_can And a. SR_HDR_INT_KEY = ii_sr_hdr_int_key;	See section 2.2 Mysql cursor processing in <2009002-OTMPPS-Difficult Questions-0001.doc>
11	Transaction rollback	ROLLBACK;	ROLLBACK;	Use the same method in oracle and mysql
12	GOTO statement	GOTO check_date;	GOTO check_date;	Use the same method in oracle and mysql

The above is all the information about the ing between MySQL and Oracle data types.