【MySQL 保姆级教学】内置函数（9）

内置函数

1. 日期函数

1.1 日期函数的种类

函数名称说明current_date()返回当前日期current_time()返回当前时间current_timestamp()返回时间戳（日期和时间）now()返回当前时间date(datetime)返回datetime参数的时间部分date_add(date, interval d_value_type)在d_value_type值上添加时间或日期，interval后的单位可以是：year，day，minute，seconddate_sub(date, interval d_value_type)在d_date_type值上添加时间或日期，interval后的单位可以是：year，day，minute，seconddatediff(date1, date2)两个日期的差（date1-date2）

now()

和

current_timestamp()

的区别：

• now()返回当前日期和时间，数据类型为 DATETIME；current_timestamp()返回当前日期和时间，数据类型为 TIMESTAMP。
• now()返回的是服务器的本地时间；current_timestamp()返回的是服务器的本地时间，但在某些上下文中可能会自动转换为客户端的时区。

为什么需要 interval？

1. 明确性： interval 参数明确了你要增减的时间单位和数量，使得函数的行为非常明确。例如，INTERVAL 1 DAY 表示增加一天，而 INTERVAL 2 HOUR 表示增加两个小时。
2. 灵活性： 通过使用 interval，你可以灵活地增减不同单位的时间。这包括天、小时、分钟、秒、月、年等。
3. 可读性和维护性： 使用 interval 使代码更具可读性和维护性。其他开发人员可以很容易地理解你在做什么，而不需要猜测。

1.2 示例

1. 获取当前日期（年月日） 命令：select current_date();
2. 获取当前时间 命令：select current_time();
3. 获取当前日期和时间 命令：select now();
4. 获取当前时间戳（日期和时间） 命令：select current_timestamp();
5. 在日期的基础上加上时间 命令：select date_add('2020-10-1', interval 10 day);
6. 在日期的基础上减去时间 命令：select date_sub('2020-10-1', interval 10 day);
7. 两个时间相减，左-右，返回的单位是天 命令：select datediff('2024-10-10', '2020-8-8');

1.3 日期的转换

1. 创建一个留言板 命令：> create table comment(> id int primary key auto_increment,> comment text,> sendtime datetime> );
2. 插入数据 命令：> insert into comment(comment, sendtime)> values(‘放假啦’,now()),> (‘一起出去玩吧’,now());命令:insert into comment (comment, sendtime) values('好的，约个地点吧', current_date());命令：insert into comment(comment, sendtime) values('去哪玩呢？', current_time());命令：insert into comment(comment, sendtime) values('去游乐场吧', current_timestamp);
3. 查询在两分钟内发布的帖子 命令：select comment from comment where date_add(sendtime, interval 2 minute) > now();或命令：select comment from comment where date_sub(now(), interval() 2 minute) < sendtime;

同学们有没有发现，我们的

comment

表中

comment

字段设置的是

datetime

类型，而在插入的过程中有

current_date()

，

current_time()

，

current_timestamp()

。
这样类型的数据差进去后还是

datetime

类型吗？

命令：

select * from comment;

DATETIME 类型的列可以接受 CURRENT_DATE()、CURRENT_TIME() 和 CURRENT_TIMESTAMP() 函数返回的值，因为这些函数返回的值可以被隐式转换为 DATETIME 类型。以下是对每个函数的详细解释以及它们如何与 DATETIME 类型交互：

1. current_date返回值是date日期，当将 CURRENT_DATE() 的值插入到 DATETIME 列时，数据库会自动将 DATE 值转换为 DATETIME 值，通常会将时间部分设置为 00:00:00。
2. current_time返回值是time时间，当将 CURRENT_TIME() 的值插入到 DATETIME 列时，数据库会自动将 TIME 值转换为 DATETIME 值，通常会将日期部分设置为current_date()的值。
3. current_timestamp()的返回值是时间戳（格式是datetime类型），当将 CURRENT_TIMESTAMP() 的值插入到 DATETIME 列时，数据库会直接使用返回的 TIMESTAMP 或 DATETIME 值。

默认行为：

• 数据库系统通常会尽量避免数据丢失，因此它会尝试将不同类型的日期/时间数据转换为 DATE 类型，而不是直接拒绝插入。

具体的转换还是根据MySQL的版本。

为了表示数据的准确，可以使用

date(time/datetime/timestamp)

函数进行日期提取

2. 字符串函数

2.1 种类

函数名称说明charset(str)返回字符串的字符集collation(str)返回字符串的校验集concat(string [,…])连接字符instr(string, substring)返回substring在string中的位置ucase(string)转换成大写lcase(string)转换成小写left(string, length)从string左边开始取length多个字符right(string, length)从string右边开始取length多个字符length(string)string的长度replace(str, searth_str, replace_str)把str中的search_str替换成replace_strstrcmp(string1,string2)逐字比较字符串string1和string2的大小substring(string, position [, length])从position开始取leng个字符，没有length则从position到末ltrim(string)去掉string左边的空格rtrim(string)去掉string右边的空格trim(string)去掉string左右两边的空格

2.2 示例

