文案处理mysql

木头没有瓜

于 2022-10-11 13:02:34 发布

阅读量260

点赞数

分类专栏： MySQL 文章标签： mysql servlet java

本文链接：https://blog.csdn.net/weixin_40918145/article/details/127261427

版权

MySQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

update love_word set title = concat(title, ",", substring_index(content, CHAR(10), 1)) where locate(substring_index(content, CHAR(10), 1), title) = 0 and app_id = '1' and CHAR_LENGTH(concat(title, ",", substring_index(content, CHAR(10), 1))) < 90

UPDATE love_word set title = REPLACE(title, ",", "，")

UPDATE love_word set title = REPLACE(title, "？，", "？")
UPDATE love_word set title = REPLACE(title, "。，", "。")
UPDATE love_word set title = REPLACE(title, "！，", "！")
UPDATE love_word set title = substring(title, 2) WHERE substring(title, 1, 1) = "，"

<!-- 获取列表 -->
    <select id="query" parameterType="java.util.Map" resultType="com.hi.hailiaowenan.affair.bean.LoveWord">
    	select id, f_id as fId, f_id_name as fIdName, state, active, content, app_id as appId, is_top as isTop,
    	user_id as userId, user_name as userName, avatar, title, image,
    	download_init_count as downloadInitCount, collect_init_count as collectInitCount, download_count as downloadCount, collect_count as collectCount, report_count as reportCount,
        <if test="fvs != null and fvs.size > 0">
           <foreach collection="fvs" open="(" close=")" separator="+" item="item">
               if(LOCATE(#{item},f_id_name),5,0) + if(LOCATE(#{item},title),5,0)
           </foreach>
           as title_matching_degree,
           <foreach collection="fvs" open="(" close=")" separator="+" item="item">
               if(LOCATE(#{item},content),1,0)
           </foreach>
           as content_matching_degree,
       </if>
    	create_time as createTime, update_time as updateTime
    	from
    	<include refid="table_name"></include>
        <trim prefix="WHERE" prefixOverrides="AND | OR">
        <!-- 有图 -->        
	      <if test="image != '' and image != null">
	    		and images != ''
	    	</if>
	    	<!-- 上架状态 -->        
	      <if test="state != null and state != '' or state == 0">
	    		and state = #{state}
	    	</if>
	    	<!-- 审核状态 -->        
	      <if test="active != null and active != '' or active == 0">
	    		and active = #{active}
	    	</if>
	    	<!-- 范畴 -->        
	      <if test="fId != null and fId != ''">
	    		and f_id like CONCAT('%',#{fId},'%')
	    	</if>
	    	<!-- 用户 -->        
	        <if test="userId != null">
	    		and user_id = #{userId}
	    	</if>
        <!-- 用户所分享 -->        
	      <if test="byUser != null and byUser != ''">
	    		and user_id != ''
	    	</if>
				<!-- 举报量 -->        
				<if test="reportCount != null and reportCount != '' or reportCount == 0">
						and report_count = #{reportCount}
				</if>
	    	<!-- 应用 -->        
	      <if test="appId != null and appId != ''">
	    		and app_id = #{appId}
	    	</if>
				<!--  <if test="keywords != null and keywords != ''">
				bind写法：预防SQL注入  通过关键词搜索
						and content like CONCAT('%',#{keywords},'%') or f_id_name like CONCAT('%',#{keywords},'%') or title like CONCAT('%',#{keywords},'%')
				</if> -->
				<if test="content != null and content != ''">
					<!-- bind写法：预防SQL注入  匹配词语内容匹配 -->
					<bind name="pattern" value="'%'+content+'%'" />
					and content like #{pattern}
				</if>
				<if test="fIdName != null and fIdName != ''">
					<!-- bind写法：预防SQL注入  匹配词语标题匹配 -->
					<bind name="pattern" value="'%'+fIdName+'%'" />
					and (f_id_name like #{pattern} or title like #{pattern})
				</if>
				<if test="fvs != null and fvs.size > 0">
					and (
						<foreach collection="fvs" open="(" close=")" separator="or" item="item">
							content like CONCAT('%',#{item},'%') or f_id_name like CONCAT('%',#{item},'%') or title like CONCAT('%',#{item},'%')
						</foreach>
						)
				</if>
			</trim>
    </select>