在处理数据的时候发现有两个数据表没有任何关联,唯一有联系的是两列看起来有些相似的数据,名字相似但是不一样,例如下面的课程名称和教材名称:
两列数据不相同但是相似,Mysql里面不存在这样的直接函数功能,所以没有办法去join,只能用where去连接两个表。
另外,看到其他博主都说Mysql有个自带的编辑距离函数,直接拿来用就可以,亲身试验了一下根本不行,MySQL不认识什么similarity函数,也不认识LEVENSHTEIN函数,所以没办法,要自己去定义才行,下面是可执行的代码。
自定义similarity函数和LEVENSHTEIN函数,然后调用两个函数,亲们可以直接拿走直接用。在代码中我标记了两条虚线,第一条虚线以上部分就是自定义函数,亲们可以拿走直接用,完全不用更改直接复制就好。第二条虚线上面那段代码的是用where去连接两个具有相似字段的表,需要注意的是两个表的数据量最好不要过千,不然会很慢。
如果有大佬有更好的模糊匹配的方法可以在评论区艾特我,欢迎指教。
#jcjb_62111
#jhkc_62102
#pksj_6251
#kcjbsj_6211
#自定义编辑距离函数1
$word = mysql_real_escape_string($word);
mysql_qery("SELECT `term` FROM `words` WHERE levenshtein('$word', `term`) BETWEEN 0 AND 4");
DELIMITER $$
CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
DECLARE s1_char CHAR;
-- max strlen=255
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
IF s1 = s2 THEN
RETURN 0;
ELSEIF s1_len = 0 THEN
RETURN s2_len;
ELSEIF s2_len = 0 THEN
RETURN s1_len;
ELSE
WHILE j <= s2_len DO
SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
END WHILE;
WHILE i <= s1_len DO
SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
WHILE j <= s2_len DO
SET c = c + 1;
IF s1_char = SUBSTRING(s2, j, 1) THEN
SET cost = 0; ELSE SET cost = 1;
END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
IF c > c_temp THEN SET c = c_temp; END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
IF c > c_temp THEN
SET c = c_temp;
END IF;
SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
END WHILE;
SET cv1 = cv0, i = i + 1;
END WHILE;
END IF;
RETURN c;
END;$$
#自定义编辑距离函数2
CREATE FUNCTION levenshtein_ratio( s1 VARCHAR(255), s2 VARCHAR(255) )
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, max_len INT;
SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);
IF s1_len > s2_len THEN
SET max_len = s1_len;
ELSE
SET max_len = s2_len;
END IF;
RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);
END; $$
DELIMITER ;
DELIMITER $$
DELIMITER ;
#建立相似度函数
set global log_bin_trust_function_creators=TRUE;
CREATE FUNCTION similarity(str1 VARCHAR(255), str2 VARCHAR(255))
RETURNS INT
BEGIN
DECLARE dist INT;
SET dist = LEVENSHTEIN(str1, str2);
RETURN (1 - (dist / GREATEST(LENGTH(str1), LENGTH(str2)))) * 100;
END;
---------------------------------------------------------------1
#根据课程号配置课程名称
create table hjj_test1 as
select a.kch,b.kc_name from jhkc_62102 a
left join kcjbsj_6211 b on a.kch=b.kch;
#按照小分类建立教材基本数据
drop table hjj_test2;
create table hjj_test2 as
select * from jcjb_62111 t where t.jcname like'%技术%' order by bc desc;
#寻找课程名称当中相同类别数据
drop table hjj_test1_2;
create table hjj_test1_2 as
select * from hjj_test1 t where t.kc_name like'%技术%' ;
#课程名称与教材名称进行比对
drop table hjj_test3;
create table hjj_test3 as
select t.* ,b.jcname,b.cbh ,similarity(t.kc_name,b.jcname)is_same from hjj_test1_2 t ,hjj_test2 b ;
---------------------------------------------------------------2
#根据编辑距和表格内容离求与课程相似的教材名称
select t.* from (
select t.* ,row_number() over(partition by t.kc_name order by t.kc_name desc) rn from (
select t.kc_name,t.jcname,t.cbh,max(t.is_same)max_value from (
select * from hjj_test3 t where t.kc_name in ('计算机组网技术','工业网络与组态技术','路由与交换技术','汽车电子控制技术','传感器与智能检测技术',
'数控加工自动编程技术','汽车性能与使用技术','电力电子技术','汽车单片机技术','接触镜验配技术专项训练',
'网络安全技术','药物分析与检测技术','视觉康复训练技术','近视防控技术','智能网联汽车底盘线控技术',
'智能汽车ADAS技术','电工电子技术2','电工电子技术2','电气控制技术','微生物学与实验技术',
'低视力评估和康复技术','数字电子技术','药物制剂技术','眼镜维修检测技术专项训练',
'验光技术专项训练','药物分离与纯化技术','数控加工自动编程技术','网站前台美化技术',
'无线局域网技术')) t
group by t.kc_name,t.jcname
order by t.kc_name,t.is_same desc) t ) t where t.rn=1