mysql 函数内子事务 mysql的内置函数有哪些

转载

mob6454cc77b8eb 2023-10-20 23:20:41

文章标签 mysql 函数内子事务 mysql自定义函数回滚 mysql SQL UNIX 文章分类 MySQL 数据库

一、常见内置函数

从文档列表可知，MySQL内置函数涵盖范围非常广：控制流函数、字符串函数、数值函数、时间日期函数、全文搜索函数映射函数、XML函数、位函数、加密和压缩函数、信息函数、空间分析函数、Json函数、企业加密函数等等。在此只列举开发中常用的函数。

聚合函数

这些函数与其它函数有些不同：它们一般作用在多条记录上，一般与GROUP BY等关键字组合使用。

mysql> SELECT student_name, AVG(test_score)
-> FROM student
-> GROUP BY student_name;
mysql> SELECT student.student_name,COUNT(*)
-> FROM student,course
-> WHERE student.student_id=course.student_id
-> GROUP BY student_name;
#
# COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved,
# whether or not they contain NULL values.`
COUNT(DISTINCT expr,[expr...])：Returns a count of the number of rows with different non-NULL expr values.
mysql> SELECT COUNT(DISTINCT results) FROM student;
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
-> FROM student
-> GROUP BY student_name;
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
-> FROM student
-> GROUP BY student_name;

字符串函数

CONCAT()：返回连接字符串(如果其中任何一个参数为NULL，则返回NULL)

mysql> SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
-> NULL
mysql> SELECT CONCAT(14.3);
-> '14.3'
INSERT(str,pos,len,newstr) ：将str的从pos位置开始的len个字符替换为newstr(如果其中任何一个参数为NULL，则返回NULL)
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
-> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
-> 'QuWhat'
LEFT(str,len)和RIGHT(str,len) ：分别返回str最左边/最右边的n个字符(如果其中任何一个参数为NULL，则返回NULL)
REPLACE(str,from_str,to_str)：将字符串str中所有出现的from_str字符串替换为to_str
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
SUBSTRING(str,pos,len)：返回字符串str中从pos起，len个字符长度的字符串
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'

数值函数

ABS(x)：返回x的绝对值

mysql> SELECT ABS(2);
-> 2
mysql> SELECT ABS(-32);
-> 32
CEILING(x)或者CEIL(x)：返回大于x的最小整数
FLOOR(x) ：返回小于x的最大整数
mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEILING(-1.23);
-> -1
mysql> SELECT FLOOR(1.23), FLOOR(-1.23);
-> 1, -2
MOD(N,M) 或者 N % M 或者 N MOD M：取模
mysql> SELECT MOD(234, 10);
-> 4
mysql> SELECT 253 % 7;
-> 1
mysql> SELECT MOD(29,9);
-> 2
mysql> SELECT 29 MOD 9;
-> 2
RAND()

用法1：返回[0 ~1.0)之间的随机数

//要得到i <= R < j的随机数： FLOOR(i + RAND() * (j−i))

//比如，要得到5~10之间的随机数：FLOOR(5 + RAND()*5)

用法2：SELECT * FROM tbl_name ORDER BY RAND();：retrieve rows in random order

ROUND(X)或者ROUND(X,D)：对X四舍五入，含有D位小数的值，D值默认为0

mysql> SELECT ROUND(-1.23);
-> -1
mysql> SELECT ROUND(-1.58);
-> -2
mysql> SELECT ROUND(1.58);
-> 2
mysql> SELECT ROUND(1.298, 1);
-> 1.3
mysql> SELECT ROUND(1.298, 0);
-> 1
mysql> SELECT ROUND(23.298, -1);
-> 20
TRUNCATE(X,D)：返回数字X被截断为D位小数的数值(与ROUND()类似，只是不四舍五入)
mysql> SELECT TRUNCATE(1.223,1);
-> 1.2
mysql> SELECT TRUNCATE(1.999,1);
-> 1.9
mysql> SELECT TRUNCATE(1.999,0);
-> 1
mysql> SELECT TRUNCATE(-1.999,1);
-> -1.9
mysql> SELECT TRUNCATE(122,-2);
-> 100
mysql> SELECT TRUNCATE(10.28*100,0);
-> 1028

