刚学习mysql数据库,在创建表测试加载数据的时候,就遇到了ERROR 1064 (42000)错误。

mysql>  CREATE TABLE FUL_D_STOCK_H_201512
    ->    (    STOCK_DATE DATE,
    ->         WH_NO VARCHAR(15),
    ->         PRD_ID VARCHAR(40),
    ->         UNT_PRD_ID VARCHAR(40),
    ->         STOCK_MDA_CD VARCHAR(7),
    ->         WH_DTL_TP_CD VARCHAR(7),
    ->         G_QTY INT,
    ->         B_QTY INT,
    ->         STKI_CMD_QTY INT,
    ->         STKI_CMD_APV_QTY INT,
    ->         STKI_SO_ARR_QTY INT,
    ->         SHIP_CMD_QTY INT,
    ->         CLL_CMD_QTY INT,
    ->         RTN_DLR_CMD_G_QTY INT,
    ->         RTN_DLR_CMD_B_QTY INT,
    ->         WH_MV_CMD_STKO_G_QTY INT,
    ->         WH_MV_CMD_STKO_B_QTY INT,
    ->         WH_MV_CMD_STKI_G_QTY INT,
    ->         WH_MV_CMD_STKI_B_QTY INT,
    ->         MOVING_STK_G_QTY INT,
    ->         MOVING_STK_B_QTY INT,
    ->         MDA_MV_STKO_G_QTY INT,
    ->         MDA_MV_STKO_B_QTY INT,
    ->         MDA_MV_STKI_G_QTY INT,
    ->         MDA_MV_STKI_B_QTY INT,
    ->         INST_ID VARCHAR(40),
    ->         INST_DTM TIMESTAMP，
    ->         MDF_ID VARCHAR(40),
    ->         MDF_DTM TIMESTAMP）;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP，

        MDF_ID VARCHAR(40),
        MDF_DTM TIMESTAMP）' at line 28

看提示应该是SQL语法的问题，仔细观察发现原来两个TIMESTAMP后面都出了问题，一个是逗号全角了，一个是括号全角啦，修改后问题解决。

mysql>  CREATE TABLE FUL_D_STOCK_H_201512
    ->    (    STOCK_DATE DATE,
    ->         WH_NO VARCHAR(15),
    ->         PRD_ID VARCHAR(40),
    ->         UNT_PRD_ID VARCHAR(40),
    ->         STOCK_MDA_CD VARCHAR(7),
    ->         WH_DTL_TP_CD VARCHAR(7),
    ->         G_QTY INT,
    ->         B_QTY INT,
    ->         STKI_CMD_QTY INT,
    ->         STKI_CMD_APV_QTY INT,
    ->         STKI_SO_ARR_QTY INT,
    ->         SHIP_CMD_QTY INT,
    ->         CLL_CMD_QTY INT,
    ->         RTN_DLR_CMD_G_QTY INT,
    ->         RTN_DLR_CMD_B_QTY INT,
    ->         WH_MV_CMD_STKO_G_QTY INT,
    ->         WH_MV_CMD_STKO_B_QTY INT,
    ->         WH_MV_CMD_STKI_G_QTY INT,
    ->         WH_MV_CMD_STKI_B_QTY INT,
    ->         MOVING_STK_G_QTY INT,
    ->         MOVING_STK_B_QTY INT,
    ->         MDA_MV_STKO_G_QTY INT,
    ->         MDA_MV_STKO_B_QTY INT,
    ->         MDA_MV_STKI_G_QTY INT,
    ->         MDA_MV_STKI_B_QTY INT,
    ->         INST_ID VARCHAR(40),
    ->         INST_DTM TIMESTAMP,
    ->         MDF_ID VARCHAR(40),
    ->         MDF_DTM TIMESTAMP);
Query OK, 0 rows affected (0.09 sec)

然后在使用LOAD DATA INFILE的方式直接加载txt文件的时候，又遇到了这个错误。

mysql> load data infile '/tmp/FUL_D_STOCK_H_201512.sql' into table FUL_D_STOCK_H_201512 terminated by ',';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'terminated by ','' at line 1

很明显这也是语法的错误，但是这不是全角的问题了，而是少了一个fields关键字，修改后，问题解决。

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

Mysql数据库的ERROR 1064 (42000)错误，通常是和语法错误有关，如果遇到这个错误，应该仔细检查是否是符号全角或者单词拼错等一些不容易发现语法问题。

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