mysql进阶01

mysql使用函数

• 命令开启：允许创建函数设置：

  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 //
  DELIMITER ;

  #假如要删除
  #drop function rand_string;

-- 创建存储过程

    #创建往stu表中插入数据的存储过程
  DELIMITER //
  CREATE PROCEDURE insert_stu(  START INT , max_num INT )
  BEGIN 
    DECLARE i INT DEFAULT 0; 
    SET autocommit = 0;   #设置手动提交事务
    REPEAT  #循环
    SET i = i + 1;  #赋值
    INSERT INTO student (stuno, name ,age ,classId ) VALUES
    ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); 
    UNTIL i = max_num 
    END REPEAT; 
    COMMIT;  #提交事务
  END //
  DELIMITER ;

  #假如要删除
  #drop PROCEDURE insert_stu;

-- 调用存储过程

  CALL insert_class(10000);

-- 删除某个表上的索引

  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=1来控制游标的结束
   DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;   
#若没有数据返回,程序继续,并将变量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 //
DELIMITER ;

CALL proc_drop_index("dbname","tablename");

-- 不适用查询缓存 （SQL_NO_CACHE ）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;