MySQL Fulltext或唯一的varchar性能(MySQL Fulltext or unique varchar performance)

MySQL Fulltext或唯一的varchar性能(MySQL Fulltext or unique varchar performance)

方法＃1（全文）：

CREATE TABLE `addresses` ( `id` int(10) unsigned NOT NULL auto_increment, `mail` text NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `mail` (`mail`) );

找出存在与搜索：

SELECT * FROM addresses WHERE MATCH(email) AGAINST('"name@example.com"' IN BOOLEAN MODE)

或方法＃2（唯一varchar）：

CREATE TABLE `addresses` ( `id` int(10) unsigned NOT NULL auto_increment, `mail` varchar(100) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `mail` (`mail`) );

找出存在与插入：

INSERT INTO `addresses` (`id`, `mail`) VALUES (NULL , 'name@example.com');

如果失败那么存在：

#1062 - Duplicate entry 'name@example.com' for key 2

哪种方法更适合查找数千行中存在的邮件地址？

Method #1 (fulltext):

CREATE TABLE `addresses` ( `id` int(10) unsigned NOT NULL auto_increment, `mail` text NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `mail` (`mail`) );

find out exists with searching:

SELECT * FROM addresses WHERE MATCH(email) AGAINST('"name@example.com"' IN BOOLEAN MODE)

or method #2 (unique varchar):

CREATE TABLE `addresses` ( `id` int(10) unsigned NOT NULL auto_increment, `mail` varchar(100) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `mail` (`mail`) );

find out exists with insert:

INSERT INTO `addresses` (`id`, `mail`) VALUES (NULL , 'name@example.com');

if fail then exists:

#1062 - Duplicate entry 'name@example.com' for key 2

which method is better for find out exists mail addresses in thousands of rows?

最满意答案

方法3（使用唯一的varchar）更好：

SELECT * FROM addresses WHERE email = 'name@example.com'

FullText搜索完全匹配并不是一个好主意，并使用插入来测试是否存在某些东西是错误的（尽管如果你计划插入它是正确的）。

请注意，根据编码，唯一约束可以不区分大小写。 如果你使用utf-8-bin，它将是基本敏感的。

Method 3 (with the unique varchar) is better:

SELECT * FROM addresses WHERE email = 'name@example.com'

FullText search for a exact match isn't a great idea and using an insert to test if something exists is just wrong (although correct if you're planning to insert it regardless).

Note that the unique constraint can be case insensitive depending on the encoding. If you use utf-8-bin it will be base sensitive.