MySQL必知必会13-18-高级检索知识

MySQL PV:

分组数据

分组数据可以用于按照某一列数据作为分类标准来统计数据。所以其一般与聚集函数一起用。

创建分组

分组是使用SELECT子句和GROUP BY子句建立。

例如：统计每个vend_id的数据数量：

1
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

将语句分为两部分，

  • SELECT vend_id, COUNT(*) AS num_prods FROM products
  • GROUP BY vend_id

可以看作是先统计了总数，然后将总数按照vend_id分类。其结果可能如下：

vend_id num_prods
1001 3
1002 2
1003 7

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

在具体使用GROUP BY子句前，需要知道一些重要的规定。

  • GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更加细致的控制。
  • 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总，换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
  • 如果分组列中具有NULL值，则NULL值将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

过滤分组

分组过滤允许GROUP BY得出的分组进行过滤，规定得到哪些分组，排除哪些分组。

WHERE子句不能对其进行过滤，因为分组是针对行的，而不是针对分组的。事实上，WHERE没有分组的概念。

MySQL提供了HAVING子句，HAVING非常类似于WHERE。只不过WHERE过滤行，HAVING过滤分组。

HAVING和WHERE

  • 实际上有关WHERE的所有这些技术和选项都适用于HAVING。它们的句法是相同的，只是关键字有差别。
  • 另一种理解方式是WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
  • 基于上面一条，可以得：WHERE用于筛选数据表中的字段，而HAVING用于筛选前面确定的字段（临时表的一部分）。

分组过滤例子

  1. 单使用HAVING

    1
    SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

    该语句增加了一个HAVING COUNT(*) >= 2子句。所以其意义是按照cust_id分组然后筛选分组中总数COUNT(*)大于等于2的分组。

  2. 同时使用WHERE和HAVING

    1
    SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) > 2;

    这里在前面加了WHERE prod_price >= 10 子句，如上所说，WHERE在分组统计之前进行计算，所以后面的统计中就不再包含prod_price < 10的数据。然后将统计出的数据按照COUNT(*) > 2进行分组过滤。

分组和排序

GROUP BY和ORDERED BY子句的区别：

OEDERED BY GROUP BY
排序产生的输出 分组行。但输出可能不是分组的顺序
任意行都可以使用（甚至非选择的列也可以使用） 只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要 如果于聚集函数一起使用列（或表达式），则必须使用

GROUP BY和ORDERED BY可以一起使用，可以先将数据筛选出来，然后按某列数据（可能是聚集的那列数据）对筛选的数据进行排序。

例如：

1
SELECT order_num, SUM(quantity * itenm_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity * itenm_price) >= 50 ORDER BY ordertotal;

SELECT子句顺序

下表中从上到小是SELECT中子句的出现顺序。

子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

使用子查询

MySQL在4.1版本引入了子查询功能。

SELECT语句是单个SQL的查询。迄今为止我们看到的SELECT语句都是简单查询，即从单个数据表中检索数据的单条语句。

SQL还允许创建子查询，即嵌套在其他查询中的查询。

使用子查询进行过滤

订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。

现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索?下面列出具体的步骤。

(1)检索包含物品TNT2的所有订单的编号。

(2)检索具有前一步骤列出的订单编号的所有客户的ID。

(3)检索前一步骤返回的所有客户ID的客户信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。

  1. SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'

    order_num
    20005
    20007

  2. SELECT cust_id FROM orders WHERE order_num IN (20005, 20007)

    cust_id
    10001
    10004

  3. SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004)

    cust_name cust_contact
    Coyote Inc. Y Lee
    Yosemite Plcae Y sam

