文章/答案/技术大牛

发布

社区首页 >专栏 >【MySQL基础篇】七、内置函数

【MySQL基础篇】七、内置函数

利刃大大

发布于 2025-05-21 03:53:03

6700

代码可运行

文章被收录于专栏：csdn文章搬运csdn文章搬运

运行总次数：0

代码可运行

Ⅰ. 日期函数

上面的操作比较简单，这里就不演示了！

案例一

首先创建一张表，记录生日：

create table tmp(
    id int primary key auto_increment,
    birthday date
);

然后添加当前日期：

mysql> insert into tmp (birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)

mysql> select * from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2023-08-01 |
|  2 | 2023-08-01 |
+----+------------+
2 rows in set (0.00 sec)

案例二

我们这里创建一张用来记录留言的表：

create table msg (
    id int primary key auto_increment,
    content varchar(30) not null,
    sendtime datetime
);

然后插入数据：

mysql> insert into msg(content, sendtime) values('hello', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into msg(content, sendtime) values('bye~', current_timestamp());
Query OK, 1 row affected (0.00 sec)

mysql> select * from msg;
+----+---------+---------------------+
| id | content | sendtime            |
+----+---------+---------------------+
|  1 | hello   | 2023-08-01 15:13:04 |
|  2 | bye~    | 2023-08-01 15:13:28 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

而当我们想要查找特定时间比如五分钟内发布的留言，可以按如下操作使用 date_add() 函数或者 date_sub() 函数都行：

mysql> select content, sendtime from msg where date_add(sendtime, interval 5 minute) > now();
+---------+---------------------+
| content | sendtime            |
+---------+---------------------+
| hello   | 2023-08-01 15:13:04 |
| bye~    | 2023-08-01 15:13:28 |
+---------+---------------------+
2 rows in set (0.00 sec)

Ⅱ. 字符串函数

常见字符串函数

下面结合案例来学习这几个函数！并且下面使用的案例表是在表的增删查改那个笔记中设定的。

使用案例

1、显示对应的字符集 – `charset`

mysql> select charset('s');
+--------------+
| charset('s') |
+--------------+
| utf8         |
+--------------+
1 row in set (0.00 sec)

mysql> select charset('中国');
+-------------------+
| charset('中国')   |
+-------------------+
| utf8              |
+-------------------+
1 row in set (0.00 sec)

mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
+----------------+
14 rows in set (0.00 sec)

2、要求显示 exam_result 表中的信息，显示格式：“XXX 的语文是 XXX 分，数学 XXX 分，英语 XXX 分” – `concat`

mysql> select concat(name,'的语文是',chinese,'分，数学是',math,'分，英语是',english,'分') 分数 from exam_result;
+--------------------------------------------------------------+
| 分数                                                         |
+--------------------------------------------------------------+
| 唐三藏的语文是67分，数学是98分，英语是56分                   |
| 孙悟空的语文是87分，数学是78分，英语是77分                   |
| 猪悟能的语文是88分，数学是98分，英语是90分                   |
| 曹孟德的语文是82分，数学是84分，英语是67分                   |
| 刘玄德的语文是55分，数学是85分，英语是45分                   |
| 孙权的语文是70分，数学是73分，英语是78分                     |
| 宋公明的语文是75分，数学是65分，英语是30分                   |
+--------------------------------------------------------------+
7 rows in set (0.00 sec)

3、求学生表中学生姓名占用的字节数 – `length`

mysql> select name,length(name) from exam_result;
+-----------+--------------+
| name      | length(name) |
+-----------+--------------+
| 唐三藏    |            9 |
| 孙悟空    |            9 |
| 猪悟能    |            9 |
| 曹孟德    |            9 |
| 刘玄德    |            9 |
| 孙权      |            6 |
| 宋公明    |            9 |
+-----------+--------------+
7 rows in set (0.00 sec)

注意：length() 函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数。（这与字符集编码有关）

4、将 emp 表中所有名字中有 S 的替换成 ‘上海’ – `replace`

mysql> select ename, replace(ename, 'S', '上海') from emp;
+--------+-------------------------------+
| ename  | replace(ename, 'S', '上海')   |
+--------+-------------------------------+
| SMITH  | 上海MITH                      |
| ALLEN  | ALLEN                         |
| WARD   | WARD                          |
| JONES  | JONE上海                      |
| MARTIN | MARTIN                        |
| BLAKE  | BLAKE                         |
| CLARK  | CLARK                         |
| SCOTT  | 上海COTT                      |
| KING   | KING                          |
| TURNER | TURNER                        |
| ADAMS  | ADAM上海                      |
| JAMES  | JAME上海                      |
| FORD   | FORD                          |
| MILLER | MILLER                        |
+--------+-------------------------------+
14 rows in set (0.00 sec)

5、截取 emp 表中 ename 字段的第二个到第三个字符 – `substring`

mysql> select ename, substring(ename, 2, 2) from emp;
+--------+------------------------+
| ename  | substring(ename, 2, 2) |
+--------+------------------------+
| SMITH  | MI                     |
| ALLEN  | LL                     |
| WARD   | AR                     |
| JONES  | ON                     |
| MARTIN | AR                     |
| BLAKE  | LA                     |
| CLARK  | LA                     |
| SCOTT  | CO                     |
| KING   | IN                     |
| TURNER | UR                     |
| ADAMS  | DA                     |
| JAMES  | AM                     |
| FORD   | OR                     |
| MILLER | IL                     |
+--------+------------------------+
14 rows in set (0.00 sec)

6、以首字母小写的方式显示所有员工的姓名 – `lcase`

这里的要求是首字母小写而已，所以我们想要做一下截取和拼接，也就是配合前面的 concat() 和 substring()：

mysql> select ename, concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) from emp;
+--------+------------------------------------------------------------+
| ename  | concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) |
+--------+------------------------------------------------------------+
| SMITH  | sMITH                                                      |
| ALLEN  | aLLEN                                                      |
| WARD   | wARD                                                       |
| JONES  | jONES                                                      |
| MARTIN | mARTIN                                                     |
| BLAKE  | bLAKE                                                      |
| CLARK  | cLARK                                                      |
| SCOTT  | sCOTT                                                      |
| KING   | kING                                                       |
| TURNER | tURNER                                                     |
| ADAMS  | aDAMS                                                      |
| JAMES  | jAMES                                                      |
| FORD   | fORD                                                       |
| MILLER | mILLER                                                     |
+--------+------------------------------------------------------------+
14 rows in set (0.00 sec)

