Differences and interchange of oarcle mysql Fields

There are many application projects for the differences and interchange of oarcle mysql fields. At the beginning, MYSQL Databases can basically meet various functional requirements. As the number of application users increases, the amount of data increases, MYSQL gradually became overwhelmed: the connection was slow or even down, so there was a need to migrate data from MYSQL to ORACLE, and the application had to make some modifications accordingly. Summarize the following considerations. Description: mysql oracle VARCHAR variable-length string VARCHAR [0-65535] defined length is calculated by character length by default, for GBK-Encoded chinese characters, VARCHAR2 [1-4000] VARCHAR is a synonym for VARCHAR2. The default byte length is tinyint smallint mediumint intbigint integer TINYINT (-128-127) SMALLINT (-32768-32767) MEDIUMINT (-8388608-8388607) INT (-2147483648-2147483647) BIGINT (-9223372036854775808-9223372036854775807) has no dedicated type. TINYINT can use NUMBER) instead of SMALLINT, you can use NUMBER (5, 0) instead of MEDUIMINT. You can use NUMBER (7, 0) instead of INT. To replace BIGINT with NUMBER (), you can replace SMALLINT, INT, and INTEGER in ORACLE with NUMBER (). However, this is NUMBER) decimalnumeric numeric type DECIMAL [1-65 [, 0-30] NUMERIC is a synonym for DECIMAL. NUMBER can represent the NUMBER range: 1*10 ^-130 to 1*10 ^ 126 NUMBER ([1-38] [,-84-127]) DECIMAL, NUMERIC, and DEC are synonyms of number float (D, M) oracle10g. The BINARY_FLOAT type is increased by 10g. No dedicated type is available before. You can use NUMBER to replace the FLOAT and REAL types in ORACLE, however, this is the synonym for DOUBLE (D, M) of NUMBER. oracle10g begins to add BINARY_DOUB. The LE type has no special type before 10 Gb. You can use NUMBER to replace the double precision type in ORACLE. However, this is a synonym for NUMBER. BIT (1-64) does not have datetime date type, 3-byte storage, only date storage, no TIME, supported range: [1000-01-01] to [9999-12-31] TIME, 3-byte storage, only storage TIME, no date. The supported range is [-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 and can represent the date and time, the value range is [00:00:00] to [2038-01-19 03:14:07] DATE-type 7-byte storage, which can represent the DATE and time. The value range is [-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, up to 6 decimal seconds, that is, the microsecond-level TIMESTAMP [0-9] occupies 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 digits, that is to say, the YEAR, one-byte storage, only the YEAR is stored, the range is [1901] to [2155] no corresponding type, NUMBER () can be used) instead of CHAR [0-255], the defined length is calculated based on the Character length by default, A maximum of 255 characters can be saved in CHAR [1-2000]. By default, the UNSIGNED description is supported by byte length. It is used for numeric types that do not support CLOB large strings, it is generally used to store TEXT files or ultra-large descriptions and remarks. TINYTEXT supports up to 255 bytes of TEXT and up to 655 bytes of TEXT. The 35-byte MEDIUMTEXT supports up to 16 MB of LONGTEXT. up to 4 GB of fields are supported. The default value is not supported (CLOB). The maximum value is 4 GB of data blocks starting from 4 GB of oracle10g, 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. We do not recommend using BLOB large binary objects, it is generally used to store files or image data. TINYBLOB supports up to 255 bytes. BLOB supports up to 65535 bytes. MEDIUMBLOB supports up to 16 Mb. LONGBLOB supports up to 4 GB bytes. fields do not support default values. (BLOB) previously, oracle10g supported a maximum of 4 GB of data blocks starting from oracle10g. The data block size was 2KB-32KB. oracle also had a long raw type, which is an early binary storage type and supports a maximum of 2 GB of data blocks, BI is not recommended now Nary binary information BINARY (0-255), fixed-length VARBINARY (0-65535), extended RAW (1-2000) ENUM Enumeration type ENUM (v1, v2, v3 ,...), up to 65535 elements 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) for the specified character encoding for each field) nclob bfile external file pointer type not supported file size up to 4 GB file name up to 255 characters custom data type not supported XML type not supported Support auto-increment Type Auto-increment type supported simple not supported SEQUENCE is generally used to solve the problem, the usage differs greatly from the auto-increment type and is complex to use. However, it can be used flexibly, including the Character Auto-incrementing primary key, global primary key, and Other Default field expressions. Function and expression are not supported. Type TEXT and BLOB field types do not support default value support function and expression field order modification support, 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 the virtual field of the table or field can be rebuilt. The virtual field is a logical field definition, and the 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 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) A maximum of 1000 fields. the Automatically increasing data type processes MYSQL Data Types that automatically increase. When you insert a record, you do not need to operate on this field and 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. Create sequence serial number name (preferably table name + Serial number mark) increment by 1 start with 1 MAXVALUE 99999 cycle nocache; the maximum value is determined BY the length of the field, if the defined auto-increment serial NUMBER (6) is used, the maximum value is 999999 INSERT. INSERT this field value as the serial NUMBER name. NEXTVAL 2. MYSQL can use double quotation marks to enclose strings. ORACLE can only use single quotation marks to enclose strings. You must replace single quotes before inserting and modifying strings: replace all the existing single quotes with two single quotes. 3. Processing of paging SQL statements mysql SQL statements that process paging is relatively simple. Use LIMIT to start the position and record the number. In PHP, you can also use SEEK to locate the result set. It is complicated for ORACLE to process paging SQL statements. Each result set has only one ROWNUM field to indicate its position, and only ROWNUM <100, not ROWNUM> 80 can be used. The following are two SQL statements (ID is the field name of the unique keyword) that have been analyzed: Statement 1: SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME where id in (select id from (select rownum as numrow, id from TABLE_NAME WHERE Condition 1 order by condition 2) where numrow> 80 and numrow <100) order by Condition 3; statement 2: SELECT * FROM (select rownum as numrow, c. * from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE Condition 1 order by condition 2) c) where numrow> 80 and numrow <100) Order by Condition 3; 4. Processing of long strings in ORACLE also has a special place. The maximum length of a string that can be operated during INSERT and UPDATE is less than or equal to 4000 single bytes. If you want to INSERT a longer string, use the CLOB type for the field to use the DBMS_LOB package that comes with ORACLE. Before inserting a modification record, make sure to judge non-null and length. The field values that cannot be empty and those that exceed the length should both be proposed. 5. the DATE Field Processing mysql date Field is divided into two types: DATE and TIME. The oracle date Field only contains DATE, 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 convert the string to the date type function TO_DATE ('<st1: chsdate isrocdate = "False" islunardate = "False" day = "1" month = "8" year = "2001"> </st1: chsdate> ', 'yyyy-MM-DD ') 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 a datetime field to a string function TO_CHAR ('<st1: chsdate isrocdate = "False" islunardate = "False" day = "1" month = "8" yea R = "2001"> </st1: chsdate> ', 'yyyy-MM-DD HH24: MI: ss') the mathematical formula of the date field is very different. MYSQL uses DATE_FIELD_NAME> SUBDATE (NOW (), INTERVAL 7 DAY) to locate the current time seven days from the current time using DATE_FIELD_NAME> SYSDATE-7; 6. empty Character Processing MYSQL non-empty fields are also empty content, ORACLE defines non-empty fields are not allowed to be empty content. 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. 7. fuzzy comparison of strings MYSQL uses the field name like '% string %', and ORACLE can also use the field name like '% string %'. However, this method cannot use indexes and is not fast, if you use a string to compare the instr function (field name, 'string') to 0, you will get more accurate search results. 8. After operating the database in programs and functions, pay attention to the release of the result set and pointer.