mysql5.7版本 - 函数sql函数编写（减少sql代码量）

1.学习准备

数据库准备：
这里我们使用一个简单的、自己定义的数据表来 便于学习。下面代码在mysql工具中直接运行即可。

/*
SQLyog Community v13.1.6 (64 bit)
MySQL - 5.7.39-log : Database - data
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`data` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `data`;

/*Table structure for table `test` */

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `test` */

insert  into `test`(`id`,`name`,`age`) values 
(1,'whl',20),
(2,'zj',18),
(3,'wz',16),
(4,'ww',12),
(5,'zz',11);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

2.知道并了解mysql5.7版本的函数编写规范（格式）

首先看一下我的表目录

2.1 简单sql函数编写

DELIMITER $$   # 固定格式

USE `data`$$   # 使用data数据库 看我目录结构

DROP FUNCTION IF EXISTS `simpleFun`$$ # 删除该数据库中叫 simpleFun 的函数


# RETURNS VARCHAR(20) 定义返回的结果类型与大小

CREATE DEFINER=`root`@`%` FUNCTION `simpleFun`() RETURNS VARCHAR(20) CHARSET utf8 COLLATE utf8_bin # 创建函数 
    COMMENT '函数输出测试' # 注释
    BEGIN
    RETURN "Hello World!"; # 结果返回一个 Hello World!
    END$$

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.2 上述sql函数中DEFINER=root@%解释

1. MySQL的用户名都是username@hostname的形式

2. 授权用户的命令：
　　mysql >grant all privileges on *.* to root@"%" identified by "123" // 123是密码

　　mysql >flush privileges

以上命令授权root@%用户所有权限，如果root@%用户不存在，则自动创建该用户。也可以借助于Navigate工具设
置。

如果要使新建的帐户也具备建帐户的权限，则要这样：

mysql >grant all privileges on *.* to root@"%" identified by "123"  with grant option;


3. root@%的意思是允许在任何机器上以root用户访问数据库，也就是说只要数据库存在名为root@%的用户，则

root在任何机器上都可以访问数据库，否则，如果数据库不存在root@%用户，则只有被授权了用户名可以访问数

据库，被授权的用户可以通过Navigate工具查看。
4. 存储过程中有个DEFINER
(1) DEFINER的意思是“定义者”，也就是指明此存储过程有哪个用户定义的，它跟存储过程的使用权限无关；

可以说，存储和过程是没有使用限制的，任何人都可以使用存储过程，那么为什么还要加上DEFINER指定存储过

程的定义者呢，因为，存储过程虽然不限制任何人使用，但是它本身的行为必须受到权限限制，也就是存储过程

本身内部具备哪些对数据库的访问权限，而这访问权限便是DEFINER用户对数据库的访问权限。
(2) DEFINER被定义为一个普通的username@hostname用户就不必说了，如果定义为root@%，有什么特别的吗？没

有。只不过数据库中要存在root@%用户，否则创建的时候没事，调用的时候就提示root@% is not registered，

也就是用户不存在。
(3) DEFINER也可以省略掉，这样存储过程默认的定义者是root@localhost，而一般数据库在安装的时候都会有

一个root@localhost用户，所以，该存储过程也能正常在不同的机器上被使用。
5. 既然上述存储过程任何人都以访问，那么它本身运行时的访问权限怎么限制呢。可以通过SQL SECURITY 

INVOKER ，也就是，其本身的访问权限由调用者权限设置。
6.命令分隔符DELIMITER
MySQL默认是以分号作为两个命令的分割点的。但有些命令块中包含分号，比如存储过程或触发器的定义中。为了使MySQL不把命令块中的分号误认做命令的分割点，需要在执行这种命令块前临时改一下命令分隔符。尤其注意，SQLYog里面写存储过程的时候，必须自行定义命令分隔符。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

2.3 上述sql函数中CHARSET utf8 COLLATE utf8_bin解释

