Detailed introduction to the differences between MYSQL and ORACLE

There are many shoulduseitems, just Use MYSQL# when DataLibrary Basically can realize various types of functions Demand, with the increase of applicationusers, the amount of data increases,MYSQL GraduallyThe earth emergesNowoverwhelmednegative situation:Continuous The connection is very slow or even down, so there is a need to migrate the data from MYSQL to ORACLE, The application programshould also shouldmake some modifications accordingly. SummaryThe following points are noteditems. 1.
automatic Add long dataclass TypeProcessManagement MYSQLYes AutoMoveincreaselong
datatype,insertWhen recordingThere is no need to operate this field, the datavalue will be automatically obtained. ORACLENo automatic #increaselongdata Type , you need to create a serial number that automatically increases and is long, insert When recordingThe next value of the serial number should be assigned to this field. CREATE SEQUENCE The name of the serial number (is preferably the table name+
Serial numberMark) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;The largest valueis determined by the length of the field, If the defined define automaticincreaselong sequence Number Number (6), The largest value is 999999 Insert
# #Sentence Insert The fields have the value : The name of the serial number. NEXTVAL2.
#ProcessingManagement##                                                                String can be wrapped in double quotes in MYSQL##, ORACLE## In
#, only single quotation marks can be used to wrap the string. Before inserting or modifying a string, mustreplace single quotes Replace : Replace all with a single quotation mark with for into two single quotation marks. 3. Turn over
pageSQL
############ ########deal with###############MYSQLDepartmentManagementTurn ##SQL# The ## language sentence is simpler than , use LIMIT to open Starting position , record number; in PHP is still You can use SEEK to locate the fruit set. ORACLE DepartmentManagementTurn over The #SQL language sentence is more complicatedcomplicated. Each resultresult set has only oneROWNUMfieldMark its location , and only use ROWNUM<100, cannot use ROWNUM>80
.   The following is afteranalysisbetter##ORACLETurn pageSQL语句(ID is the field name with the only key ):

## #

SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN (
 　　SELECT ID
 FROM (
　　　　SELECT ROWNUM AS NUMROW, ID
　　　　FROM TABLE_NAME
　　　　WHERE 条件1
　　　　ORDER BY 条件2)
WHERE NUMROW > 80 AND NUMROW < 100 )
ORDER BY 条件3;
;

4. Handling of long strings
Long string部理ORACLE also has its special place. INSERT and UPDATE#Maximum operable stringLong Degree is less than or equal to 4000singlewordssection, If you want to insert a longer \Consider the field to be of CLOB class type, and the method to be borrowed from ORACLE ##DBMS_LOB package with . Insert modificationBefore recordingBe sure to doEnterThe line is not empty andLong Degree judgment, cannot
be
empty field value and exceed length
Degree field######Values######all######should######raise a warning######,######Return to the last time operate. ############5. Date field #########processing###############MYSQL date fields are divided into two types: DATE and TIME, ORACLE The date field only has DATE, which contains the year, month, day, hour, minute and second information, using the current DataThe system time of library is SYSDATE, accurate to seconds , or use the string to convert into a date function TO_DATE('2001-08-01','YYYY-MM-DD')年-月-日 24小hour: MinutesClocks:Seconds Format of YYYY-MM-DD HH24:MI:SS TO_DATE()There are many#date formats, See ORACLE DOC.Date fieldConvert into string function TO_CHAR('2001-08-01','YYYY-MM-DD HH24:MI:SS')     The mathematical formulas for date fields are very different.
MYSQL
Find days away from the current 7 DATE_FIELD_NAME > SUBDATE（NOW（），INTERVAL 7 DAY）ORACLE Find 7 days away from the current time using DATE_FIELD_NAME >SYSDATE - 7;6. Null character

deal withMYSQL's non-empty fields also have empty content. If ORACLE is defineddefinition, non-empty fields will not be allowed. XuEmpty content. Press MYSQLNOT NULL to define the definitionORACLETable Structure Structure, DirectorData Sometimes #occurrenceerror occurs. Therefore When importing data#should#null characters Check the line. If is NULL or a null character, you need to change it to a space string.
7. Fuzzy comparison of strings
Used in MYSQL
Field name like '%String%', ORACLE can also be usedField name like '%String%' But this method cannot use index , The speed is not fast, use string comparison function instr(Field name,'String')>0 will get more accuratesearchfindresult .
8. In programs and functions, please pay attention to the results after the work of operating
datalibrary is completed. Release of sets and pointers.

The above is the detailed content of Detailed introduction to the differences between MYSQL and ORACLE. For more information, please follow other related articles on the PHP Chinese website!