【MySQL】操纵表及全文本搜索

一、创建表

使用create table语句，必须包括的信息：新表的名字、表列的名字和定义。如下：

create table usertables(
    user_id      int      not null auto_increment,
    user_name    char(50) not null,
    user_address char(50) not null default sichuan,
    primary key (user_id)
)engine=InnoDB;

注意：创建新表时，指定的表名必须不存在，否则将出错。如果只想在一个表不存在时创建它，应在表名前给出if not exists，查看表名是否存在。

1. null：就是没有值或者缺值。

  • 不允许null值的列，插入或更新行时该列必须有值。

  • 每个表列或者是null列，或者是not null列，这种状态在创建时由表的定义规定。

  • 注意：null为默认值，如果不指定not null，则认为指定的是null。

2. 主键：用primary key指定

  • 主键值必须唯一。 如果主键使用单个列，则它的值必须唯一；如使用多个列，则这些列的组合值必须唯一。

  • 创建由多个列组成的主键：primary key (order_num, order_item)

  • 主键中只能使用not null列，null值的列不能作为唯一标识。

3. 自动增量：cust_id int not null auto_increment

  • 每次执行一个insert操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。

  • 每个表只允许一个auto_increment列，且它必须被索引（如，通过使它成为主键）

  • last_insert_id：返回最后一个auto_increment值，然后可将它应用于后续MySQL语句。如：select last_insert_id()

4. 指定默认值：使用default指定

  • MySQL不允许使用函数作为默认值，它只支持常量。

5. 引擎类型

  • 一般DBMS都有一个具体管理和处理数据的内部引擎。使用create table语句时，该引擎具体创建表；使用select语句或进行其他数据库处理时，该引擎内部处理请求。

  • 相比于其他DBMS，MySQL具有多种引擎，它们具有各自不同的功能和特性。为不同的任务选择正确的引擎能获得良好的功能和灵活性。

  • 如果省略engine=语句，则使用默认引擎（很可能是MYISAM），但并不是所有情况都这样。所以，engine=语句很重要！

常见的几种搜索引擎：

  • InnoDB：一个可靠的事务处理引擎，不支持全文本搜索；

  • MEMORY：在功能上等同于MyISAM。由于数据存储在内存（不是磁盘），速度很快（特别适合临时表）；

  • MyISAM：一个性能极高的引擎，支持全文本搜索，但不支持事务处理；

注意：引擎类型可以混用。但缺陷在于：外键不能跨引擎，即：使用一个引擎的表不能引用具有使用不同引擎的表的外键。

二、更新表

理想状态下，当表中存储数据以后，该表就不应该再被更新。

为更新表定义，可以使用alter table语句。必须给出如下信息：要更改的表名、所做更改的列表。（该表必须存在，否则会报错）

alter table usertable add user_country char(50); 增加列
alter table usertable drop column user_country; 删除列

alter table的一种常见用途：定义外键。

alter table products
add constraint fk_products_vendors
foreign key (vend_id) peferences vendors(vend_id);

注意：使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销。

如果要对比较复杂的表进行更改，一般需要手动删除过程，涉及的步骤如下：

  1. 用新的列布局创建一个表；

  2. 使用insert select从旧表复制数据到新表。若有必要，可使用转换函数和计算字段；

  3. 检验包含所需数据的新表；

  4. 重命名旧表（如果确定，可以删除它）；

  5. 用旧表原来的名字重命名新表；

  6. 根据需要，重新创建触发器、存储过程、索引和外键。

三、删除/重命名表

删除表：删除整个表而不是其内容，使用drop table语句，例如：drop table usertable；。删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

重命名表：rename table usertable to usertablebase；

四、全文本搜索

MySQL最常用的两个引擎为：

  • MyISAM：支持全文本搜索，查询效率高；但局限在于不支持事务和外键；

  • InnoDB：支持事务和外键，和MyISAM各有优劣；

与全文本搜索功能类似的有：通配符、正则表达式匹配，存在的限制有：

  • 性能：通常会匹配表中所有行，且极少使用索引；

  • 很难做到明确地控制匹配什么和不匹配什么；

  • 返回的结果不智能化。如：一个词的搜索会返回包含该词的所有行，不区分包含单个匹配的行和包含多个匹配的行；

