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> USE test
Database changed
mysql> DROP TABLE IF EXISTS numtest;
Query OK, 0 rows affected (0.01 sec)

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

mysql> 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> SELECT * FROM numtest;
+----+------+
| id | xx   |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
+----+------+
5 rows in set (0.00 sec)

mysql>

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

You brought up a post I answered : MySQL is inserting "" 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> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> 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>

Next, let's make some data

C:\>type C:\MySQLDBA\bug_test.txt
name,
name,0
,
name,6
name,2
name,
name,0
name,0
name,
name,0

C:\>

Let's run the LOAD DATA INFILE

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> 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> 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> 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>

What's the sql_mode ?

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

mysql>

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

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

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

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

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

mysql> 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>

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

C:\>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:\>

L

StackExchange Database Administrators Q#104915, answer score: 7