1.navicat新建存储过程
选择 过程
增加输入输出参数(若是无参,直接点击完成)
完成后代码如下:
CREATE DEFINER = CURRENT_USER PROCEDURE `kxy_prco2`()
BEGIN
#Routine body goes here...
END;
2.添加代码
2.1 无参
CREATE DEFINER=`root`@`%` PROCEDURE `kxy_proc2`() BEGIN #Routine body goes here... UPDATE v5_service_type t set t.lastmodifyuser='kxy2' where t.service_type_code='01'; END
调用
call kxy_proc2();
2.2 带参
CREATE DEFINER=`root`@`%` PROCEDURE `kxy_proc`(in typecode VARCHAR(20),out typename VARCHAR(20)) BEGIN #Routine body goes here... SELECT t.service_type_name into typename from v5_service_type t WHERE t.service_type_code=typecode; END
调用
set @typecode='01'; set @typename=''; call kxy_proc(@typecode,@typename); SELECT @typename;
2.函数
创建函数的时候,可能出现错误:
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)
解决方法:
set global log_bin_trust_function_creators=TRUE;
例子:
CREATE DEFINER=`kxy`@`%` FUNCTION `GetUserNameById`(`id` int) RETURNS varchar(255) BEGIN #Routine body goes here... declare strname varchar(255); select u.username into strname from tb_user u where u.id=id; return strname; END
3.触发器
navicat新建触发器:
表格右键->设计表->触发器
例子:
代码:
create trigger User_TRIGGER after update on tb_user
FOR EACH ROW
begin
insert into tb_xt (user,logintime)VALUES(NEW.username,now());
end;
4.Docker Mysql
docker pull mysql:5.7 # 拉取 mysql 5.7 docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
如果遇到容器时间和系统时间不一致
容器内部执行:
cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
重启容器