?感謝您閱讀本篇文章,文章內容是個人學習筆記的整理,如果哪里有誤的話還請您指正噢?
? 個人主頁:余輝zmh–CSDN博客
? 文章所屬專欄:MySQL篇–CSDN博客
文章目錄
- 內置函數
- 一.日期函數
- 二.字符串函數
- 三.數學函數
- 四.其他函數
內置函數
一.日期函數
1.相關函數
current_date()
:當前日期,年-月-日
mysql> SELECT current_date();
+----------------+
| current_date() |
+----------------+
| 2025-07-31 |
+----------------+
1 row in set (0.00 sec)
current_time()
:當前時間,時-分-秒
mysql> SELECT current_time();
+----------------+
| current_time() |
+----------------+
| 17:52:46 |
+----------------+
1 row in set (0.00 sec)
current_timestamp()
:當前時間戳,年-月-日 時-分-秒
mysql> SELECT current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2025-07-31 17:53:11 |
+---------------------+
1 row in set (0.00 sec)
date(datetime)
:返回datemtime
類型參數的日期部分
mysql> SELECT date('1945-10-01 10:00:00');
+-----------------------------+
| date('1945-10-01 10:00:00') |
+-----------------------------+
| 1945-10-01 |
+-----------------------------+
1 row in set (0.00 sec)
now()
:當前日期時間
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2025-07-31 17:56:58 |
+---------------------+
1 row in set (0.00 sec)
date_add(date, interval d_value_type)
:
在日期date
的基礎上添加日期或時間,interval
后的數值單位可以是年,日,分,秒;
-- 當前日期加上10天
mysql> SELECT date_add(now(), interval 10 day);
+----------------------------------+
| date_add(now(), interval 10 day) |
+----------------------------------+
| 2025-08-10 17:55:38 |
+----------------------------------+
1 row in set (0.00 sec)-- 當前日期加上10分鐘
mysql> SELECT date_add(now(), interval 10 minute);
+-------------------------------------+
| date_add(now(), interval 10 minute) |
+-------------------------------------+
| 2025-07-31 18:06:16 |
+-------------------------------------+
1 row in set (0.00 sec)
date_sub(date, interval d_value_type)
:
在日期date
的基礎上減去日期或時間,interval
后的數值單位可以是年,日,分,秒;
-- 當前日期減去1年
mysql> SELECT date_sub(now(), interval 1 year);
+----------------------------------+
| date_sub(now(), interval 1 year) |
+----------------------------------+
| 2024-07-31 17:58:07 |
+----------------------------------+
1 row in set (0.00 sec)-- 當前日期減去10秒
mysql> SELECT date_sub(now(), interval 10 second);
+-------------------------------------+
| date_sub(now(), interval 10 second) |
+-------------------------------------+
| 2025-07-31 17:58:53 |
+-------------------------------------+
1 row in set (0.00 sec)
datediff(date1, date2)
:兩個日期相差多少天date1-dat2
mysql> SELECT datediff(now(), '1945-10-01');
+-------------------------------+
| datediff(now(), '1945-10-01') |
+-------------------------------+
| 29158 |
+-------------------------------+
1 row in set (0.00 sec)mysql> SELECT datediff('1945-10-01', now());
+-------------------------------+
| datediff('1945-10-01', now()) |
+-------------------------------+
| -29158 |
+-------------------------------+
1 row in set (0.00 sec)
2.示例:
- 創建一個留言表
mysql> CREATE TABLE msg(-> id int unsigned PRIMARY KEY AUTO_INCREMENT,-> message varchar(100) NOT NULL,-> sendtime datetime NOT NULL-> );
Query OK, 0 rows affected (0.05 sec)
- 插入測試數據
mysql> INSERT INTO msg (message, sendtime) values -> ('你好', now()),-> ('你也好', now());
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> INSERT INTO msg(message, sendtime) values ('你是誰', now());
Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM msg;
+----+-----------+---------------------+
| id | message | sendtime |
+----+-----------+---------------------+
| 1 | 你好 | 2025-07-31 18:19:59 |
| 2 | 你也好 | 2025-07-31 18:19:59 |
| 3 | 你是誰 | 2025-07-31 18:25:43 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)
- 顯示所有留言信息,發布日期只顯示日期,不用顯示時間
mysql> SELECT id, message, date(sendtime) FROM msg;
+----+-----------+----------------+
| id | message | date(sendtime) |
+----+-----------+----------------+
| 1 | 你好 | 2025-07-31 |
| 2 | 你也好 | 2025-07-31 |
| 3 | 你是誰 | 2025-07-31 |
+----+-----------+----------------+
3 rows in set (0.00 sec)
- 查詢五分鐘內發布的貼子
-- 當前時間減去五分后后要小于發布的時間
mysql> SELECT * FROM msg WHERE date_sub(now(), interval 5 minute) < sendtime;
+----+-----------+---------------------+
| id | message | sendtime |
+----+-----------+---------------------+
| 3 | 你是誰 | 2025-07-31 18:25:43 |
+----+-----------+---------------------+
1 row in set (0.00 sec)-- 當前發布的時間加上五分鐘后要大于當前時間
mysql> SELECT * FROM msg WHERE date_add(sendtime, interval 5 minute) > now();
+----+-----------+---------------------+
| id | message | sendtime |
+----+-----------+---------------------+
| 3 | 你是誰 | 2025-07-31 18:25:43 |
+----+-----------+---------------------+
1 row in set (0.00 sec)
二.字符串函數
charset(str)
:返回字符串字符集
mysql> SELECT charset('abcd');
+-----------------+
| charset('abcd') |
+-----------------+
| utf8mb4 |
+-----------------+
1 row in set (0.00 sec)
concat(string1, string2, ...)
:連接字符集
mysql> SELECT concat('a', 'b', 'c', 1, 2);
+-----------------------------+
| concat('a', 'b', 'c', 1, 2) |
+-----------------------------+
| abc12 |
+-----------------------------+
1 row in set (0.00 sec)
instr(string, substring)
:返回substring
在string
中出現的位置,沒有返回0
mysql> SELECT instr('abcdefg123', 'def');
+----------------------------+
| instr('abcdefg123', 'def') |
+----------------------------+
| 4 |
+----------------------------+
1 row in set (0.00 sec)mysql> SELECT instr('abcdefg123', 'zmh');
+----------------------------+
| instr('abcdefg123', 'zmh') |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)
ucase(string)
:轉為成大寫
mysql> SELECT ucase('abcdABCD123');
+----------------------+
| ucase('abcdABCD123') |
+----------------------+
| ABCDABCD123 |
+----------------------+
1 row in set (0.01 sec)
lcase(string)
:轉換成小寫
mysql> SELECT lcase('abcdABCD123');
+----------------------+
| lcase('abcdABCD123') |
+----------------------+
| abcdabcd123 |
+----------------------+
1 row in set (0.01 sec)
left(string, length)
:從字符串的左邊起取length
個字符
mysql> SELECT left('abcdefg', 4);
+--------------------+
| left('abcdefg', 4) |
+--------------------+
| abcd |
+--------------------+
1 row in set (0.00 sec)
length(string)
:字符串的長度
mysql> SELECT length('abcdefg');
+-------------------+
| length('abcdefg') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)
replace(str, search_str, replace_str)
:在str
中用replace_str
替換search_str
字符
mysql> SELECT replace('abcdabcdabcd', 'a', 'A');
+-----------------------------------+
| replace('abcdabcdabcd', 'a', 'A') |
+-----------------------------------+
| AbcdAbcdAbcd |
+-----------------------------------+
1 row in set (0.00 sec)
strcmp(string1, string2)
:逐字符比較兩個字符串的大小
mysql> SELECT strcmp('abcd', 'abcd');
+------------------------+
| strcmp('abcd', 'abcd') |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.01 sec)mysql> SELECT strcmp('abcd', 'a');
+---------------------+
| strcmp('abcd', 'a') |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
substring(str, position, length)
:從str
的position
位置開始取length
個字符
mysql> SELECT substring('abcdefg', 2, 3);
+----------------------------+
| substring('abcdefg', 2, 3) |
+----------------------------+
| bcd |
+----------------------------+
1 row in set (0.00 sec)mysql> SELECT substring('abcdefg', 3);
+-------------------------+
| substring('abcdefg', 3) |
+-------------------------+
| cdefg |
+-------------------------+
1 row in set (0.01 sec)
ltrim(string) rtrim(string) trim(string)
:去除前空格或后空格
-- 測試字符串 左邊3個空格+hello+中間兩個空格+world+右邊3個空格 = 18
mysql> SELECT ltrim(' hello world ');
+-----------------------------+
| ltrim(' hello world ') |
+-----------------------------+
| hello world |
+-----------------------------+
1 row in set (0.00 sec)
-- 去除左邊3個長度變為15
mysql> SELECT length(ltrim(' hello world '));
+-------------------------------------+
| length(ltrim(' hello world ')) |
+-------------------------------------+
| 15 |
+-------------------------------------+
1 row in set (0.00 sec)-- 去除右邊的,但是這里效果不明顯(可能是因為 MySQL 命令行工具輸出表格時自動補齊的,不是字符串本身的內容。)
mysql> SELECT rtrim(' hello world ');
+-----------------------------+
| rtrim(' hello world ') |
+-----------------------------+
| hello world |
+-----------------------------+
1 row in set (0.00 sec)
-- 打印長度,也是變為15
mysql> SELECT length(rtrim(' hello world '));
+-------------------------------------+
| length(rtrim(' hello world ')) |
+-------------------------------------+
| 15 |
+-------------------------------------+
1 row in set (0.00 sec)-- 兩邊都去除,但效果也不明顯
mysql> SELECT trim(' hello world ');
+----------------------------+
| trim(' hello world ') |
+----------------------------+
| hello world |
+----------------------------+
1 row in set (0.00 sec)
-- 打印長度,變為12
mysql> SELECT length(trim(' hello world '));
+------------------------------------+
| length(trim(' hello world ')) |
+------------------------------------+
| 12 |
+------------------------------------+
1 row in set (0.00 sec)
三.數學函數
abs(number)
:絕對值函數
mysql> SELECT abs(-100);
+-----------+
| abs(-100) |
+-----------+
| 100 |
+-----------+
1 row in set (0.00 sec)mysql> SELECT abs(100);
+----------+
| abs(100) |
+----------+
| 100 |
+----------+
1 row in set (0.01 sec)
bin(decimal_number)
:十進制轉換為二進制
mysql> SELECT bin(10);
+---------+
| bin(10) |
+---------+
| 1010 |
+---------+
1 row in set (0.00 sec)
hex(number)
:十進制轉為十六進制
mysql> SELECT hex(10);
+---------+
| hex(10) |
+---------+
| A |
+---------+
1 row in set (0.01 sec)
conv(number, from_base, to_base)
:進制轉換
-- 10從十進制轉換為二進制
mysql> SELECT conv(10, 10, 2);
+-----------------+
| conv(10, 10, 2) |
+-----------------+
| 1010 |
+-----------------+
1 row in set (0.00 sec)-- 10從十進制轉換為十六進制
mysql> SELECT conv(10, 10, 16);
+------------------+
| conv(10, 10, 16) |
+------------------+
| A |
+------------------+
1 row in set (0.00 sec)
ceiling(number)
:向上取整
mysql> SELECT ceiling(3.1);
+--------------+
| ceiling(3.1) |
+--------------+
| 4 |
+--------------+
1 row in set (0.01 sec)mysql> SELECT ceiling(3.9);
+--------------+
| ceiling(3.9) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)mysql> SELECT ceiling(-3.1);
+---------------+
| ceiling(-3.1) |
+---------------+
| -3 |
+---------------+
1 row in set (0.01 sec)mysql> SELECT ceiling(-3.9);
+---------------+
| ceiling(-3.9) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)
floor(number)
:向下取整
mysql> SELECT floor(3.1);
+------------+
| floor(3.1) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)mysql> SELECT floor(3.9);
+------------+
| floor(3.9) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)mysql> SELECT floor(-3.1);
+-------------+
| floor(-3.1) |
+-------------+
| -4 |
+-------------+
1 row in set (0.00 sec)mysql> SELECT floor(-3.9);
+-------------+
| floor(-3.9) |
+-------------+
| -4 |
+-------------+
1 row in set (0.00 sec)
format(number, decimal_places)
:格式化,保留小數位數
mysql> SELECT format(3.1415926, 2);
+----------------------+
| format(3.1415926, 2) |
+----------------------+
| 3.14 |
+----------------------+
1 row in set (0.01 sec)mysql> SELECT format(3.1415926, 3);
+----------------------+
| format(3.1415926, 3) |
+----------------------+
| 3.142 |
+----------------------+
1 row in set (0.00 sec)mysql> SELECT format(3.1415926, 4);
+----------------------+
| format(3.1415926, 4) |
+----------------------+
| 3.1416 |
+----------------------+
1 row in set (0.00 sec)
rand()
:返回隨機浮點數,范圍[0.0-1.0]
mysql> SELECT rand();
+---------------------+
| rand() |
+---------------------+
| 0.03627621851514673 |
+---------------------+
1 row in set (0.01 sec)mysql> SELECT rand()*1000;
+-------------------+
| rand()*1000 |
+-------------------+
| 478.7169121938915 |
+-------------------+
1 row in set (0.00 sec)mysql> SELECT format(rand()*1000, 4);
+------------------------+
| format(rand()*1000, 4) |
+------------------------+
| 284.7559 |
+------------------------+
1 row in set (0.00 sec)
mod(number, denominator)
:取模,求余
mysql> SELECT mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
| 1 |
+------------+
1 row in set (0.01 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)mysql> SELECT mod(-10, 3);
+-------------+
| mod(-10, 3) |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
四.其他函數
user()
:查詢當前用戶
mysql> SELECT user();
+-----------------+
| user() |
+-----------------+
| zmh_1@localhost |
+-----------------+
1 row in set (0.00 sec)
md5(str)
:對一個字符串進行md5
摘要,摘要后得到一個32位字符串
mysql> SELECT md5('abcdefg');
+----------------------------------+
| md5('abcdefg') |
+----------------------------------+
| 7ac66c0f148de9519b8bd264312c4d64 |
+----------------------------------+
1 row in set (0.00 sec)mysql> SELECT md5('a');
+----------------------------------+
| md5('a') |
+----------------------------------+
| 0cc175b9c0f1b6a831c399e269772661 |
+----------------------------------+
1 row in set (0.00 sec)
database()
:顯示當前正在使用的是哪個數據庫
mysql> SELECT database();
+------------+
| database() |
+------------+
| test1 |
+------------+
1 row in set (0.00 sec)
password()
:MySQL數據庫使用該函數對用戶加密
但是這個函數在新版本 MySQL 已經不能用了,所以這里也沒辦法做演示,就了解一下吧。
ifnull(val1, val2)
:如果val1
為空,返回val2
,否則返回val1
mysql> SELECT ifnull(NULL, 100);
+-------------------+
| ifnull(NULL, 100) |
+-------------------+
| 100 |
+-------------------+
1 row in set (0.00 sec)mysql> SELECT ifnull(200, 100);
+------------------+
| ifnull(200, 100) |
+------------------+
| 200 |
+------------------+
1 row in set (0.00 sec)-- 只考慮第一個值是否為空,不考慮第二個值
mysql> SELECT ifnull(200, NULL);
+-------------------+
| ifnull(200, NULL) |
+-------------------+
| 200 |
+-------------------+
1 row in set (0.00 sec)
以上就是關于MySQL常用內置函數的講解,如果哪里有錯的話,可以在評論區指正,也歡迎大家一起討論學習,如果對你的學習有幫助的話,點點贊關注支持一下吧!!!