mysql 行比较,比较MySql中两个几乎相同的行/表之间的文本差异

I have 2 identical tables with different set of data's, now I would like to compare the words in a single field against multiple rows of the same column in table b and let me know the percentage of matches against each id

Example:

The following are the entries in Table A

Row1: 1, salt water masala

Row2: 2, water onion maggi milk

The following are the entries in Table B

Row1: 1, salt masala water

Row2: 2, water onion maggi

The desired result

Row1: Match 100% (All the 3 words are available but different order)

Row2: Match 75% as 1 word does not match out of the 4 words.

It would be really great if someone could help me with the same.

解决方案

Although it would be easier to accomplish this in your application code, it is possible via a couple of MySQL functions:

delimiter //

drop function if exists string_splitter //

create function string_splitter(

str text,

delim varchar(25),

pos tinyint) returns text

begin

return replace(substring_index(str, delim, pos), concat(substring_index(str, delim, pos - 1), delim), '');

end //

drop function if exists percentage_of_matches //

create function percentage_of_matches(

str1 text,

str2 text)returns double

begin

set str1 = trim(str1);

set str2 = trim(str2);

while instr(str1, ' ') do

set str1 = replace(str1, ' ', ' ');

end while;

while instr(str2, ' ') do

set str2 = replace(str2, ' ', ' ');

end while;

set @i = 1;

set @numWords = 1 + length(str1) - length(replace(str1, ' ', ''));

set @numMatches = 0;

while @i <= @numWords do

set @word = string_splitter(str1, ' ', @i);

if str2 = @word or str2 like concat(@word, ' %') or str2 like concat('% ', @word) or str2 like concat('% ', @word, ' %') then

set @numMatches = @numMatches + 1;

end if;

set @i = @i + 1;

end while;

return (@numMatches / @numWords) * 100;

end //

delimiter ;

The first function is used in the second, which is the one you want to call in your code, like so:

select percentage_of_matches('salt water masala', 'salt masala water');

select percentage_of_matches('water onion maggi milk', 'water onion maggi');