NUMBER Data Type - Oracle to MySQL Migration

In Oracle, the NUMBER is the main data type to store all numeric values including integers, fixed and floating point numbers.

In MySQL, you can use TINYINT, SMALLINT, INT and BIGINT for integers, DECIMAL or NUMERIC for fixed point numbers, FLOAT and DOUBLE for approximate floating point numbers.

Oracle:

  -- Sample table  
  CREATE TABLE specs
  (
    cnt1 NUMBER(5),           -- Integer 
    cnt2 NUMBER(15),
    cnt3 NUMBER(15, 2),     -- Fixed point number
    cnt4 NUMBER,               -- Exact floating point number
    cnt5 NUMBER(*)
  );

MySQL:

  -- Sample table  
  CREATE TABLE specs
  (
    cnt1 INT,                        -- Integer 
    cnt2 BIGINT,
    cnt3 DECIMAL(15, 2),     -- Fixed point number
    cnt4 DECIMAL(18, 6),     -- Exact floating point number (see notes below)
    cnt5 DECIMAL(18, 6)
  );

Conversion summary:

Oracle		MySQL
NUMBER(p)	8-bit integer, 1 ⇐ p < 3	SMALLINT
NUMBER(p)	16-bit integer, 3 ⇐ p < 5	SMALLINT
NUMBER(p)	32-bit integer, 5 ⇐ p < 9	INT
NUMBER(p)	64-bit integer, 9 ⇐ p < 19	BIGINT
NUMBER(p)	Large integer, 19 ⇐ p ⇐ 38	DECIMAL(p)
NUMBER(p,s)	Fixed-point number, s > 0	DECIMAL(p,s)
NUMBER, NUMBER(*)	Exact floating-point number	DECIMAL(p,s)	DOUBLE (see notes below)

The NUMBER or NUMBER(*) data type without precision and scale can store exactly any arbitrary number. At the same time the MySQL DOUBLE is an inexact (approximate) data type:

MySQL:

  -- Sum 0.1 + 0.1 + 0.1 gives 0.30000000000000004, not 0.3
  SELECT SUM(c1)
  FROM 
    (
       SELECT CAST(0.1 AS DOUBLE) c1
       UNION ALL
       SELECT CAST(0.1 AS DOUBLE) c1
       UNION ALL
       SELECT CAST(0.1 AS DOUBLE) c1
     ) t;
 
     # sum
     # ---------------------
     # 0.30000000000000004

At the same time, using MySQL DECIMAL data type:

  -- Sum is correct now
  SELECT SUM(c1)
  FROM 
    (
       SELECT CAST(0.1 AS DECIMAL(18,6)) c1
       UNION ALL
       SELECT CAST(0.1 AS DECIMAL(18,6)) c1
       UNION ALL
       SELECT CAST(0.1 AS DECIMAL(18,6)) c1
     ) t;
 
     # sum
     # -----
     # 0.3

Consider one more example when DOUBLE fails on a key constraint:

Oracle:

  -- Primary key on NUMBER column
  CREATE TABLE units 
  (
    val NUMBER NOT NULL PRIMARY KEY
  );
 
  INSERT INTO units VALUES(10000000000000000000);
  # 1 row created.
 
  INSERT INTO units VALUES(10000000000000000001);
  # 1 row created.

MySQL:

  -- Primary key on DOUBLE column
  CREATE TABLE units 
  (
    val DOUBLE NOT NULL PRIMARY KEY
  );
 
  INSERT INTO units VALUES(10000000000000000000);
  # Query OK, 1 row affected (0.02 sec)
 
  INSERT INTO units VALUES(10000000000000000001);
  # ERROR 1062 (23000): Duplicate entry '1e19' for key 'units.PRIMARY'

By default, SQLines SQL Converter converts NUMBER to DECIMAL(18,6) but if a column is the part of a primary key or unique constraint it is converted to INT by default.

You can modify the default conversion and set any other data type including DOUBLE using -ora_number and -ora_number_int configuration options.

For more information, see Oracle to MySQL Migration.