mysql 数据表修改类型过慢 mysql修改字段类型很慢

admin2024-06-01  6

说来,mysql数据库是我们项目中用的比较多的库,ORM工具喜欢采用细粒度的MyBatis。这里面就这么引出了两者之间的故事!

 

首先,说改字段吧,将一个表中的varchar字段改为enum字段。如下:

mysql> desc ucc_purchase_status;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| status_type | varchar(64) | NO   |     | NULL    |                |
| timestamp   | datetime    | NO   |     | NULL    |                |
| purchase_id | int(11)     | YES  | MUL | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

这个表,主要是用来记录订单的状态。因为业务场景,这个订单的状态是有限的,目前只有"下单","付款","发货","收货","撤单","退货"。于是想将其修改成enum类型。

 

如下操作,得到错误了!!!

mysql> alter table ucc_purchase_status modify status_type enum("xd","fk","fh","sh","cd","th") not null default "xd";
ERROR 1265 (01000): Data truncated for column 'status_type' at row 1

这个错误,是什么意思呢?看到data truncated,应该想到什么呢? 通常和数据记录的内容有关系! 是不是因为我改类型后,默认值与表中当前的值有冲突呢?

 

带着这个疑问,看了下这个表中的内容:

mysql> select * from ucc_purchase_status;   
+----+-------------+---------------------+-------------+
| id | status_type | timestamp           | purchase_id |
+----+-------------+---------------------+-------------+
|  1 | 下单        | 2017-05-22 14:32:04 |           1 |
|  2 | 发货        | 2017-05-23 17:32:44 |           2 |
|  3 | 发货        | 2017-05-24 11:01:19 |           2 |
|  4 | 收货        | 2017-05-22 14:18:23 |           3 |
+----+-------------+---------------------+-------------+
4 rows in set (0.00 sec)

的确,我这个表里面的status_type的值,的确和枚举的值,是不同的,真是这个原因造成的么?试试!

mysql> alter table ucc_purchase_status modify status_type enum("下单","付款","发货","收货","撤单","退货") not null default "下单";
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

呵呵,看来,这个是真的,这个分析是成立的! 改后的表结构:

mysql> desc ucc_purchase_status;
+-------------+-------------------------------------------------------------+------+-----+---------+----------------+
| Field       | Type                                                        | Null | Key | Default | Extra          |
+-------------+-------------------------------------------------------------+------+-----+---------+----------------+
| id          | int(11)                                                     | NO   | PRI | NULL    | auto_increment |
| status_type | enum('下单','付款','发货','收货','撤单','退货')                 | NO   |     | 下单    |                |
| timestamp   | datetime                                                    | NO   |     | NULL    |                |
| purchase_id | int(11)                                                     | YES  | MUL | NULL    |                |
+-------------+-------------------------------------------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

 

这个数据类型的变化,对应的mapper文件也要修改!用mybatisGenerator工具生成mapper数据!

这个是字段改成enum之前的mapper文件部分:

<resultMap id="BaseResultMap" type="com.tg.ecs.ucc.model.UccPurchaseStatus" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="status_type" property="statusType" jdbcType="VARCHAR" />
    <result column="timestamp" property="timestamp" jdbcType="TIMESTAMP" />
    <result column="purchase_id" property="purchaseId" jdbcType="INTEGER" />
</resultMap>

下面这个,是status_type改成enum后的mapper文件部分:

<resultMap id="BaseResultMap" type="com.tg.ecs.ucc.model.UccPurchaseStatus">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="status_type" jdbcType="CHAR" property="statusType" />
    <result column="timestamp" jdbcType="TIMESTAMP" property="timestamp" />
    <result column="purchase_id" jdbcType="INTEGER" property="purchaseId" />
</resultMap>

 

好,到此,数据都改的差不多了,当然,dao里面的相关数据也都已经修正了。最后,就是mapper里面的sql查询,做相应的修改。之前对status_type的传值,都是hard code写死的,但是呢,这个业务场景,我希望查询数据,是基于状态的,比如付款的,未付款的,等等,逻辑都一样,就是状态传入的值不一样。于是,写一个通用的sql,通过参数进行过滤,是非常容易想到的方案!

 

这里,就涉及到mybatis增删改查中dao接口函数中传参数的问题了。

