• 【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数据库引擎中支持全文本搜索。

  • 相关阅读:
    ASP.NET 2.0的页面缓存功能介绍
    我对针对接口编程的浅解
    接口和抽象类的区别
    面向接口编程到底有什么好处
    泛型编程是什么
    方法的重写、重载及隐藏
    基于事件的编程有什么好处
    Socket Remoting WebService对比
    技术讲座:.NET委托、事件及应用兼谈软件项目开发
    ny589 糖果
  • 原文地址:https://www.cnblogs.com/mind18/p/14655885.html
Copyright © 2020-2023  润新知