MySQL数据库:内置函数

发布时间:2023年12月23日

目录

一. 日期相关函数

二. 字符串相关函数

三. 常用数学函数

四. 其它函数


一. 日期相关函数

表1. 日期相关函数
函数功能
current_date()获取当前的日期 ( 年-月-日 ),格式为'yyyy-mm-dd'
current_time()获取当前时间 ( 时-分-秒 ),格式为'hh:mm:ss'
current_timestamp()获取当前时间戳,前端显示格式为'yyyy-mm-dd HH:ii:ss'
date(datetime)返回datetime中的日期部分(去掉时间部分)
date_add(date, interval d_val)

从日期类型数据中加上一定的时间,

增加时间的单位可以是year / month / day / hour / minute / second

date_sub(date, interval d_val)

从日期类型数据中减去一定的时间,

减去时间的单位可以是year / month / day / hour / minute / second

datediff(date1, date2)计算两个日期之间的差值,单位是天
now()获取当前时间

日期类型数据之间可以进行比较,时间靠后的数据 > 时间靠前的数据。

  • 获取当前的日期、时间和时间戳
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-12-23     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 19:35:44       |
+----------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-12-23 19:35:49 |
+---------------------+
1 row in set (0.00 sec)
  • 截取datetime类型参数中的日期部分
mysql> select date('2023-11-23 22:23:05');
+-----------------------------+
| date('2023-11-23 22:23:05') |
+-----------------------------+
| 2023-11-23                  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2023-12-23  |
+-------------+
1 row in set (0.00 sec)

mysql> select date(current_timestamp());
+---------------------------+
| date(current_timestamp()) |
+---------------------------+
| 2023-12-23                |
+---------------------------+
1 row in set (0.00 sec)
  • 获取当前的日期和时间

可以通过时间戳来获取,也可以直接调用now函数获取。

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-12-23 19:39:03 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-12-23 19:39:05 |
+---------------------+
1 row in set (0.00 sec)
  • 计算在当前时间now()加上和减去特定时间后的时间
-- 当前时间向后2年
mysql> select date_add(now(), interval 2 year);
+----------------------------------+
| date_add(now(), interval 2 year) |
+----------------------------------+
| 2025-12-23 19:39:58              |
+----------------------------------+
1 row in set (0.00 sec)

-- 当前时间向后2个月
mysql> select date_add(now(), interval 2 month);
+-----------------------------------+
| date_add(now(), interval 2 month) |
+-----------------------------------+
| 2024-02-23 19:40:03               |
+-----------------------------------+
1 row in set (0.01 sec)

-- 当前时间向后35天
mysql> select date_add(now(), interval 35 day);
+----------------------------------+
| date_add(now(), interval 35 day) |
+----------------------------------+
| 2024-01-27 19:40:19              |
+----------------------------------+
1 row in set (0.01 sec)

-- 当前时间向后60min
mysql> select date_add(now(), interval 60 minute);
+-------------------------------------+
| date_add(now(), interval 60 minute) |
+-------------------------------------+
| 2023-12-23 20:40:33                 |
+-------------------------------------+
1 row in set (0.00 sec)

-- 当前时间向前1000秒
mysql> select date_sub(now(), interval 1000 second);
+---------------------------------------+
| date_sub(now(), interval 1000 second) |
+---------------------------------------+
| 2023-12-23 19:24:11                   |
+---------------------------------------+
1 row in set (0.00 sec)

-- 当前时间向前30天
mysql> select date_sub(now(), interval 30 day);
+----------------------------------+
| date_sub(now(), interval 30 day) |
+----------------------------------+
| 2023-11-23 19:41:04              |
+----------------------------------+
1 row in set (0.00 sec)
  • 两个日期相差天数的计算
mysql> select datediff('2023-12-20','2023-02-07') as GapDays;
+---------+
| GapDays |
+---------+
|     316 |
+---------+
1 row in set (0.00 sec)

mysql> select datediff('2023-02-07','2023-12-20') as GapDays;
+---------+
| GapDays |
+---------+
|    -316 |
+---------+
1 row in set (0.00 sec)

二. 字符串相关函数

