Oracle Migration Workbench - Part Two - Page 2

November 10, 2004

Getting data into a MySQL database

As an Oracle wizard, no doubt you are intimately familiar with how to create tables and insert data. There is very little difference when using MySQL. Three of the biggest differences are:

• To make a MySQL table more like Oracle, specify the type of table at the end of a create table statement. The type to specify is "InnoDB" and is specified with a "type=InnoDB" clause at the end of the create table statement.

• Your choice of datatypes is quite similar, but note that Oracle's VARCHAR2 is MySQL's VARCHAR, and that number datatypes are slightly different. If you just want a whole number, use "int" and for decimal type numbers, use decimal(L,P) where L is the length and P is the precision.

• MySQL's date format may cause a problem for you as it uses a YYYY-MM-DD format. In a way, that is actually a lot more convenient as there is no doubt as to whether or not you are using day/month or month/day.

If you like SQL*Loader, you will like MySQL's data loading and outputting via an INFILE and OUTFILE. Even before getting to Oracle Migration Workbench, you have the means to transfer data, even if it is just one table at a time.

I have taken the Scott schema and arranged it into a MySQL suitable format. The root user will create the tables and perform the "load data infile" to populate the "msemp" table. The example uses the same table names but with an "ms" placed in front (msemp, msdept, and so on). You can choose how the data is delimited in the infile (comma separated or tab delimited, for example). I used tab delimited to make the data easier to read (plus that is the default), but the CSV version works just as well.

You can create the comma separated version of the infile by using

select empno||','||ename||','||job||','||mgr||','
||to_char(hiredate,'YYYY-MM-DD')||','||sal||','
||comm||','||deptno "MySQL data load example"
from emp;

The tab delimited version can be created in same manner, or you can use the following (I cut off the some letters so the columns line up nicely):

The command syntax to load data using an infile is

load data infile 'c:\\mysql\\load_msemp_table.txt'
into table msemp
ignore 3 lines;

The "ignore 3 lines;" takes care of the two comment lines and the blank line before the data.

Data from the scott.emp table used in
the MySQL tiger.msemp table example

7369	SMITH	CLERK	7902	1980-12-17	800		NULL	20
7499	ALLEN	SALESMA	7698	1981-02-20	1600	300		30
7521	WARD	SALESMA	7698	1981-02-22	1250	500		30
7566	JONES	MANAGER	7839	1981-04-02	2975	NULL	20
7654	MARTIN	SALESMA	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	NULL	10
7788	SCOTT	ANALYST	7566	1987-07-13	3000	NULL	20
7839	KING	PRESIDE	NULL	1981-11-17	5000	NULL	10
7844	TURNER	SALESMA	7698	1981-09-08	1500	0		30
7876	ADAMS	CLERK	7788	1987-07-13	1100	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950		NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300	NULL	10

What follows are the commands to create the tables and use the infile to load the msemp table. Create a data infile and reference where shown below, using the backslash to escape the Windows directory path backslash separator.

use omwb;

create table msdept 
(
  deptno decimal(2) not null primary key,
  dname varchar(14),
  loc varchar(13)
) type=InnoDB;

create table msemp
(
  empno int not null auto_increment primary key,
  ename varchar(10),
  job varchar(9),
  mgr int,
  hiredate date not null,
  sal decimal(7,2),
  comm decimal(7,2),
  deptno int not null references msdept(deptno)
) type=InnoDB;

create table msbonus
(
  ename varchar(10),
  job varchar(9),
  sal int,
  comm int
) type=InnoDB;

create table mssalgrade
(
  grade int,
  losal int,
  hisal int
) type=InnoDB;

load data infile 'c:\\mysql\\load_msemp_table.txt'
into table msemp
ignore 3 lines;

The end result in MySQL, using "show tables;" and "select * from msemp;" is:

Connect as tiger and load the remaining tables.

use omwb;
insert into msdept values
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');
	
insert into mssalgrade values (1,700,1200);
insert into mssalgrade values (2,1201,1400);
insert into mssalgrade values (3,1401,2000);
insert into mssalgrade values (4,2001,3000);
insert into mssalgrade values (5,3001,9999);

If you want to see MySQL's "interpretation" of the OMWB database schema, use the following at a DOS prompt and then view the resulting file:

c:\mysql\bin> mysqldump -u root -padmin --opt omwb > omwb_dumpfile.sql

This should remind you of how you can do the same thing using Oracle's export utility. This concludes the setup phase of MySQL. Take note of the fact that Migration Workbench connects to your MySQL database as the user "root." In a way, the MySQL user named tiger wasn't necessary, but creating him parallels the privileged user versus schema owner concept in Oracle.

Before leaving MySQL, issue a "\s" at the MySQL prompt and note the port number shown (TCP port of 3306 shown below). Migration Workbench will use that port number to establish a connection between your Oracle and MySQL databases.