软件开发架构师

MySQL(十)操纵表及全文本搜索

数据库 121 2019-06-13 04:09

一、创建表

MySQL不仅用于表数据操作,还可以用来执行数据库和表的所有操作,包括表本身的创建和处理。

创建表一般有如下两种方式:

使用具有交互式创建和管理表的工具;

直接使用MySQL语句操纵表;

1、表创建基础

使用程序创建表,可使用SQL中的create table语句,需要以下两个信息:

新表的名字,在关键字create table后给出;

表列的名字和定义,用逗号分隔;

例如:

create table usertables

(

user_id              int          not  null auto_increment,

user_name            char(50)     null,

user_address         char(50)     null,

user_city            char(50)     null,

user_email           char(50)     null,

user_mobile          char(50)     null,

primary key (user_id)

)engine=InnoDB;

这条SQL语句中,表名usertables紧跟在create table之后,实际的表定义(所有列)括在圆括号内,各列间用逗号分隔,表的主键用primary key指定为user_id。

PS:创建新表时,指定的表名必须不存在(如果只想在一个表不存在时创建它,应在表名前给出if not exists:这样做不检查表模式是否与打算创建的表模式匹配,只检查表名是否存在)。

2、使用null值

null就是没有值或者缺值;允许null值的列也允许在插入行时不给出该列的值,不允许null值的列不接受该列没有值的行(插入或更新行时,该列必须有值);

每个表列或者是null列,或者是not null列,这种状态在创建时由表的定义规定,比如上面的例子;或者表中混合存在null和not null列。

PS:null为默认值,如果不指定not null,则认为指定的是null。

3、主键

主键值必须唯一,即表中的每个行必须具有唯一的主键值。如果主键使用单个列,则它的值必须唯一;如使用多个列,则这些列的组合值必须唯一。

创建表时,主键都用类似primary key的语句定义:primary key(column);创建由多个列组成的主键,应该以逗号分隔的列表给出各列名(主键可以在创建表时定义,或者在创建表之后定义)。

4、自动增量

例如:cust_id  int  nut  null  auto_increment,

auto_increment告诉MySQL,本列每当增加一行时自动增量;每次执行一个insert操作时,MySQL自动对该列增量,给该列赋予下一个可用的值;

每个表只允许一个auto_increment列,而且它必须被索引(比如,通过使它成为主键)

last_insert_id:此函数指示MySQL返回最后一个auto_increment值,然后可将它应用于后续MySQL语句。

5、指定默认值

如果在插入行时没有给出值,MySQL允许指定此时使用的默认值;默认值用create table语句的列定义中的default关键字指定,例如:

create table usertables

(

user_id              int          not  null,

user_name            char(50)     not  null,

user_address         char(50)     not   null,

user_city            char(50)     not   null  default shanghai,

user_email           char(50)     not   null,

user_mobile          char(50)     not   null,

primary key (user_id)

)engine=InnoDB;

这条语句中创建包含组成user信息的表,user_city列包含user的所在城市,该列的描述添加了default shanghai,在未给出城市的情况下使用上海(MySQL不允许使用函数作为默认值,它只支持常量)。

6、引擎类型

MySQL有一个具体管理和处理数据的内部引擎,使用create table语句时,该引擎具体创建表;使用select语句或进行其他数据库处理时,该引擎内部处理请求(引擎隐藏在DBMS内,不需要过多关注)。

MySQL相比于其他DBMS的区别在于,它具有多种引擎;因为各个引擎有不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。

如果省略engine=语句,则使用默认引擎(很可能是MYISAM),但并不是所有情况都这样,所以,engine语句很重要!

常见的几种搜索引擎:

InnoDB:一个可靠的事务处理引擎,不支持全文本搜索;

MEMORY:在功能上等同于MyISAM,数据存储在内存(不是磁盘),速度很快(特别适合临时表);

MyISAM:一个性能极高的引擎,支持全文本搜索,但不支持事务处理;

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

 

二、更新表

为更新表定义,可以使用alter table语句(很少使用);为了更新表结构,必须给出如下信息:

alter table之后给出要更改的表名(该表必须存在,否则会报错)

所做更改的列表;

例如:alter table usertable add user_country char(50);

这条SQL语句给usertable表增加了一个user_country的列,必须明确其数据类型。

删除刚添加的列,可以这样:

