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

This article introduces the relevant knowledge of "what do you need to pay attention to in the migration of MYSQL to ORACLE programs?" in the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

There are many application projects, at the beginning, using MYSQL database can basically achieve a variety of functional requirements. With the increase of application users and the increase of the amount of data, MYSQL is gradually overburdened: the connection is very slow or even downtime, so there is a need to move the data from MYSQL to ORACLE, and the application should be modified accordingly. I would like to summarize the following points for attention, hoping to be helpful to you.

1. Auto-growing data types processing MYSQL has auto-growing data types. You don't have to operate this field when inserting records, and you will automatically get the data value. 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. The name of the CREATE SEQUENCE serial number (preferably table name + serial number tag) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE; where the maximum value is determined by the length of the field, if the auto-growing serial number NUMBER (6) is defined, the maximum value is 999999 INSERT statement insert this field value is: the name of the serial number. NEXTVAL

two。 Dealing with single quotation marks you can wrap strings in double quotes in MYSQL and only in single quotation marks in ORACLE. Single quotation marks must be replaced before inserting and modifying strings: replace all occurrences of one single quote with two single quotation marks.

3. The processing of page turning SQL statement MYSQL handles page turning SQL statement is relatively simple, using the LIMIT start position, record the number; PHP can also use SEEK to locate the result set. It is more tedious for ORACLE to deal with SQL statements that turn pages. Each result set has only one ROWNUM field to indicate its location, and can only use ROWNUM80. The following are two better ORACLE page flipping SQL statements after analysis (ID is the field name of the only keyword): statement one: 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 条件3; 语句二： SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW >

80 AND NUMROW

< 100 ) ORDER BY 条件3; 4． 长字符串的处理 长字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告, 返回上次操作。 5. 日期字段的处理 MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒，或者用字符串转换成日期型函数 TO_DATE('2001-08-01','YYYY-MM-DD') 年-月-日 24小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC. 日期型字段转换成字符串函数TO_CHAR('2001-08-01','YYYY-MM-DD HH24:MI:SS') 日期字段的数学运算公式有很大的不同。 MYSQL找到离当前时间7天用 DATE_FIELD_NAME >

SUBDATE ((NOW (), INTERVAL 7 DAY) ORACLE found 7 days from the current time with DATE_FIELD_NAME > SYSDATE-7; 6. Dealing with null characters the non-empty field of MYSQL also has empty content. If non-empty field is defined in ORACLE, empty content is not allowed. 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. 7. Fuzzy comparison of strings in MYSQL Field name like'% string% 'ORACLE can also use field name like'% string% 'but this method cannot use index, so it is not fast to use string comparison function instr (field name, string) > 0 to get more accurate search results. In programs and functions, please pay attention to the release of result sets and pointers after the work of manipulating the database is completed.

Undefined

This is the end of the content of "what do you need to pay attention to when migrating MYSQL to ORACLE programs"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.