MySQL數據庫之內置函數和自定義函數 function
前言:
函數分為兩類:系統函數和自定義函數
使用函數:
select 函數名(參數列表);
1、內置函數
1.1、字符串函數
函數名 | 說明 |
---|---|
char_length | 判斷字符串的字符數 |
length | 判斷字符串的字節數,與字符集有關 |
concat | 連接字符串 |
insrt | 檢查字符是否在目標字符串中,存在返回其位置,不存在返回 0 |
lcase | 全部小寫 |
ltrim | 消除左邊的空格 |
left(str, length) | 左側開始截取字符串,直到指定位置 |
right(str, length) | 右側開始截取字符串,直到指定位置 |
mid | 從中間指定位置開始截取,如果不指定截取長度,直接到最後 |
substring(str, index, [length]) |
從指定位置開始,指定截取長度 |
substring_index(str, delim, count) | 按照關鍵字截取 |
示例:
select char_length('你好中國'); // 4 select length('你好中國'); // 12 select length('hello'); // 5 select char_length('hello'); // 5 select concat('你好', '中國'); // 你好中國 -- 下標從 1 開始 select instr('你好中國', '中國'); // 3 select instr('你好中國', '我'); // 0 select lcase('aBcd'); // abcd select left('aBcd', 2); // aB select right('abcdef', 2); // ef select substring('abcdef', 2, 3); // bcd select substring('abcdef', -2, 3); // ef select ltrim(' abc d '); // abc d select mid('你好中國', 3); // 中國 select substring_index('www.baidu.com', '.', 2); // www.baidu select substring_index('www.baidu.com', '.', -2); // baidu.com
1.2、時間函數
函數名 | 說明 |
---|---|
now() | 返回當前時間,日期 時間 |
curdate() | 當前日期 |
curtime() | 當前時間 |
datediff() | 判斷兩個日期之間的天數之差,日期使用字符串格式(用引號) |
date_add(日期, interval 時間數字 type) | 時間增加(type: |
unix_timestamp() | 獲取時間戳 |
from_unixtime() | 將指定時間戳轉換成對應的日期時間格式 |
示例:
select now(); // 2022-04-10 22:05:38 select curdate(); // 2022-04-10 select curtime(); // 22:05:51 select datediff('2022-01-09', '2022-01-01'); // 8 select date_add('2000-10-01', interval 10 day); // 2000-10-11 select unix_timestamp(); // 1649599799 select from_unixtime(1649599799); // 2022-04-10 22:09:59
1.3、數學函數
函數名 | 說明 |
---|---|
abs | 絕對值 |
ceiling | 向上取整 |
floor | 向下取整 |
pow | 指數 |
rand | 隨機數(0-1) |
round | 四舍五入 |
示例:
select abs(-1); // 1 select ceiling(1.1); // 2 select floor(1.9); // 1 select pow(2, 4); // 16 select rand(); // 0.2616088308967732 select round(1.5); // 2
1.4、其他函數
函數名 | 說明 |
---|---|
md5() | MD5 |
version() | 版本號 |
database() | 顯示當前所在數據庫 |
uuid() | 生成一個唯一標識符,全局唯一 |
示例:
select md5('abc'); // 900150983cd24fb0d6963f7d28e17f72 select version(); // 8.0.16 select database(); // mydatabase select uuid(); // c44a06a2-b8d8-11ec-a53c-504259f9d746
2、自定義函數
mysql一旦見到分號結束符,就會開始執行
修改語句結束符
基本語法:
delimiter 符號;
2.1、創建函數
基本語法:
-- 修改語句結束符 delimiter $$; create function 函數名(形參) returns 返回值類型 begin // 函數體 return 返回值數據; end 語句結束符 -- 將語句結束符修改回來 delimiter ;
示例:
-- 修改語句結束符 delimiter $$ create function my_func1() returns int begin return 10; end -- 結束 $$ -- 將語句結束符改回來 delimiter ;
如果隻有一條語句,可以省略begin 和 end
-- 最簡單的函數 create function foo() returns int return 10;
為函數的形參指定數據類型
基本語法:
形參 數據類型
示例:
create function my_func2(a int, b int) returns int return a * b;
2.2、查看函數
基本語法:
show function status [like 'pattern'];
示例:
-- 查看所有函數 show function status\G -- 查看單個函數 mysql> show function status like 'foo'\G *************************** 1. row *************************** Db: mydatabase Name: foo Type: FUNCTION Definer: root@localhost Modified: 2022-04-10 22:34:06 Created: 2022-04-10 22:34:06 Security_type: DEFINER Comment: character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8mb4_general_ci 1 row in set (0.00 sec) -- 查看函數創建語句 mysql> show create function foo\G *************************** 1. row *************************** Function: foo sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) return 10 character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8mb4_general_ci 1 row in set (0.01 sec)
2.3、調用函數
基本語法
select 函數名(實參列表);
示例:
mysql> select foo(); +-------+ | foo() | +-------+ | 10 | +-------+ mysql> select my_func2(2, 3); +----------------+ | my_func2(2, 3) | +----------------+ | 6 | +----------------+
2.4、刪除函數
基本語法
drop function 函數名;
示例:
drop function my_func1;
2.5、註意事項
- 自定義函數屬於用戶級別,隻有當前客戶端對應的數據庫中可以使用
- 可以在不同數據庫下看到函數,但是不可以調用
- 自定義函數通常是為瞭將多行代碼集合到一起解決一個重復性的問題
4.函數必須規范返回值,那麼在函數內部不能使用select指令,select一旦執行就會的到一個結果集 result set;
可以使用給變量賦值語句
select 字段 into @變量;
3、函數流程結構案例
需求:
從1開始,直到用戶傳入的對應的值位置,自動求和,凡是5的倍數都不要
設計:
- 創建函數
- 需要一個形參,確定要累加到什麼位置
- 需要定義一個變量來保存對應的結果
- 內容部需要一個循環來實現迭代累加
- 循環內部需要進行條件判斷控制,5的倍數
定義函數:
-- 創建一個自動求和的函數 -- 修改語句結束符 delimiter $$ -- 創建函數 create function my_sum(end_value int) returns int begin -- 聲明局部變量 declare res int default 0; declare i int default 0; -- 循環處理 mywhile: while i <= end_value do -- mysql中沒有++ set i = i + 1; -- 判斷當前數據是否合理 if i % 5 = 0 then iterate mywhile; end if; -- 修改變量,累加 set res = res + i; end while; -- 返回值 return res; end -- 結束 $$ -- 修改語句結束符 delimiter ;
調用函數:
-- 實參個數必須等於形參個數 select my_sum(10);
推薦閱讀:
- MySQL一次性創建表格存儲過程實戰
- MySQL數據庫 觸發器 trigger
- MySQL 權限控制詳解
- mysql存儲中使用while批量插入數據(批量提交和單個提交的區別)
- MySQL校對規則(COLLATION)的具體使用