MySQL的一些注意问题

/ 数据库 / 没有评论 / 648浏览

1、当mysql中字段的内容中含有转义字符时,如:家轿「老字号」出奇招\\b!福美来F5要给你点颜色看看?,若要以此字段的内容作为查询条件去查询数据,转义字符需要转义,这里的每个\前面都要添加上一个\,也就是说原来的\\b要变成\\\\b,即执行以下命令:

select * from table_name where title = '家轿「老字号」出奇招\\\\b!福美来F5要给你点颜色看看?';

结果如下: mysql字段内容含转义字符的正确查询 若没有在每个\前面添加上一个\,是查询不到这条结果的: mysql字段内容含转义字符的错误查询

2、若mysql在执行group by语句时出现以下错误: mysql不开启分组查询 ERROR(1055):42000, which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, 这是因为mysql没有开启group by查询,需要修改配置开启。 先执行以下sql语句,

select @@sql_mode;

会返回以下内容:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

查询sql_mode返回结果 把这一串字符最前面的ONLY_FULL_GROUP_BY,去掉,后面的复制粘贴写到/etc/mysql/my.cnf文件里,如下:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

my.cnf配置开启分组查询 然后重启mysql,执行,

service mysql restart

看看重启后的mysql: mysql开启分组查询

3、mysql的表名和字段名不要用关键字命名,例如:你经常会碰到处理订单这样的需求,这时或许你会一股脑子地就建一个order表,因为order是订单的意思嘛,但是之后你可能会发现莫名其妙地出错,查询order表总是抛出错误。这是因为order是mysql的关键字,orm框架没有生成正确的sql语句。我们可以做个实验来验证orm框架有没有生成正确的sql语句,先导入以下的sql语句,

drop table if exists `order`;
create table `order` (
  `id` int(10) not null auto_increment comment '订单id',
  `goods` varchar(255) not null comment '商品',
   primary key (`id`)
) engine=InnoDB auto_increment=1 default charset=utf8 comment='订单表';
insert into `order`(id, goods) values(null,'牙膏'),(null,'沐浴露'),(null,'益达'),(null,'枕头');

建立order表,再插入一些测试数据,这里用springboot的jpa来测试查询order表,项目结构如下, springboot项目结构 dao类如下:

@Transactional
public interface OrderDao extends JpaRepository<OrderEntity, Integer>, JpaSpecificationExecutor<OrderEntity> {
}

entity类如下:

@Entity
@Table(name = "order")
@SuppressWarnings("unused")
public class OrderEntity {

    /**
     * 订单id
     */
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    /**
     * 商品
     */
    private String goods;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getGoods() {
        return goods;
    }

    public void setGoods(String goods) {
        this.goods = goods;
    }

}

dto类如下:

@SuppressWarnings("unused")
public class OrderDto {

    /**
     * 订单id
     */
    private Integer id;
    /**
     * 商品
     */
    private String goods;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getGoods() {
        return goods;
    }

    public void setGoods(String goods) {
        this.goods = goods;
    }

}

controller类如下:

@RestController
@RequestMapping(value = "/test/order")
public class OrderController {

    @Autowired
    private OrderService orderService;

    /**
     * 获取订单列表
     * @return 订单列表
     */
    @RequestMapping(value = "/get_order_list", method = {RequestMethod.POST})
    @ResponseBody
    public Response<List<OrderDto>> getOrderList() {
        List<OrderDto> orderDtoList = orderService.getOrderList();
        return new Response<>(orderDtoList);
    }

}

service接口如下:

public interface OrderService {

    List<OrderDto> getOrderList();

}

service实现类如下:

@Service
public class OrderServiceImp implements OrderService {

    @Autowired
    private OrderDao orderDao;

    @Override
    public List<OrderDto> getOrderList() {
        List<OrderEntity> orderEntityList = orderDao.findAll();
        if (orderEntityList != null && orderEntityList.size() > 0) {
            List<OrderDto> orderDtoList = new ArrayList<>();
            for (OrderEntity orderEntity : orderEntityList) {
                OrderDto orderDto = new OrderDto();
                BeanUtils.copyProperties(orderEntity, orderDto);
                orderDtoList.add(orderDto);
            }
            return orderDtoList;
        }
        return null;
    }
}

启动springboot项目,测试访问获取订单列表的接口, postman测试获取订单列表接口 再来看看springboot的输出, 访问获取订单列表接口的输出 出错是因为执行了这句sql语句,

select orderentit0_.id as id1_0_, orderentit0_.goods as goods2_0_ from order orderentit0_

这句sql是service实现类OrderServiceImp中的orderDao.findAll()执行的,为什么出错呢?因为from后面是order,mysql识别成了关键字order,其实如果把order改为用``包起来是可以的,但是orm框架在生成sql语句时没有这样生成,所以抛出了错误,可以这样修改:先在dao类OrderDao中添加方法自己书写查询order表的sql语句,

@Transactional
public interface OrderDao extends JpaRepository<OrderEntity, Integer>, JpaSpecificationExecutor<OrderEntity> {

    /**
     * 查询订单列表
     */
    @Query(value = "SELECT * FROM `order`", nativeQuery = true)
    List<OrderEntity> getOrderList();

}

再修改service实现类OrderServiceImp,把orderDao.findAll()改为orderDao.getOrderList(),

@Service
public class OrderServiceImp implements OrderService {

    @Autowired
    private OrderDao orderDao;

    @Override
    public List<OrderDto> getOrderList() {
        List<OrderEntity> orderEntityList = orderDao.getOrderList();
        if (orderEntityList != null && orderEntityList.size() > 0) {
            List<OrderDto> orderDtoList = new ArrayList<>();
            for (OrderEntity orderEntity : orderEntityList) {
                OrderDto orderDto = new OrderDto();
                BeanUtils.copyProperties(orderEntity, orderDto);
                orderDtoList.add(orderDto);
            }
            return orderDtoList;
        }
        return null;
    }
}

重启springboot项目,再测试访问获取订单列表的接口, postman测试获取订单列表接口2 再来看看springboot的输出, 访问获取订单列表接口的输出2

这次没有抛出错误了,而且获取到了订单列表,所以验证了是orm自带的查询方法没有正确地生成sql语句造成的,但是不建议这样做,因为你也不知道还有没有可能会出现其它的错误,建议还是改表名,不与mysql的关键字相同。

4、在mysql中不能通过嵌套子查询来直接删除或者修改记录,就是说delete或update语句中的where后面不能跟子查询。解决方法是:先给嵌套子查询的结果取一个别名来建立一个临时表,然后从这个临时表中再次查询出结果,最后再对从这个临时表中查询出的结果做删除或修改操作。例如: 嵌套子查询删除 嵌套子查询删除记录,会抛出错误ERROR 1093 (HY000): You can't specify target table 'emp' for update in FROM clause,正确的删除方法是, 建临时表删除子查询结果

5、在mysql中,where子句筛选的是全部的结果,having子句筛选的是每个分组的结果,而且where和having同时出现的时候,where的优先级比having的优先级高。下面是where子句的应用: where子句的应用 这里先执行where子句对全部的结果进行筛选,再进行group by的分组。下面是having子句的应用: having子句的应用

having子句筛选的是每个分组的结果,having score > 79指的是这个组的第一条记录的score要大于79,而having min(score) > 79指的是这个组的score的最小值要大于79。下面是where和having同时出现: where和having同时出现 where和having同时出现,先执行where子句对全部结果进行筛选,再执行having对每个分组进行筛选。

个人微信公众号

微信公众号:纯洁的技术分享(chunjie_tech)

纯洁的技术分享