MySQL心得（一）

最新推荐文章于 2024-07-12 17:43:50 发布

北瑟

最新推荐文章于 2024-07-12 17:43:50 发布

阅读量353

点赞数

文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/weixin_46461502/article/details/130760844

版权

如果确定值是英文逗号拼接则可以直接使用

find_in_set 函数
WHERE FIND_IN_SET(authorId, #{authorIds})

弊端：
find_in_set会让索引失效（因为大部分情况用的主键或int类型查询，而这个函数只会以str查询所以出现了隐式类型转换）

应用场景

组合字段查询如，字段是部门存储了父、子级等关系的时候使用

select id,channel from sel_topic_info where FIND_IN_SET('3',channel)

union 连表的正巧使用方式

核心思想:

需要相同字段，需要不同类型区分时使用，或者需要根据类型分页查询等操作（如果需要分页则可以使用( sql limit 10)）如果业务需要可以采用from 子查询结果

(SELECT
IFNULL(p.`name`, '兴宾区')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) ) limit 10)
AND p.NAME IN ( '兴宾区' )
<if test="dateType != 0">
    AND UNIX_TIMESTAMP(c.create_date) &gt;= UNIX_TIMESTAMP(#{beforeDate})
    AND UNIX_TIMESTAMP(c.create_date) &lt;= UNIX_TIMESTAMP(#{nowDate})
</if>


UNION


SELECT
IFNULL(p.`name`, '武宣县')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '武宣县' )
<if test="dateType != 0">
    AND UNIX_TIMESTAMP(c.create_date) &gt;= UNIX_TIMESTAMP(#{beforeDate})
    AND UNIX_TIMESTAMP(c.create_date) &lt;= UNIX_TIMESTAMP(#{nowDate})
</if>


UNION


SELECT
IFNULL(p.`name`, '象州县')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '象州县' )
<if test="dateType != 0">
    AND UNIX_TIMESTAMP(c.create_date) &gt;= UNIX_TIMESTAMP(#{beforeDate})
    AND UNIX_TIMESTAMP(c.create_date) &lt;= UNIX_TIMESTAMP(#{nowDate})
</if>


UNION


SELECT
IFNULL(p.`name`, '金秀县')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '金秀县' )
<if test="dateType != 0">
    AND UNIX_TIMESTAMP(c.create_date) &gt;= UNIX_TIMESTAMP(#{beforeDate})
    AND UNIX_TIMESTAMP(c.create_date) &lt;= UNIX_TIMESTAMP(#{nowDate})
</if>


UNION


SELECT
IFNULL(p.`name`, '忻城县')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '忻城县' )
<if test="dateType != 0">
    AND UNIX_TIMESTAMP(c.create_date) &gt;= UNIX_TIMESTAMP(#{beforeDate})
    AND UNIX_TIMESTAMP(c.create_date) &lt;= UNIX_TIMESTAMP(#{nowDate})
</if>

UNION

SELECT
IFNULL(p.`name`, '合山市')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '合山市' )
<if test="dateType != 0">
    AND UNIX_TIMESTAMP(c.create_date) &gt;= UNIX_TIMESTAMP(#{beforeDate})
    AND UNIX_TIMESTAMP(c.create_date) &lt;= UNIX_TIMESTAMP(#{nowDate})
</if>
order by `value` desc