根据mybatis的官方文档,支持三种类型的传参模式:

1. 通过位置序号进行参数映射(序号从0开始,从函数参数列表中,自左向右依次递增,0,1,2,3)
2. 通过注解@Param("xxx")来进行参数名映射,其中的xxx字符串代表mapper中sql里面的传入参数。
3. 通过map对参数进行装载,通过KV的方式,K就是map的key,对应mapper文件里的sql中的传入参数的变量名。

对这三个方式,做一个简单的概括,也是mybatis的官方说法:任何参数传递,最终都将转化为map的形式,传入到mybatis的解析系统。默认是用key作为sql中的参数名,除非指定@Param进行重命名!

 

第一种模式,通过位置序号,比较不建议,因为在mapper的sql中不能顾名思义。

从这三个模式,你是不是觉得比较简单?mybatis里面也只是这么简单的描述!但是没有说这三个情况的组合情况!你有没有想过呢?至少我在我的应用场景,就上面的订单问题上,我有这个考虑!

 

下面请看我用的第2种模式:

dao的interface函数:
List<PurchaseElement> findPElementByCustomerInfoAndStatus(@Param("uid") Integer id, @Param("start") Integer start, 
            @Param("limit") Integer limit, @Param("list") List<String>stats);

这个其实比较简单,对应的mapper的sql如下(注意,红色部分名称的对应关系):

<!-- 根据参数用户ID,订单状态两个参数,进行过滤 -->
  <select id="findPElementByCustomerInfoAndStatus" resultMap="purchaseElementResultMap">
      select
          up.id as up_id,
          ups.status_type as ups_status_type,
          uua.id as uua_id,
          uua.username as uua_username,
          uua.address as uua_address,
          uua.mobile as uua_mobile,
          uua.zipcode as uua_zipcode,
          mp.name as mp_name,
          mp.price as mp_price,
          (select count(tupp.product_id) from ucc_purchase_product tupp  
           where tupp.purchase_id = up.id and tupp.product_id = upp.product_id) as upp_quantity
      from 
          ucc_purchase as up
        left join ucc_purchase_product as upp on upp.purchase_id = up.id
        left join mcc_product as mp on mp.id = upp.product_id
        left join ucc_purchase_status as ups on ups.purchase_id = upp.purchase_id
        left join ucc_user_address as uua on uua.id = up.address_id
    where 
        ups.status_type is not null and  ups.status_type != ''
        and (uua.id is not null and uua.id != '')
        and (uua.username is not null and uua.username != '')
        and (uua.address is not null and uua.address != '')
        and (uua.mobile is not null and uua.mobile != '')
        and (mp.name is not null and mp.name != '')
        and (mp.price is not null and mp.price !='')
        and up.customer_id = #{uid, jdbcType=INTEGER}
        and (ups.status_type in 
              <foreach item="st" collection="list" index="idx" open="(" separator="," close=")">
                #{st, jdbcType=CHAR}
              </foreach>
            ) 
    order by up.timestamp desc 
    <if test="start != null and limit!=null">
        limit #{start}, #{limit}
    </if>    
  </select>

对应的controller里面的业务逻辑代码如下:

@GET
    @Path("/load/paid")    
    public String loadPaid(@Context HttpServletRequest req){
       
        SysUser su = infos.getCurrentUser();
        
        DataTablePager.generatePager(req, su);        

        Integer id = su.getId().intValue();
        Integer start = su.getStart();
        Integer limit = su.getLimit();
        List<PurchaseElement> list = pes.findPElementByCustomerInfoAndStatus(id, start, limit, pes.paidStatus());        
int count = pes.findAllPElementCountByStatus(id, pes.paidStatus());
        
        String sEcho = req.getParameter("sEcho");
        return DataTablePager.getPageJson(list, count, sEcho);
    }

 

至于第三种,构建map这个就不用说了,将要传递的参数都写入一个map里面,用的时候通过map里面的key的名称取变量就可以了。

这里,我要说的是复杂类型,传递的参数,含有javaBean以及集合类型两个参数。当然,参数只有集合或者只有javaBean都比较简单,只有一个javaBean参数,mybatis会转化为map形式,但是对于既有javaBean,又有其他参数,例如我这里的List类型,会如何呢?

 

