Mysql数据库在使用LOAD DATA INFILE的方式加载数据的时候，遇到了ERROR 1064 (42000)错误。

mysql> load data infile '/tmp/FUL_D_STOCK_H_201512.sql' into table FUL_D_STOCK_H_201512 fields terminated by ',';
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

这个错误是因为当前的binlog是STATEMENT，STATEMENT模式不支持LOAD DATA INFILE，需要将binlog的保存方式修改成ROW模式。

mysql> show variables like 'BINLOG_FORMAT';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

查看参数文件发现binlog_format参数备注释掉了，而STATEMENT模式应该是默认的设置。

# binary logging is required for replication
log-bin=mysql-bin
expire_logs_days=3
binlog_format=ROW
binlog_cache_size = 64M

重启mysql数据库使之生效，或者在mysql命令行工具通过set命令来修改这个参数，这样可以不重启就可以生效。

[root@mysql-1 3308]# mysqladmin -uroot -p -S /mysql/data/3308/mysql.sock shutdown 
Enter password: 
160215 11:54:36 mysqld_safe mysqld from pid file /mysql/data/3308/mysql-1.pid ended
[1]+  Done                    mysqld_safe --defaults-file=/mysql/data/3308/my.cnf  (wd: ~)
(wd now: /mysql/data/3308)

[root@mysql-1 ~]# mysqld_safe --defaults-file=/mysql/data/3308/my.cnf &
[1] 8532
[root@mysql-1 ~]# 160220 09:43:57 mysqld_safe Logging to '/mysql/data/3308/mysql-1.err'.
160220 09:43:57 mysqld_safe Starting mysqld daemon with databases from /mysql/data/3308

查看设置是否生效。

mysql> show variables like 'BINLOG_FORMAT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.04 sec)

再次加载，问题解决。

mysql> load data infile '/tmp/FUL_D_STOCK_H_201512.sql' into table FUL_D_STOCK_H_201512 fields terminated by ',';
Query OK, 2000000 rows affected (35.04 sec)
Records: 2000000  Deleted: 0  Skipped: 0  Warnings: 0

本文固定链接: https://www.dbdream.com.cn/2016/04/mysql%e6%95%b0%e6%8d%ae%e5%ba%93error-1665-hy000%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！