mysql的触发器(借助OLD, NEW) 、存储过程、方法定义

注: 
check_rawrecord_device, check_rawrecord_location, check_rawdata_device, check_rawdata_location, check_data表有外键record_id, 来自check_record表主键
创建触发器, 当设置check_record.is_valid = 0 时(删除), 删除挂在check_record表上的子表数据
调用删除check_record某条数据的方法(带有事务注解的)时, 假设事务啥的正常, 若出现/ by zero等异常时, 能够回滚. 
1.下面是创建触发器代码, 需要以 DELIMITER 'x' 开始, 以'x'结束(x表示大部分字符)
DELIMITER |
CREATE TRIGGER check_record_del_trigger 
AFTER UPDATE ON check_record FOR EACH ROW
BEGIN
	IF NEW.is_valid=0
	THEN
		IF NEW.template_id=1 OR NEW.template_id=2 -- 不同条件判断
		THEN
			UPDATE check_rawrecord_location SET is_valid = 0 WHERE record_id = OLD.id;
			UPDATE check_rawdata_location SET is_valid = 0 WHERE record_id = OLD.id;
			UPDATE check_data SET is_valid = 0 WHERE record_id = OLD.id;
		ELSEIF NEW.template_id=12
		THEN
			UPDATE check_rawrecord_device SET is_valid = 0 WHERE record_id = OLD.id;
			UPDATE check_rawdata_location SET is_valid = 0 WHERE record_id = OLD.id;
			UPDATE check_data SET is_valid = 0 WHERE record_id = OLD.id;
		ELSE 
			UPDATE check_rawrecord_device SET is_valid = 0 WHERE record_id = OLD.id;
			UPDATE check_rawdata_device SET is_valid = 0 WHERE record_id = OLD.id;
			UPDATE check_data SET is_valid = 0 WHERE record_id = OLD.id;
		END IF;
	END IF;
END
| 

-- 2.创建存储过程, 插入100w条数据 mysql
DELIMITER |
CREATE PROCEDURE check_record_add_1w_PROCEDURE()
BEGIN
	DECLARE num INT;
	SET num = 0;
	WHILE num < 1000 DO
		SET num = num + 1;
		INSERT INTO check_record(`name`, `group_id`) VALUES(CONCAT("name",num), num);
	END WHILE ;
END
| 
-- 删除和执行存储过程
DROP PROCEDURE check_record_add_1w_PROCEDURE;
CALL check_record_add_1w_PROCEDURE();

-- 3.SQL方法的定义function
-- 一下定义了一个方法：将"12,13,14,15,16"类似字段添加前缀"xxx"
DELIMITER |
 CREATE DEFINER=`root`@`%` FUNCTION `strSplitPadding`(`str` VARCHAR(255), `split` VARCHAR(255), `pad` VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
    COMMENT '字符串切割，并统一加前缀'
BEGIN
DECLARE result VARCHAR(255) DEFAULT '';
DECLARE tmp VARCHAR(255) DEFAULT '';
DECLARE count int DEFAULT 1;

-- 获取分割符数量 char_length不管汉字字母数字都按一个字符统计
SELECT CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, ',', '')) + 1 INTO count;

SET @i = 1;
-- 根据分割符遍历
WHILE @i <= count DO
-- 获取切割遍历字符串 
SET tmp = SUBSTRING_INDEX(SUBSTRING_INDEX(str,',',@i),',',-1);
-- 添加是否已经添加前缀的判断，可适当依据情况修改
IF length(tmp)<6
THEN
    -- 获取数字对应字母，进行拼接
	SET result = CONCAT(result, pad, tmp, split);
ELSE
	SET result = CONCAT(result, tmp, split);
END IF;
	SET @i = @i + 1;
END WHILE;
RETURN SUBSTRING_INDEX(result,',',count);
END;
|
-- 调用方法STRSPLITPADDING，更新某一个业务数据字段，添加前缀"10"
UPDATE student
SET fileids = STRSPLITPADDING(fileids, ',', '10')
WHERE LENGTH(fileids) > 0;
-- SUBSTRING_INDEX函数使用
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5', ',', 3), ',', -1); -- 3
SELECT SUBSTRING_INDEX('1,2,3,4,5,', ',', 5); -- 1,2,3,4,5
SELECT SUBSTRING_INDEX('1,2,3,4,5', ',', -1); -- 5
SELECT SUBSTRING_INDEX('1,2,3,4,5,', ',', -1); -- ''


-- 以下内容非原创引用网为：https://www.cnblogs.com/rinack/p/6555483.html
-- 创建方法fristPinyin(name)：获取字段name的首字母并返回(eg：'重'：Z)
DELIMITER |
CREATE FUNCTION `fristPinyin`(P_NAME VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
    DECLARE V_RETURN VARCHAR(255);
    SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING gbk),1)),16,10), 
        0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7, 
        0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
        0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),    
    'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
    RETURN V_RETURN;
END
|

-- 创建方法pinyin(name)：获取字段name的各中文字的首字母并返回(eg：'重庆'：ZQ)
DELIMITER |
CREATE FUNCTION `pinyin`(P_NAME VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
    DECLARE V_COMPARE VARCHAR(255);
    DECLARE V_RETURN VARCHAR(255);
    DECLARE I INT;
    SET I = 1;
    SET V_RETURN = '';
    WHILE I < LENGTH(P_NAME) DO
        SET V_COMPARE = SUBSTR(P_NAME, I, 1);
        IF (V_COMPARE != '') THEN
            #SET V_RETURN = CONCAT(V_RETURN, ',', V_COMPARE);
            SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));
            #SET V_RETURN = fristPinyin(V_COMPARE);
        END IF;
        SET I = I + 1;
    END WHILE;
    IF (ISNULL(V_RETURN) OR V_RETURN = '') THEN
        SET V_RETURN = P_NAME;
    END IF;
    RETURN V_RETURN;
END |

-- 去除字段内容中的HTML标签函数
-- 摘自https://blog.csdn.net/qq_27811247/article/details/81001854
SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty text(0) )
RETURNS text(0)
DETERMINISTIC 
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END;
|
DELIMITER ;