简单客服聊天数据库设计

1、主要功能包含：

    收发消息，聊天列表，未读消息，修改为已读消息，双方对话内容记录。

2、表结构：

bds_user_message（聊天消息内容表）

3、业务代码没有特殊处理，就只放几个业务sql.

1、查询聊天列表和未读消息

<!--查询消息列表和未读消息-->
    <select id="selectBdsUserMessage" parameterType="String" resultType="BdsUserMessageResult">
        select send_id as sendId,send_name as sendName,count(send_name) as count from bds_user_message
        group by send_name,send_id,receiver_id,is_read
        having  receiver_id = #{receiverId} and is_read = '0'
        order by count desc
    </select>

2、查询双方消息内容

 <!--查询双方消息内容-->
    <select id="selectBdsUserMessageContent" parameterType="net.beidousky.web.domain.request.BdsUserMessageParam" resultMap="BdsUserMessageResult">
        <include refid="selectBdsUserMessageVo"/>  where receiver_id = #{receiverId} and send_id = #{sendId}
        UNION
        <include refid="selectBdsUserMessageVo"/> where receiver_id = #{sendId} and send_id = #{receiverId}
        order by create_time desc
    </select>

3、保存消息（收发消息）
就新增sql就行了，略

4、修改消息（修改为已读消息）

<update id="updateBdsUserMessage" parameterType="net.web.domain.request.BdsUserMessageParam">
    update bds_user_message set is_read = '1' where receiver_id=#{receiverId} and send_id = #{sendId}
</update>