Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of the corresponding relationship between MySQL and Oracle data types. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Both MySQL and Oracle databases are widely used in work. Because there are some differences in data types between MySQL and Oracle, we will encounter some trouble when we migrate the database. The corresponding relationship between MySQL and Oracle database data types is introduced below.

1. The expression of common data types in MySQL and Oracle databases

Description

Mysql

Oracle

Variable length string

VARCHAR [0-65535]

The definition length is calculated by default according to the character length, and the GBK-encoded Chinese characters will take up 2 bytes.

VARCHAR2 [1-4000]

VARCHAR is synonymous with VARCHAR2

Define the default calculation by byte length

Integer number

TINYINT (- 128127)

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 (1010 0)

BIGINT can be replaced by NUMBER (2010).

There is a SMALLINT,INT,INTEGER type in ORACLE, but this is a synonym for NUMBER

Numerical type

DECIMAL [1-65 [, 0-30]]

NUMERIC is synonymous with DECIMAL

NUMBER can represent a range of numbers: 1 * 10 ^-130 to 1 * 10 ^ 126

NUMBER ([1-38] [,-84-127])

DECIMAL, NUMERIC and DEC are synonyms for NUMBER

Floating point type

FLOAT (DPerm)

Oracle10g begins to add BINARY_FLOAT types

There is no special type before 10g, and NUMBER can be used instead.

There are FLOAT and REAL types in ORACLE, but this is synonymous with NUMBER

Double precision floating point type

DOUBLE (DPerm)

Oracle10g begins to add BINARY_DOUBLE types

There is no special type before 10g, and NUMBER can be used instead.

There is a DOUBLE PRECISION type in ORACLE, but this is synonymous with NUMBER

Bit type

BIT (1-64)

None

Date Typ

DATE,3 byte storage, only date storage, no time, support range from [1000-01-01] to [9999-12-31]

TIME,3 byte storage, only time storage, no date. Support range is from [- 838 59] to [838 59].

DATETIME, which occupies 8 bytes of storage and can represent date and time, and can range from [1000-01-01 00:00:00] to [9999-12-31 23:59:59]

TIMESTAMP, which occupies 4 bytes of storage and can represent a date and time, ranging from [1970-01-01 00:00:00] to [2038-01-19 03:14:07]

DATE Typ

7-byte storage, which can represent date and time, and can range from [- 4712-01-01 00:00:00] to [9999-12-31 23:59:59]

High precision date

5.6.4 fractional second precision was not supported before

5.6.4 starting with TIME,DATETIME,TIMESTAMP support, up to 6 decimal seconds, that is, microseconds

TIMESTAMP [0-9]

Occupies 7-11 bytes, which is the same as DATE type when the fractional second precision is 0, and the highest fractional second precision can reach 9 digits, that is, nano precision.

Year

YEAR,1 byte storage. Only years are stored. The range of support is from [1901] to [2155].

There is no corresponding type, you can use NUMBER (3pm 0) instead.

Fixed length string

CHAR [0-255]. The defined length is calculated according to character length by default, with a maximum of 255characters.

CHAR [1-2000]

Define the default calculation by byte length

Unsigned description

Support, for numeric types

Not supported

Large string, commonly used to store text files or oversized descriptions and comments

TINYTEXT supports a maximum of 255byt

TEXT supports up to 65535 bytes

MEDIUMTEXT supports up to 16MB bytes

LONGTEXT supports up to 4GB bytes

Field does not support default values

Support (CLOB)

Oracle10g used to support a maximum of 4GB bytes

Oracle10g starts to support a maximum of 4GB blocks with a block size of 2KB-32KB

Oracle also has a LONG type, which is an early storage big string type that supports up to 2GB bytes and is no longer recommended.

Binary object, typically used to store file or picture data

TINYBLOB supports a maximum of 255byt

BLOB supports up to 65535 bytes

MEDIUMBLOB supports up to 16MB bytes