alter table usertable drop column user_country;

PS:alter table的一种常见用途是定义外键。

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

用新的列布局创建一个表;

使用insert  select语句从旧表复制数据到新表,如果有必要,可使用转换函数和计算字段;

检验包含所需数据的新表;

重命名旧表(如果确定,可以删除它);

用旧表原来的名字重命名新表;

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

 

三、删除表

删除表(删除整个表而不是其内容),使用drop table语句,例如:

drop table usertable;

这条语句删除usertable表(假设它存在);删除表没有确认,也不能撤销,执行这条语句将永久删除该表。

还可以重命名表,例如:

rename table usertable to usertablebase;

这条语句用rename table语句重命名一个表;还可以对多个表进行重命名,每个表之间用逗号间隔。

 

四、全文本搜索

1、启用索引

MySQL支持几种基本的数据库引擎,MySQL最长用的两个引擎为:MyISAM和InnoDB:

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

InnoDB支持事务和外键,和MyISAM各有优劣;

与全文本搜索功能类似的有通配符正则表达式匹配,但性能较低,通常会匹配表的所有行,而且这些搜索极少使用表索引,不能做到明确控制,且返回的结果不智能化

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

一般在创建表时启用全文本搜索(必须索引被搜索的列),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;

这条create table语句定义表productnotes并列出它所包含的列即可;其中MySQL根据子句fulltext对(note_text)进行索引。

在定义之后,MySQL自动维护该索引;在增加、删除、或更新行时,索引随之自动更新。

PS:不要再导入数据时使用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作为搜索文本。

PS:传递给match()的值必须与fulltext()定义中的相同;如果指定多个列,则必须列出它们(次序正确);除非使用binary方式,否则全文本搜索不区分大小写(上面的例子没有使用该方式)。

   like子句具有和全文本搜索相同的功能,但区别在于:全文本搜索的特点是对结果进行排序,具有较高等级的行先返回(如果排序多个搜索项,则包含多数匹配词的行将具有更高的优先级)。

3、查询扩展

作用:用来设法放宽所返回的全文本搜索结果的范围(MySQL对数据和索引进行两遍扫描来完成搜索)。

检索过程:

进行一个基本的全文本搜索,找出与搜索条件匹配的所有行;

MySQL检查这些匹配行并选择所有有用的词(将会简要的解释MySQL如何断定什么有用什么无用);

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

用法:where子句中against指定的搜索表达式后跟with query erpansion;

比如:where match(note_text) against('rabbit' with query erpansion);

PS:表中的行越多(行的文本越多),实用查询扩展返回的结果越好。

4、布尔文本搜索

MySQL还支持另一种全文本搜索方式,称为布尔方式(boolean mode);使用布尔方式需要提供一下条件:

要匹配的词;

要排斥的词(如果某行包含这个词,则不返回,即使它包含其他指定的词也是如此);

排列提示(指定某些词比其他词重要,更重要的词返回的等级更高);

表达式分组;

其他的内容。

PS:即使没有fulltext索引也可以使用布尔方式(但这种方式很缓慢)。

例如:select note_text

     from productnotes

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

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

全文本布尔操作符列表如下:

下面列举几个例子,说明这些操作符的使用:

这个搜索匹配包含次rabbit和biat的行:

select note_text

from froductnotes

where match(note_text) against('+rabbit +bait' in boolean mode);

没有指定操作符,这个搜索匹配包含rabbit和bait中的至少一个词的行:

select note_text

from froductnotes

where match(note_text) against('rabbit bait' in boolean mode);

这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait:

select note_text

from froductnotes

where match(note_text) against('"rabbit  bait"' in boolean mode);

PS:在布尔方式中,不按照等级降序排序返回的行

5、全文本搜索使用说明

在索引全文本数据时,短词被忽略且从索引中删除(短词定义为3个或3个以下字符的词:如果需要可以更改);

MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本搜索时总被忽略(如果需要,可以覆盖这个列表);

MySQL50%规则:如果一个词出现在50%以上的行中,则将它作为一个非用词忽略;50%规则不用于in boolean mode;

如果表中的行数少于3行,则全文本搜索不返回结果;

忽略词中的单引号,例如don't索引为dont;

不具有词分隔符的语言不能恰当的返回全文本搜索结果;

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

 

文章评论