SQL
Transfer data from table A to table B
-- ----------------------------
-- Table structure for THREAT_REPORT
-- ----------------------------
DROP TABLE IF EXISTS `THREAT_REPORT`;
CREATE TABLE `THREAT_REPORT` (
`FILE_HASH` varchar(256) NOT NULL,
`VENDOR` varchar(10) NOT NULL COMMENT 'preserved for 30 vendors, 2^30=1073741824',
`HASH_TYPE` enum('md5','sha1','sha256') NOT NULL,
`REPORT` json NOT NULL COMMENT 'threat report/encyclopedia info from vendor',
`REPORT_DIGEST` varchar(256) NOT NULL,
`CREATE_TIME` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'create time about the record',
`UPDATE_TIME` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'update time about the record',
PRIMARY KEY (`FILE_HASH`, `VENDOR`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO SELECT
-- WebRoot
-- format {"4": {"det": "B", "md5": "00070b378c87e5bb935c60fcf55b0c9a", "fseen": 1581019932, "detdate": 1581020431, "pccount": 1, "filesize": 385380, "malwaregroup": "W32.Trojan.Emotet"}}
INSERT INTO THREAT_REPORT
SELECT
FILE_HASH,
VIRUS_SOURCE,
HASH_TYPE,
JSON_EXTRACT(REPORT_RESULT, '$."4"') as REPORT,
MD5(CONCAT('{"det":', JSON_EXTRACT(REPORT_RESULT, '$."4".det'), ',"detdate":', JSON_EXTRACT(REPORT_RESULT, '$."4".detdate'),',"filesize":',JSON_EXTRACT(REPORT_RESULT, '$."4".filesize'),',"fseen":', JSON_EXTRACT(REPORT_RESULT, '$."4".fseen'),',"malwaregroup":',JSON_EXTRACT(REPORT_RESULT, '$."4".malwaregroup'),',"md5":', JSON_EXTRACT(REPORT_RESULT, '$."4".md5'), ',"pccount":',JSON_EXTRACT(REPORT_RESULT, '$."4".pccount'),'}')) as REPORT_DIGEST,
FROM_UNIXTIME(RESULT_TIMESTAMP) as CREATE_TIME,
FROM_UNIXTIME(RESULT_TIMESTAMP) as UPDATE_TIME
FROM `SCORE_DB` WHERE VIRUS_SOURCE=4 AND JSON_EXTRACT(REPORT_RESULT, '$."4"') is not null;
Add new column value from origin column in the same table
SET SQL_SAFE_UPDATES = 0;
UPDATE REPUTATION_DB t1
INNER JOIN REPUTATION_DB t2 ON t2.HOST = t1.HOST
SET t1.CATEGORIES = CAST( CONCAT('["', REPLACE(t2.CATEGORY_NAME, ',', '","'), '"]') AS JSON);
SET SQL_SAFE_UPDATES = 1;
add multi-value index
As of MySQL 8.0.17, InnoDB supports multi-valued indexes
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
ALTER TABLE REPUTATION_DB ADD INDEX idx_categories_hosttype_rankscore( (CAST(CATEGORIES->'$' AS CHAR(32) ARRAY)), HOST_TYPE, RANK_SCORE );
SELECT CATEGORIES, HOST, RANK_SCORE, SCORE
FROM ZSDNDCDB.REPUTATION_DB
where JSON_OVERLAPS(CATEGORIES->'$', CAST('["Anonymizers", "Browser Exploits", "Malicious Sites/Botnet", "Malicious Sites", "Malicious Downloads", "Phishing", "Spam URLs", "Spyware/Adware/Keyloggers"]' AS JSON))
AND HOST_TYPE = 'domain'
ORDER BY RANK_SCORE DESC
LIMIT 0, 2500