1. 获取t1表的id字段的字符集和校验集 命令：select charset(id), collation(id) from t1;
2. concat()拼接字符串 命令：select concat(name, '的分数是：',score) from t1; 如果拼接的字段有一行为空，则该行直接显示为空
3. t1表中，姓名占用的字节数 命令：select length(name) from t1; length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数； 如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数 （与字符集编码有关）；使用utf8时，一个汉字占3个字节；使用gbk时，一个汉字占2个字节。
4. 把t1b表姓名中的‘李’替换成‘li’ 命令：select replace(name, '李', 'li') from t1;
5. 截取姓名的第2到第三个字符（相当于下标从1开始）。substring(string, position, length)：从position开始的length个字符（包含position） 命令：select substring(name, 2,2) from t1;如果字段从第二个字符开始不足两个字符，则取一个字符。
6. 把t2表的name字段的x姓和名分开打印，姓转换为大写，名为小写 命令：select ucase(substring(name, 1, 1)), lcase(substring(name, 2)) from t2;
7. instr(string, substring),返回substring在string中出现的位置 命令：select instr('abcdefg', 'bc'); 命令：select instr(''abcdefg, 'abcd'); 同学们可以发现，返回的是查询到substring的首位置。string的下标从1开始。
8. 清空字符串的空格 清空左边空格命令：select ltrim(' 你好 ! '); 清空右边空格命令：select rtrim(‘ 你好 ! ’); 清空两边空格命令：select trim(' 你好 ! '); 字符串包含的空格清楚不了。

3. 数学函数

3.1 种类

函数名称说明abs(number)绝对值函数bin(decimal_number)十进制转换成二进制hex(decimal_number)十进制转换成十六进制conv(number, from_number, to_number)进制转化ceiling(number)向上取整floor(number)向下取整format(number,decimal_replace)格式化，保留小数位数rand()返回随机浮点数，范围[0.0, 1.0)mod(number,denominator)取模，求余md5(str)生成32位的唯一标识符

3.2 向上取整和向下取整

向上取整 (Ceiling)

• 定义：向上取整是指将一个浮点数转换为大于或等于该浮点数的最小整数。
• 符号：通常用 ceil 表示。
• 数学表示：ceil(x) 返回不小于 x 的最小整数。

向下取整 (Floor)

• 定义：向下取整是指将一个浮点数转换为小于或等于该浮点数的最大整数。
• 符号：通常用 floor 表示。
• 数学表示：floor(x) 返回不大于 x 的最大整数。

使用t3表：

向上取整命令：

select ceiling(score) from t3;

向下取整命令：

select flooor(score) from t3;

3.3 示例

1. 绝对值函数abs() 命令：select abs(-111.11);
2. 保留两位小数，foemat(number, n), n是保留小数位的个数 命令：select format(score, 2) from t3;
3. rand()产生随机数。 产生0-99的随机数：select rand()*100;产生0-999的随机数：select rand()*1000;
4. 十进制转换为二进制 命令：select bin(64);
5. 十进制转化为十六进制 命令：select hex(64);
6. 取模,求余 命令：select mod(100,3);

4. 其他函数

4.1 查询当前用户/数据库

1. 查询当前用户 命令：seelct user()
2. 查询当前数据库 命令：select database();

4.2 ifnull(val1,val2)

ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
示例：

1. 命令：select ifnull(null,'为空');

1. 查询t2表的姓名，如果姓名为空，则替换为’未输入名字’ 命令：select ifnull(name, '未输入名字') from t1;

4.3 md5()函数

MySQL 中，MD5 哈希函数主要用于生成数据的哈希值。虽然 MD5 在密码存储和高安全性要求的应用中已经不被推荐使用，但在某些情况下，它仍然可以用于数据完整性验证、简单的数据加密或生成唯一标识符等场景。

MD5 在 MySQL 中的作用

• 数据完整性验证： 通过计算数据的 MD5 哈希值，可以验证数据在传输过程中是否被篡改。 例如，在文件上传或下载时，可以通过比较文件的 MD5 值来确保文件的完整性。
• 简单的密码存储： 尽管不推荐，但在一些旧系统中，MD5 仍然用于存储用户密码的哈希值。 现代系统应使用更强的哈希算法（如 SHA-256 或 bcrypt）并结合盐（salt）来存储密码。
• 生成唯一标识符： 可以使用 MD5 生成数据的唯一标识符，例如在生成 URL 缩短服务中的短链接时。
• 数据去重： 通过计算数据的 MD5 哈希值，可以快速检测和删除重复的数据记录。

示例：

1. 把字符串生成哈希值 命令：select md5('abcdefg');
2. 这是一个把密码加密的场景。password()函数的加密程度更高。md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串创建表t4，命令：> create table t4(> id char(11) primary key ,> password char(32) - - MD5摘要后是32位的字符串> );插入MD5数据，命令：> insert into t4> values(‘111’, md5(‘111’)),> (‘222’,md5(‘222’)),> (‘333’, md5(‘333’)); 查看全部数据where查询 命令：select id, password from t4 where id='111'and password=md5('111');

4.4 password()函数

在 MySQL 中，PASSWORD() 函数用于计算和返回一个字符串的哈希值，这个哈希值是 MySQL 用于存储用户密码的格式。PASSWORD() 函数生成的哈希值是一个 41 字符的字符串，通常用于 MySQL 用户认证。

PASSWORD() 函数的作用

1. 生成密码哈希： - PASSWORD() 函数接受一个字符串作为输入，并返回该字符串的哈希值。这个哈希值是使用 MySQL 内部的算法生成的。- 生成的哈希值是一个 41 字符的字符串，格式为 *<hash_value>，其中 <hash_value> 是实际的哈希值。
2. 用户认证： - 在 MySQL 中，用户的密码通常是通过 PASSWORD() 函数进行哈希处理后存储在 mysql.user 表中的 password 列中。- 当用户尝试登录时，MySQL 会将用户提供的密码通过 PASSWORD() 函数进行哈希处理，并与存储在 mysql.user 表中的哈希值进行比较，以验证用户的身份。

示例：

1. 命令：select password('111');