合并子句

  1. 合并子句1和子句2得到子句4

    现在考虑合并第一个第二个子句。实际上就是将第二个子句中的数据用第一个子句来替换，并且用括号使之先进行计算。如下：

    1
    2
    SELECT cust_id FROM orders WHERE order_num IN 
    (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2')
    cust_id
    10001
    10004

  2. 合并子句4和子句3

    用同样的方法得到合并子句4和子句3：

    1
    2
    3
    4
    SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (
        SELECT cust_id FROM orders WHERE order_num IN(
            SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
        ));

为了执行上面的语句，MySQL实际上必须执行3条SELECT语句。最里面返回订单号列表，此列表用于最外面的子查询的WHERE子句。次外面的子查询返回客户ID列表。此客户ID列表用于最外层查询的WHERE子句。最外层查询才返回所需的数据。

可见，在WHERE子句中使用子查询能够编写出功能强大并且很灵活的SQL语句。对于嵌套子查询的数目没有限制，不过在实际使用时由于性能的限制，最好不要嵌套太多的子查询。

注意：在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回当单个列并且与单个列匹配，但如果需要也可以使用多个列。

虽然子查询一般与IN操作符相结合，但也可以用于测试等于（=）、不等于（<>）等。

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假设需要显示customers表中的每个客户的订单总数。订单与相应的客户ID存储在orders表中。

为了执行整个操作，遵循下面的步骤：

  1. 从costumers表中检测客户列表。
  2. 对于检索出的每个客户，统计其在orders表中的订单数目。

同样写出如果分开查询的步骤：

  1. SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001

    orders
    2

  2. SELECT cust_name, cust_state FROM customers ORDER BY cust_name

    cust_name cust_status
    Coyote Inc. MI
    E fudd IL
    Mouse House IN

合并子句

现在将1，2合并，与上面不同，子句不作为筛选条件，而是一个计算属性。

1
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
cust_name cust_status oders
Coyote Inc. MI 2
E fudd IL 1
Mouse House IN 0

注意这里的第三个属性是通过一个子查询得到的一个计算属性，注意这里子查询中的WHERE语句的条件，其会自动判断当前外层筛选出oders表中的行的cust_id，然后和customers表的cust_id进行比较。且必须加上限定名，如果不加上限定名，MySQL就会假设是将oders表中的cust_id进行自身匹配。

所以注意：虽然子查询在构造SELECT语句时极其有用，但必须注意限制有歧义性的列名。

技巧：对于子查询这种技术，可以嵌套很多层进行很强大的查询。但是为了在很多嵌套时其仍然保持正确，可以按照从内向外的调试技巧，即先从内部查询开始调试，一层层的增加，可以有效减少错误率。

联结表

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。

为什么要联结表

分解数据为多个表能够有效地存储、更方便地处理，并且具有更加强大的可伸缩性。但是分解后数据也随之分解，为检索制造了很多麻烦。

而使用联结可以将多个表结合在一起。可以在一条SELECT语句中关联表。

创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

例如：

1
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE venders.vend_id = products.vend_id ORDER BY vend_name, prod_name;
vend_name prod_name prod_price
ACME Bird seed 10.00
ACME Carrots 2.50
ACME Detonator 13.00

这条语句的关键是WHERE子句，其指定了联立表的条件。

注意要匹配的两个列以venders.vend_id和products.vend_id来指定。这里需要这种完全限定名，因为如果只给出vend_id，则MySQL不知道指定的是两张表中的哪一个。

在列可能出现二义性的时候，必须使用完全限定名。如果引用一个没有用表名限定的具有二义性的列名，则MySQL经返回错误结果。

WHERE子句的重要性

利用WHERE子句建立联结关系似乎有点奇怪，但实际上，有一个很充分的理由。请记住，在一条SELECT语句中联结几个表时，相应的关系是 在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情。在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管 它们逻辑上是否可以配在一起。

笛卡尔积：由没有联结条件的表返回的结果为笛卡尔积。检索出的行数将是第一个表中的行数乘以第二个表中的行数（即将第一张表中的所有数据与第二张表中所有数据进行组合，因为没有限定条件指定两张表中的哪些数据应该被指定）。

内部联结

目前为止所用的联结称为等值联结（equal join），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。下面的SELECT语句返回与前面使用where =的例子完全相同的数据：

1
2
SELECT vend_name，prod_name，prod_price FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

这种语法将联结条件通过ON关键词来指定，并且由INNER JOIN给出要联立的两张表。

联结多个表

SQL对一条SELECT语句可以联结的表的数目没有限制。创建多张表联结的规则基本相同：首先列出所有的表，然后定义表的关系，例如：

1
SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.pord_id AND order_num =20005;

此语句联立了3张表，orderitems、products和vendors。并且通过WHERE指定了两个联立条件和一个值限定条件。

性能考虑：MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常消耗资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

考虑子查询中的例子：

1
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (    SELECT cust_id FROM orders WHERE order_num IN(        SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'    ));

此时就可以用联立表来进行查询，而不需要进行子查询，子查询消耗的性能往往比联立更高：

1
SELECT cust_name, cust_contact FROM customers. orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND pord_id = 'TNT2';

通过两个限定条件来联立三张表，然后通过一个值限定来确定返回的是要求的产品。

创建高级联结

还有其他类型的表联结方式，也可以对被联结的表使用别名和聚集函数。

使用表别名

SQL中不仅允许给列起别名，还允许给表取别名。这样做主要有以下两个理由：

  • 缩短SQL语句。
  • 允许在单个SELECT语句中多次使用相同的表。

比如下面的语句使用别名来替代表名来缩短SQL语句：

1
SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2';

可以看到这里使用别名简化了后面的全限定名的写法。

使用不同类型的联结

前面只看到了内部连接（等价联结）的简单联结。实际上一共有4中联结方式：

  • 自联结
  • 自然联结
  • 内部连接
  • 外部联结

自联结

所谓子查询就是表自己和自己联立，一般是为了通过表中的某一个信息来查询该表中的其他信息。

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是通过子查询解决此问题的一种方法:

1
SELECT pord_id, pord_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

同样，其可以用联结来重写子查询的语句。而这时我们是通过一张表得到的信息来查询这张表中的信息，此时就该使用自联结：

1
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因此MySQL不知道你引用的products表中的哪个实例。

为解决此问题，使用了表别名。products的第一次出现为别名p1，第二次出现为别名p2。现在可以将这些别名用作表名。例如，SELECT语句使用p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪一个列（即使它们事实上是同一个列)。WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然联结

标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

自然联结是这样一种联结，其中你只能选择那些唯一的列。这一 般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子
集来完成的。下面举一个例子：

1
2
3
4
SELECT c.*, o.order_num，o.order_date,
oi.prod_id，oi.quantity ，o1.item_price
FROM customers AS c,orders As o，orderitems As oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB' ;

1、自然连接是特殊的内联结（等值联结），自然联结不能有where和on去限制筛选
2、等值连接要求相等的分量，不一定是公共属性（即相同的列名）；而自然连接要求相等的公共属性（列名）。　　

外部联结

联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

下面的SELECT语句给出一个简单的内部联结。它检索所有客户及其订单：

1
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customer.cust_id = orders.cust_id;

类似于上一章中所看到的内部联结，这条SELECT语句使用了关 键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字 指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT 指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM 子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN，如下例所示：

1
SELECT customers.cust_id, orders.order_num FROM customers RIGHT JOIN orders ON orders.cust_id = customers.cust_id;

外部联结的类型存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒语句中表的顺序来达到互相的效果。

使用聚集函数

聚集函数也可以用于联结表的聚集。

例如：如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

1
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON costomers.cust_id = orders.cust_id GROUP BY customers.cust_id;

此SELECT语句使用INNER JOIN将customers和orders表互相关联。 GROUP BY子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

使用联结的条件

  • 注意使用联结的类型，根据不同情况选择内还是外。
  • 应该提供联结条件，否则将返回笛卡尔积。
  • 在一个联结中，可以包含多个表，并且不同的表也可以使用不同的联结方式，但是应该先单个测试，防止混乱出错。

组合查询

MySQL允许执行多个查询操作（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常被称为并（union）或复合查询（compound query）。

有两种基本情况，其中需要使用组合查询：

  • 在单个查询中从不同的表返回类似结构的数据。
  • 对单个表执行多个查询，按单个查询返回数据。

组合查询和多个WHERE条件：多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。 这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

创建组合查询

可用UNION操作符来组合数条SQL查询。

使用UNION

UNION的使用很简单。所需做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION。

举一个例子，假如需要价格小于等于5的所有物品的一个列表，而且 还想包括供应商1001和1002生产的所有物品（不考虑价格）。当然，可以利用WHERE子句来完成此工作，不过这次我们将使用UNION。

语句1：

1
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5;
vend_id prod_id prod_price
1003 FC 2.50
1002 FUI 3.42
1003 SLING 4.49

语句2：

1
SELECT  vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
vend_id prod_id prod_price
1001 ANV01 5.99
1001 ANV02 9.99
1001 ANV03 14.99

组合查询

现在将两条语句组合起来

1
2
3
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION
SELECT  vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
vend_id prod_id prod_price
1003 FC 2.50
1002 FUI 3.42
1003 SLING 4.49
1001 ANV01 5.99
1001 ANV02 9.99
1001 ANV03 14.99

WHERE方式

前面提到，一般从同一个表中进行联合查询，也可以用WHERE来替代：

1
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 AND vend_id IN (1001, 1002);
vend_id prod_id prod_price
1003 FC 2.50
1002 FUI 3.42
1003 SLING 4.49
1001 ANV01 5.99
1001 ANV02 9.99
1001 ANV03 14.99

这两种方式获得的结果一致。

UNION规则

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
  • UNION中的每个查询必须包含相同的列、表达式和聚集函数（不过各个列不需要以相同的次序列出）。
  • 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

如果遵守了这些基本规则或限制，则可以将并用于任何数据检索任务。

包含或取消重复的列

UNION会从查询结果集中自动去除了重复的行（换句话说，它的行为与 单条SELECT语句中使用多个WHERE子句条件一样）。

这是UNION的默认行为，但是如果需要，可以改变它。事实上，如果 想返回所有匹配行，可使用UNION ALL而不是UNION。

例如:

1
2
3
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);

此时就会将重复的行保存下来，不会被移除。

对组合查询结果进行排序

SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只 能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

例如:

1
2
3
4
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_price;

这条UNION在最后一条SELECT语句后使用了ORDER BY子句。虽 然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果。

全文本搜索

并非所有引擎都支持全文本搜索：MySQL 支持几种基本的数据库引擎。并非所有的引擎都支持本全文本搜索。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

前文介绍了LIKE关键词和正则表达式，都可以达到检索的目的。但是其有以下的问题：

  • 性能：通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
  • 明确控制：使用通配符和正则表达式匹配，很难（而且并不总 是能）明确地控制匹配什么和不匹配什么。
  • 智能化的结果：虽然基于通配符和正则表达式的搜索提供了非 常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。

所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理 每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进 行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。

使用全文搜索

使用全文搜索，包含以下两步：

  • 索引要被搜索的列，而且要随着数据的改变不断地重新索引（MySQL自动完成）。
  • 使用SELECT子句地Match()和Against()来进行全文搜索。

启用全文搜索支持

启用全文搜索支持，需要在创建表地时候使用FULLTEXT子句来确定要被索引的列，如：

1
2
3
4
5
6
7
8
9
CREATE TABLE productnotes
(
	node_id int NOT NULL AUTO_INCREMENT,
    prod_id char(10) NOT NULL,
    note_date datetime NOT NULL,
    note_text text NULL,
    PRIMARY KEEY(note_id),
    FULLTEXT(note_text)
)ENGINE=MyISAM;

如上，FULLTEXT(note_text)，使用FULLTEXT指定了note_text为要被检索的列。

不要在导入数据时使用FULLTEXT： 更新索引要花时间，虽然 不是很多，但毕竟要花时间。如果正在导入数据到一个新表， 此时不应该启用FULLTEXT索引。应该首先导入所有数据，然 后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而 且使索引数据的总时间小于在导入每行时分别进行索引所需
的总时间）

进行全文搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

例如:

1
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
note_text
Customer complaint: rabbit has been able to detect trap，foodapparently less effective now .
Quantity varies，sold by the sack load. A11 guaranteed to bebright and orange，and suitable for use as rabbit bait.

此SELECT语句检索单个列note_text。由于WHERE子句，一个全 文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本。由于有 两行包含词rabbit，这两个行被返回。

实际上刚才的搜索也可以使用LIKE关键字来解决：

1
SELECT note_text FROM produscts WHERE note_text LIKE '%rabbit%';
note_text
Quantity varies， sold by the sack load. A11 guaranteed to bebright and orange，and suitable for use as rabbit bait.
Customer complaint: rabbit has been able to detect trap，foodapparently less effective now .

这条SELECT语句同样检索出两行，但次序不同（虽然并不总是这种情况）。

其原因在于全文搜索会将具有较高等级的行先返回。而LIKE则是按照数据库中的顺序来进行返回。

这里的优先级一般是目标词越靠前，目标词越多，优先级越高。

比较

全文搜索的优点在于：

  • 其按照优先级排序
  • 检索速度更快

使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面 的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils，但你还想找出可能与你的搜索有关的所有其他行，即使它们不包含词anvils。

这也是查询扩展的一项任务。在使用查询扩展时，MySQL对数据和 索引进行两遍扫描来完成搜索：

  • 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有 行；
  • 其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简 要地解释MySQL如何断定什么有用，什么无用）。
  • 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件， 而且还使用所有有用的词。

例如，首先进行一个简单的全文本搜索，没有查询扩展： 

1
SELECT note_text FROM productsnotes WHERE Match(note_text) Aganinst("anvils");
note_text
Multiple customer returns，anvils failing to drop fast enough orfa1ling backwards on purchaser. Recommend that customer considersusing heavier anvils.

有一行包含词anvils，因此只返回一行。

现在使用查询扩展：

1
SELECT note_text FROM productsnotes WHERE Match(note_text) Aganinst("anvils" WITH QUERY EXPANSION);
note_text
Multiple customer returns，anvils failing to drop fast enough orfa1ling backwards on purchaser. Recommend that customer considersusing heavier anvils.
Customer complaint: Sticks not individua1ly wrapped，too easy tomistakenly detonate all at once. Recommend individual wrapping.Customer complaint: Not heavy enough to generate flying starsaround headof victim. If being purchased for dropping，recommend
ANV02 or ANV03 instead
Please note that no returns wil1 be accepted if safe opened usingexplosives .
Customer complaint: rabbit has been able to detect trap，foodapparently less effective now .
Customer complaint: Circular hole in safe floor can apparently beeasily cut with handsaw.
Matches not included，recommend purchase of matches or detonator(item DTNTR) .

这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（customer
和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它 们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。第三行确实也没有涉及anvils（按它们的产品名）。

布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式（boolean 170
mode）。以布尔方式，可以提供关于如下内容的细节：

  • 要匹配的词
  • 拍排斥的词（如果某行包含这个词，则不返回改行，即使它包含其他指定的词也是如此）
  • 排列提示
  • 表达式分组
  • 其他内容

即使没有FULLTEXT索引也可以使用 布尔方式不同于迄今为 止使用的全文本搜索语法的地方在于，即使没有定义 FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作
（其性能将随着数据量的增加而降低）。

例1：检索包含heavy的句。

1
2
SELECT note_textFROMproductnotes
wHERE Match(note_text) Against( 'heavy' IN BOOLEAN MODE);
note_text
Item is extremely heavy. Designed for dropping，not recommendedfor use with slings，ropes，pulleys， or tightropes.Customer complaint: Not heavy enough to generate flying starsaround head of victim. If being purchased for dropping，recommendANV02 or ANV03 instead.

此全文本搜索检索包含词heavy的所有行（有两行）。其中使用 了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同。

例2：检索包含heavy但不包含任何以rope开头的行：

1
SELECT note_text FROM productsnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
note_text
Customer complaint: Not heavy enough to generate flying stars around head of victim. If being purchased for dropping，recommend ANVO2 or ANV03 instead.

这次只返回一行。这一次仍然匹配词heavy，但-rope*明确地 指示MySQL排除包含rope*（任何以rope开始的词，包括
ropes）的行，这就是为什么上一个例子中的第一行被排除的原因。

下面是所有的全文布尔操作符

布尔操作符 说明
+ 包含，词必须存在
- 排除，词必须不出现
> 包含，而且增加等级值
< 包含，且减少等级值
() 把词组成子表达式（允许这些表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
“” 定义一个短语（与单个词的列表不一样，它匹配整个短语以便排除整个短语）

例如

1：搜索包含词rabbit和bait的行。

1
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

2：搜索匹配包含rabbit和bait中的至少一 个词的行。

1
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

3：搜索匹配短语rabbit bait而不是匹配两个词rabbit和 bait。

1
SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

4：匹配rabbit和carrot，增加前者的等级，降低后者的等级。

1
SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <bait' IN BOOLEAN MODE);

全文搜索的使用说明

  • 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为 那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
  • MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参 阅MySQL文档以了解如何完成此工作）。
  • 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。 因此，MySQL规定了一条50%规则，如果一个词出现在50%以上 的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN
    MODE。
  • 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词 或者不出现，或者至少出现在50%的行中）。
  • 忽略词中的单引号。例如，don’t索引为dont。
  • 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文 本搜索结果。
  • 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。

Powered by Hexo and Hexo-theme-hiker

Copyright © 2019 - 2024 My Wonderland All Rights Reserved.

UV : | PV :