Home  >  Article  >  Database  >  Detailed introduction to the differences between MYSQL and ORACLE

Detailed introduction to the differences between MYSQL and ORACLE

零下一度
零下一度Original
2017-05-03 17:22:461193browse

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
data
type,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 number
Mark) 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
page
SQL
############ ########deal with###############MYSQL
DepartmentManagementTurn ##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 ROWNUM80923c9adefa1c0b10a7f43cf66d0e6680
.
  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 stringORACLE 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('f2e5662023d030556474f65f4a62f6072001-08-011d84dfbc2d9afb778cee7bc27cb24dfc','YYYY-MM-DD')-- 24hour: 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('0f16cad270f7bd1cd78278e4825ec4672001-08-011d84dfbc2d9afb778cee7bc27cb24dfc','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 > SUBDATENOW(),INTERVAL 7 DAYORACLE 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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn