mysql中的leading用法_MySQL 函数笔记

统计相关函数

COUNT和SUM函数使用小技巧

在一个 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()

DATE_ADD(dateTime, INTERVAL 100 SECOND)

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)