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:\>
Let's repeat all of this with InnoDB
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE bug_repeat
-> (
-> name varchar(10),
-> price decimal(12,6)
-> )
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.05 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.00 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: '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> 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>
What version of MySQL am I using ???
mysql> 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>
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> create database test;
Query OK, 1 row affected (0.01 sec)
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=InnoDB;
Query OK, 0 rows affected (0.09 sec)
mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)
mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
-> into table test.bug_repeat
-> fields terminated by ','
-> lines terminated by '\n';
ERROR 2 (HY000): File 'C:/MySQLDBA/bug_test.txt' not found (Errcode: 2 - No such file or directory)
mysql> show warnings\G
Empty set (0.00 sec)
mysql> select * from bug_repeat;
Empty set (0.00 sec)
mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)
mysql> load data local infile '/tmp/bug_test.txt'
-> into table test.bug_repeat
-> fields terminated by ','
-> lines terminated by '\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql> show warnings\G
Empty set (0.00 sec)
mysql> 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> 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>
Today's date ???
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2015-06-25 18:48:10 |
+---------------------+
1 row in set (0.01 sec)
mysql>
It's been a year and one week since that bug report was submitted and nothing has changed.
My answer to MySQL is inserting "" 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.