PROBLEM
You need to insert nothing ?
SOLUTION
Then, insert nothing at all ... literally !!!
One of the following will work
INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
SAMPLE DATA
use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;
SAMPLE DATA LOADED
mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE test2
-> (id int not null auto_increment primary key,
-> test int default 0);
Query OK, 0 rows affected (0.34 sec)
mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
Table: test2
Create Table: CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT * FROM test2;
Empty set (0.00 sec)
mysql>
QUERIES EXECUTED
If id and test are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2 besides id and test, name the test column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0 will become 0.
mysql> SELECT * FROM test2;
Empty set (0.00 sec)
mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
+----+------+
9 rows in set (0.00 sec)
mysql>
GIVE IT A TRY !!!
CAVEAT #1 : I did this in Windows 8.1 from the command line
mysql> select * from information_schema.global_variables
-> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+------------------------------+
| VERSION_COMMENT | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64 |
| VERSION_COMPILE_OS | Win64 |
| VERSION | 5.6.15 |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)
mysql>
CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.
Something like this, checking every value for '', and inserting NULL instead, may help. Change a,b,c,d for the actual name and number of rows on the table:
$ cat /tmp/test.csv
test1, test2, test3, test4
,,,
1,2,3,4
,,,
mysql> LOAD DATA INFILE "/tmp/test.csv" INTO TABLE test.test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@a, @b, @c, @d)
SET a = IF(@a = '', NULL, @a),
b = IF(@b = '', NULL, @b),
c = IF(@c = '', NULL, @c),
d = IF(@d = '', NULL, @d);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM test.test;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
| 1 | 2 | 3 | 4 |
| NULL | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)
Check the syntax for LOAD DATA for more details.
Best Answer
Errors occured because of unescaped backshlashes. Values preceeded with \ were inrepereted as ascii signs, but they weren't. To avoid this error backslash - \ should be escaped by additional backslash - \ so occurences of backslashes should be changed to double backslashes - \\ .