data-interpretation?

mysql56> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql56> SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

mysql56> \! cat /tmp/test
1       one     hoge

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql56> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql56> truncate t1;
Query OK, 0 rows affected (0.03 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1;
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

gistfile1.txt

mysql56> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(11) DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql56> \! cat /tmp/test
1       hoge

mysql56> SET sql_mode= '';
Query OK, 0 rows affected (0.00 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' IGNORE INTO TABLE t1;
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 2

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1;
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 2

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1;
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 2

mysql56> SET sql_mode= STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql56> truncate t1;
Query OK, 0 rows affected (0.03 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' IGNORE INTO TABLE t1;
Query OK, 1 row affected, 2 warnings (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 2

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1;
Query OK, 1 row affected, 2 warnings (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 2

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1;
ERROR 1265 (01000): Data truncated for column 'num' at row 1

gistfile2.md

• LOAD DATA LOCAL INFILE .. INTO .. の場合、handle_duplicatesはDUP_ERRORなので、sql/sql_load.ccのここでignoreが設定される。

 318   /* We can't give an error in the middle when using LOCAL files */
 319   if (read_file_from_client && handle_duplicates == DUP_ERROR)
 320     ignore= 1;

Breakpoint 1, mysql_load (thd=0x36c9430, ex=0x7f217c373170, table_list=0x7f217c373200, fields_vars=..., set_fields=...,
    set_values=..., handle_duplicates=DUP_ERROR, ignore=false, read_file_from_client=true)
    at /home/yoku0825/mysql-5.6.20/sql/sql_load.cc:190
190     {
(gdb) p thd->query_string->string->str
$3 = 0x7f217c373080 "LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1"

• LOAD DATA LOCAL INFILE .. REPLACE INTO .. の場合、handle_duplicatesがDUP_REPLACEなので、↑のifをすり抜けてignoreが0のまま。

Breakpoint 1, mysql_load (thd=0x36c9430, ex=0x7f217c373180, table_list=0x7f217c373210, fields_vars=..., set_fields=...,
    set_values=..., handle_duplicates=DUP_REPLACE, ignore=false, read_file_from_client=true)
    at /home/yoku0825/mysql-5.6.20/sql/sql_load.cc:190
190     {

(gdb) p thd->query_string->string->str
$2 = 0x7f217c373080 "LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1"