Mysql 函数定义及批量数据脚本

零、说在前面

在定义函数之前需要先将 log_bin_trust_function_creators 值设为开启，原因如下

在主从复制的两台Mysql服务器中，slaver会从master复制数据，而一些操作，比如function所得到的结果可能在slaver和master中不同，可能存在安全隐患，因此，默认情况下该值为0 阻止function的创建。

有两种办法解决：

1.将log_bin_trust_function_creators设为ON 这样一来开启了log-bin的Mysql server变可以随意创建function。但这样会存在潜在的数据安全问题，除非明确知道该函数在主从上的行为一致。（另外，如果在master上创建函数想通过主从复制的方式将函数复制到slaver上则也需要开启slaver 的log_bin_trust_function_creators）

mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+

mysql> set global log_bin_trust_function_creators=1;

2.明确致命函数的类型。

DETERMINISTIC 不确定的
NO SQL 没有SQL语句，当然也不会修改数据
READS SQL DATA 只是读取数据

比如 CREATE DEFINER = 'username'@'%' READS SQL DATA FUNCTION 'function_name'(,,,,,,) RETURNS ....

这里我们先选用第一种方式，，因为我们只做一下批量数据导入，slaver不会用到这些函数，，，更重要的是也没slaver。。。

==============================================分割线=================================

还有一点点

mysql 的默认结束符为“；” (半角！，这里打全角是方便看)

DELIMITER $$ 命令会把结束符改为 $$

==============================================分割线=================================

一、自定义函数

创建函数

DELIMITER $$
CREATE FUNCTION 函数名称(参数列表)    // CREATE FUNCTION 'fun'(a int,b int)
RETURNS 返回值类型                   // RETURNS VARCHAR(255)
BEGIN
(函数体)
END $$
DELIMITER ;

查看函数

show function status; （可后面加函数名）--查看所有自定义函数
show create function (函数名) ;　 --查看创建函数语句

删除函数

drop function 函数名；

定义变量

DECLARE 变量1[,变量2...] 变量类型 [DEFAULT 默认值]

或者可以直接使用用户变量 @变量名

DECLARE a,b INT DEFAULT 0;
@ a;

变量赋值

SET 变量名1=值 [,变量名2=值];

SELECT INTO 变量名

:= 为第一个的简化版(:=为mysql中的赋值语句，因为=在默认环境下(想想sql语句里是不是‘=‘都是判断了)表示判断，所以 =必须配合 set 或者update使用）

1. SET x = 1 ; 
   SET y = fun();
=============================================
2. DECLARE x int DEFAULT 0;
   SELECT COUNT(id) FROM tdb_name INTO x;
=============================================
3. x:=1;
   y:=fun();

if语句

IF condition THEN ...;
[ELSEIF condition THEN ...;]
[ELSE ...;]
END IF

EXA:
IF age>20 THEN SET @count1=@count1+1;  
ELSEIF age=20 THEN SET @count2=@count2+1;  
ELSE SET @count3=@count3+1;  
END IF;

while 语句

WHILE condition DO 
...;
END WHILE

exa:
WHILE @count<100 DO 
SET @count=@count+1; 
END WHILE ;

实战啦~~

先随机产生随机整数DELIMITER $$

CREATE FUNCTION if exist rand_int(a INT,b INT) RETURNS INT
BEGIN
DECLARE ba INT(11) DEFAULT 0;
DECLARE rang INT(11) DEFAULT 0;
DECLARE ret INT(11) DEFAULT 0;
SET ba = a;
SET rang = b-a+1;
SET ret=FLOOR(base+RAND()*rang);      ----------FLOOR函数取整 返回小于等于x的最大整数
RETURN ret;                           ----------RAND 生成0-1随机数 不包括1
END $$
DELIMITER ;

随机产生字符串函数

DELIMITER $$
CREATE FUNCTION rand_str(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE str VARCHAR(255) DEFAULT '012345679abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE ret VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET ret = concat(ret,SUBSTRING(str,rand_int(1,62),1));
SET i = i + 1;
END WHILE;
RETURN ret;
END $$
DELIMITER ;

concat（a,b）拼接ab两个字符串

substring （a，b，c）取a的子串，从b开始取c长。（第一个字符位置是1）

二、自定义存储过程

存储过程与函数的区别。

函数只能通过return返回单个值或者表对象。而存储过程不执行return，通过out参数返回多个值(函数的参数只能是in类型)。
函数是可以内嵌在sql中使用的，在select中调用，但存储过程不行。
函数限制比较多，不能用临时表，只能用变量。
当对数据库进行复杂操作时，可以将复杂操作用一个存储过程封装起来，与事务结合使用，简化复杂语句。
存储过程中的crud会影响数据库，但函数不能。

创建存储过程

CREATE [definer = {user|current_user}] PROCEDURE 存储过程名 ( [IN|OUT|INOUT] 参数名数据类型.....)

BEGIN

.....

END

调用存储过程：

CALL 过程名(参数名) //没有参数的括号也可以省略

删除存储过程

DROP PROCEDURE 过程名

查看存储过程

1.SHOW PROCEDUER STATUS

2.SHOW CREATE PROCEDURE 过程名

测试表定义

=======================================================

A表有两个字段 id INT(primarykey autoincrement) name VARCHAR(not null default '')

B表有三个字段 id INT(primarykey autoincrement) name VARCHAR(not null default '') type INT(notnull default '')

type 字段关联A表id字段。

=======================================================

创建批量插入存储过程

A表的

DELIMITER $$
CREATE PROCEDURE MUTI_INSERT_A (IN insert_time INT)
BEGIN
DECLARE i INT DEFAULT 0;        ---这两行交换位置会报错。
SET AUTOCOMMIT = 0;             ---可能是要先声明变量。
WHILE i<insert_time D
INSERT INTO A (name) values(rand_str(8));
SET i = i+1;
END WHILE;
COMMIT;
SET AUTOCOMMIT = 1;
END $$
DELIMITER ;

　　B表

DELIMITER $$
CREATE PROCEDURE MUTI_INSERT_B (IN insert_time INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE a_id_max INT DEFAULT 0;
SET AUTOCOMMIT = 0;
SELECT MAX(id) INTO a_id_max FROM A;                                ---获取A表中的最大id 
WHILE i<insert_time DO
INSERT INTO B (name,type) values(rand_str(8),rand_int(0,a_id_max)); ---限制与A.id相关的type列最大值
SET i = i+1;
END WHILE;
COMMIT;
SET AUTOCOMMIT = 1;
END $$
DELIMITER ;

　　注意！！！此时 A表内没数据第7行会返回null 但在表约束里 type not null 故要先插入A表数据。

测试一下，先跑个百万数据。

........手残打成一千万了。。。把会话关了。。还好事务没提交.回滚了。。。先来个十万吧。。

mysql> call muti_insert_a(100000);
Query OK, 0 rows affected (57.52 sec)

以后还是不在自己电脑上玩这种骚操作了。。

然后删表又卡主了。。。

重新清空了一下，a表跑10k条，b表跑10w