在使用全文本搜索时，mysql不需要分别查看每个行，不需要分析和处理每个词，只需索引被搜索的列（随着数据的改变会不断地重新索引）。

1. 启用全文本搜索支持（索引）

一般在创建表时启用全文本搜索（必须索引被搜索的列），create table语句接受fulltext子句，它给出被索引列的一个逗号分隔的列表；如下：

CREATE TABLE productnotes
(
    note_id      int         not null auto_incerement,
    prod_id      char(10)    not null,
    note_date    datetime    not null,
    note_text    text        null,
    primary key(note_id),
    fulltext(note_text)
)engine = MyISAM;

这条语句MySQL根据子句fulltext(note_text)对列note_text进行索引。定义之后，MySQL自动维护该索引；在增加、删除、或更新行时，索引随之自动更新。

注意：不要再导入数据时使用fulltext，因为更新索引要花时间。应该先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快的导入数据。

2. 进行全文本搜索（索引）

启用索引后，使用match()和against()执行全文本搜索。match()指定被搜索的列，against()指定要使用的搜索表达式。如下：

select note_text
from productnotes
where match(note_text) against('rabbit');

这条select语句检索单个列note_text，由于where子句，一个全文本搜索被执行：match(note_text)指示MySQL针对指定的列进行搜索，against('rabbit')指定rabbit作为搜索文本。

注意：传递给match()的值必须与fulltext()定义中的相同；如果指定多个列，则必须列出它们且次序正确。全文本搜索不区分大小写，除非使用binary方式。

like子句具有和全文本搜索相同的功能，但区别在于：全文本搜索的特点是对结果进行排序，具有较高等级的行先返回。

3. 查询扩展

作用：用来设法放宽所返回的全文本搜索结果的范围。

使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索。检索过程如下：

  • 先进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；

  • 其次，MySQL检查这些匹配行并选择所有有用的词（将会简要的解释MySQL如何断定什么有用什么无用）；

  • 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，还要用所有有用的词。

方法：where子句中against指定的搜索表达式后跟with query erpansion。如where match(note_text) against('rabbit' with query erpansion)

tips：表中的行越多（行的文本越多），实用查询扩展返回的结果越好。

4. 布尔文本搜索

MySQL还支持另一种全文本搜索方式，称为布尔方式（boolean mode）；使用布尔方式可以提供如下细节：

  • 要匹配的词；

  • 要排斥的词（如果某行包含这个词，则不返回，即使它包含其他指定的词也是如此）；

  • 排列提示（指定某些词比其他词重要，更重要的词返回的等级更高）；

  • 表达式分组；

  • 其他的内容。

注意：即使没有fulltext索引也可以使用布尔方式（但这种方式非常缓慢）。如下：

select note_text
from productnotes
where match(note_text) against('heavy -rope*' in bollean mode);

此全文本搜索检索包含词heavy的所有行，其中使用了in boolean mode以及布尔操作符，-rope指示MySQL排除包含rope（任何以rope开始的词）的行。

全文本布尔操作符：

注意：在布尔方式中，不按照等级降序排序返回的行。

5. 全文本搜索使用说明

  1. 在索引全文本数据时，短词被忽略且从索引中删除。短词定义为3个或3个以下字符的词（如果需要可以更改）；

  2. MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本搜索时总被忽略（如果需要，可以覆盖这个列表）；

  3. MySQL50％规则：如果一个词出现在50％以上的行中，则将它作为一个非用词忽略。50％规则不用于in boolean mode；

  4. 如果表中的行数少于3行，则全文本搜索不返回结果；

  5. 忽略词中的单引号，如don't索引为dont；

  6. 不具有词分隔符的语言不能恰当的返回全文本搜索结果，如日语和汉语；

  7. 仅在MyISAM数据库引擎中支持全文本搜索。

posted @ 2021-04-13 23:22  --D  阅读(128)  评论(0)  编辑  收藏  举报