DEFAULT ON NULL - Oracle to MySQL Migration

In Oracle, you can use the DEFAULT ON NULL clause to apply the column's default value when a NULL is explicitly inserted into the table.

MySQL does not support the ON NULL clause, so you must avoid explicitly specifying NULL to apply the column's default value when migrating Oracle tables with DEFAULT ON NULL.

Oracle:

  -- Sample table with DEFAULT
  CREATE TABLE t1 (
     c1 INT,
     c2 DATE DEFAULT SYSDATE
  ); 
 
  -- Sample table with DEFAULT ON NULL
  CREATE TABLE t2 (
     c1 INT,
     c2 DATE DEFAULT ON NULL SYSDATE
  ); 
 
  -- DEFAULT is used if you omit a column with default
  INSERT INTO t1(c1) VALUES (1);
  INSERT INTO t2(c1) VALUES (1);
 
  -- DEFAULT is also used if you specify DEFAULT keyword in INSERT
  INSERT INTO t1 VALUES (2, DEFAULT);
  INSERT INTO t2 VALUES (2, DEFAULT);
 
  -- When specifying NULL explicitly, NULL will be inserted to t1, and DEFAULT value to t2
  INSERT INTO t1 VALUES (3, NULL);
  INSERT INTO t2 VALUES (3, NULL);

Result:

MySQL:

  -- Sample table with DEFAULT
  CREATE TABLE t2 (
     c1 INT,
     c2 DATE DEFAULT (CURRENT_DATE)
  ); 
 
  -- DEFAULT is used if you omit a column with default
  INSERT INTO t2(c1) VALUES (1);
 
  -- DEFAULT is also used if you specify DEFAULT keyword in INSERT
  INSERT INTO t2 VALUES (2, DEFAULT);
 
  -- NULL is inserted if specified explicitly, no default applied (!)
  INSERT INTO t2 VALUES (3, NULL);

Result:

In MySQL, a NULL value is inserted when explicitly specified in an INSERT statement. When migrating Oracle tables that use DEFAULT ON NULL, you should avoid explicitly inserting NULL values.

For more information, see Oracle to MySQL Migration.