mysql典型编程技巧

Mysql

数据写入

1.1 缺少记录时插入

示例一：插入多条记录

假设有一个主键为 client_id 的 clients 表，可以使用下面的语句：

复制代码代码如下:

    INSERT INTO clients
    (client_id, client_name, client_type)
    SELECT supplier_id, supplier_name, 'advertising'
    FROM suppliers
    WHERE not exists (select * from clients
    where clients.client_id = suppliers.supplier_id);

示例二：插入单条记录

复制代码代码如下:

    INSERT INTO clients
    (client_id, client_name, client_type)
    SELECT 10345, 'IBM', 'advertising'
    FROM dual
    WHERE not exists (select * from clients
    where clients.client_id = 10345);

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值，即使这些值还不存在当前表中。

1.2 存在则更新、不存在则插入-saveOrUpdate操作

有账户表account，用户充值10元现金，当用户账户记录不存在插入，存在时递增10元

当前事务事务级别为RR情况下，死锁问题分析
同类型情景分析：select for update引发死锁分析

select ac from account where user_id = c3 for update;
if ac == null then
   insert into account (user_id, amount) values (c3, 10.0)
else
   update account set amount = 10.0 where user_id = c3
end

当记录不存在时，这是并发出现两个一样的请求：

1.2.1 存在条件能命中唯一索引

通过insert into table () ON DUPLICATE KEY UPDATE 实现
注意：存在条件必须命中唯一索引

insert into account (user_id, amount) values (c3, 10.0) 
ON DUPLICATE KEY UPDATE amount = amount + 10, edit_date = now()

mybatis配置示例，通过<selectKey>配置查询结果，可以返回目标字段更新后的值

<insert id="saveOrUpdate" parameterType="com.***.MetricAmountDO"
        useGeneratedKeys="false">
    <selectKey keyProperty='record.metricAmount' resultType='DECIMAL' order='AFTER'>
        select (select metric_amount FROM suzaku_metric_amount WHERE
        target_customer_id = #{record.targetCustomerId}
        and mission_mid = #{record.missionMid}
        and metric_type = #{record.metricType}
        and cycle_begin = #{record.cycleBegin}) metric_amount
        from DUAL
        mys
    </selectKey>
    insert into metric_amount
    <include refid="insertSelectiveValues"></include>
    ON DUPLICATE KEY UPDATE
    ---更新支持多个字段
    <trim prefix="" suffix="" suffixOverrides=",">
        <if test="record.metricAmount != null and coverAmount == true">
            metric_amount = #{record.metricAmount},
        </if>
        <if test="record.metricAmount != null and coverAmount == false">
            metric_amount = metric_amount + #{record.metricAmount},
        </if>
        <if test="record.boundaryAmount != null">
            boundary_amount = #{record.boundaryAmount},
        </if>
        <if test="record.editDate != null">
            edit_date = #{record.editDate,jdbcType=TIMESTAMP},
        </if>
    </trim>
</insert>

1.2.2 存在条件不能命中唯一索引

缺

数据查询

2.1 分组后对组内数据排序

mysql使用策略检索数据与行内运算并行；

sqlserver由于不支持使用策略检索数据与行内运算并行，采用rom_number()与over partition by语法

mysql示例：红包操作记录表，通过红包编码分组，并获得根据创建时间倒序的，组内排序编号rank

 select log.red_packet_id, log.phone, log.create_date, @rowNum:=@rowNum + 1,
 if(@redId = log.red_packet_id, @rank:=@rank + 1,@rank:=1) as rank,
 ##注意：这个查询条件非常重要，用于赋值@redId并作为以上if条件
 @redId:=log.red_packet_id
 from
 (
 select red_packet_id, phone, create_date from red_packet_log 
 order by red_packet_id, create_date desc
 ) log,
 (
select @rowNum:=0, @redId:=null, @rank:=0
 ) b
 //explain
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   PRIMARY <derived3>      system                  1   100.00  
    1   PRIMARY <derived2>      ALL                 249 100.00  
    3   DERIVED                                     No tables used
    2   DERIVED red_packet_log      ALL                 249 100.00  Using filesort

sqlserver示例：领取记录表，通过注册号分组，获得组内排序号

SELECT
  ActivityId, RegNumber,
  ROW_NUMBER() over (partition by RegNumber order by RegNumber)
FROM
    LotteryRecord
WHERE
    ActivityId = 22

函数

if()

格式：IF(Condition,A,B)
意义：当Condition为TRUE时，返回A；当Condition为FALSE时，返回B。
作用：作为条件语句使用。

应用场景：例如分组后对组内数据排序，当groupKey（正序）相等时，分组编号 + 1，否则从1重新起始。

 select log.red_packet_id, log.phone, log.create_date, @rowNum:=@rowNum + 1,
 ##当groupKey（正序）相等时，分组编号 + 1，否则从1重新起始
 if(@redId = log.red_packet_id, @rank:=@rank + 1,@rank:=1) as rank,
 @redId:=log.red_packet_id
 from
 (
 select red_packet_id, phone, create_date from red_packet_log 
 order by red_packet_id, create_date desc
 ) log,
 (
select @rowNum:=0, @redId:=null, @rank:=0
 ) b

group_concat()

adddate()

adddate(): 添加天数，示例：`UPDATE red_packet_activity SET end_date=ADDDATE(current_timestamp(),20) WHERE id='17'

ifnull()

ifnull(): 当字段为null时，替换值。注意：如果缺少对应记录时，仍然为缺少记录

例如：SELECT IFNULL(name,'lxy') FROM Customer where id = 3