How to Copy Data from Oracle to MySQL

This example shows how to copy a table from Oracle to MySQL. First create a table in Oracle and add some data, including some null values and a date column:

 

SQL> create table andrews_table

  2  (first_name varchar2(10),

  3   dob        date,

  4   salary     number)

  5  /

 

Table created.

 

SQL> insert into andrews_table values

  2  ('Fred', '25-DEC-1980', null)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  ('Jim', null, 10000)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  (null, '01-SEP-1983', 20000)

  3  /

 

1 row created.

 

SQL> select * from andrews_table

  2  /

 

FIRST_NAME DOB           SALARY

---------- --------- ----------

Fred       25-DEC-80

Jim                       10000

           01-SEP-83      20000

 

SQL>

 

Write a query to extract the data to a file. Note the date format and the way that null values are represented as \N:

 

UNIX > cat extract.sql

set feedback off

set lines 100

set pages 0

set termout off

set trimspool on

spool extract

select nvl(first_name,'\N')||'|'||

       nvl(to_char(dob,'YYYY-MM-DD'),'\N')||'|'||

       nvl(to_char(salary),'\N')

from andrews_table

/

spool off

UNIX >

 

... and run the SQL:

 

SQL> @extract

SQL>

 

The output file will look like this:

 

UNIX > cat extract.lst

Fred|1980-12-25|\N

Jim|\N|10000

\N|1983-09-01|20000

UNIX >

 

Copy it to a machine running MySQL then you can load it into a table as follows:

 

--------------

create table andrews_table

(first_name varchar(10),

dob        date,

salary     int)

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

load data local infile 'extract.lst'

into table andrews_table

fields terminated by '|'

lines terminated by '\n'

(first_name, dob, salary)

--------------

 

Query OK, 3 rows affected (0.00 sec)

Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

 

--------------

select * from andrews_table

--------------

 

+------------+------------+--------+

| first_name | dob        | salary |

+------------+------------+--------+

| Fred       | 1980-12-25 |   NULL |

| Jim        | NULL       |  10000 |

| NULL       | 1983-09-01 |  20000 |

+------------+------------+--------+

3 rows in set (0.00 sec)

 

mysql>

MySQL Analyze Command

You can analyze a table to update its statistics as follows:

 

--------------

create table andrews_table like

information_schema.PROCESSLIST

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

insert into andrews_table select * from

information_schema.PROCESSLIST

--------------

 

Query OK, 12 rows affected (0.00 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

--------------

analyze table andrews_table

--------------

 

+--------------------+---------+----------+----------+

| Table              | Op      | Msg_type | Msg_text |

+--------------------+---------+----------+----------+

| test.andrews_table | analyze | status   | OK       |

+--------------------+---------+----------+----------+

1 row in set (0.00 sec)

Modifying Column Definitions in MySQL

If you change a column definition, MySQL converts the data in that column for you. If it finds data which it cannot convert, it displays a warning and you can see details by using the show warnings command. In the example, a varchar column is converted to int. MySQL is unable to convert the value ABC so it sets it to zero instead:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into andrews_table

    -> values ('ABC');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table

    -> values ('123');

Query OK, 1 row affected (0.00 sec)

 

mysql> alter table andrews_table

    -> change col1 col1 int;

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

(N.B. I have reformatted the output from the show warnings command to make it fit on the page.)

mysql> show warnings;

+---------+------+-----------------------------------+

| Level   | Code | Message                           |

+---------+------+-----------------------------------+

| Warning | 1366 | Incorrect integer value:          |
|         |      | 'ABC' for column 'col1' at row 1  |

+---------+------+-----------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_table;

+------+

| col1 |

+------+

|    0 |

|  123 |

+------+

2 rows in set (0.00 sec)

 

mysql>

MySQL Length Function

You can use the length() function to see how long column values in a table are as follows:

 

mysql> insert into andrews_table values ('Joe');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Brian');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Andrew');

Query OK, 1 row affected (0.00 sec)

 

mysql> select col1, length(col1) from andrews_table;

+--------+--------------+

| col1   | length(col1) |

+--------+--------------+

| Andrew |            6 |

| Brian  |            5 |

| Fred   |            4 |

| Joe    |            3 |

+--------+--------------+

4 rows in set (0.00 sec)

 

mysql>

MySQL Limit Clause

You can restrict the number of rows a query returns by using the limit clause at the end. It is followed by 2 numbers, which are separated by a comma. The first number tells the query which row to start at (the table's first row is numbered zero). The second number tells the query how many rows to return. You can see what I mean in the examples below:  

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into andrews_table values ('Brian');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Joe');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table values ('Andrew');

Query OK, 1 row affected (0.00 sec)

 

mysql> select col1 from andrews_table limit 0,2;

+-------+

| col1  |

+-------+

| Brian |

| Joe   |

+-------+

2 rows in set (0.00 sec)

 

mysql> select col1 from andrews_table limit 2,2;

+--------+

| col1   |

+--------+

| Fred   |

| Andrew |

+--------+

2 rows in set (0.00 sec)

 

mysql> select col1 from andrews_table limit 1,3;

+--------+

| col1   |

+--------+

| Joe    |

| Fred   |

| Andrew |

+--------+

3 rows in set (0.00 sec)

 

mysql>

MySQL create table ... like ...

If you want to create a table with the same description as another table, you can do this using like. In the example below, I create a table called andrews_table with 1 row of data. Then I use like to create a table called another_table with the same description. This table is empty to start with. To copy the data from andrews_table into another_table, I use a simple insert statement:

 

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.03 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.12 sec)

 

mysql> create table another_table

    -> like andrews_table;

Query OK, 0 rows affected (0.00 sec)

 

mysql> desc another_table;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| col1  | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.06 sec)

 

mysql> select * from another_table;

Empty set (0.00 sec)

 

mysql> insert into another_table

    -> (select * from andrews_table);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from another_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>