LONGBLOB supports up to 4GB bytes

Field does not support default values

Support (BLOB)

Oracle10g used to support a maximum of 4GB bytes

Oracle10g starts to support a maximum of 4G blocks with a block size of 2KB-32KB

Oracle also has a LONG RAW type, which is an early storage binary type and supports up to 2GB bytes. It is no longer recommended.

Binary information

BINARY (0-255), fixed length

VARBINARY (0-65535), longer

RAW (1-2000)

Enumerated type

ENUM (v1 and v2, and 3), up to 65535 elements

Not supported

Collection type

SET (v1 and v2, and v3), up to 64 elements

Not supported

International character set type, less used

None, MYSQL can specify character encoding for each field

Support

NCHAR (1-2000)

NVARCHAR (1-4000)

NCLOB

External file pointer type

Not supported

Support

Maximum file size 4GB

The file name is up to 255 characters long

Not supported

Support

Not supported

Support

Automatic growth type

Support

Easy to use

Not supported

Generally use SEQUENCE solution, usage and self-increment type is quite different, the use is more complex, but can achieve very flexible applications, including character self-increment primary key, global primary key and so on

Functions and expressions are not supported

Default values are not supported for TEXT and BLOB field types

Support for functions and expressions

Yes, for example, put 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 whose result value is usually an expression and stores physical values in the table, which does not take up space, and is mainly used to simplify query logic. For example, if there is a commodity sales table with two fields: unit price and quantity, you can create a virtual field amount with the expression = unit price * quantity.

Not supported

11g support, for example:

Create table sales

(

Id number

Quantity number

Price number

Amount GENERATED always as (quantity*price) virtual

);

Maximum 1000 fields for INNODB

The total definition length of all fields cannot exceed 65535 bytes

The total length of all fixed length fields does not exceed half of the block size (the block size is generally 16K)

Up to 1000 field

2. The corresponding relationship between MySQL and common data types in Oracle database

The serial number ORACLEMYSQL annotation 1NUMBERint / DECIMALDECIMAL is NUMBER (10Magne2). Such a structure INT is NUMBER (10), which represents an integer.

MYSQL has many types of int, tinyint mediumint bigint, etc., different int widths are different 2Varchar2 (n) varchar (n) 3DateDATATIME date field processing

MYSQL date fields are divided into DATE and TIME. Only DATE is available in the ORACLE date field, which contains the information of year, month, day, hour, minute and second. 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) year-month-day 24 hours: minute: second format YYYY-MM-DD HH24:MI:SS TO_DATE () there are many date formats, see ORACLE DOC. Convert date field to string function TO_CHAR ('2001-08-01 "grammar" YYYYMMI MmurDD HH24:MI:SS')

The mathematical formulas of the date field are very different. MYSQL finds 7 days from the current time using DATE_FIELD_NAME > SUBDATE (NOW (), INTERVAL 7 DAY) ORACLE to find 7 days from the current time using DATE_FIELD_NAME > SYSDATE-7

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

The current time in oracle is the equivalent of INTEGER in sysdate4INTEGERint / INTEGERMysql to int5EXCEPTIONSQLEXCEPTION in 2.5 Mysql exception handling 6CONSTANT VARCHAR2 (1) there is no CONSTANT keyword migration from ORACLE to MYSQL in mysql, all CONSTANT constants can only be defined as variables 7TYPE g_grp_cur IS REF CURSOR; cursor: there are alternatives in mysql see 8TYPE unpacklist_type IS TABLE OF VARCHAR2 (2000) INDEX BY BINARY_INTEGER; array in mysql: temporary table processing in mysql

Or write the logic directly into the corresponding code

Direct processing of each value in the collection details see Array processing 9 automatically growing data types MYSQL has automatically growing data types, insert records do not need to operate this field, will automatically obtain data values. ORACLE does not have an auto-growing data type, so you need to create an auto-growing serial number, and when you insert a record, you assign the next value of the serial number to this field. Processing of 10NULLNULL Null characters

