One data type comparison between MySQL and Oracle

One of the data types to compare the differences between MySQL and Oracle

Data type Number ORACLE MYSQL Comment

1 NUMBER int / DECIMAL DECIMAL is NUMBER (10, 2) Such a structure INT is NUMBER (10), which represents an integer type;

MYSQL has many types of int types, tinyint mediumint bigint, etc. Different int widths are different

2 Varchar2 ( n) varchar(n)

3 Date DATATIME Date field processing

MYSQL date field is divided into DATE and TIME. ORACLE date field only has DATE, which contains year, month, day, hour, minute and second information. Use The system time of the current database is
SYSDATE, accurate to seconds, or use the string to convert the date function TO_DATE ('2001-08-01', 'YYYY-MM-DD') year-month-day
The format of 24 hours: minutes: seconds is YYYY-MM-DD HH24: MI: SS TO_DATE() and there are many date formats.
You can refer to ORACLE DOC. The date field is converted into a string function TO_CHAR('2001-08 -01','YYYY-MM-DD HH24:MI:SS')

The mathematical formulas for date fields are very different. MYSQL finds 7 days from the current time using DATE_FIELD_NAME
＞SUBDATE(NOW(),INTERVAL 7 DAY) ORACLE finds 7 days from the current time using
DATE_FIELD_NAME＞SYSDATE - 7;

Insert the current time in MYSQL Several functions of time are: The NOW() function returns the current date and time in `'YYYY-MM-DD HH:MM:SS', which can be directly stored in the DATETIME field. CURDATE() returns today's date in the format of 'YYYY-MM-DD', which can be directly stored in the DATE field. CURTIME() returns the current time in the format of 'HH:MM:SS', which can be directly stored in the TIME field. Example: insert
into tablename (fieldname) values ​​(now())

The current time in Oracle is sysdate

4 INTEGER int / INTEGER INTEGER in Mysql is equivalent to int

5 EXCEPTION SQLEXCEPTION For details, see 2.5 Mysql exception handling in <<2009001-eService-O2MG.doc>>

6 CONSTANT VARCHAR2(1) There is no CONSTANT keyword in mysql Migrate from ORACLE to MYSQL, all CONSTANT constants can only be defined as variables

7 TYPE g_grp_cur IS REF CURSOR; Cursor: There are alternatives in mysql. For details, see 2.2 Cursor Processing in <<2009001-eService-O2MG.doc>>

8 TYPE unpacklist_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; Array: use temporary table processing in mysql

or write logic directly into the corresponding code,

Directly perform corresponding processing on each value in the collection. For details, see 2.4 Array Processing in <<2009001-eService-O2MG.doc>>

9 Automatically growing sequence Automatically growing data type MYSQL has automatic For the growing data type, there is no need to operate this field when inserting records, and the data value will be obtained automatically. ORACLE does not have an auto-increasing data type. It is necessary to create an auto-increasing sequence number. When inserting a record, the next value of the sequence number must be assigned to this field. ​

10 ​ NULL ​ NULL ​ Handling of null characters

MYSQL's non-null fields also have empty content. ORACLE defines non-null fields and does not allow empty content. Define the ORACLE table structure according to MYSQL's NOT NULL.
An error will occur when importing data. Therefore, when importing data, you need to judge the null character. If it is NULL or a null character, you need to change it into a space string.

The above is the content of one of the data types in the difference comparison between MySQL and Oracle. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!