在这个ssql查询中，我在AGAINST子句中使用%。
SELECT firstname,lastname,middlename,company_name,
primary_emailaddress,alternate_emailaddress,personal_address_line1,
personal_address_line2,personal_address_city,facebook_username,
twitter_username,googleplus_username,linkedin_username,
personal_website_url,birthday_month,notes,personal_address_zipcode,
company_address_zipcode,home_phonenumber,company_phonenumber,
cell_phonenumber,birthday_day,birthday_year,hash,image_file
FROM contacts
WHERE (
MATCH(
firstname,middlename,lastname,
primary_emailaddress,alternate_emailaddress,personal_address_line1,
personal_address_city,company_name,
company_address_line1,company_address_city,
facebook_username,twitter_username,googleplus_username,linkedin_username,
personal_website_url,birthday_month,notes
)
AGAINST ('someemail@email.com%' IN BOOLEAN MODE)
OR personal_address_zipcode REGEXP('(someemail@email.com*)')
OR company_address_zipcode REGEXP('(someemail@email.com*)')
OR home_phonenumber REGEXP('(someemail@email.com*)')
OR company_phonenumber REGEXP('(someemail@email.com*)')
OR cell_phonenumber REGEXP('(someemail@email.com*)')
OR birthday_day REGEXP('(someemail@email.com*)')
OR birthday_year REGEXP('(someemail@email.com*)')
)
AND addressbook_id = 4
在这个ssql查询中，我在AGAINST子句中使用了一个*。
SELECT firstname,lastname,middlename,company_name,
primary_emailaddress,alternate_emailaddress,personal_address_line1,
personal_address_line2,personal_address_city,facebook_username,
twitter_username,googleplus_username,linkedin_username,
personal_website_url,birthday_month,notes,personal_address_zipcode,
company_address_zipcode,home_phonenumber,company_phonenumber,
cell_phonenumber,birthday_day,birthday_year,hash,image_file
FROM contacts
WHERE (
MATCH(
firstname,middlename,lastname,
primary_emailaddress,alternate_emailaddress,personal_address_line1,
personal_address_city,company_name,
company_address_line1,company_address_city,
facebook_username,twitter_username,googleplus_username,linkedin_username,
personal_website_url,birthday_month,notes
)
AGAINST ('someemail@email.com*' IN BOOLEAN MODE)
OR personal_address_zipcode REGEXP('(someemail@email.com*)')
OR company_address_zipcode REGEXP('(someemail@email.com*)')
OR home_phonenumber REGEXP('(someemail@email.com*)')
OR company_phonenumber REGEXP('(someemail@email.com*)')
OR cell_phonenumber REGEXP('(someemail@email.com*)')
OR birthday_day REGEXP('(someemail@email.com*)')
OR birthday_year REGEXP('(someemail@email.com*)')
)
AND addressbook_id = 4
两者都没有返回内容恰好等于至少someemail@email.com的位置。它通过com、电子邮件或其他方式返回所有内容。我需要做哪些更改？匹配列上有一个FULLTEXT索引。
最好将地址放在引号中，并且根本不使用通配符：
AGAINST ('"someemail@email.com"' IN BOOLEAN MODE)
就像那样。
我认为你不需要布尔模式。
您的全文搜索不起作用，因为@和.（实际上大多数非字母数字字符）都是单词分隔符。因此1。电子邮件被索引为三个独立的单词和2。搜索字符串中将忽略单词分隔符。
只需在列上创建一个常规的多列索引，然后使用标准的LIKE:进行搜索
WHERE firstname LIKE 'someemail@email.com%' OR ...
这个查询将能够使用索引，搜索将非常高效。
另外，去掉这些REGEXP()。它们是无用的（多余的），会扼杀你的表现（不能使用索引）。