mysql关键字踩坑

mysql关键字踩坑

场景再现

本项目是springboot+mybatis-plus的项目

正是因为使用MP导致我没发现是sql的问题

提取重要信息

实体类

Permission.java

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("permission")
@ApiModel(value="Permission对象", description="")
public class Permission extends Model<Permission> {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "权限名称")
    private String permission;

    @ApiModelProperty(value = "权限描述")
    private String desc;

}

测试mapper

@Test
    public void testFindByids(){
        //    List<String> findByRoleId(@Param("roleIds") List<Integer> roleIds);
        List<Integer> idList  = new ArrayList<>();
        idList.add(1);
        idList.add(2);
        System.out.println(permissionMapper.selectBatchIds(idList));

    }

报错信息

The error may exist in com/example/demo/mapper/PermissionMapper.java (best guess)

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: SELECT id,permission,desc FROM permission WHERE id IN ( ? , ? )

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'desc FROM permission WHERE id IN (

最后提取sql到navcati执行

报错信息

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘desc FROM permission’ at line 1

改造sql

SELECT id,permission,  `desc`  FROM permission

报错原因

因为我们使用了mysql关键字

总结

1.我们定义数据库字段禁止使用mysql关键字,不是尽量,而是直接禁止使用mysql关键字

2.万一使用mysql关键字,我们可以在关键字上使用``符号

结

1.我们定义数据库字段禁止使用mysql关键字,不是尽量,而是直接禁止使用mysql关键字

2.万一使用mysql关键字,我们可以在关键字上使用``符号

3.一般check the manual that corresponds to your MariaDB server version for the right syntax就是sql关键字错误