下面的经历,可以看出mybatis的强大!先看dao的接口:

List<PurchaseElement> findPElementByCustomerBeanAndStatus(@Param("su") SysUser su, @Param("list") List<String>stats);

再看看mapper文件:

<!-- 根据参数用户ID,订单状态两个参数,进行过滤 -->
  <select id="findPElementByCustomerBeanAndStatus" resultMap="purchaseElementResultMap">
      select
          up.id as up_id,
          ups.status_type as ups_status_type,
          uua.id as uua_id,
          uua.username as uua_username,
          uua.address as uua_address,
          uua.mobile as uua_mobile,
          uua.zipcode as uua_zipcode,
          mp.name as mp_name,
          mp.price as mp_price,
          (select count(tupp.product_id) from ucc_purchase_product tupp  
           where tupp.purchase_id = up.id and tupp.product_id = upp.product_id) as upp_quantity
      from 
          ucc_purchase as up
        left join ucc_purchase_product as upp on upp.purchase_id = up.id
        left join mcc_product as mp on mp.id = upp.product_id
        left join ucc_purchase_status as ups on ups.purchase_id = upp.purchase_id
        left join ucc_user_address as uua on uua.id = up.address_id
    where 
        ups.status_type is not null and  ups.status_type != ''
        and (uua.id is not null and uua.id != '')
        and (uua.username is not null and uua.username != '')
        and (uua.address is not null and uua.address != '')
        and (uua.mobile is not null and uua.mobile != '')
        and (mp.name is not null and mp.name != '')
        and (mp.price is not null and mp.price !='')
        and up.customer_id = #{su.id, jdbcType=INTEGER}
        and (ups.status_type in 
              <foreach item="st" collection="list" index="idx" open="(" separator="," close=")">
                #{st, jdbcType=CHAR}
              </foreach>
            ) 
    order by up.timestamp desc 
    <if test="start != null and limit!=null">
        limit #{su.start}, #{su.limit}
    </if>    
  </select>

 

controller里面的代码如下:

@GET
    @Path("/load/paid")    
    public String loadPaid(@Context HttpServletRequest req){
        
        SysUser su = infos.getCurrentUser();
        
        DataTablePager.generatePager(req, su);        

        Integer id = su.getId().intValue();
    
        List<PurchaseElement> list = pes.findPElementByCustomerBeanAndStatus(su, pes.paidStatus());
        int count = pes.findAllPElementCountByStatus(id, pes.paidStatus());
        
        String sEcho = req.getParameter("sEcho");
        return DataTablePager.getPageJson(list, count, sEcho);
    }

可以看到,其中SysUser是一个用户数据的定义,以及后端分页信息的继承。

 

运行后,居然报错!!!!

五月 26, 2017 4:33:04 下午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [default] in context with path [/ecs] threw exception [org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'start' not found. Available parameters are [param1, param2, su, list]] with root cause
org.apache.ibatis.binding.BindingException: Parameter 'start' not found. Available parameters are [param1, param2, su, list]
    at org.apache.ibatis.binding.MapperMethod$ParamMap.get(MapperMethod.java:165)
    at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:123)
    at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:1657)
    at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:92)

仔细查询,涉及findPElementByCustomerBeanAndStatus函数调用逻辑的所有函数,以及mapper文件,最后,发现mapper文件中,的确有个start是独立的。就是那个动态sql if中判断分页数据的地方有问题。应该加上命名空间。

 

对比上面运行出错的mapper的内容,正确的应该是如下这个样子的:

