MySQL loading NULLs in numeric columns MySQL 5.6.23, InnoDB

I am loading tables from character delimited text files using the LOAD DATA INFILE commnd and I would like every field with a \N, which is the NULL character in this setting, to place a NULL in the table. Some numeric types have this behavior, whereas others place a 0. I am using FIELDS TERMINATED BY and some columns do properly get NULL values, so it it not a fixed-row format issue.

These are the types I've observed while testing:


INT inserts NULLs
DECIMAL(x,0) inserts NULLs
DECIMAL(x,y) inserts 0.0s
FLOAT inserts 0s
DOUBLE(x,y) inserts 0.0s
DOUBLE inserts 0s


All of the columns in question are defined with DEFAULT NULL. I know that various functions could convert these 0s into NULLs. The question is whether there is a datatype that can handle decimal precision and will also insert NULLs on load.

Additionally, I see a whole bunch of questions relating to misunderstanding of the difference between a string, an empty string, and a null value. (example example example) This is not the issue, as the NULLs are there and are loaded properly into the same column when I redefine it as DECIMAL(x,0), then improperly when defined as DECIMAL(x,3).
Very short answer : No new datatypes have been created to accommodate you.

While we are on this subject 

Let's try plain SQL

USE test
DROP TABLE IF EXISTS numtest;
CREATE TABLE numtest
(
  id int not null auto_increment,
  xx decimal(10,3) default null,
  primary key (id)
);
INSERT INTO numtest (id) values (0),(0),(0),(0),(0);
SELECT * FROM numtest;


Does this work ???

mysql&gt; USE test
Database changed
mysql&gt; DROP TABLE IF EXISTS numtest;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; CREATE TABLE numtest
    -&gt; (
    -&gt;   id int not null auto_increment,
    -&gt;   xx decimal(10,3) default null,
    -&gt;   primary key (id)
    -&gt; );
Query OK, 0 rows affected (0.03 sec)

mysql&gt; INSERT INTO numtest (id) values (0),(0),(0),(0),(0);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM numtest;
+----+------+
| id | xx   |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
+----+------+
5 rows in set (0.00 sec)

mysql&gt;


OK, fine. It works with SQL. You are asking about LOAD DATA INFILE

You brought up a post I answered : MySQL is inserting &quot;&quot; as 0 in decimal fields. How to stop that?

Let's see if that bug was addressed since it was submitted. I will try to duplicate the code in that bug that did not work.

First let's create that table from the bug report

mysql&gt; USE test
Database changed
mysql&gt; DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql&gt; CREATE TABLE bug_repeat
    -&gt; (
    -&gt;   name varchar(10),
    -&gt;   price decimal(12,6)
    -&gt; )
    -&gt; ENGINE=MYISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SHOW CREATE TABLE bug_repeat\G
*************************** 1. row ***************************
       Table: bug_repeat
Create Table: CREATE TABLE `bug_repeat` (
  `name` varchar(10) COLLATE ascii_bin DEFAULT NULL,
  `price` decimal(12,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
1 row in set (0.00 sec)

mysql&gt;


Next, let's make some data

C:\&gt;type C:\MySQLDBA\bug_test.txt
name,
name,0
,
name,6
name,2
name,
name,0
name,0
name,
name,0

C:\&gt;


Let's run the LOAD DATA INFILE

mysql&gt; load data local infile 'C:/MySQLDBA/bug_test.txt'
    -&gt; into table test.bug_repeat
    -&gt; fields terminated by ','
    -&gt; lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4


Ouch, what happened

mysql&gt; show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: '
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: '
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: '
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: '
4 rows in set (0.00 sec)

mysql&gt; select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name | 0.000000 |
| name | 0.000000 |
|      | 0.000000 |
| name | 6.000000 |
| name | 2.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql&gt;


What's the sql_mode ?

mysql&gt; select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql&gt;


Let's blank out the sql_mode, truncate the table and reload

mysql&gt; set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql&gt; select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql&gt; truncate table bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; load data local infile 'C:/MySQLDBA/bug_test.txt'
    -&gt; into table test.bug_repeat
    -&gt; fields terminated by ','
    -&gt; lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.02 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

mysql&gt; show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: '
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: '
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: '
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: '
4 rows in set (0.00 sec)

mysql&gt;


Let doctor the input file with \N like the bug report had

C:\&gt;type C:\MySQLDBA\bug_test.txt
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

C:\&gt;


Let's repeat all of this with InnoDB

mysql&gt; USE test
Database changed
mysql&gt; DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; CREATE TABLE bug_repeat
    -&gt; (
    -&gt;   name varchar(10),
    -&gt;   price decimal(12,6)
    -&gt; )
    -&gt; ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql&gt; truncate table bug_repeat;
Query OK, 0 rows affected (0.05 sec)

mysql&gt; load data local infile 'C:/MySQLDBA/bug_test.txt'
    -&gt; into table test.bug_repeat
    -&gt; fields terminated by ','
    -&gt; lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

mysql&gt; show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: 'N
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: 'N
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: 'N
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: 'N
4 rows in set (0.00 sec)

mysql&gt; select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name | 0.000000 |
| name | 0.000000 |
| NULL | 0.000000 |
| name | 6.000000 |
| name | 2.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql&gt;


What version of MySQL am I using ???

mysql&gt; show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.22                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql&gt;


What about Linux ???

$ cat /tmp/bug_test.txt
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

$


Logging in to mysql and trying ...

mysql&gt; create database test;
Query OK, 1 row affected (0.01 sec)

mysql&gt; USE test
Database changed
mysql&gt; DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql&gt; CREATE TABLE bug_repeat
    -&gt; (
    -&gt;   name varchar(10),
    -&gt;   price decimal(12,6)
    -&gt; )
    -&gt; ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql&gt; truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)

mysql&gt; load data local infile 'C:/MySQLDBA/bug_test.txt'
    -&gt; into table test.bug_repeat
    -&gt; fields terminated by ','
    -&gt; lines terminated by '\n';
ERROR 2 (HY000): File 'C:/MySQLDBA/bug_test.txt' not found (Errcode: 2 - No such file or directory)
mysql&gt; show warnings\G
Empty set (0.00 sec)

mysql&gt; select * from bug_repeat;
Empty set (0.00 sec)

mysql&gt; truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)

mysql&gt; load data local infile '/tmp/bug_test.txt'
    -&gt; into table test.bug_repeat
    -&gt; fields terminated by ','
    -&gt; lines terminated by '\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql&gt; show warnings\G
Empty set (0.00 sec)

mysql&gt; select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name |     NULL |
| name | 0.000000 |
| NULL |     NULL |
| name | 6.000000 |
| name | 2.000000 |
| name |     NULL |
| name | 0.000000 |
| name | 0.000000 |
| name |     NULL |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql&gt; show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.21-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql&gt;


Today's date ???

mysql&gt; select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-25 18:48:10 |
+---------------------+
1 row in set (0.01 sec)

mysql&gt;


It's been a year and one week since that bug report was submitted and nothing has changed.

My answer to MySQL is inserting &quot;&quot; as 0 in decimal fields. How to stop that? still stands as of today.

You need to do this test against MySQL 5.6.23 and see if something has changed.