-
Notifications
You must be signed in to change notification settings - Fork 0
mysql
zhangjie edited this page Aug 9, 2020
·
6 revisions
- 例如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.1 临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间
- 例如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)
大于小于的转义:
< <
> >
批量更新
<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的唯一索引