表2. 字符串操作相关函数
函数功能
charset(str)获取字符串str的字符编码集。
concat(str1, str2, str3, ...)将若干个字符串进行拼接。
instr(str, subStr)返回subStr在字符串中首次出现的位置下标,注意此时下标从1开始,如果str中找不到字符串subStr,那么就返回0。
ucase(str)将小写字母全部转换为大写字母,不对非英文字母字符做处理。
lcase(str)将大写字母全部转换为小写字母,不对非英文字母字符做处理。
left(str, length)从最左边开始,截取str的前length个字符,如果length超过str的字符数,那么就获取全部的str字符。
right(str, length)从最右边开始,截取str的后length个字符,如果length超过str的字符数,那么就获取全部的str字符。
length(str)计算字符串str的长度,注意是以字节数为单位,在utf8编码中,一个C/C++字符占一个字节,一个汉字占3个字节。
replace(str, subStr, replaceStr)将str中的子串subStr,全部替换为replaceStr,注意是全部替换,而不是仅替换第一次出现的subStr
strcmp(str1, str2)按照字典序比较str1和str2的大小,功能与C语言库函数strcmp完全相同,如果str1>str2返回1,str1<str2返回-1,str1=str2返回0。
substring(str, pos, [length])从字符串str的pos下标处开始截取长度为length的子串,下标从1开始,length如果省略或长度过大就截取到字符串末尾。
ltrim(str)截掉字符串str左边的空格。
rtrim(str)截掉字符串str右边的空格。
trim(str)截掉字符串str左边和右边的空格,中间空格不受影响。
  • 获取字符串编码集

默认情况下,MySQL数据库采用utf8编码集。

+------------------+
| charset('hello') |
+------------------+
| utf8             |
+------------------+
1 row in set (0.00 sec)
  • 拼接字符串

concat函数,无论传给这个函数多少个字符串,都能进行拼接,如果只传递一个字符串,就返回这个字符串本身。

mysql> select concat('aa');
+--------------+
| concat('aa') |
+--------------+
| aa           |
+--------------+
1 row in set (0.00 sec)

mysql> select concat('aa','bbb','cc');
+-------------------------+
| concat('aa','bbb','cc') |
+-------------------------+
| aabbbcc                 |
+-------------------------+
1 row in set (0.00 sec)
  • 在字符串中查找特定子串
-- 'bc'在'abckmbcc'中首次出现在下标为2的位置处
mysql> select instr('abckmbcc','bc');
+------------------------+
| instr('abckmbcc','bc') |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)

-- 'hello'在'abckmbcc'中没有出现
mysql> select instr('abckmbcc','hello');
+---------------------------+
| instr('abckmbcc','hello') |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)
  • 大小写字母之间的转换
mysql> select ucase('hello%%abc');
+---------------------+
| ucase('hello%%abc') |
+---------------------+
| HELLO%%ABC          |
+---------------------+
1 row in set (0.00 sec)

mysql> select lcase('HELLO%%ABC');
+---------------------+
| lcase('HELLO%%ABC') |
+---------------------+
| hello%%abc          |
+---------------------+
1 row in set (0.00 sec)
  • 在字符串的左端和右端截取子串
mysql> select left('abcdef', 4);
+-------------------+
| left('abcdef', 4) |
+-------------------+
| abcd              |
+-------------------+
1 row in set (0.01 sec)

mysql> select left('abcdef', 10);
+--------------------+
| left('abcdef', 10) |
+--------------------+
| abcdef             |
+--------------------+
1 row in set (0.00 sec)

mysql> select right('abcdef', 4);
+--------------------+
| right('abcdef', 4) |
+--------------------+
| cdef               |
+--------------------+
1 row in set (0.00 sec)

mysql> select right('abcdef', 10);
+---------------------+
| right('abcdef', 10) |
+---------------------+
| abcdef              |
+---------------------+
1 row in set (0.00 sec)
  • 用length()函数计算字符串长度

length = 3 * 汉字数 + 1 * C/Cpp字符数