<!-- 根据参数用户ID,订单状态两个参数,进行过滤 -->
  <select id="findPElementByCustomerBeanAndStatus" resultMap="purchaseElementResultMap">
      select
          up.id as up_id,
          ups.status_type as ups_status_type,
          uua.id as uua_id,
          uua.username as uua_username,
          uua.address as uua_address,
          uua.mobile as uua_mobile,
          uua.zipcode as uua_zipcode,
          mp.name as mp_name,
          mp.price as mp_price,
          (select count(tupp.product_id) from ucc_purchase_product tupp  
           where tupp.purchase_id = up.id and tupp.product_id = upp.product_id) as upp_quantity
      from 
          ucc_purchase as up
        left join ucc_purchase_product as upp on upp.purchase_id = up.id
        left join mcc_product as mp on mp.id = upp.product_id
        left join ucc_purchase_status as ups on ups.purchase_id = upp.purchase_id
        left join ucc_user_address as uua on uua.id = up.address_id
    where 
        ups.status_type is not null and  ups.status_type != ''
        and (uua.id is not null and uua.id != '')
        and (uua.username is not null and uua.username != '')
        and (uua.address is not null and uua.address != '')
        and (uua.mobile is not null and uua.mobile != '')
        and (mp.name is not null and mp.name != '')
        and (mp.price is not null and mp.price !='')
        and up.customer_id = #{su.id, jdbcType=INTEGER}
        and (ups.status_type in 
              <foreach item="st" collection="list" index="idx" open="(" separator="," close=")">
                #{st, jdbcType=CHAR}
              </foreach>
            ) 
    order by up.timestamp desc 
    <if test="su.start != null and su.limit!=null">
        limit #{su.start}, #{su.limit}
    </if>    
  </select>

 

到此,有必要补充一下,上述resultMap的内容:

<mapper namespace="com.tg.ecs.ucc.dao.PurchaseElementMapper" >
  <resultMap id="purchaseElementResultMap" type="com.tg.ecs.ucc.model.PurchaseElement" > 
      <id column="up_id" property="purchaseId" jdbcType="INTEGER"/>
    <result column="ups_status_type" property="statusType" jdbcType="VARCHAR"/>
    <!-- 特别注意:association以及collection这样子的标签,必须放在resultMap的最后面,且association在collection的前面 -->
    <association property="receivedBy" column="up_id" javaType="com.tg.ecs.ucc.model.ConsigneeElement">
         <id column="uua_id" property="aid" />    
        <result column="uua_username" property="name" />
        <result column="uua_address" property="address" />
        <result column="uua_mobile" property="mobile" />
        <result column="uua_zipcode" property="zipcode"/>        
    </association>    
    <!--一个订单号对应多个产品 -->
    <collection property="products" column="up_id" ofType="com.tg.ecs.ucc.model.PurchaseProduct">        
        <result column="mp_name" property="productName"/>
        <result column="mp_price" property="productPrice"/>
        <result column="upp_quantity" property="quantity"/>
    </collection>
  </resultMap>

注意,其中的红色部分,若不按照其规则配置,将会出现下面的错误:

The content of element type "resultMap" must match "(constructor?,id*,result*,association*,collection*,discriminator?)".

 

 

总结一下:

基于mybatis的参数传递功能,mybatis是支持任何参数的传递的,不仅是基础类型(String,Integer等)的参数传递,也支持javaBean类型的传递,同时,也支持各种类型的组合传递。只是使用的过程中,dao接口函数中参数基于@Param注解进行重命名,方便和mapper中sql语句的变量映射。最重要的一点,要注意:若参数中是javaBean的话,且有多个入口参数,就要注意变量的命名空间问题,我上面遇到的错误,找不到start变量(其实,start是SysUser里面的一个成员变量名),就是这个原因造成的!

 

另外,插曲一段:

在做订单状态传递进入sql的过程中,in 后面的foreach的使用,遇到了一点问题。故事是这样的,开始我的sql是这样的:

<foreach item="st", collection="list", index="idx", open="(", separator=",", close=")">
        #{st, jdbcType=CHAR}
    </foreach>

这个时候,我的mapper文件中,总是报错:

Element type "foreach" must be followed by either attribute specifications, ">" or "/>".

 

仔细核对mybatis的技术手册,才发现,粗心大意造成了这个错误,因为属性字段之间不应该有逗号!是用“空格”,切记!

正确的写法应该是这样的:

<foreach item="st" collection="list" index="idx" open="(" separator="," close=")">
    #{st, jdbcType=CHAR}
</foreach>

 

好了,今天,这个博文,就写到这里吧! 下一篇,将总结一下foreach中的collection以及index的使用,因为我看到好多人在纠结这两个字段的用法!

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明原文出处。如若内容造成侵权/违法违规/事实不符,请联系SD编程学习网:675289112@qq.com进行投诉反馈,一经查实,立即删除!