函数
1. 字符串函数
1.1 lpad
用法: LPAD(str,len,padstr)
返回字符串str，用字符串padstr左对齐填充至长度为len的字符。如果str长度超过len，则返回值将缩短为len个字符。如果 str、padstr或len为NULL，则函数返回NULL。
sql
mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
mysql> SELECT LPAD('hi',1,'??');
-> 'h'1.2 rpad
用法: RPAD(str,len,padstr)
该函数返回字符串st，并在其右侧填充字符串padstr，使其长度达到len个字符。如果str的长度大于len，则返回值将被缩短至len个字符。如果 str、padstr或len为NULL，则函数返回NULL。
sql
mysql> SELECT RPAD('hi',5,'?');
-> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
-> 'h'1.3 repeat
用法: REPEAT(str,count) 返回一个由字符串str重复count次组成的字符串。如果count小于1，则返回空字符串。如果str或count为NUL，返回NULL。
sql
mysql> SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'1.4 md5
sh
## 获取md5数据摘要
mysql> select md5('123456');
+----------------------------------+
| md5('123456') |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)1.5 upper
shell
## 转换大小写
mysql> select upper('aBc');
+--------------+
| upper('aBc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)1.6 lower
shell
+--------------+
| lower('aBc') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)1.7 concat
shell
mysql> select concat_ws('.', 'a', 'b', 'c');
+-------------------------------+
| concat_ws('.', 'a', 'b', 'c') |
+-------------------------------+
| a.b.c |
+-------------------------------+
1 row in set (0.00 sec)2. 日期函数
2.1 now(current_timestamp)
current_timestamp和now同义
sh
## 获取当前时间，可以支持毫秒
mysql> select now(), now(3);
+---------------------+-------------------------+
| now() | now(3) |
+---------------------+-------------------------+
| 2025-03-17 10:28:34 | 2025-03-17 10:28:34.703 |
+---------------------+-------------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp(), current_timestamp(3);
+---------------------+-------------------------+
| current_timestamp() | current_timestamp(3) |
+---------------------+-------------------------+
| 2025-03-17 10:29:39 | 2025-03-17 10:29:39.588 |
+---------------------+-------------------------+
1 row in set (0.00 sec)2.2 sysdate
sysdate表示sql实际开始执行时间，now表示当前时间
sh
## 延迟3秒执行
mysql> select sysdate(),sysdate(6), now(6), sleep(3), sysdate(), now();
+---------------------+----------------------------+----------------------------+
| sysdate() | sysdate(6) | now(6) |
+---------------------+----------------------------+----------------------------+
| 2025-03-17 10:32:54 | 2025-03-17 10:32:54.289806 | 2025-03-17 10:32:54.289633 |
+---------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)2.3 date_add
sh
## 日期加减
mysql> select date_add(now(), interval 1 day);
+---------------------------------+
| date_add(now(), interval 1 day) |
+---------------------------------+
| 2025-03-18 10:36:34 |
+---------------------------------+
1 row in set (0.00 sec)2.5 date_sub
sh
mysql> select date_sub(now(), interval 1 hour);
+----------------------------------+
| date_sub(now(), interval 1 hour) |
+----------------------------------+
| 2025-03-17 09:36:34 |
+----------------------------------+
1 row in set (0.00 sec)2.5 date_format
用法: DATE_FORMAT(date,format)
根据格式字符串对日期值进行格式化。如果任一参数为NULL，函数返回NULL。
sh
## 格式化日期成字符串
mysql> select date_format(now(), '%Y%m%d'), date_format(now(), '%Y%m%d %H:%i:%s');
+------------------------------+---------------------------------------+
| date_format(now(), '%Y%m%d') | date_format(now(), '%Y%m%d %H:%i:%s') |
+------------------------------+---------------------------------------+
| 20250317 | 20250317 10:38:58 |
+------------------------------+---------------------------------------+
1 row in set (0.00 sec)4. Json函数
4.1 json_extract
用法: JSON_EXTRACT(json_doc, path[, path] ...)
从json中按照路径参数匹配返回数据，如果是匹配多个值，则返回数据为数组。
sh
## 数据查询1，json_extract函数提取字段值，但是默认有双引号，可以使用json_unquote函数
mysql> select id, json_extract(data, '$.name') quote_name, json_unquote(json_extract(data, '$.name')) name from t_json_table;
+----+------------+------+
| id | quote_name | name |
+----+------------+------+
| 1 | "jack" | jack |
+----+------------+------+
1 row in set (0.00 sec)
## 数据查询2，使用字段名->方式，如果使用字段名->>则去掉引号
mysql> select id, data->"$.name", data->>"$.name" from t_json_table;
+----+----------------+-----------------+
| id | data->"$.name" | data->>"$.name" |
+----+----------------+-----------------+
| 1 | "jack" | jack |
| 2 | NULL | NULL |
+----+----------------+-----------------+
2 rows in set (0.01 sec)