Migration table structure

Using the Navicat tool, you can easily migrate as follows:

  1. Create a new user and synchronize the table structures that need to be migrated (skip this step if data needs to be synchronized);
    • Create user: create user XXX identified by PPP;
    • Grant dba to XXX;
    • Click on the Navicat menu bar: Tools > Structure Sync and follow the steps
    • The database comparison operation is slow. Wait until ~





2. Create a table structure using the data transfer function

  • Click Navicat menu bar: Tools –> Data Transfer, follow the steps to enter
  • If there are no tables in MySQL, the tool will create them automatically







At this point, the table structure is migrated

The migration view

There is no easy way to migrate views except manually because Oracle and MySQL functions are inconsistent and the syntax is different.

This is the function used to migrate the view from Oracle to MySQL. It is not complete, and will be updated continuously

  1. In the from view, Oracle allows subqueries but MySQL does not allow subqueries. You can create an additional view for subqueries and use it in this view. Note that: If the database is migrated to another database, some views may fail to be created when the SQL dump is used. For example, if view B is referenced in view A, the view A is created in the DUMPED SQL file before view B exists. As A result, the creation of view A fails. Of course, it is not recommended to use views too much, especially if they are nested;
  2. Oracle NVL function corresponds to MySQL IFNULL;
  3. Oracle wm_concat corresponds to group_concat in MySQL.
  4. Oracle uses — for comments, MySQL uses #;
  5. Oracle uses to_number to convert to a number. MySQL has three ways to convert to a number:
    • +0 is converted to a number after a string or string field;
    • CAST(‘5.45’ AS SIGNED) converts to an integer, and CAST(‘5.45’ AS DECIMAL(9,2)) converts to a floating point number;
    • CONVERT(’67’,SIGNED) to integers, CONVERT(‘67.35’,DECIMAL(9,2)) to floating point numbers;
  6. To_char (); to_char (); to_char (); to_char ();
    • Converts + “after a number or number field to a string;
    • CAST(123 AS CHAR) converts to a string;
    • CONVERT(123, CHAR) CONVERT to a string;
  7. Decode can use the MySQL if function, but if function is not as powerful as decode, can also use case when then [when then] [else] end;
  8. Date_format (date_format); date_format (date_format); trunc (date_format); Trunc (sysdate, ‘mm’); DATE_FORMAT(sysdate(), ‘%Y-%m-01’);
  9. MySQL uses CURRENT_TIMESTAMP(), LOCALTIME(), NOW(), sysdate ();
  10. The add_months function in Oracle corresponds to date_add and date_sub in MySQL.
  11. Oracle uses to_char to convert dates to strings, MySQL uses date_format, and formats wildcards differently: Oracle yyyY-MM-DD HH24 :mi:ss, MySQL %Y-%m-%d %H:% I :%S;