Mysql函数使用技巧

1、数据去重

高级且优雅的自连接

输入:
Person 表:
±—±-----------------+
| id | email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
±—±-----------------+
输出:
±—±-----------------+
| id | email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
±—±-----------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。
链接：https://leetcode.cn/problems/delete-duplicate-emails

DELETE p1 
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id

2、列转行

输入：
Products table:
±-----------±-------±-------±-------+
| product_id | store1 | store2 | store3 |
±-----------±-------±-------±-------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
±-----------±-------±-------±-------+
输出：
| ------------ | -------- |-------|
| product_id | store | price |
±-----------±-------±------+
| 0 | store1 | 95 |
| 0 | store2 | 100 |
| 0 | store3 | 105 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
±-----------±-------±------+
解释：
产品0在store1，store2,store3的价格分别为95,100,105。
产品1在store1，store3的价格分别为70,80。在store2无法买到。
链接：https://leetcode.cn/problems/rearrange-products-table

select product_id, 'store1' store, store1 price  from products where store1 is not null
union
select product_id, 'store2' store, store2 price  from products where store2 is not null
union
select product_id, 'store3' store, store3 price  from products where store3 is not null

3、行转列

输入：
Products table:
±-----------±-------±------+
| product_id | store | price |
±-----------±-------±------+
| 0 | store1 | 95 |
| 0 | store2 | 100 |
| 0 | store3 | 105 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
±-----------±-------±------+

输出：

| product_id | store1 | store2 | store3 |
±-----------±-------±-------±-------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
±-----------±-------±-------±-------+

SELECT product_id,
  SUM(IF(store = 'store1', price, NULL)) 'store1',
  SUM(IF(store = 'store2', price, NULL)) 'store2',
  SUM(IF(store = 'store3', price, NULL)) 'store3' 
FROM
  Products1 
GROUP BY product_id ;

4、对日期的一些处理（函数）

1、在一个时间上加上一个时间

例如：在‘2017-10-10’加上10天

select date_add("2017-10-10", interval 10 day);  
->2017-10-20

在‘2017-10-10 15:25:18’加上15分钟

select date_add("2017-10-10 15:25:18", interval 15 minute);
->2017-10-10 15:40:18

2、两日期之间的差值

例如：2001-01-01与2001-02-02相差多少天

select datediff('2001-01-01','2001-02-02');
->-32
#说明：这相当于前一个日期减去后一个日期，可以为负也可以为正

5、模糊查询，匹配查询

经常在各个博客上看到说模糊查询会使索引失效，类似 like ‘关键词%’ 不会失效，但是like ‘%关键词%’ 会失效。然后就有使用全文检索的说法，但是全文检索只支持myisam、并且不支持联表查询。于是对于这种需求应该怎么做才能走索引，或者说怎么做才能使查询更快？

explain SELECT * FROM user where username like '%ab%'

explain select * from user where instr(username, 'ab')

explain select * from user where locate('ab', username)

可以看到使用上面三个sql执行结果分析不同在于filtered，对于这个列的解释为越大越好。所以可以得出结论，使用函数比使用like + %的性能 要好，至于走不走索引就不得而知了