-- 2个汉字,4个C++字符,总共10字节
mysql> select length('中国2023');
+----------------------+
| length('中国2023')   |
+----------------------+
|                   10 |
+----------------------+
1 row in set (0.00 sec)
  • ?替换字符串中的指定子串
mysql> select replace('bcXXaaXXmmXXc','XX','HELLO');
+---------------------------------------+
| replace('bcXXaaXXmmXXc','XX','HELLO') |
+---------------------------------------+
| bcHELLOaaHELLOmmHELLOc                |
+---------------------------------------+
1 row in set (0.00 sec)
  • 替换特定的子串
-- 从下标为2的位置开始,取长度为4的子串
mysql> select substring('abcdef',2,4);
+-------------------------+
| substring('abcdef',2,4) |
+-------------------------+
| bcde                    |
+-------------------------+
1 row in set (0.00 sec)

-- 从下标为2的位置开始,取长度为20的子串
-- 由于长度20会超过字符串末尾,那么就截取到末尾字符
mysql> select substring('abcdef',2,20);
+--------------------------+
| substring('abcdef',2,20) |
+--------------------------+
| bcdef                    |
+--------------------------+
1 row in set (0.00 sec)

-- 截取从下标2开始到末尾字符的子串
mysql> select substring('abcdef',2);
+-----------------------+
| substring('abcdef',2) |
+-----------------------+
| bcdef                 |
+-----------------------+
1 row in set (0.00 sec)
  • 去除字符串左边和右边位置的空格
mysql> select ltrim('   hello   world          ') as ans;
+-------------------------+
| ans                     |
+-------------------------+
| hello   world           |
+-------------------------+
1 row in set (0.01 sec)

mysql> select rtrim('   hello   world          ') as ans;
+------------------+
| ans              |
+------------------+
|    hello   world |
+------------------+
1 row in set (0.00 sec)

mysql> select trim('   hello   world          ') as ans;
+---------------+
| ans           |
+---------------+
| hello   world |
+---------------+
1 row in set (0.00 sec)

三. 常用数学函数

表2. 数学函数
函数功能
abs(number)求number的绝对值。
bin(decimal_number)将十进制数转换为其二进制表示,注意对于小数并不是直接返回其对应二进制,而是输出其向0取整后的二进制表示。
hex(decimal_number)将十进制数转换为16进制,对浮点数的处理规则与bin相同。
conv(number, from_base, to_base)进制转换,将number转换为目标进制表示,from_base为数据原本的进制,to_base为目标进制。
ceil(number)向上取整。
floor(number)向下取整。
format(number, places)格式化数据,保留places位精度。
rand()随机生成位于[0.0, 1.0)的浮点数。
mod(number, denominator)取模运算。
  • 对整数和浮点数求绝对值
mysql> select abs(123);
+----------+
| abs(123) |
+----------+
|      123 |
+----------+
1 row in set (0.00 sec)

mysql> select abs(-123);
+-----------+
| abs(-123) |
+-----------+
|       123 |
+-----------+
1 row in set (0.00 sec)

mysql> select abs(-123.1234);
+----------------+
| abs(-123.1234) |
+----------------+
|       123.1234 |
+----------------+
1 row in set (0.00 sec)
  • 调用bin函数获取正整数、负整数和浮点数对应的二进制表示
mysql> select bin(-1);
+------------------------------------------------------------------+
| bin(-1)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111111 | -- -1的二进制补码为全1
+------------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> select bin(3.56);
+-----------+
| bin(3.56) |
+-----------+
| 11        |  -- 返回3的二进制表示,小数部分被忽略
+-----------+
1 row in set (0.00 sec)
  • 使用conv函数进行进制转换
-- 十进制数30转为16进制表示
mysql> select conv(30,10,16);
+----------------+
| conv(30,10,16) |
+----------------+
| 1E             |
+----------------+
1 row in set (0.00 sec)

-- 十进制数9转为2进制表示
mysql> select conv(9,10,2);
+--------------+
| conv(9,10,2) |
+--------------+
| 1001         |
+--------------+
1 row in set (0.00 sec)

-- 十进制数38转为20进制表示
mysql> select conv(38,10,20);
+----------------+
| conv(38,10,20) |
+----------------+
| 1I             |
+----------------+
1 row in set (0.00 sec)
  • 向下取整和向上取整

