MySQL implements TF-IDF to traverse an indeterminate number of columns

There is a problem that requires the use of pure MySQL to implement a TF-IDF algorithm.

The original input is a articles table with 100 columns and one word per column. In fact, the core difficulty is how to traverse the comparison of these 100 words and specified words such as ' apple ' for comparison. First of all, brute force is poor to give all the column names, such as Word1, Word2 ... But this code must be ugly ugly, and if it is 1000 columns what to do.

The first step in traversing a column is to take the column name out.

Create temporary table column_name #建立临时表

Select Ordinal_position, column_name #选择主位置 (similar to primary key), column name

From Information_schema.columns

where Table_name= ' articles1 ' and column_name like ' word% '; # removes columns from the Articles1 table, only the names that begin with Word

The second step is to implement the loop, using the loop to take out all the column names in turn, where the main use of procedure, Concat, preparestatement. Prcedure is similar to a function in other languages, concat the string, and Preparestatement is used to execute the string as a SQL program.

Tip1:procedure There is a pit, the beginning of the debugging is not successful, this is because of the delimiter problem, that is, how to count your statement is over. The default delimiter is '; ', but when defining procedure, because the middle cannot be interrupted and there will be more than one statement, use Delimter//command to change the default delimiter

CREATE PROCEDURE Pro1 (

In article_id Integer,

In Wordmatch varchar (30)

)

Begin

Set @wordmatch = Wordmatch; #声明要匹配的词

Set @col_cnt = (select count (column_name) from information_schema.columns where table_name= ' articles1 ' and column_name Like ' word% '); #得到列的总素数量

Set @article_id =article_id; #声明要匹配的文章id

Set @i=2;//because of the ID of the first field in the Articles1 table, the match starts from the second field

Set @cnt =0;//The number of column values that are identical to the word set

While @i<[email protected]_cnt+1 do

#产生需要执行的命令

Set @thiscol = (select column_name from column_name where [email protected]); Remove a field from the Column name table

Set @s=concat (' Set @[email protected] + (SELECT count (', @thiscol, ') ', ' from articles1 where id= ', @article_id, ' and ' , @thiscol, ' =\ ', @wordmatch, ' \ ');

Prepare stmt1 from @s;

#执行命令

Execute STMT1;

deallocate prepare STMT1;

Set @[email protected]+1;

End while;

Select @cnt;

end//

Resources

[1]http://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable Preparestatement usage

[2]http://www.cnblogs.com/wangtao_20/archive/2011/02/21/1959734.html the use of variables in MySQL

[Use of 2]http://blog.sina.com.cn/s/blog_71f4cdbe0100yut4.html procedure

MySQL implements TF-IDF to traverse an indeterminate number of columns