深夜一次数据库执行SQL思考（怎么看执行报错信息）

时间：2021-03-15 05:42:58

如下sql在执行时

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `cid` int(10) DEFAULT NULL,
  `bookType` int(1) DEFAULT NULL,
  `price` double(10, 2) DEFAULT NULL,
  `originalPrice` double(10, 2) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `press` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `version` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `degree` double(2, 1) DEFAULT NULL,
  `publishDate` varchar(7) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `description` text CHARACTER SET utf8 COLLATE utf8_general_ci,
  `date` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_book_category`(`cid`) USING BTREE,
  INDEX `fk_book_user`(`uid`) USING BTREE,
  CONSTRAINT `fk_book_category` FOREIGN KEY (`cid`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_book_user` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 90 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

　

会报出该异常：

CREATE TABLE `book`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `cid` int(10) DEFAULT NULL,
  `bookType` int(1) DEFAULT NULL,
  `price` double(10, 2) DEFAULT NULL,
  `originalPrice` double(10, 2) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `press` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `version` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `degree` double(2, 1) DEFAULT NULL,
  `publishDate` varchar(7) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `description` text CHARACTER SET utf8 COLLATE utf8_general_ci,
  `date` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_book_category`(`cid`) USING BTREE,
  INDEX `fk_book_user`(`uid`) USING BTREE,
  CONSTRAINT `fk_book_category` FOREIGN KEY (`cid`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_book_user` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 90 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic
> 1064 - 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 '(0) DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `fk_book_category`' at line 15
> 时间: 0s

　　

关键在于该句提示：

(0) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_book_category`

 

当出现报错信息时，一定要在报错提示起始位置查找，比如上面这个提示，起始就是datetime不能设置大小。提示中很明确的已经指出是在哪个位置出错的。

因此报错时，一定要检查的第一个词的位置，要么写错了，要么不合法。比如 （0） 这个就是不能加的。

标签：

相关文章