MySQL常用积累

1、用一个分组查询的结果集作为更新另一张表的数据

   如分组统计报名表中各个活动的报名人数，然后根据活动id以及人数去更新活动表中的报名人数字段

  

UPDATE event e
INNER JOIN (SELECT event_id,count(1) as num FROM event_sign_up GROUP BY event_id) s ON e.id=s.event_id
SET e.num_sign_up=s.num

再如查询活动名及报名人数：

SELECT `event`.`subject`,s.num
FROM `event` 
LEFT JOIN (SELECT event_id,count(1) as num FROM event_sign_up GROUP BY event_id) s ON `event`.id=s.event_id
ORDER BY s.num DESC

 

 注：

     sql中left join 、right join 、inner join之间的区别：

　　left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等（根据条件ON）的记录 ;左表有，右表没有的补空NULL。
　　right join(右联接)返回包括右表中的所有记录和左表中联结字段相等（根据条件ON）的记录;右表有，左表没哟的补空NULL。
　　inner join(等值连接) 只返回两个表中联结字段相等（根据条件ON）的行，不相等的忽略。

 2.查询时自定义排序

MySQL可以通过field()函数自定义排序

格式： 
field(value,str1,str2,str3,str4)

value与str1、str2、str3、str4比较，返回1、2、3、4，如遇到null或者不在列表中的数据则返回0.

   order by field(`status`,'S','C','R'),apply_date desc

 

应用层面的思考

1. 兼容性 
本文提到的 FIELD 函数，毕竟只是 MySQL 数据库内置提供的一种函数，除非你非常明确你的项目就是只用 MySQL 数据库，否则，你的 SQL 代码在未来迁移到其他数据库的过程中就会遇到语法兼容性问题（只是 PostgreSQL 数据库不支持 FIELD）。

2. 性能问题 
我们都知道，数据库在进行 ORDER BY 排序的时候，除非它是按照某个已经存在索引的键的值进行排序，否则数据库则需要通过计算 ORDER BY 中表达式的值并且按照查询结果建立新的临时表，这个过程会带来额外的时间开销跟内存开销，对数据库本身就是一种性能负担。这样的方式在单一数据库多个数据库客户端连接的时候，可能对数据库造成太大负担。

3. 与应用层代码的结合 
尽管使用 FIELD 函数可能带来兼容性以及性能方面的隐患，但是 FIELD 的使用并非全是有损之处。

比如在与 Ruby 的 active_record 结合时，这种通过数据库直接完成排序等 SQL 语句可以方便我们构建 ActiveRecord::Relation 对象，因为我们不再需要先将查询结果集从内存中转为数组排序，再进行二次查询，可以帮助我们减少 N+1 查询问题，后者也是常见的影响数据库服务器性能的现象之一。除此之外，这样的写法也可以有效地帮助我们简化代码，保持代码简洁。

但是在不需要对数据进行二次查询或者查询数据量太大的情况下，我反而建议可以通过 Ruby 的 Array#sort_by 方法对数据进行排序，这样的话，排序的任务就转移给了客户端代码，排序任务的压力就自然分散，减轻了服务器端的压力。

总结

1. FIELD 函数结合 ORDER BY 可以帮助我们将查询结果集按照参数列表顺序返回；
2. FIELD 函数结合 ORDER BY 的方式可以帮助我们在数据库层面完成排序，简化了业务代码逻辑；
3. FIELD 函数结合 ORDER BY 可能带来 SQL 兼容性以及性能方面的问题； 
   在确认项目数据库不大可能为 MySQL 之外的数据库的前提下，查询数据量少或者需要保持业务代码简洁的场景下，我建议可以采用 FIELD 函数排序；而在数据量庞大的情况下，或者不大可能出现大量 N+1 查询的情况下，我建议可以采用先在数据库中查询数据集（只查询 IN 条件，不排序）再到内存中通过业务代码排序(比如 Ruby 的 Array#sort_by)的方式。

 3.根据逗号分割的字符串去关联查询另一个表

如： 

方法一：正则方法不够严格，如：是17，那么117也会被找出来

先把字符串替换成正则需要的样式，把‘210,205,208’转成210|205|208，再用正则匹配 

SELECT
    GROUP_CONCAT(company.name)
FROM
    company
WHERE
company.id REGEXP (
SELECT
   REPLACE (
  (
   SELECT
       company_id
FROM
    user
   WHERE
       user.id = 583
   ),
     ',',
   '|'
)
)

方法二： 用FIND_IN_SET

 

语法：FIND_IN_SET(str,strlist)

定义：

1. 假如字符串str在由N子链组成的字符串列表strlist中，则返回值的范围在1到N之间。

2. 一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。

3. 如果第一个参数是一个常数字符串，而第二个是typeSET列，则FIND_IN_SET()函数被优化，使用比特计算。

4. 如果str不在strlist或strlist为空字符串，则返回值为0。

5. 如任意一个参数为NULL，则返回值为NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

strlist：一个由英文逗号“,”链接的字符串，例如："a,b,c,d"，该字符串形式上类似于SET类型的值被逗号给链接起来。

示例：SELECT FIND_IN_SET('b','a,b,c,d'); //返回值为2，即第2个值

 

SELECT  c.*
FROM  wm_code c
left join wm_fin_prod_published p on FIND_IN_SET(c.id,p.guarantee_type)
where p.id=46

SELECT  GROUP_CONCAT(c.description)
FROM  wm_code c
left join wm_fin_prod_created p on FIND_IN_SET(c.id,p.guarantee_type)
where p.id=26 GROUP BY p.id

 

4.根据经纬度计算直线距离

传入参数 纬度 40.0497810000 经度 116.3424590000

/*传入的参数为  纬度 纬度 经度 ASC升序由近至远 DESC 降序 由远到近 */
SELECT
    *,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            40.0497810000 * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(40.0497810000 * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            116.3424590000 * PI() / 180 - lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS juli
FROM
    customer
ORDER BY
    juli ASC

5.根据两张表的查询结果，一张多条、一张一条，批量插入到数据库中

INSERT INTO role_func(func_id,role_id)
SELECT func.func_id,(SELECT id FROM role WHERE code='admin' and domain_id='aaa') as role_id
FROM (SELECT id FROM ...) as func