当前位置: 首页 > Mysql > 正文

mysql数据库ERROR 1064 (42000)错误

刚学习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)错误,通常是和语法错误有关,如果遇到这个错误,应该仔细检查是否是符号全角或者单词拼错等一些不容易发现语法问题。

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

该日志由 dbdream 于2016年04月05日发表在 Mysql 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: mysql数据库ERROR 1064 (42000)错误 | 信春哥,系统稳,闭眼上线不回滚!
关键字: ,

mysql数据库ERROR 1064 (42000)错误:目前有1 条留言

  1. 沙发
    shunzi:

    [给力][威武]

    2016-04-06 18:34 [回复]

发表评论

快捷键:Ctrl+Enter