Oracle to Mysql Migration Guide

Oracle to Mysql Migration Guide

Oracle Statments	Equivalent Mysql Statements
Sequence	Mysql doesn’t support Sequence, Use auto_increment attribute for the column during table creation.
Rownum	Limit
Concat(+)	“+” wont work in Mysql use concat() function
Joins with annotations(+)	Using (+) in joins mysql won’t recognize. Explicitly specify the type of join you use
To_char(systimestamp,’date format’)	Date_format(systimestamp,’date format’) %Y – year(2012) %m – month(06) %d – date(22) %b – month in letters (JUN) %T – time in 24 hour format
Milliseconds	Doesn’t give timestamp with milli seconds
NVL()	Ifnull()
Decode()	Case when then statement
Trunc()	Use date_format(column name,’%d-%b-%Y’)
Select…. For update no wait statement	Select …. For update statement
Sysdate + 1 = add 1 with the current date	ADD_DATE(sysdate(),1)
Sysdate - 1 = subtract 1 from the current date	DATE_SUB(curdate(), interval 1 day)
Key	Donot use “key” as column, it is a Mysql keyword
Sysdate	Sysdate() / curdate()
Systimestamp	Systimestamp()
While fetching int value from db, Oracle will give a BigDecimal Object by default	While fetching int value from db, Mysql will give a BigInteger Object by default
Oracle’s table / column names are non case-sensitive	Mysql table / column name are case-sensitive.       To make Mysql’s table / column names case-sensitive, set the following variable in my.cnf file. lower_case_table_names=1
Oracle uses row level mapping by default	Mysql uses row-gap locking. Gap locking will lock the index gaps between the locked rows, thus preventing other transaction from updating those index gaps. Based on your application requirement, enable/disable the gap locking by using the following Mysql server variable in my.cnf innodb_locks_unsafe_for_binlog = true //disable innodb_locks_unsafe_for_binlog = false // enable
Autocommit is always false by default	Autocommit is true by default in Mysql