mysql 函数创建

创建表

CREATE TABLE `dept` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `deptName` VARCHAR(30) DEFAULT NULL,
  `address` VARCHAR(40) DEFAULT NULL,
  ceo INT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `empno` INT NOT NULL ,
  `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
  `deptId` INT(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建函数

-- 查看mysql是否允许创建函数：
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
-- 命令开启：允许创建函数设置：（global-所有session都生效）
SET GLOBAL log_bin_trust_function_creators=1; 

-- 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
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 $$

-- 假如要删除
-- drop function rand_string;

-- 用于随机产生区间数字
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;
END$$

-- 假如要删除
-- drop function rand_num;

创建存储过程

-- 插入员工数据
DELIMITER $$
CREATE PROCEDURE  insert_emp(START INT, max_num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	#set autocommit =0 把autocommit设置成0
	SET autocommit = 0;
	REPEAT
		SET i = i + 1;
		INSERT INTO emp (empno, NAME, age, deptid ,create_time) VALUES ((START+i) ,rand_string(6), rand_num(30,50), rand_num(1,10000),DATE_ADD(now(), INTERVAL i SECOND));
		UNTIL i = max_num
	END REPEAT;
	COMMIT;
END$$

-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_emp;

-- 插入部门数据
DELIMITER $$
CREATE PROCEDURE insert_dept(max_num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
		SET i = i + 1;
		INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
		UNTIL i = max_num
	END REPEAT;
	COMMIT;
END$$

-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_dept;

调用存储过程

-- 执行存储过程，往dept表添加1万条数据
CALL insert_dept(10000);

-- 执行存储过程，往emp表添加50万条数据，编号从100000开始
CALL insert_emp(100000,500000);

批量删除表索引

-- 批量删除某个表上的所有索引
DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE ct INT DEFAULT 0;
	DECLARE _index VARCHAR(200) DEFAULT '';
	DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY'  ;
	DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
	OPEN _cur;
		FETCH _cur INTO _index;
		WHILE  _index<>'' DO
			SET @str = CONCAT("drop index ",_index," on ",tablename );
			PREPARE sql_str FROM @str ;
			EXECUTE sql_str;
			DEALLOCATE PREPARE sql_str;
			SET _index='';
			FETCH _cur INTO _index;
		END WHILE;
	CLOSE _cur;
END$$

-- 执行批量删除：dbname 库名称, tablename 表名称
CALL proc_drop_index("dbname","tablename");