这篇文章上次修改于 1249 天前，可能其部分内容已经发生变化，如有疑问可询问作者。
###################################################################################
###################################################################################
###################################################################################
###################################################################################
###################################################################################
###################################################################################
TRUNCATE TABLE `PlanTab`;
INSERT INTO `PlanTab` VALUES ('1', "call proc_impl_YX_failure_or_success('517&518&520&521^511');", '5', '0', '1');
INSERT INTO `PlanTab` VALUES ('2', 'call procedure_test();', '3', '0', '1');
###################################################################################
DROP PROCEDURE IF EXISTS procedure_plan;
delimiter //
CREATE PROCEDURE procedure_plan()
BEGIN
DECLARE v_id INT DEFAULT 1; # ID
DECLARE v_cur INT DEFAULT 0; # 默认起始计数0，从表中读取
DECLARE v_step INT DEFAULT 1; # 默认计数每次增加1分钟，过程内置
DECLARE v_preset INT DEFAULT 5; # 默认执行周期每5分钟，从表中读取
DECLARE data_end INT DEFAULT 0;
DECLARE data_cur CURSOR FOR SELECT `ID`,PresetCount,CurrentCount FROM PlanTab;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET data_end=1;
open data_cur;
REPEAT
FETCH data_cur INTO v_id, v_preset, v_cur;
IF data_end <> 1 THEN
SET v_cur = v_cur + v_step;
IF v_cur > v_preset THEN
SET v_cur = FLOOR(MOD(v_cur, v_preset) - 1);
END IF;
UPDATE PlanTab SET CurrentCount = v_cur WHERE `ID` = v_id;
END IF;
UNTIL data_end END REPEAT;
-- select v_begin, v_end, v_value, v_total;
END //
delimiter ;
call procedure_plan();
###################################################################################
DROP PROCEDURE IF EXISTS procedure_init;
delimiter //
CREATE PROCEDURE procedure_init()
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp_message_tab;
CREATE TEMPORARY TABLE temp_message_tab (
`id` INTEGER PRIMARY KEY NOT NULL,
`totab` VARCHAR(16) NOT NULL,
`policy` VARCHAR(64) NOT NULL,
`inners` INTEGER DEFAULT NULL,
`outers` INTEGER DEFAULT NULL,
`vendor` INTEGER DEFAULT NULL,
`message` VARCHAR(64) character set utf8 DEFAULT '' ;
) ENGINE=MEMORY ;
INSERT INTO temp_message_tab VALUES ('11', 'YXDataTab', '', '511', '11', '3', '遥信变位');
INSERT INTO temp_message_tab VALUES ('12', 'YXDataTab', '', '512', '12', '3', '遥信抖动');
INSERT INTO temp_message_tab VALUES ('13', 'YXDataTab', '', '513', '13', '3', '遥信质量码');
INSERT INTO temp_message_tab VALUES ('14', 'YXDataTab', '', '514', '14', '3', 'SOE发生');
INSERT INTO temp_message_tab VALUES ('15', 'YXDataTab', '', '515', '15', '3', 'SOE抖动');
INSERT INTO temp_message_tab VALUES ('16', 'YXDataTab', '', '516', '16', '3', 'SOE延迟传输');
INSERT INTO temp_message_tab VALUES ('17', 'YKDataTab', '', '517', '17', '3', '遥控选择');
INSERT INTO temp_message_tab VALUES ('18', 'YKDataTab', '', '518', '18', '3', '遥控选择成功');
INSERT INTO temp_message_tab VALUES ('19', 'YKDataTab', '', '519', '19', '3', '遥控选择失败');
INSERT INTO temp_message_tab VALUES ('20', 'YKDataTab', '', '520', '20', '3', '遥控执行');
INSERT INTO temp_message_tab VALUES ('21', 'YKDataTab', '', '521', '21', '3', '遥控执行成功');
INSERT INTO temp_message_tab VALUES ('22', 'YKDataTab', '', '522', '22', '3', '遥控执行失败');
INSERT INTO temp_message_tab VALUES ('23', 'YKDataTab', '', '523', '23', '3', '遥控取消');
INSERT INTO temp_message_tab VALUES ('24', 'YKDataTab', '', '524', '24', '3', '遥控取消成功');
INSERT INTO temp_message_tab VALUES ('25', 'YKDataTab', '', '525', '25', '3', '遥控取消失败');
INSERT INTO temp_message_tab VALUES ('35', 'YCDataTab', '', '535', '35', '3', '遥测变化');
INSERT INTO temp_message_tab VALUES ('36', 'YCDataTab', '', '536', '36', '3', '遥测上送');
INSERT INTO temp_message_tab VALUES ('37', 'YCDataTab', '', '537', '37', '3', '遥测长时间未变化');
INSERT INTO temp_message_tab VALUES ('38', 'YCDataTab', '', '538', '38', '3', '遥测质量码');
INSERT INTO temp_message_tab VALUES ('39', 'YCDataTab', '', '539', '39', '3', '遥测跳变');
-- SELECT * FROM temp_message_tab;
DROP TEMPORARY TABLE IF EXISTS temporary_logic;
CREATE TEMPORARY TABLE temporary_logic (
`id` INTEGER PRIMARY KEY NOT NULL,
`msgtype` INTEGER NOT NULL,
`msgname` VARCHAR(64) NOT NULL,
`msgidx` INTEGER NOT NULL,
`record` INTEGER NOT NULL,
`status` INTEGER NOT NULL,
`analys` INTEGER NOT NULL
) ENGINE=MEMORY ;
END //
delimiter ;
call procedure_init();
###################################################################################
###################################################################################
###################################################################################
DELIMITER //
DROP FUNCTION IF EXISTS setDotRelated;
CREATE FUNCTION setDotRelated() RETURNS INT
BEGIN
DECLARE v_begin INT DEFAULT 0;
DECLARE v_end INT DEFAULT 0;
DECLARE v_value VARCHAR(32767) DEFAULT '';
DECLARE v_title VARCHAR(32767) DEFAULT '';
DECLARE v_total VARCHAR(32767) DEFAULT '';
DECLARE v_msgidx INT DEFAULT 0;
DECLARE v_record INT DEFAULT 0;
DECLARE v_status INT DEFAULT 0;
DECLARE v_analys INT DEFAULT 0;
DECLARE flag_end INT DEFAULT 0;
DECLARE data_cur CURSOR FOR SELECT `msgidx`,`record`,`status`,`analys` FROM `temporary_logic` WHERE `msgidx` > 0 AND `id` < 5;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag_end=1;
open data_cur;
SET v_begin = 1;
REPEAT
FETCH data_cur INTO v_msgidx, v_record, v_status, v_analys;
IF flag_end <> 1 THEN
set v_end = v_end+1;
-- PREPARE stmt FROM @proc_call_name;
-- EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;
SELECT `ReportTime`,`TextValue` INTO v_title, v_value FROM `temp_term_tab` WHERE `ID` = v_msgidx;
set v_total = TRIM(LEADING '\n' FROM CONCAT_WS('\n',v_total, CONCAT(v_title,' ',v_value)));
INSERT INTO `YKDataTab`
(SELECT
'0' AS `ID`,
`IpAddress`,`Port`, `DestinationIpAddress`, `DestinationPort`,`ReportTime`,
CURRENT_TIMESTAMP AS `StorageTime`,
`Ypoint` AS `YkPointIndex`,
`Ydesc` AS `YkPointNumberDescription`,
`MessageType`,
`Ydot` AS `Dot`,
`Yval` AS `DataValue`,
v_record AS `YKConclusionRecord`,
v_status AS `YKConclusionStatus`,
v_analys AS `YKConclusionIndex`,
v_total AS `ContentOfProof`
FROM `temp_term_tab` WHERE `ID` = v_msgidx);
DELETE FROM `BaseDataTab` WHERE `ID` = v_msgidx AND 'HwMsas1000' IN ( SELECT DATABASE() );
END IF;
UNTIL flag_end END REPEAT;
-- SELECT v_begin, v_end, v_value, v_total;
RETURN 4;
END //
DELIMITER ;
SELECT setDotRelated();
###################################################################################
DROP PROCEDURE IF EXISTS procedure_each;
DELIMITER //
CREATE PROCEDURE procedure_each( IN p_logic TINYTEXT, IN p_table TINYTEXT)
BEGIN
DECLARE v_begin INT DEFAULT 0;
DECLARE v_end INT DEFAULT 0;
DECLARE v_value VARCHAR(32767) character set utf8 DEFAULT '' ;
DECLARE v_total VARCHAR(32767) character set utf8 DEFAULT '' ;
DECLARE v_i int DEFAULT 0;
DECLARE v_len int DEFAULT 0;
DECLARE v_id int DEFAULT 0;
DECLARE v_num int DEFAULT 0;
DECLARE v_msgidx int DEFAULT 0;
DECLARE v_msgtype int DEFAULT 0;
DECLARE v_msgtext VARCHAR(64) character set utf8 DEFAULT '' ;
DECLARE flag_end int DEFAULT 0;
DECLARE data_cur CURSOR FOR SELECT GROUP_CONCAT(CONCAT_WS(',', `ID`,`MessageType`, SUBSTRING_INDEX(SUBSTRING_INDEX(`TextValue`, '-', -2), '-', 1)) SEPARATOR ';')
FROM `temp_term_tab` WHERE `MessageType` IN ('517', '518', '520', '521', '511') GROUP BY `YDot` ORDER BY `ReportTime`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag_end=1;
-- SELECT * FROM `temp_term_tab` WHERE `MessageType` GROUP BY `YDot` ORDER BY `ReportTime`;
-- SELECT `YDot`,`Yindex`,GROUP_CONCAT(CONCAT_WS(',', `ID`,`MessageType`, SUBSTRING_INDEX(SUBSTRING_INDEX(`TextValue`, '-', -2), '-', 1)) SEPARATOR ';')
-- FROM `temp_term_tab` WHERE `MessageType` IN ('17', '18', '20', '21', '11') GROUP BY `YDot` ORDER BY `ReportTime`;
DROP TEMPORARY TABLE IF EXISTS temporary_logic;
CREATE TEMPORARY TABLE temporary_logic (
`id` INTEGER PRIMARY KEY NOT NULL,
`msgtype` INTEGER NOT NULL,
`msgname` VARCHAR(64) NOT NULL,
`msgidx` INTEGER NOT NULL,
`record` INTEGER NOT NULL,
`status` INTEGER NOT NULL,
`analys` INTEGER NOT NULL
) ENGINE=MEMORY ;
INSERT INTO `temporary_logic` VALUES
('1', '517', '遥控选择' , 0, 1, 2, 3),
('2', '518', '遥控选择成功' , 0, 1, 2, 3),
('3', '520', '遥控执行' , 0, 1, 2, 3),
('4', '521', '遥控执行成功' , 0, 2, 2, 3),
('5', '511', '遥信变位' , 0, 1, 2, 3);
-- SELECT * FROM `temporary_logic`;
open data_cur;
set v_begin = 1;
REPEAT
FETCH data_cur INTO v_total;
IF flag_end != 1 THEN
set v_end = v_end + 1;
set v_len = LENGTH(v_total) - LENGTH(REPLACE(v_total,';',''))+1;
set v_i = 0;
IF v_len >= 4 THEN
UPDATE `temporary_logic` SET `msgidx` = 0;
END IF;
WHILE v_len >= 4 AND v_i < v_len DO
set v_i = v_i+1;
set v_value = SUBSTRING_INDEX(SUBSTRING_INDEX(v_total, ';', v_i), ';', -1);
set v_msgidx = SUBSTRING_INDEX(v_value, ',', 1);
set v_msgtype = SUBSTRING_INDEX(SUBSTRING_INDEX(v_value, ',', 2), ',', -1);
set v_msgtext = SUBSTRING_INDEX(v_value, ',', -1);
SELECT `id` INTO v_id FROM `temporary_logic` WHERE `msgtype` = v_msgtype;
-- SET: FIRST MSG OR LAST MSG NOT NULL SHOUL UPDATE CURRENT MSG INDEX
IF v_id = 1 THEN
UPDATE `temporary_logic` SET `msgidx` = v_msgidx WHERE `msgtype` = v_msgtype;
ELSE
SELECT `msgidx` INTO v_num FROM `temporary_logic` WHERE `id` IN (SELECT v_id - 1);
-- LAST MSG GOT IT OR PREVIEW MSG IS NONE, SHOULD CLENN ALL
IF v_id = 5 OR v_num = 0 THEN
UPDATE `temporary_logic` SET `msgidx` = 0;
ELSE
UPDATE `temporary_logic` SET `msgidx` = v_msgidx WHERE `msgtype` = v_msgtype;
END IF;
-- SOME DATA OR NODATA ---> CLEAN, RECV YK SELECT, OR REACH END
END IF;
-- CHECK IF RECV FIRST MSG(PROMISE FULL AND WITHOUT YXBW) OR REACH END
IF v_id = 1 OR v_i = v_len THEN
SELECT COUNT(*) INTO v_num FROM `temporary_logic` WHERE `msgidx` > 0 AND `id` != 5;
IF v_num = 4 THEN
-- FULL DATA ---> STORE , RECV YK SELECT, OR REACH END
UPDATE `temporary_logic` SET `msgname` = 999 WHERE `msgtype` = v_msgtype;
-- SELECT v_begin, v_end, v_value, v_total, v_msgidx, v_id, v_msgtype;
SELECT * FROM `temporary_logic`;
-- SELECT * FROM `temp_term_tab`;
SELECT setDotRelated() INTO v_num;
-- SELECT v_begin, v_end, v_value, v_total, v_id, v_msgidx, v_msgtype, v_msgtext;
END IF;
END IF;
END WHILE;
END IF;
UNTIL flag_end END REPEAT;
close data_cur;
-- SELECT v_begin, v_end, v_value, v_total;
END //
DELIMITER ;
call procedure_each('517&518&520&521^511', 'temp_table_term');
###################################################################################
DROP PROCEDURE IF EXISTS procedure_term;
DELIMITER //
CREATE PROCEDURE procedure_term( IN p_logic TINYTEXT, IN p_termid INT )
BEGIN
DECLARE v_begin INT DEFAULT 0;
DECLARE v_end INT DEFAULT 0;
DECLARE v_value TINYTEXT DEFAULT '';
DECLARE v_total TINYTEXT DEFAULT '';
DECLARE v_i INT DEFAULT 0;
DECLARE v_len INT DEFAULT 0;
DECLARE v_delim TINYTEXT DEFAULT '|';
DECLARE v_logic TINYTEXT DEFAULT '';
set v_len = LENGTH(p_logic) - LENGTH(REPLACE(p_logic,v_delim,''))+1;
set v_i = 0;
WHILE v_i < v_len do
set v_i = v_i+1;
set v_logic = replace(substring(substring_index(p_logic, v_delim, v_i),
length(substring_index(p_logic, v_delim, v_i -1)) + 1), v_delim, '');
set v_total = CONCAT(v_total, ' ', v_logic);
call procedure_each(v_logic, 'temp_term_tab');
END WHILE;
-- SELECT v_begin, v_end, v_value, v_total;
END //
DELIMITER ;
call procedure_term('517&518&520&521^511', '20511');
###################################################################################
DELIMITER //
DROP FUNCTION IF EXISTS genDotRelated;
CREATE FUNCTION genDotRelated(msgType INT,dot INT, termid INT, flag VARCHAR(16)) RETURNS VARCHAR(64)
BEGIN
DECLARE v_result VARCHAR(64) DEFAULT '';
DECLARE v_decideTab VARCHAR(64) DEFAULT '';
DECLARE v_dotAbsAddr INT DEFAULT 0;
DECLARE v_pointIdx INT DEFAULT 0;
DECLARE v_pointDesc VARCHAR(64) DEFAULT '';
DECLARE has_error int DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;
IF ISNULL(msgType) || LENGTH(trim(msgType))<1 OR ISNULL(dot) || LENGTH(trim(dot))<1 OR ISNULL(termid) || LENGTH(trim(termid))<1 THEN
RETURN v_result;
END IF;
IF 11 <= msgType AND msgType <= 16 THEN
set v_decideTab = 'YXDataTab';
set v_dotAbsAddr = 0x1 + dot;
-- SELECT IFNULL(`InternalPointIndex`, 0),IFNULL(`DotDescription`, ' ') INTO v_pointIdx,v_pointDesc FROM `YXPointDataTab`
-- WHERE `TerminalIndex` = termid AND `DotAbsoluteAddress` = v_dotAbsAddr;
ELSEIF 17 <= msgType AND msgType <= 25 THEN
set v_decideTab = 'YKDataTab';
set v_dotAbsAddr = 0x4001 + dot;
-- SELECT `InternalPointIndex`,`DotDescription` INTO v_pointIdx,v_pointDesc FROM `YKPointDataTab`
-- WHERE `TerminalIndex` = termid AND `DotAbsoluteAddress` = v_dotAbsAddr;
ELSEIF 35 <= msgType AND msgType <= 39 THEN
set v_decideTab = 'YCDataTab';
set v_dotAbsAddr = 0x6001 + dot;
-- SELECT `InternalPointIndex`,`DotDescription` INTO v_pointIdx,v_pointDesc FROM `YCPointDataTab`
-- WHERE `TerminalIndex` = termid AND `DotAbsoluteAddress` = v_dotAbsAddr;
END IF;
SELECT CASE
WHEN flag = 'TYPE' THEN v_decideTab
WHEN flag = 'POINT' THEN v_pointIdx
WHEN flag = 'DESC' THEN v_pointDesc
ELSE NULL
END AS ret INTO v_result;
RETURN v_result;
END //
DELIMITER ;
SELECT genDotRelated(17,3,20511, 'TYPE');
###################################################################################
DROP PROCEDURE IF EXISTS procedure_work;
delimiter //
CREATE PROCEDURE procedure_work(IN p_logic VARCHAR(64) )
BEGIN
DECLARE v_begin INT DEFAULT 0;
DECLARE v_end INT DEFAULT 0;
DECLARE v_value TINYTEXT DEFAULT '';
DECLARE v_total TINYTEXT DEFAULT '';
DECLARE v_num INT DEFAULT 0;
DECLARE v_termid INT DEFAULT '';
DECLARE v_ip TINYTEXT DEFAULT '';
DECLARE v_port INT DEFAULT 0;
DECLARE flag_end int DEFAULT 0;
DECLARE data_cur CURSOR FOR SELECT `TerminalIndex`,`IpAddress`,`Port` FROM TerTab;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag_end=1;
open data_cur;
set v_begin = 1;
REPEAT
FETCH data_cur INTO v_termid, v_ip, v_port;
SELECT COUNT(*) INTO v_num FROM `BaseDataTab` WHERE `IpAddress` = v_ip AND `Port` = v_port AND `DataType` = 3;
IF flag_end != 1 AND v_num > 0 THEN
set v_end = v_end + 1;
-- SET @v_termid=20511; -- test v_termid
-- SET @p_ip='192.168.10.15'; -- test p_ip
-- SET @p_port='2404'; -- test p_port
DROP TEMPORARY TABLE IF EXISTS `temp_term_tab`;
CREATE TEMPORARY TABLE `temp_term_tab`
SELECT `ID`,`IpAddress`,`Port`,`DestinationIpAddress`,`DestinationPort`,`ReportTime`,`MessageType`,`TextValue`,
'' AS `Yflag`,
-- CASE
-- WHEN 11 <= `MessageType` AND `MessageType` <= 16 THEN 'YXDataTab'
-- WHEN 17 <= `MessageType` AND `MessageType` <= 25 THEN 'YKDataTab'
-- WHEN 35 <= `MessageType` AND `MessageType` <= 39 THEN 'YCDataTab'
-- ELSE NULL
-- END AS `YType`, '' AS `Ypoint`, '' AS `Ydesc`,
genDotRelated(`MessageType`, SUBSTRING_INDEX(SUBSTRING_INDEX(`TextValue`, ']', 1), '[', -1), 20511, 'TYPE') AS `YType`,
genDotRelated(`MessageType`, SUBSTRING_INDEX(SUBSTRING_INDEX(`TextValue`, ']', 1), '[', -1), 20511, 'POINT') AS `Ypoint`,
genDotRelated(`MessageType`, SUBSTRING_INDEX(SUBSTRING_INDEX(`TextValue`, ']', 1), '[', -1), 20511, 'DESC') AS `Ydesc`,
SUBSTRING_INDEX(SUBSTRING_INDEX(`TextValue`, ']', 1), '[', -1) `YDot`,
SUBSTRING_INDEX(`TextValue`, '-', -1) AS `Yval`,
'' AS `Yrecord`,'' AS `Ystatus`, '3' AS `Yindex`, '' AS `Yproof`
-- FROM `BaseDataTab` WHERE `IpAddress` = '192.168.10.15' AND `Port` = '2404' AND `DataType` = 3
FROM `BaseDataTab` WHERE `IpAddress` = v_ip AND `Port` = v_port AND `DataType` = 3
ORDER BY `ReportTime`;
SELECT * FROM `temp_term_tab`;
-- 开始处理每一个终端，应用所有的逻辑
call procedure_term(p_logic, v_termid);
END IF;
UNTIL flag_end END REPEAT;
close data_cur;
-- select v_begin, v_end, v_value, v_total;
END //
delimiter ;
call procedure_work('517&518&520&521^511');
###################################################################################
DROP PROCEDURE IF EXISTS proc_impl_YX_failure_or_success;
delimiter //
CREATE PROCEDURE proc_impl_YX_failure_or_success(IN p_logic TINYTEXT)
BEGIN
DECLARE v_begin INT DEFAULT 0;
DECLARE v_end INT DEFAULT 0;
DECLARE v_value TINYTEXT DEFAULT '';
DECLARE v_total TINYTEXT DEFAULT '';
-- 处理每一个可配置的存储过程
call procedure_work(p_logic);
-- SELECT v_begin, v_end, v_title, v_value, v_total;
END //
delimiter ;
call proc_impl_YX_failure_or_success('517&518&520&521^511');
-- -- 8.2 终端异常分析
-- -- 8.2.1 遥控成功但无遥信变位
-- call proc_impl_YK_exec_success_YX_failure('517&518&520&521^511');
-- -- 8.2.2 开关处于分位但仍有电流
-- call proc_impl_YX_switch_off_alternate_on('1&2|3|4');
-- -- 8.2.3 蓄电池活化状态持续为1
-- call proc_impl_YC_bettery_always_one('6|6', '');
-- -- 8.2.4 三相电流不平衡但无零序电流上报
-- call proc_impl_YC_alternate_imbalance_none_zreo( );
-- -- 8.2.5 蓄电池电压突变
-- call proc_impl_YC_bettery_voltage_jump('3&3', '24');
-- -- 8.2.6 某一终端遥测值长时间保持不变
-- call proc_impl_YC_longtime_stable('1&2|1&3&4&5|1&3&4&6&7');
-- -- 8.2.7 终端持续上报“故障总”遥信
-- call proc_impl_YX_continue_report_fault('5&5');
-- -- 8.2.8 终端响应速率慢异常分析
-- call proc_impl_YX_response_slowly( );
###################################################################################
-- 主存储过程调用入口，由事件定时器以最小粒度，比如配置事件每分钟定时调用
DROP PROCEDURE IF EXISTS procedure_main;
delimiter // #将语句的结束符号从分号;临时改为两个//(可以是自定义)
CREATE PROCEDURE procedure_main()
BEGIN
DECLARE v_begin INT DEFAULT 0;
DECLARE v_end INT DEFAULT 0;
DECLARE v_value TINYTEXT DEFAULT '';
DECLARE v_total TINYTEXT DEFAULT '';
DECLARE time_cur INT DEFAULT 0;
DECLARE time_per INT DEFAULT 0;
DECLARE flag_end INT DEFAULT 0;
DECLARE data_cur CURSOR FOR SELECT StoredProcedureName FROM PlanTab WHERE PresetCount = CurrentCount;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag_end=1;
-- 1，初始化逻辑消息配置表，在内存中创建表，每一次调用都执行
call procedure_init();
-- 2，刷新定时器的计数，超出已做翻转重置处理
call procedure_plan();
-- 3，遍历 PlanTab 表，找出到期的任务
open data_cur;
SET v_begin = 1;
REPEAT
FETCH data_cur INTO v_value;
IF flag_end <> 1 THEN
-- 3，开始调用查询到的存储过程，执行已配置并且到期的存储过程接口
SET @proc_call_name = (SELECT v_value); # call proc_impl_YX_failure_or_success();
PREPARE stmt FROM @proc_call_name;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL flag_end END REPEAT;
SELECT v_begin, v_end, v_value, v_total;
END //
delimiter ; #将语句的结束符号恢复为分号
call procedure_main();
###################################################################################
DROP PROCEDURE IF EXISTS procedure_test;
delimiter //
CREATE PROCEDURE procedure_test()
BEGIN
DECLARE v_begin INT DEFAULT 0;
DECLARE v_end INT DEFAULT 0;
DECLARE v_value TEXT DEFAULT '';
DECLARE v_total TEXT DEFAULT '';
DECLARE v_title TEXT DEFAULT '';
SELECT v_begin, v_end, v_title, v_value, v_total;
-- select v_begin, v_end, v_IpAddress,v_TextValue, v_total, v_title;
END //
delimiter ;
call procedure_test();
###################################################################################
-- 每隔一分钟执行一次，开始执行时间为当前时间的下一分钟
DELIMITER //
DROP EVENT IF EXISTS event_minute;
CREATE EVENT event_minute
ON SCHEDULE EVERY 1 MINUTE STARTS DATE_ADD(now(), INTERVAL 1 MINUTE)
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
INSERT INTO `BakDataTab` VALUES ('0', '', '0', '', '0', '', '', CURRENT_TIMESTAMP, '0', '0', '0', '0', '');
call procedure_main();
END //
DELIMITER ;
###################################################################################
###################################################################################
###################################################################################
###################################################################################
###################################################################################
###################################################################################
-- concat字符串连接函数，只要其中一个是NULL，那么将返回NULL
-- concat_ws字符串连接函数，只要有一个字符串不是NULL，就不会返回NULL。需要指定分隔符。
DROP PROCEDURE IF EXISTS test_procedure;
delimiter //
CREATE PROCEDURE test_procedure()
BEGIN
DECLARE v_begin INT DEFAULT 0; -- default
DECLARE v_end INT DEFAULT 0; -- default
DECLARE v_value VARCHAR(64) character set utf8 DEFAULT '' ; -- default
DECLARE v_total VARCHAR(64) character set utf8 DEFAULT '' ; -- default
DECLARE flag_end int DEFAULT 0;
DECLARE data_cur CURSOR FOR SELECT name FROM mysql.event;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag_end=1;
open data_cur;
set v_begin = 1; -- default
REPEAT
FETCH data_cur INTO v_value;
IF flag_end <> 1 THEN
set v_end = v_end + 1; -- default
set v_total = CONCAT_WS(',', v_value, v_value);
-- todo.
END IF;
UNTIL flag_end END REPEAT;
close data_cur;
-- OPEN data_cur;
-- set v_begin = 1; -- default
-- FETCH data_cur INTO v_value;
-- WHILE flag_end <> 1 DO
-- set v_end = v_end + 1; -- default
-- set v_total = CONCAT_WS(',', v_value, v_value);
-- todo.
-- FETCH data_cur INTO v_value;
-- END WHILE;
-- CLOSE data_cur;
select v_begin, v_end, v_value, v_total;
END //
delimiter ;
call test_procedure();
###################################################################################
###################################################################################
###################################################################################
###################################################################################
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
###################################################################################
###################################################################################
###################################################################################
###################################################################################
###################################################################################
-- 数据表中有自增长主键时，当用SQL插入语句中插入语句带有ID列值记录的时候；
-- 如果指定了该列的值，则新插入的值不能和已有的值重复，而且必须大于其中最大的一个值；
-- 也可以不指定该列的值，只将其他列的值插入，让ID还是按照MySQL自增自己填；
-- 这种情况在进行插入的时候，两种解决方法：
-- ①可以把id的值设置为null或者0，这样子mysql都会自己做处理
-- ②手动指定需要插入的列，不插入这一个字段的数据！
###################################################################################
###################################################################################
-- FILE权限与SELECE/DELETE/UPDATE等不同，后者是可以具体指定到某个db的某个表的，而FILE则是全局的，
-- 即只能通过grant FILE on *.* to 'abcde'@'%'才能使FILE权限对所有db的所有tables生效。
-- 通过grant all on db.* to 'abcde'@'%'不能使指定的user在指定的db上具有FILE权限。
###################################################################################
###################################################################################
-- MySql的CURRENT_TIMESTAMP在创建时间字段的时候
-- DEFAULT CURRENT_TIMESTAMP 表示当插入数据的时候，该字段默认值为当前时间
-- ON UPDATE CURRENT_TIMESTAMP 表示每次更新这条数据的时候，该字段都会更新成当前时间
-- 这两个操作是mysql数据库本身在维护，生成【创建时间】和【更新时间】两个字段，且不需要代码来维护
-- CREATE TABLE `alls_create_time_update_time` (
-- `index` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'INDEX',
-- `text` varchar(255) DEFAULT '' COMMENT '内容',
-- `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-- `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 注意：MySQL 5.6.5 以及之前的版本不支持同时指定两个自动time字段。
###################################################################################
###################################################################################
-- MySQL错误：删除数据库的时候报错ERROR 1010 (HY000)
-- 原因是数据/var/lib/mysql/目录下有除了数据库文件以外的文件,可以cd到其目录，清空后。再drop
###################################################################################
###################################################################################
-- 问题说明（修改 variables 变量配置，系统重启后将失效，需要修改配置文件，重启数据库）：
-- 1，导入文件路径问题，需要修改 secure_file_priv 字段为空，或者指定的某个路径
-- 2，修改创建数据库的默认编码，character_set_server，character_set_database 重启失效。
-- 3，
-- 8，忘记密码，修改mysql配置文件 skip-grant-tables 跳过密码检查
-- 9，LOAD DATA 必须针对 用户及访问方式设置 grant file 单独设置，不能针对数据库。
###################################################################################
###################################################################################
###################################################################################
###################################################################################
show variables like 'event%';
show variables like '%char%';
show variables like 'autocommit';
SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'secure_file_priv';
show global variables like "secure_file_priv";
show global variables like 'local_infile';
show variables like 'datadir';
show variables like '%dir%';
show variables like '%log%';
###################################################################################
###################################################################################
###################################################################################
mysql -u root -p
mysql -h 127.0.0.1 -P 3306 -uandy -p543604
mysqld_safe --user=mysql --skip-grant-tables --skip-networking
###################################################################################
UPDATE mysql.user SET password=PASSWORD('test') WHERE user='test';
UPDATE mysql.user SET password='test' WHERE user='test';
SELECT * FROM mysql.user WHERE user='test';
create user 'hwuser'@'localhost' identified by 'hwuser';
create user 'hwuser'@'%' identified by 'hwuser';
flush privileges;
create database HwMsas1000 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
grant all privileges on `HwMsas1000`.* to 'hwuser'@'%' identified by 'hwuser';
grant all privileges on `HwMsas1000`.* to 'hwuser'@'localhost' identified by 'hwuser';
flush privileges;
grant file on `TestDev`.* to 'test'@'%' identified by 'test';
grant file on *.* to 'test'@'%' identified BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
grant file on *.* to 'test'@'localhost' identified BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
FLUSH PRIVILEGES;
###################################################################################
-- 查询所有事件定时器的特定字段信息
select * from mysql.event;
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
###################################################################################
-- 更改单个事件定时器的开启关闭
alter event second_event on completion preserve enable;//开启定时任务
alter event second_event on completion preserve disable;//关闭定时任务
-- 开启事件 定时器执行计划
SET GLOBAL event_scheduler = ON;
show variables like 'event%';
###################################################################################
set character_set_server=utf8;
set character_set_database=utf8;
show variables like '%char%';
alter table `BaseDataTab` convert to character set utf8;
###################################################################################
--
DROP DATABASE IF EXISTS dataDictDb;
CREATE DATABASE IF NOT EXISTS dataDictDb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
USE dataDictDb;
--
TRUNCATE table `TypeTab`;
delete from TypeTab;
alter table `TypeTab` auto_increment=1;
--
DROP TEMPORARY TABLE IF EXISTS `temp_event_term`;
CREATE TEMPORARY TABLE `temp_event_term` ENGINE=MEMERY SELECT * mysql.event;
CREATE TEMPORARY TABLE temp_a (`inId` int auto_increment, b VARCHAR(20)) ENGINE=MEMORY ;
CREATE TABLE `TypeTab` (PRIMARY KEY (`Index`),`Index` int NOT NULL AUTO_INCREMENT,`TerminalName` text NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=utf8;
SELECT * INTO OUTFILE 'TypeTab.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM TypeTab;
load data local infile "TypeTab.csv" INTO table TypeTab FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
load data infile '/var/lib/mysql-files/BaseDataTab.csv' into table BaseDataTab fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n';
load data infile '/tmp/t0.txt' into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n' (`name`,`age`,`description`) set update_time=current_timestamp;
SELECT LOAD_FILE('/root/TypeTab.csv') AS csvResult;
###################################################################################
SELECT * FROM `temp_event_term`;
select SUBSTRING("Hello World",1,3) as test;
select SUBSTRING("Hello World",2) as test;
###################################################################################
SELECT 'YK[-359]-YKSEFA-0.000000' REGEXP '(YKSEFA)';
###################################################################################
SELECT * FROM BaseDataTab WHERE `ID` IN ( SELECT `ID` FROM YKDataTab );
###################################################################################
SELECT * FROM BaseDataTab WHERE `ID` = 17 AND 'HwMsas1000' IN ( SELECT DATABASE() );
###################################################################################
delete from BakDataTab;
alter table `BakDataTab` auto_increment=1;
###################################################################################
delete from BakDataTab;
alter table `BakDataTab` auto_increment=1;
SELECT * FROM BakDataTab WHERE date(BackupTime) >= '2020-03-11';
SELECT * FROM BakDataTab WHERE date(BackupTime) between '2013-01-01' and '2013-01-31';
###################################################################################
SELECT "2020-03-10 19:00:04" + INTERVAL 30 SECOND;
SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND);
SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
select TO_DAYS(950501);
select TO_DAYS('1997-10-07');
select FROM_DAYS(729669);
select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
select DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
###################################################################################
SELECT FIND_IN_SET(',' ,'YK,20;YX,10;YX,5') pos;
SELECT POSITION(',' IN 'YK,20;YX,10;YX,5');
###################################################################################
CREATE TABLE `t_test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(1) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
INSERT INTO t_test (`name`)VALUES('a')
REPLACE INTO t_test (`name`)VALUES('a')
没有评论
博主关闭了评论...