MySql利用Trigger实现SQL优化

问题

向数据库中插入多个标签数据,每个标签的schema如下:

tag{  
    title: String;
    slug: String;
    topics_count: Integer;
}

其中,titleslug字段都是唯一性索引。

  • 首先的一个问题是当title不重复而slug重复时,这在多音词使用拼音做slug时常会出现,我们希望自动给slug加上后缀使之不重复。
  • 第二个问题是当标签title重复时,变更insert操作为update并最终返回操作的记录的自增主键id数组。

使用MySql Trigger功能实现自动替换slug

MySql的Trigger是一个可在insert, update, 和delete操作之前或之后执行的hook。基于问题1的需求,需要添加一个insert之前执行的hook。

DROP TRIGGER IF EXISTS `changeSlug`;  
delimiter ;;  
CREATE TRIGGER `changeSlug` BEFORE INSERT ON `t_tag` FOR EACH ROW BEGIN  
    declare original_slug varchar(30);
    declare slug_counter int;
    set original_slug = new.slug;
    set slug_counter = 1;
    while exists (select true from t_tag where slug = new.slug) do
        set new.slug = concat(original_slug, '-', slug_counter);
        set slug_counter = slug_counter + 1;
    end while;
END  
;;
delimiter ;  

多次插入重复的slug记录,结果如下: insert tags

MySql插入记录重复时变更为更新操作

发布一篇话题时,要为此话题新增一些标签记录。如果部分标签已经存在,直接插入会报重复唯一键错误,这时候就需要利用条件插入更新。

基本语法:

insert into table (field1, field2, ...) values (value1, value2, ...) on duplicate key update field1 = value  

在mybatis里面,批量插入一般的mapper语法如下:

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">  
    insert into t_tag
    <trim prefix="(" suffix=")" suffixOverrides=",">
        title,
        slug,
        topics_count,
        create_time
    </trim>
    values
    <foreach collection="list" item="item" index="index" separator=",">
      (
        #{item.title,jdbcType=VARCHAR},
        #{item.slug,jdbcType=VARCHAR},
        #{item.topicsCount,jdbcType=INTEGER},
        #{item.createTime,jdbcType=INTEGER}
    )
  </foreach>
</insert>  

这一段代码对应的dao操作一旦commit,是能够获取到插入记录的自增主键id的列表的。但是如果加上on duplicate key ...语句,则只能返回第一条记录的id。

这时候变通的做法就是利用mybatis进行MySql事务操作,逐条插入,一次提交。

<insert id="insertOrUpdateSelective" parameterType="com.elune.entity.TagEntity" useGeneratedKeys="true" keyProperty="id">  
    insert into t_tag
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="title != null">
        title,
      </if>
      <if test="slug != null">
        slug,
      </if>
      <if test="topicsCount != null">
        topics_count,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="title != null">
        #{title,jdbcType=VARCHAR},
      </if>
      <if test="slug != null">
        #{slug,jdbcType=VARCHAR},
      </if>
      <if test="topicsCount != null">
        #{topicsCount,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=INTEGER},
      </if>
    </trim>
    on duplicate key update id = LAST_INSERT_ID(id), topics_count = topics_count + 1
  </insert>

需要注意的是id = LAST_INSERT_ID(id)确保更新操作返回更新的记录id。