Description:
Official documents：https://dev.mysql.com/doc/refman/8.0/en/load-data.html
"Those factors combine to produce restrictive or nonrestrictive data interpretation by LOAD DATA:
1)Data interpretation is restrictive if the SQL mode is restrictive and neither the IGNORE nor the LOCAL modifier is specified. Errors terminate the load operation.
2)Data interpretation is nonrestrictive if the SQL mode is nonrestrictive or the IGNORE or LOCAL modifier is specified. (In particular, either modifier if specified overrides a restrictive SQL mode.) Errors become warnings and the load operation continues."
But "load data local infile" with "replace" can't overrides a restrictive SQL mode, the details are as follows.
How to repeat:
1）set session sql_mode = 'traditional' or set session sql_mode = default;
##########################################################################
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> CREATE TABLE t1(a INT NOT NULL, b INT, c INT);
mysql> load data local infile 'std_data/loaddata_fun01.dat' replace into table t1 columns terminated by "," (a,b,@var) set c = 'x';
mysql> load data local infile 'std_data/loaddata_fun01.dat' into table t1 columns terminated by "," (a,b,@var) set c = 'x';
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 5
mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 1 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 2 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 3 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 4 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 5 |
+---------+------+------------------------------------------------------+
5 rows in set (0.00 sec)
#########################################################################
# cat std_data/loaddata_fun01.dat
1,10,60
2,20,70
3,30,80
4,40,90
5,50,100
Suggested fix:
I suggest a fix to the official docs，for example
“In particular, either modifier if specified overrides a restrictive SQL mode.”
can be modified to
“In particular, either modifier if specified overrides a restrictive SQL mode when the replace modifier is not used.”