Ⅲ. 数学函数

这里只要举 conv() 和 mod() 函数的使用例子：

-- 转化进制函数
mysql> select conv(10, 10, 16);
+------------------+
| conv(10, 10, 16) |
+------------------+
| A                |
+------------------+
1 row in set (0.00 sec)

mysql> select conv(10, 10, 2);
+-----------------+
| conv(10, 10, 2) |
+-----------------+
| 1010            |
+-----------------+
1 row in set (0.00 sec)


-- 求余数函数
mysql> select mod(10, 2);
+------------+
| mod(10, 2) |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> select mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

Ⅳ. 其它常见函数

1、`user()`

用于查询当前的用户：

mysql> select user();
+--------+
| user() |
+--------+
| root@  |
+--------+
1 row in set (0.00 sec)

2、`md5(str)`

这个函数是一个常见的哈希函数，用于将输入字符串转换为 128 位的哈希值，也就是对一个字符串进行 md5 摘要，摘要后得到一个 32 位字符串，这是一种加密方式！相同的字符串，其 md5 编码是一样的！

MD5 算法是单向的，不可逆的，这意味着 无法通过哈希值还原原始字符串。在 mysql 中，MD5() 函数可以用于加密字符串，但它 不是专门用于密码加密的函数。

需要注意的是，使用 MD5() 函数加密密码并不是一种安全的做法，因为 MD5 算法已经被证明存在一些弱点，容易受到暴力破解和彩虹表攻击。为了更好地保护密码，推荐使用更安全的哈希算法，如 SHA-256，并结合使用盐值来增加密码的复杂性和安全性。

mysql> select md5('liren');
+----------------------------------+
| md5('liren')                     |
+----------------------------------+
| c9ad4aedb509ccd1d92931ef904c88b4 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select md5('liren');
+----------------------------------+
| md5('liren')                     |
+----------------------------------+
| c9ad4aedb509ccd1d92931ef904c88b4 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select md5('中国');
+----------------------------------+
| md5('中国')                      |
+----------------------------------+
| c13dceabcb143acd6c9298265d618a9f |
+----------------------------------+
1 row in set (0.00 sec)

3、`database()`

显示当前正在使用的数据库：

mysql> select database();
+------------+
| database() |
+------------+
| scott      |
+------------+
1 row in set (0.00 sec)

4、`password()`

mysql 数据库通常使用该函数对用户加密，只有相同的字符串，它们加密后的编码才是一样的！

它使用了一个特定的算法，称为 mysql加密算法，该算法是不可逆的，这意味着无法通过解密函数来还原原始密码。PASSWORD() 函数在 MySQL 4.1 版本之前是默认的密码加密函数，但在之后的版本中被弃用，不再是默认的加密方式。

password() 函数是 mysql 特有的密码加密函数，而 MD5() 函数是一个常见的哈希函数，可以用于加密字符串，但不推荐用于密码加密。

此外还有一个细节，我们使用了 password 字眼的时候，在 mysql 中再使用上方向键回滚的时候会发现找不到刚才的带有 password 字眼的语句，说明 mysql 其实对我们输入的指令做了一些优化，涉及到例如 password 等敏感字眼，那么我们在回滚的时候是看不到该语句的！

mysql> select password('liren');
+-------------------------------------------+
| password('liren')                         |
+-------------------------------------------+
| *A56DBAEC100FD8FCBB73DF6C44002D472C047746 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select password('liren');
+-------------------------------------------+
| password('liren')                         |
+-------------------------------------------+
| *A56DBAEC100FD8FCBB73DF6C44002D472C047746 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

5、`ifnull(val1, val2)`

如果 val1 为 null，则返回 val2，否则返回 val1 的值：

mysql> select ifnull(1, 2);
+--------------+
| ifnull(1, 2) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> select ifnull(null, 2);
+-----------------+
| ifnull(null, 2) |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)