I am solving in these days following situation: In my DB table I have columns for name and surname.
我正在解决以下几种情况:在我的数据库表中,我有姓名和姓氏的列。
On my page I have input for searching people, and I am struggling with a problem, how to search the name in database that is stored in two columns and I have the name as string ("Joe Green").
在我的页面上,我有搜索人的输入,我正在努力解决问题,如何搜索存储在两列中的数据库中的名称,并将名称作为字符串(“Joe Green”)。
For example, in database I have followings:
例如,在数据库中我有以下内容:
Joe New
Joe Blue
Joe Green
Joe Francois Green
What could be the best way, how this problem to solve? I am currently working with MySQL database and Rails 3.
什么是最好的方法,这个问题怎么解决?我目前正在使用MySQL数据库和Rails 3。
EDIT: Thank you guys for you replies, but I don't know, how to make the query in Rails 3 notation, is it possible to use "concat"?
编辑:谢谢各位回复,但我不知道,如何在Rails 3表示法中进行查询,是否可以使用“concat”?
5 个解决方案
#1
5
if your database table engine is myISAM then use FULLTEXT search
如果您的数据库表引擎是myISAM,那么使用FULLTEXT搜索
first create FULLTEXT index by
首先创建FULLTEXT索引
CREATE FULLTEXT INDEX fx_name ON pages (name, surname)
then use below query to retrieve required data
然后使用下面的查询来检索所需的数据
SELECT *
FROM table
WHERE MATCH(name,surname) AGAINST ('keyword' IN BOOLEAN MODE)
update
alternative:use CONCAT
first create index
首先创建索引
CREATE INDEX users_firstandlast ON users(name, surname);
option1: repeat the CONCAT in your WHERE clause (because AS doesn't create a name you can use in the WHERE clause):
option1:在WHERE子句中重复CONCAT(因为AS不会创建可在WHERE子句中使用的名称):
SELECT CONCAT(name, ' ', surname) as fullname
FROM users
WHERE CONCAT(name, ' ', surname) LIKE '%joe green%';
option 2:
SELECT CONCAT(name, ' ', surname) as fullname
FROM users
WHERE name LIKE '%joegreen%' or surname LIKE '%joegreen%';
#2
1
One way is:
* split user's input to words
* search each word in name and surname columns
一种方法是:*将用户的输入拆分为单词*搜索名称和姓氏列中的每个单词
#3
1
first use the split function to split the string
首先使用split函数拆分字符串
namearr = name.split
namearr = name.split
namestr = ""
namearr.each do |i|
namestr = namestr + i
end
then use the values in your query
然后使用查询中的值
stmt = "select * from mytable where CONCAT(firstname,surename)= " + namestr
stmt =“select * from mytable where CONCAT(firstname,surename)=”+ namestr
#4
1
you can use the sql server full text search which i do not recommend because of the performance is very bad ,or use the lucin .net which is more powerful and her are some links : http://incubator.apache.org/lucene.net/
你可以使用我不推荐的sql server全文搜索,因为性能非常差,或者使用更强大的lucin .net,她有一些链接:http://incubator.apache.org/lucene。净/
http://msdn.microsoft.com/en-us/library/ms142571.aspx
mark as answered if it helps :)
如果有帮助就标记为已回答:)
#5
1
A lot of this would be significantly simpler if you had given some indication of what language is being used at the interface between the input and the database (since Rails is usually associated with Ruby I'll assume you mean that), however....
如果您已经在输入和数据库之间的接口上使用了什么语言(由于Rails通常与Ruby相关联,我认为你的意思是这样),那么很多这将会非常简单,但是...... 。
SELECT *
FROM yourtable
WHERE CONCAT(name, ' ', surname)
LIKE CONCAT('%',REPLACE(?,' ', '%'),'%')
ORDER BY LEVENSTEIN(CONCAT(name, ' ', surname), ?)
(where '?' is replaced by your search string, and the levenstein function is described here)
(其中'?'由搜索字符串替换,此处描述了levenstein函数)
Performance will be poor - a better solution would be to split the string Ruby and attempt matches of varying combinations.
性能会很差 - 更好的解决方案是拆分字符串Ruby并尝试不同组合的匹配。
#1
5
if your database table engine is myISAM then use FULLTEXT search
如果您的数据库表引擎是myISAM,那么使用FULLTEXT搜索
first create FULLTEXT index by
首先创建FULLTEXT索引
CREATE FULLTEXT INDEX fx_name ON pages (name, surname)
then use below query to retrieve required data
然后使用下面的查询来检索所需的数据
SELECT *
FROM table
WHERE MATCH(name,surname) AGAINST ('keyword' IN BOOLEAN MODE)
update
alternative:use CONCAT
first create index
首先创建索引
CREATE INDEX users_firstandlast ON users(name, surname);
option1: repeat the CONCAT in your WHERE clause (because AS doesn't create a name you can use in the WHERE clause):
option1:在WHERE子句中重复CONCAT(因为AS不会创建可在WHERE子句中使用的名称):
SELECT CONCAT(name, ' ', surname) as fullname
FROM users
WHERE CONCAT(name, ' ', surname) LIKE '%joe green%';
option 2:
SELECT CONCAT(name, ' ', surname) as fullname
FROM users
WHERE name LIKE '%joegreen%' or surname LIKE '%joegreen%';
#2
1
One way is:
* split user's input to words
* search each word in name and surname columns
一种方法是:*将用户的输入拆分为单词*搜索名称和姓氏列中的每个单词
#3
1
first use the split function to split the string
首先使用split函数拆分字符串
namearr = name.split
namearr = name.split
namestr = ""
namearr.each do |i|
namestr = namestr + i
end
then use the values in your query
然后使用查询中的值
stmt = "select * from mytable where CONCAT(firstname,surename)= " + namestr
stmt =“select * from mytable where CONCAT(firstname,surename)=”+ namestr
#4
1
you can use the sql server full text search which i do not recommend because of the performance is very bad ,or use the lucin .net which is more powerful and her are some links : http://incubator.apache.org/lucene.net/
你可以使用我不推荐的sql server全文搜索,因为性能非常差,或者使用更强大的lucin .net,她有一些链接:http://incubator.apache.org/lucene。净/
http://msdn.microsoft.com/en-us/library/ms142571.aspx
mark as answered if it helps :)
如果有帮助就标记为已回答:)
#5
1
A lot of this would be significantly simpler if you had given some indication of what language is being used at the interface between the input and the database (since Rails is usually associated with Ruby I'll assume you mean that), however....
如果您已经在输入和数据库之间的接口上使用了什么语言(由于Rails通常与Ruby相关联,我认为你的意思是这样),那么很多这将会非常简单,但是...... 。
SELECT *
FROM yourtable
WHERE CONCAT(name, ' ', surname)
LIKE CONCAT('%',REPLACE(?,' ', '%'),'%')
ORDER BY LEVENSTEIN(CONCAT(name, ' ', surname), ?)
(where '?' is replaced by your search string, and the levenstein function is described here)
(其中'?'由搜索字符串替换,此处描述了levenstein函数)
Performance will be poor - a better solution would be to split the string Ruby and attempt matches of varying combinations.
性能会很差 - 更好的解决方案是拆分字符串Ruby并尝试不同组合的匹配。