背景：为了清晰的说明这个问题，说明一下用例背景：此用例想修改某张表的备注，但是其他的数据表都有collate = utf8_bin操作，但是这张表没有，因此研究了一下最终得到以下结论：
问题：collate = utf8_bin具体表示什么？
用例：alter table carrier_master comment '承运商信息维护表' collate = utf8_bin;
结论：
    collate = utf8_bin表示以二进制的形式存储输入的每个字符，因此会导致数据会区分大小写(collate有核对的意思)大小写敏感。
    （另外一种）
    utf-8_general_ci表示一般性比较，不会有大小写的区分(case insensitive)，大小写不敏感。
    utf-8_general_cs也是一种，它区分大小写(case sensitive),既大小写敏感。
1
2
3
4
5
6
7
8

3.mysql 5.7 一些简单的函数简写

创建随机数字生成

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `random_num`( ) RETURNS INT(5)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END$$
DELIMITER ;
1
2
3
4
5
6
7
8

生成随机字符串

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `random_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END$$
DELIMITER ;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

生成随机数字，返回varchar类型数据组合，例如手机号

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `random_string_phone`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT '1234567890';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*10),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END$$
DELIMITER ;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

创建用户表myisam引擎

CREATE TABLE `sys_user_myisam` (
  `user_id` bigint(100) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `salt` varchar(100) DEFAULT NULL COMMENT '盐',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `mobile` varchar(100) DEFAULT NULL COMMENT '手机号',
  `status` int(1) DEFAULT '1' COMMENT '状态  0：禁用   1：正常',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 COMMENT='系统用户MyISAM';


1
2
3
4
5
6
7
8
9
10
11
12

创建存储过程生成数据

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `insert_sys_user_myisam`(IN START INT(10),IN max_num INT(10))
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO sys_user_myisam (user_id,username,password,salt,email,mobile,status) VALUES (START+i,random_string(10),random_string(6),random_string(10),random_string(20),random_string_phone(12),1);  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$
DELIMITER ;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

创建表innodb引擎

create table sys_user_innodb ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 as select * from sys_user_myisam;

1
2

4.实战测试（test表）

4.1 写一个将test表中的所有人的age + 2的函数

函数：

DELIMITER $$

USE `data`$$

DROP FUNCTION IF EXISTS `add_age`$$

CREATE DEFINER=`root`@`%` FUNCTION `add_age`(ages INT(20)) RETURNS INT(20)
   BEGIN
    DECLARE age INT(20);
    SET age = ages + 2;
    RETURN age;
   END$$

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

测试：

# 写一个将test表中的所有人的age + 2的函数
SELECT id,NAME,add_age(age)FROM test
1
2

4.2 写一个只返回name中有w的用户信息

函数：

DELIMITER $$

USE `data`$$

DROP FUNCTION IF EXISTS `find_name_by_w`$$

CREATE DEFINER=`root`@`%` FUNCTION `find_name_by_w`(namew VARCHAR(20)) RETURNS VARCHAR(20)
   BEGIN
    DECLARE namesw VARCHAR(20);
    IF(LOCATE('w',namew) > 0) THEN
     SET namesw = namew;
    ELSE 
     SET namesw = '';
    END IF;      # 注意这里需要使用 end if；进行结尾
    RETURN namesw;
   END$$

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

测试：

# 写一个只返回name中有w的用户信息

SELECT id,find_name_by_w(NAME) AS NAME,age 
FROM test
GROUP BY find_name_by_w(NAME)
HAVING find_name_by_w(NAME) != ''
1
2
3
4
5
6

5.知识扩展（sql中的常用函数的使用）

mysql函数大全学习地址

1. LOCATE(s1,s) 从字符串 s 中获取 s1 的开始位置
2. LEFT(s,n) 返回字符串 s 的前 n 个字符
3. LTRIM(s) 去掉字符串 s 开始处的空格
4. DATE_ADD(d，INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期，type 值可以是：
5. SUBSTRING_INDEX(s, delimiter, number) 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
6. CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串

详细：请看上方链接