Sem 3‎ > ‎DBMS (SQL) LAB‎ > ‎

Oracle v/s MySQL: Datatypes

posted Aug 7, 2011, 12:33 AM by Neil Mathew   [ updated Nov 10, 2011, 12:56 AM ]
Comparing the Datatypes of the two. (Click for Source of Info)



NUMERIC


MySQLSizeOracle

TINYINT

1 Byte

NUMBER(3,0)

SMALLINT

2 Bytes

NUMBER(5,0)

MEDIUMINT

3 Bytes

NUMBER (7,0)`

INT

4 Bytes

NUMBER (10,0)

INTEGER

4 Bytes

NUMBER (10,0)

BIGINT

8 Bytes

NUMBER (19,0)

FLOAT(X<=24)

4 Bytes

FLOAT(0)

FLOAT(25<=X <=53)

8 Bytes

FLOAT(24)

DOUBLE

8 Bytes

FLOAT(24)

DOUBLE PRECION

8 Bytes

FLOAT(24)

REAL

8 Bytes

FLOAT(24)

DECIMAL

M Bytes(D+2, if M<D)

FLOAT(24)

NUMERIC

M Bytes(D+2, if M<D)

NUMBER



Note: Noticed in oracle:

NUMBER ( 5, 2 ) is a floating type number which allows a column to have a 5digit number with two decimal places. That is, only 3 non-decimal places are allowed. eg: 300.02

I had some errors arising because I entered less digits in the decimal places or too many overall. Just a note.

STRING


MySQLSizeOracle

CHAR(m)

M Bytes, 1<=M<=255

CHAR

VARCHAR(m)

L+1 Bytes whereas L<=M and 1<=M<=255

VARCHAR2

TINYBLOB

L + 1 Bytes whereas L<2 ^8

RAW, BLOB

BLOB

L + 2 Bytes whereas L<2^16

RAW, BLOB

TEXT

L + 2 Bytes whereas L<2^16

RAW, BLOB

MEDIUMBLOB

L + 3 Bytes whereas L < 2^ 24

RAW, BLOB

MEDIUMTEXT

L + 3 Bytes whereas L < 2^ 24

RAW, BLOB

LONGBLOB

L + 4 Bytes whereas L < 2 ^ 32

RAW, BLOB

LONGTEXT

L + 4 Bytes whereas L < 2 ^ 32

RAW, BLOB



DATE & TIME:


MySQLSizeOracle

DATE

3 Bytes

DATE

DATETIME

8 Bytes

DATE

TIMESTAMP

4 Bytes

NUMBER

TIME

3 Bytes

DATE

YEAR

1 Byte

NUMBER



Comments