MySQL 函数笔记

统计相关函数

COUNT和SUM函数使用小技巧

参考自: MySQL - Conditional COUNT with GROUP BY

在一个 SQL 中统计多个指标的个数:

SELECT COUNT(DISTINCT col1)
  , COUNT(DISTINCT CASE WHEN col2 > 10 THEN col2 ELSE 0 END) - 1
  , SUM(CASE WHEN col3 > 0 THEN 1 ELSE 0 END)
FROM tableName;

---

Date and Time Functions

DATE_ADD()/DATE_SUB()

MySQL 5.7 DATE_ADD()

DATE_ADD(dateTime, INTERVAL 100 SECOND)

---

DATE_FORMAT()

MySQL 5.7 DATE_FORMAT()

DATE_FORMAT(dateTime, "%Y-%m-%d %H:%i:%S")

---

TIMESTAMPDIFF()/TIMESTAMPADD()

TIMESTAMPDIFF(unit, expr1, expr2): expr2 - expr1 得到多少个 unit .

TIMESTAMPADD(unit,interval,datetime_expr)

---

String Functions

LENGTH() VS CHAR_LENGTH()

LENGTH(): 字符串占用存储空间的字节数;

CHAR_LENGTH(): 字符串在这种字符集下的字符个数;

在 UTF-8 编码下, 一个中文汉字一般占用三个字节:

mysql> SELECT LENGTH(_utf8mb4 '中文'), CHAR_LENGTH(_utf8mb4 '中文');
+---------------------------+--------------------------------+
| LENGTH(_utf8mb4 '中文')   | CHAR_LENGTH(_utf8mb4 '中文')   |
+---------------------------+--------------------------------+
|                         6 |                              2 |
+---------------------------+--------------------------------+
1 row in set (0.00 sec)

---

SUBSTRING()/LOCATE()

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)`

LOCATE(substr,str), LOCATE(substr,str,pos)

mysql> SELECT SUBSTRING('大部门-中部门-小部门', 
                  LOCATE('大部门-中部门', '大部门-中部门-小部门') + CHAR_LENGTH('大部门-中部门') + 1) 
              AS sub_dept_name;
+---------------+
| sub_dept_name |
+---------------+
| 小部门        |
+---------------+
1 row in set (0.01 sec)

---

TRIM()

删除字符串中的 前缀/后缀/任意位置 的 空白/特定 字符串(MySQL 5.7 TRIM):

mysql> SELECT TRIM('  bar   ');
+------------------+
| TRIM('  bar   ') |
+------------------+
| bar              |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
+------------------------------------+
| TRIM(LEADING 'x' FROM 'xxxbarxxx') |
+------------------------------------+
| barxxx                             |
+------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
+---------------------------------+
| TRIM(BOTH 'x' FROM 'xxxbarxxx') |
+---------------------------------+
| bar                             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
+-------------------------------------+
| TRIM(TRAILING 'xyz' FROM 'barxxyz') |
+-------------------------------------+
| barx                                |
+-------------------------------------+
1 row in set (0.01 sec)