时间日期函数

CURDATE()：返回当前日期，格式为'YYYY-MM-DD'或者YYYYMMDD，取决于该函数的使用场景

mysql> SELECT CURDATE();
-> '2008-06-13'
mysql> SELECT CURDATE() + 0;
-> 20080613
CURTIME([fsp])：返回当前时间，格式为'HH:MM:SS'或者HHMMSS
mysql> SELECT CURTIME();
-> '23:50:26'
mysql> SELECT CURTIME() + 0;
-> 235026.000000
NOW([fsp])：返回当前日期+时间
mysql> SELECT NOW();
-> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
-> 20071215235026.000000
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1496132631 |
+------------------+
1 row in set
mysql> SELECT UNIX_TIMESTAMP('2017-05-30 16:25:19');
+---------------------------------------+
| UNIX_TIMESTAMP('2017-05-30 16:25:19') |
+---------------------------------------+
| 1496132719 |
+---------------------------------------+
1 row in set
mysql> SELECT FROM_UNIXTIME(1447430881);
-> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
-> 20151113100801
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
-> '%Y %D %M %h:%i:%s %x');
-> '2015 13th November 10:08:01 2015'
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'

其他函数

database() ：当前数据库
version()：当前数据库版本
user() ：当前登录用户
mysql> select database(), version(), user();
+------------+------------+----------------+
| database() | version() | user() |
+------------+------------+----------------+
| cpgl | 5.7.17-log | root@localhost |
+------------+------------+----------------+
1 row in set

二、自定义函数

基本语法

创建

CREATE FUNCTION [db_name.]fn_name(func_parameter[,...])
RETURNS type
[characteristic...]
routine_body

#解释

db_name：数据库名，不指明则为当前数据库

type: 任何mysql支持的类型

routine_body: 函数体

characteristic:
LANGUAGE SQL | [NOT]DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'String'

注意：函数中一定要有return返回值语句

删除

DROP FUNCTION [IF EXISTS] fn_name;

更改

ALTER FUNCTION fn_name [characteristic...]

查看状态或定义语句

SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW CREATE FUNCTION fn_name;

函数中的变量

定义变量

a.通过DECLARE可以定义一个局部变量，变量的作用范围BEGIN...END块中；

b.全局变量不用声明，可以直接@XXX使用。定义全局变量：set @var_name=value，如：

mysql> set @a=1;
Query OK, 0 rows affected

c.变量语句必须写在复合语句开头，并且在其他语句的前面;

d.一次性可以声明多个变量;

DECLARE var_name[,...] type [DEFAULT value]

为变量赋值

a.直接赋值：SET var_name = expr[,var_name=expr]...

b.通过查询语句赋值：SELECT...INTO...，如：

SELECT 表中某字段 INTO 变量名 FROM 表名 WHERE条件;

这种方式下，SELECT语句的查询结果只能有一个。

其他说明

运行包含DDL语句，允许提交或回滚

可以在函数中调用其它函数或者存储过程

与存储过程类似，可以一块看

实例

例子1

#创建表

CREATE TABLE `t_user_main` (
`f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',
`f_userName` varchar(5) DEFAULT NULL COMMENT '用户名',
`f_age` int(3) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`f_userId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#插入数据

INSERT INTO t_user_main (f_userName, f_age)
VALUES('one',24),('two',25),('three',26),('four',27),('five',28),('six',29);

#创建一个函数

DELIMITER $$ -- 定界符

-- 开始创建函数

CREATE FUNCTION user_main_fn(v_id INT)
RETURNS VARCHAR(50)
BEGIN
-- 定义变量
DECLARE v_userName VARCHAR(50);
-- 给定义的变量赋值
SELECT f_userName INTO v_userName FROM t_user_main
WHERE f_userId = v_id;
-- 返回函数处理结果
RETURN v_userName;
END $$ -- 函数创建定界符
DELIMITER ;

例子2

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。