不要以order为表名，表名为order,mybatis-plus的insert插入报错， Cause: java.sql.SQLSyntaxErrorException

快乐小炮灰

978人浏览 · 2022-07-24 20:22:12

快乐小炮灰 · 2022-07-24 20:22:12 发布

表结构

CREATE TABLE `order` (
  `id` bigint(20) NOT NULL COMMENT '秒杀成功生成的订单编号',
  `product_id` bigint(20) DEFAULT NULL COMMENT '产品id',
  `name` char(50) DEFAULT NULL COMMENT '秒杀产品名称',
  `content` char(255) DEFAULT NULL COMMENT '秒杀产品描述',
  `activity_id` bigint(11) DEFAULT NULL COMMENT '秒杀id',
  `account_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `status` tinyint(4) DEFAULT '-1' COMMENT '秒杀结果: -1无效  0成功(未付款)  1已付款  2已取消',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `start_time` datetime DEFAULT NULL COMMENT '起始时间',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `modiffied_time` datetime DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='秒杀成功订单表';

执行SQL

INSERT INTO order (`id`, `product_id`, `name`, `content`, `activity_id`, `account_id`, 
`status`, `end_time`, `start_time`, `create_time`, `modiffied_time`, `is_deleted`) 
VALUES ('1', '1', '1', '1', '1', '1', '0', '2022-07-24 19:41:22', '2022-07-24 19:41:25', 
'2022-07-24 19:41:28', '2022-07-24 19:41:31', '0')

报错

[SQL]INSERT INTO order (`id`, `product_id`, `name`, `content`, `activity_id`, 
`account_id`, `status`, `end_time`, `start_time`, `create_time`, `modiffied_time`, `is_deleted`) 
VALUES ('2', '1', '1', '1', '1', '1', '0', '2022-07-24 19:41:22', '2022-07-24 19:41:25', 
'2022-07-24 19:41:28', '2022-07-24 19:41:31', '0')

[Err] 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 'order (`id`, `product_id`, `name`, `content`, `activity_id`, `account_id`, `stat' at line 1

报错原因

order是MySQL中的关键字，不能直接给表名order。如果需要在表名必须为order,可以在顺序周围使用反引号。反引号允许用户将关键字视为表名或列名。

可以插入SQL（不报错）

INSERT INTO `order` (`id`, `product_id`, `name`, `content`, `activity_id`, `account_id`, `status`, `end_time`, `start_time`, `create_time`, `modiffied_time`, `is_deleted`) 
VALUES ('1', '1', '1', '1', '1', '1', '0', '2022-07-24 19:41:22', '2022-07-24 19:41:25', '2022-07-24 19:41:28', '2022-07-24 19:41:31', '0')