Skip to content

mysql

Jump to bottom
zhangjie edited this page Aug 9, 2020 · 6 revisions

MySQL常见问题积累

查询不存在的条件

  1. 例如SELECT mail_no FROM test WHERE mail_no IN ('3370247156375','1111') 找出'3370247156375','1111'中不存在的条件
    1.1 创建临时表：CREATE TEMPORARY TABLE xxx
    1.2 将'3370247156375','1111'存入临时表
    1.3 SELECT t.mail_no FROM (SELECT mail_no FROM t_oper_waybill WHERE mail_no IN ('3370247156375','1111')) t WHERE t.mail_no IN ('3370247156375','1111');
  2. 关于临时表知识点补充
    2.1 临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间

批量更新值（待更新的值不一样）

  1. 例如test表length、width、height字段的值已经置为Null,现在想还原（原本值备份在EXCEL）
    2.1 创建表（储存备份值）:test_bak
    2.2 联表更新：update test o inner join test_bak oo on o.id = oo.id set o.length=oo.length,o.width=oo.width,o.height=oo.height where o.mail_no in( select mail_no from test_bak)

Mybatis使用时问题

大于小于的转义：

&lt; <  
&gt; >

批量更新

<update id="updateBatch"  parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update xxx_table set
            result_code = #{item.resultCode},result_content = #{item.resultContent},
            last_time = #{item.lastTime},count = count+1
        where tracking_no = #{item.trackingNo}
    </foreach>
</update>
2.1 注意open、close、separator属性，其他用法：批量插入 open="(" close=")"  separator=","

mybatis批量实现有则更新，无则插入

   <insert id="batchInsert">
        INSERT INTO s_task_to_postal_statistic(push_type,`code`,wait_push_count,push_fail_count)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.pushType},#{item.code},#{item.waitPushCount},#{item.pushFailCount})
        </foreach>
        ON DUPLICATE KEY UPDATE
        wait_push_count = values(wait_push_count),
        push_fail_count = values(push_fail_count)
    </insert>
    1. 注意需要建立pushType和code的唯一索引

Clone this wiki locally