本文源码: GitHub. 点这里 https://github.com/cicadasmile/mysql-data-base || GitEE. 点这里 https://github.com/cicadasmile/mysql-data-base
一, 系统封装函数
MySQL 有很多内置的函数, 可以快速解决开发中的一些业务需求, 大概包括流程控制函数, 数值型函数, 字符串型函数, 日期时间函数, 聚合函数等. 以下列出了这些分类中常用的函数.
1, 控制流程函数
case...when
根据值判断返回值, 类比编程中的 IF-ELSE 判断.
- -- DEMO 01
- SELECT CASE DATE_FORMAT(NOW(),'%Y-%m-%d')
- WHEN '2019-12-29' THEN 'today'
- WHEN '2019-12-28' THEN 'yesterday'
- WHEN '2019-12-30' THEN 'tommor'
- ELSE 'Unknow' END;
- -- DEMO 02
- SELECT (CASE WHEN 1>0 THEN 'true' ELSE 'false' END) AS result;
- if(expr1,expr2,expr3)
如果表达式 expr1 是 TRUE, 则 IF() 的返回值为 expr2; 否则返回值则为 expr3.
- SELECT IF(1>2,'1>2','1<2') AS result ;
- SELECT IF(1<2,'yes','no') AS result ;
- SELECT IF(STRCMP('test','test'),'no','yes');
- ifnull(expr1,expr2)
如果表达式 expr1 不为 NULL, 则返回值为 expr1; 否则返回值为 expr2.
- SELECT IFNULL(NULL,'cicada');
- SELECT IFNULL(1/1,'no');
2, 常用字符串函数
CHAR_LENGTH()
返回值为字符串的长度 .
SELECT CHAR_LENGTH('c i c') ;-- 包含空格
- SELECT LENGTH('S q l') ;
- CONCAT(str1...)
拼接串联字符串.
SELECT CONCAT('My', 'S', 'ql');
SELECT CONCAT('My', NULL, 'QL'); -- 包含 Null 则返回 Null
SELECT CONCAT("%", "Java", "%"); -- mybatis 中拼接模糊查询
ELT(N,str1,str2,...)
若 N = 1, 则返回值为 str1 , 若 N = 2, 则返回值为 str2 , 以此类推, 可以用来转换返回页面的状态.
- SELECT ELT(1,'提交','审核中','规则通过') ;
- SELECT ELT(2,'提交','审核中','规则通过') ;
- FORMAT(X,D)
格式化数字类型.
SELECT FORMAT(3.1455,2) ; -- 四舍五入保留两位
SELECT TRUNCATE(3.1455,2) ; -- 直接截取两位
TRIM(str)
清空字符串空格.
SELECT LTRIM('hel l o') ;-- 清空左边
SELECT RTRIM('hel l o') ;-- 清空右边
SELECT TRIM('hel l o') ; -- 清空两边
SELECT REPLACE('M y S Q L','','') ; -- 替换掉全部空格
3, 数值函数
FLOOR(X)
返回不大于 X 的最大整数值 .
- SELECT FLOOR(1.23); -- 1
- SELECT FLOOR(-1.23); -- -2
- MOD(N,M)
模操作. 返回 N 被 M 除后的余数.
- SELECT MOD(29,9); -- 2
- SELECT 29 MOD 9; -- 2
- RAND() RAND(N)
返回一个随机浮点值, 范围在 0 到 1 之间. 若已指定一个整数参数 N , 则它被用作种子值, 用来产生重复序列.
- SELECT RAND(); -- 0.923
- SELECT RAND(20) = RAND(20) ; -- TRUE
4, 时间日期函数
ADDDATE(date,INTERVAL expr type)
给指定日期, 以指定类型进行运算.
- SELECT DATE_ADD('2019-12-29', INTERVAL 3 DAY); -- 2020-01-01
- CURDATE()
将当前日期按照'YYYY-MM-DD' 或 YYYYMMDD 格式的值返回, 具体格式根据函数用在字符串或是数字语境中而定.
SELECT CURDATE(); -- '2019-12-29' 字符串
SELECT CURDATE() + 0; -- 20180725 数字
DATE(expr)
提取日期或时间日期表达式 expr 中的日期部分.
- SELECT DATE('2019-12-31 01:02:03'); -- '2019-12-31'
- SELECT DATE('2019-12-31 01:02:03')+0; -- 20191231
- DATE_FORMAT(date,format)
根据 format 字符串进行 date 值的格式化.
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2019-12-29
SELECT DATE_FORMAT(NOW(), '%Y 年 %m 月 %d 日'); -- 2019 年 12 月 29 日
5, 聚合函数
AVG([distinct] expr) 求平均值
COUNT({*|[distinct] } expr) 统计行的数量
MAX([distinct] expr) 求最大值
MIN([distinct] expr) 求最小值
SUM([distinct] expr) 求累加和
二, 自定义函数
1, 概念简介
函数存储着一系列 sql 语句, 调用函数就是一次性执行这些语句. 所以函数可以降低语句重复. 函数注重返回值, 而触发器注重执行过程, 所以一些语句无法执行. 所以函数并不是单纯的 sql 语句集合.
2, 使用方式
create function 函数名 ([参数列表]) returns 数据类型
begin
sql 语句;
return 值;
end;
参数列表的格式是: 变量名 数据类型.
无参案例
- CREATE FUNCTION mysum1 () RETURNS INT RETURN (2+3)*2;
- SELECT mysum1 () ;
有参函数
表结构
- CREATE TABLE t01_user (
- id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键 ID',
- user_name varchar(20) DEFAULT NULL COMMENT '用户名称'
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';
函数用法
- create function get_name(p_id INT) returns VARCHAR(20)
- begin
- declare userName varchar(20);
- select user_name from t01_user where id=p_id into userName;
- return userName;
- end;
- SELECT get_name(1) ;
3, 函数查看
show create function get_name ;
4, 删除函数
drop function get_name ;
5, 函数注意事项
函数是事先经过编译, 才能在服务器环境调用, 所以 MySQL 集群环境需要同步编译; MySQL 是多线程环境, 所以要保证函数也是线程安全 .
三, 触发器
1, 触发器简介
触发器是特殊的存储过程, 不同的是存储过程要用 CALL 来调用, 而触发器不需要使用 CALL. 也不需要手工启动, 只要当一个预定义的事件发生的时候, 就会被 MySQL 自动触发调用.
2, 创建触发器
触发器语法
- CREATE TRIGGER trigger_name trigger_time trigger_event
- ON tbl_name FOR EACH ROW trigger_stmt
trigger_name: 触发器命名 ;
trigger_time: 触发动作的时间 ;
trigger_event: 激活触发器的语句类型 ;
tbl_name: 触发器作用的表明, 非临时表 ;
trigger_stmt: 触发程序执行的语句 ;
表数据同步
当向用户表 t01_user 写入数据时, 同时向 t02_back 表写入一份备份数据.
-- 用户备份表
- CREATE TABLE t02_back (
- id int(11) NOT NULL PRIMARY KEY COMMENT '主键 ID',
- user_name varchar(20) DEFAULT NULL COMMENT '用户名称'
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户备份';
-- 触发器程序
- DROP TRIGGER IF EXISTS user_back_trigger ;
- CREATE TRIGGER user_back_trigger AFTER INSERT ON t01_user FOR EACH ROW
- BEGIN
- INSERT INTO t02_back (id,user_name)
- VALUES (new.id,new.user_name);
- END ;
-- 测试案例
- INSERT INTO t01_user (user_name) VALUES ('smile'),('mysql') ;
- SELECT * FROM t02_back ;
3, 查看触发器
查看触发器是指数据库中已存在的触发器的定义, 状态, 语法信息等. 可以在 TRIGGERS 表中查看触发器信息.
- SELECT * FROM `information_schema`.`TRIGGERS`
- WHERE `TRIGGER_NAME`='user_back_trigger';
4, 删除触发器
DROP TRIGGER 语句可以删除 MySQL 中已经定义的触发器, 删除触发器的基本语法.
DROP TRIGGER [schema_name.]trigger_name
5, 触发器注意事项
触发事件
对于相同的表, 相同的事件只能创建一个触发器, 比如对表 t01_user 创建两次 AFTER INSERT 触发器, 就会报错.
执行效率
触发器可以减少应用端和数据库的通信次数和业务逻辑, 但是基于行触发的逻辑, 如果数据集非常大, 效率会降低.
事务问题
触发器执行和原表的执行语句是否在同一个事务中, 取决于触发表的存储引擎是否支持事务.
四, 源代码地址
GitHub. 地址
https://github.com/cicadasmile/mysql-data-base
GitEE. 地址
https://gitee.com/cicadasmile/mysql-data-base
来源: https://www.cnblogs.com/cicada-smile/p/12122604.html