MYSQL's non-empty fields also have empty content, and ORACLE defines non-empty fields that do not allow empty content. Define the structure of the ORACLE table according to the NOT NULL of MYSQL, and errors will occur when importing data. Therefore, it is necessary to judge the empty character when importing the data, and if it is a NULL or empty character, you need to change it to a space string.

The basic Grammar of difference comparison between MySQL and Oracle

The numbered category ORACLEMYSQL comment 1 variable is declared in a different way li_index NUMBER: = 0DECLARE li_index INTEGER DEFAULT 01. Mysql uses DECLARE to define local variables.

Define the variable syntax as: DECLARE var_name [,...] Type [DEFAULT value] to provide a default value for a variable, you need to include a DEFAULT clause. Value can be specified as an expression and does not need to be a constant. If there is no DEFAULT clause, the initial value is NULL. 2 variables are assigned in different ways: lv_inputstr: = iv_inputstrSET lv_inputstr = iv_inputstr1. The oracle variable assignment uses: =

Mysql uses the set keyword for assignment. When assigning a value to a variable, use the "=" .3 jump (exit) statement different from 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 procedure or method.

Mysql: if the leave statement is followed by a stored procedure name, exits the current stored procedure. If the leave statement is followed by a lable name. Exits the current lable.

While conditional loop

Exit

End loop;label_name:while conditional do

Leave label_name

End while label_name;4 defines cursor TYPE g_grp_cur IS REF CURSOR

DECLARE cursor_name CURSOR FOR SELECT_statement;oracle can define cursors and then assign values to cursors.

Mysql needs to assign values to cursors when defining cursors. The Mysql definition cursor is from the Mysql 5.1 reference manual 20.2.11.1. Declaration cursor .5 defines an array TYPE unpacklist_type IS TABLE OF VARCHAR2 (2000) INDEX BY BINARY_INTEGER; can use temporary tables instead of oracle arrays, or loop split characters instead of oracle arrays. Currently, temporary tables can be used instead of oracle arrays.

For details, see "--message" or "/ * * …. * /" or "/ * …. * /"-- message "or" / *.... * / "or" # "mysql annotations from MySQL 5.1 reference manual 9.5. Comment syntax, the same as oracle, single-line, multi-line / * * / 7 with date-time function format different Oracle time format: yyyy-MM-dd hh:mi:ssMysql time format:% Y-%m-%d% H:%i:%s1. The MYSQL date field is divided into DATE and TIME.

The ORACLE date field contains only DATE, which contains information about the year, month, day, hour, minute and second.

2. In mysql, take the current system time as the now () function, accurate to seconds.

The system time of the current database in oracle is SYSDATE, accurate to seconds. 8-day plus or minus current time plus N days: sysdate+N

Current time minus N days: sysdate-N date add: date_add (now (), INTERVAL 180 DAY)

Date subtraction: date_sub ('1998-01-01 00 result 0000, interval' 1 1day_second 1day_second) 9 string concatenation different result: = v_int1 | | concat int1 match set result = concat)

1. Oracle uses | | connection string, or you can use the concat function. However, the concat function of Oracle can only concatenate two strings.

Mysql uses the concat method to concatenate strings. The concat function of MySQL can concatenate one or more strings, such as

Mysql > select concat ('10'); the result is: 10.

Mysql > select concat ('11', 22', 22, 33, 33): 112233aa

2. "| |" in Mysql, it is different from the cursor defined by operation 10 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

For details, see Mysql cursor processing section 11 transaction rollback ROLLBACK;ROLLBACK;oracle and mysql using the same 12GOTO statement GOTO check_date;GOTO check_date Oracle and mysql use the same method on "sample analysis of the corresponding relationship between MySQL and Oracle data types" this article shares here, I hope the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.