【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，second
date_sub(date, interval d_value_type)	在d_date_type值上添加时间或日期，interval后的单位可以是：year，day，minute，second
datediff(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_str
strcmp(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,'为空');

2. 查询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');