在实际的项目中,常用的取整方式有三种:零向取整、向上取整、向下取整。

图3.1 三种取整方式
mysql> select ceil(3.1);
+-----------+
| ceil(3.1) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql> select ceil(-3.1);
+------------+
| ceil(-3.1) |
+------------+
|         -3 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(-3.9);
+------------+
| ceil(-3.9) |
+------------+
|         -3 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(-4.2);
+-------------+
| floor(-4.2) |
+-------------+
|          -5 |
+-------------+
1 row in set (0.00 sec)
  • 格式化数据
mysql> select format(1.234567,3);
+--------------------+
| format(1.234567,3) |
+--------------------+
| 1.235              |
+--------------------+
1 row in set (0.00 sec)

mysql> select format(1.234567,10);
+---------------------+
| format(1.234567,10) |
+---------------------+
| 1.2345670000        |
+---------------------+
1 row in set (0.01 sec)

mysql> select format(1.234567,0);
+--------------------+
| format(1.234567,0) |
+--------------------+
| 1                  |
+--------------------+
1 row in set (0.00 sec)
  • 随机生成数据

通过rand()可以随机生成位于[0.0, 1.0)之间的数据,如果希望生成随机范围的数据,可以通过rand配合乘法与加减法来实现,通过配合format(... , 0)可以获取整数。如下代码,实现了通过rand()获取[0,10)、[5,15)以及[-3.0, 8) 范围内的整数。

-- 生成[0.0, 10.0)范围内的数据
mysql> select format(10 * rand(), 2);
+------------------------+
| format(10 * rand(), 2) |
+------------------------+
| 9.08                   |
+------------------------+
1 row in set (0.00 sec)

-- 生成[5.0, 15.0)范围内的数据
mysql> select format(10 * rand() + 5, 2);
+----------------------------+
| format(10 * rand() + 5, 2) |
+----------------------------+
| 5.56                       |
+----------------------------+
1 row in set (0.00 sec)

-- 生成[-3.0, 8.0)范围内的数据
mysql> select format(11 * rand() - 3, 2);
+----------------------------+
| format(11 * rand() - 3, 2) |
+----------------------------+
| 3.08                       |
+----------------------------+
1 row in set (0.00 sec)
  • 取模运算

取模运算的规则:假设计算A % B,那么计算结果res的符号(正负)应与A相同,并且计算结果的绝对值为:abs(res) = abs(A) % abs(B)。

如:(-10) % 3 = -1,10 % (-3) = 1。

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

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

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

四. 其它函数

表4. 其它函数
函数功能
user()查看当前使用数据库的用户。
database()查看当前使用的数据库。
row_count()查看上一个操作影响的行数,-1表示影响0行数据。
md5(str)获取字符串对应的md5码。
password(str)对字符串进行加密以作为密码来使用。
ifnull(val1, val2)如果val1为null返回val2,否则返回val1,类似三目运算符。
  • 查看当前的用户、数据库和上次操作影响的行数
mysql> select user();
+--------+
| user() |
+--------+
| root@  |  -- 当前使用数据库的是超级管理员
+--------+
1 row in set (0.00 sec)

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

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |  -- 上次操作影响0行数据
+-------------+
1 row in set (0.00 sec)
  • 获取字符串的md5码以及对字符串加密
mysql> system clear;
mysql> select md5('abcdef');
+----------------------------------+
| md5('abcdef')                    |
+----------------------------------+
| e80b5017098950fc58aad83c8c14978e |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select password('abcdef');
+-------------------------------------------+
| password('abcdef')                        |
+-------------------------------------------+
| *C2D24DCA38E9E862098B85BF0AB35CAA52803797 |
+-------------------------------------------+
1 row in set, 1 warning (0.01 sec)
  • 调用ifnull模拟三目运算符

对于ifnull(val1, val2),如果val1和val2都为null,那么ifnull也会返回null。

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

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

mysql> select ifnull(null, null);
+--------------------+
| ifnull(null, null) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)
文章来源:https://blog.csdn.net/weixin_43908419/article/details/135173028
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。