MySQL快查
因为在日常工作学习中经常忘记mysql的一些语句、关键字、操作等内容,所以最近抽取时间写了以下关于mysql相关内容。相当于一本字典吧
重置mysql密码
数据类型
运算符
常用函数
数据完整性
数据库的基本操作
对表本身的操作
对表中数据的操作
子查询
多表连接
索引
视图
预处理SQL语句
本文
在MySQL中编程
自定义函数与存储过程
函数和存储过程的官方说明:
CREATE PROCEDURE and CREATE FUNCTION Statements
自定义函数
MySQL函数的概念类似于编程语言(如C语言)的概念。
创建自定义函数
create function func_name([[in | out | inout]func_parameter type[, ...]])
returns return_type
[characteristic...]
begin
# 函数体;
return [return_values];
end;
# func_name 函数名
# [in | out | inout] in表示输入参数,out表示输出参数,inout表示输入输出参数
# func_parameter 参数名
# type 参数类型
# return_type 函数返回值类型
# characteristic 指定函数的特征参数,可选值:
language sql
# 默认选项,用于说明函数体使用SQL语言编写
| [not] deterministic
# detrministic(确定性),当函数返回值不确定时,该选项是为了防止“复制”时的不一致。如果
# 函数总是对同样的输入参数产生同样的结果,则被认为是“确定的”,否则认为是“不确定的”。
# 不指定默认是“not deterministic”
| {contains sql | no sql | reads sql data | modifies sql data}
# 指明子程序使用SQL语句的限制。
# contains sql 表示函数体中不包含读或写数据的语句,如set
# no sql 表示函数体中不包含SQL语句
# reads sql data 表示函数体中包含select查询语句,但不包含更新语句
# modifies sql data 表示函数体包含更新语句
# 不指明默认时contains sql
| sql security {definer | invoker}
# 设置执行权限。用于指定函数的执行许可
# definer表示该函数只能由创建者调用
# invoker 表示该函数可以被其他数据库用户调用
# 默认是definer
| comment 'string'
# 注释,引号内就是要添加的注释内容
例:
# 创建一个做减法的函数
delimiter //
create function sub(arg1 int, arg2 int)
returns int
DETERMINISTIC
begin
return arg1 - arg2;
end //
delimiter ;
# 调用自定义函数与调用MySQL中的系统函数的方法一样
select sub(3, 1);
+-----------+
| sub(3, 1) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
delimiter
MySQL使用delimiter来更改命令结束标记。默认的结束标记是“;”。
为什么要更改结束标记?
因为在函数和存储过程中包含了很多的“;”,比如上面的例子——sub函数中的语句“return arg1 - arg2;”就有“;”,如果不更改结束符,我们在命令行中编写sub函数,写到这句语句时我们回车换行,由于“;”是默认的结束标记,这时候MySQL就试着执行,因为我们并没有写完函数,执行就会报错;所以在创建函数或者存储过程的时候要先将结束符换成其他字符,等创建好了再换回来。
查看函数的定义
show function status; # 查看全部函数
show function status like 模式; # 模糊查询
show create function 函数名; # 精确查看
show function status; # 查出的内容太多,不方便
+-------+----------------------------------+----------+---------------------+---------------------+---------------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+----------------------------------+----------+---------------------+---------------------+---------------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| sys | extract_schema_from_file_name | FUNCTION | mysql.sys@localhost | 2021-07-27 13:37:12 | 2021-07-27 13:37:12 | INVOKER |
Description
-----------
...太多了
############################################################
show function status like 'su%'; # 模糊查询
+-------+------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| testt | sub | FUNCTION | root@localhost | 2021-10-21 17:24:39 | 2021-10-21 17:24:39 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+-------+------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
################################################
show create function sub;
+----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation |
+----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| sub | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `sub`(arg1 int, arg2 int) RETURNS int
DETERMINISTIC
begin
return arg1 - arg2;
end | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
函数的信息都保存在information_schema数据库中的routines表中。
select * from information_schema.routines where routine_name = 'sub';
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+-------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED | LAST_ALTERED | SQL_MODE | ROUTINE_COMMENT | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+-------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| sub | def | testt | sub | FUNCTION | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int | SQL | begin
return arg1 - arg2;
end | NULL | SQL | SQL | YES | CONTAINS SQL | NULL | DEFINER | 2021-10-21 17:24:39 | 2021-10-21 17:24:39 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+-------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
删除自定义函数
drop function func_name;
修改自定义函数
alter function func_name[characteristic ...];
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
# 解释在“创建自定义函数”
存储过程
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。 存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
- 优点
- 存储过程可封装,并隐藏复杂的商业逻辑。
- 存储过程可以回传值,并可以接受参数。
- 存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
- 存储过程可以用在数据检验,强制实行商业逻辑等。
- 缺点
- 存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
- 存储过程的性能调校与撰写,受限于各种数据库系统。
——菜鸟教程
创建存储过程
CREATE
[DEFINER = user]
PROCEDURE proc_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
characteristic: { # 同“创建自定义函数”
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
存储过程体,这部分以begin开始,end结束。当只有一个SQL语句时可省略begin...end
调用存储过程
CALL proc_name([parameter[,...]])
CALL proc_name[()]
例:
# 存在表
desc people;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| age | int | NO | | NULL | |
| sex | enum('f','m') | YES | | NULL | |
| sar | float | YES | | NULL | |
+-------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
# 创建一个通过id获取people的存储过程
delimiter //
create procedure get_people_by_id(in p_id int)
reads sql data
begin
select * from people where id = p_id;
end //
delimiter ;
# 使用存储过程
call get_people_by_id(3);
+----+--------+-----+------+--------+
| id | name | age | sex | sar |
+----+--------+-----+------+--------+
| 3 | 嘎子 | 18 | m | 5400.4 |
+----+--------+-----+------+--------+
1 row in set (0.00 sec)
删除存储过程
DROP PROCEDURE [IF EXISTS] proc_name;
修改存储过程
ALTER PROCEDURE proc_name [characteristic ...]
characteristic: { # 含义同“创建自定义函数”小节所讲
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
函数 VS 存储过程
- 共同特点
- 两者都可以重复使用,可以减少开发人员的工作量
- 两者都可以增强数据的安全访问控制,可以设定只有某些数据库用户才具有某些函数或存储过程的执行权限
- 不同之处
- 函数必须有且仅有一个返回值,且必须指定返回值为字符串或数值两个数据类型;存储过程可以有返回值,也可以没有或者有多个返回值,所以的返回值需要使用out或inout参数定义
- 函数体可以使用select…into语句为某个变量赋值,但不能使用select返回结果集。存储过程没有这方面的限制
- 函数可以直接嵌入SQL语句或者MySQL表达式中;存储过程一般需要单独调用,调用时需要关键字call
- 函数中的函数体限制比较多,例如函数体内不能使用以显式或隐式方式打开、开始或结束事务的语句;不能使用预处理SQL语句;存储过程的限制相对较少,基本上所有的SQL语句或者MySQL命令都可以在存储过程中使用
报错ERROR 1418 (HY000)
如果创建自定义函数或存储过程如果报这个错误,根据错误信息修改即可:
- 为函数添加 DETERMINISTIC, NO SQL, 或者 READS SQL DATA
- 设置变量log_bin_trust_function_creators = 1
我这里使用的就是方法1.
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)