Menu
Woocommerce Menu

忽然发现会报一个date类型的字段的默认值错误,但也随之返回在检索结果中

0 Comment


在给一个表添加字段的时候,忽然发现会报一个date类型的字段的默认值错误,郁闷~

如何在MySQL中获得更好的全文搜索结果  
作者: Techrepublic.com.com 
2006-04-03 11:14:53 

一、概述
     
MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索,可以利用全文索引来提高匹配的速度。

在我的理解中mysql本身就对中文支持不好,如果我们再在操作时有一点小不标准那么查出来的中文就更乱了,下面我来给大家介绍mysql中文模糊查找不精确解决办法

经过排查,原来是MySQL的配置问题,在wamp下,MySQL
5.7里是没有设置 SQL_MODE 的。

很多互联网应用程序都提供了全文搜索功能,用户可以使用一个词或者词语片断作为查询项目来定位匹配的记录。在后台,这些程序使用在一个SELECT查询中的LIKE语句来执行这种查询,尽管这种方法可行,但对于全文查找而言,这是一种效率极端低下的方法,尤其在处理大量数据的时候。

二、语法
      MATCH (col1,col2,…) AGAINST (expr [search_modifier])
      search_modifier: { IN BOOLEAN MODE | WITH QUERY EXPANSION }
      
      例如:SELECT * FROM tab_name WHERE MATCH (‘列名1,列名2…列名n’)
AGAINST(‘词1 词2 词3 … 词m’);
    
      即:MATCH 相当于要匹配的列,而 AGAINST 就是要找的内容。  
      这里的table需要是MyISAM类型的表,col1、col2
必须是char、varchar或text类型,在查询之前需要在 col1 和 col2
上分别建立全文索引(FULLTEXT索引)。

例如,通过“标题”对新闻库进行检索,关键字可能包含是中英文,如下SQL语句:

1.my.ini文件中找到 [mysqld]

MySQL针对这一问题提供了一种基于内建的全文查找方式的解决方案。在此,开发者只需要简单地标记出需要全文查找的字段,然后使用特殊的MySQL方法在那些字段运行搜索,这不仅仅提高了性能和效率(因为MySQL对这些字段做了索引来优化搜索),而且实现了更高质量的搜索,因为MySQL使用自然语言来智能地对结果评级,以去掉不相关的项目。

三、检索方式
     1、自然语言检索: IN NATURAL LANGUAGE MODE

select id,title,name from achech_com.news where title like ‘%a%’

2.如果没有SQL_MODE,就添加,有就修改一下

这篇文章将向您讲述在MySQL中如何进行全文搜索。

     2、布尔检索: IN BOOLEAN MODE
         
剔除一半匹配行以上都有的词,譬如说,每个行都有this这个字的话,那用this去查时,会找不到任何结果,这在记录条数特别多时很有用,
         
原因是数据库认为把所有行都找出来是没有意义的,这时,this几乎被当作是stopword(中断词);但是若只有两行记录时,是啥鬼也查不出来的,
          因为每个字都出现50%(或以上),要避免这种状况,请用IN BOOLEAN
MODE。

返回的结果,某些title字段确定带了“a”关键字,而有些则只有中文,但也随之返回在检索结果中。

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

1、设置基本表格

       ● IN BOOLEAN MODE的特色: 
          ·不剔除50%以上符合的row。 
          ·不自动以相关性反向排序。 
          ·可以对没有FULLTEXT index的字段进行搜寻,但会非常慢。 
          ·限制最长与最短的字符串。 
          ·套用Stopwords。
 
       ● 搜索语法规则:
         +   一定要有(不含有该关键词的数据条均被忽略)。 
         –   不可以有(排除指定关键词,含有该关键词的均被忽略)。 
         >   提高该条匹配数据的权重值。 
         <   降低该条匹配数据的权重值。
         ~   将其相关性由正转负,表示拥有该字会降低相关性(但不像 –
将之排除),只是排在较后面权重值降低。 
         *   万用字,不像其他语法放在前面,这个要接在字符串后面。 
         ” ” 用双引号将一段句子包起来表示要完全相符,不可拆字。

解决方法,使用 BINARY 属性进行检索:

3.重启MySQL;

从创建例子表格开始,使用以下的SQL命令:

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘+apple -banana’ IN BOOLEAN MODE);
         + 表示AND,即必须包含。-
表示NOT,即必须不包含。即:返回记录必需包含 apple,且不能包含 banner。

select id,title,name from achech_com.news where binary title like ‘%a%’

mysql> CREATE TABLE reviews (id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT, data TEXT);

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘apple banana’ IN BOOLEAN MODE);
        
apple和banana之间是空格,空格表示OR。即:返回记录至少包含apple、banana中的一个。

返回的结果较之前正确,但英文字母区分大小写,故有时在检索如“Achech”及“achech”的结果是不一样的。知道了使用
BINARY 属性可以解决前面这个问题,再看看 MySQL 支持的UCASE 及 CONCAT
函数,其中 UCASE
是将英文全部转成大写,而CONCAT函数的作用是对字符进行连接,以下是完全解决后的SQL
语句:

以上命令创建了一个简单的音乐专集资料库(主要是整段的文字),然后向这个表格中添加一些记录:

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘+apple banana’ IN BOOLEAN MODE);
        
返回记录必须包含apple,同时banana可包含也可不包含,若包含的话会获得更高的权重。

select id,title,name from achech_com.news where binary ucase(title)
like concat(‘%’,ucase(‘a’),’%’)

mysql> INSERT INTO `reviews` (`id`, `data`) VALUES

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘+apple ~banana’ IN BOOLEAN MODE);
         ~
是我们熟悉的异或运算符。返回记录必须包含apple,若也包含了banana会降低权重。
         但是它没有 +apple -banana
严格,因为后者如果包含banana压根就不返回。

检索的步骤是先将属性指定为 BINARY ,以精确检索结果,而被 like 的
title内容存在大小写字母的可能,故先使用 ucase
函数将字段内容全部转换成大写字母,然后再进行 like 操作,而 like
的操作使用模糊方法,使用
concat的好处是传进来的可以是直接的关键字,不需要带“%”万用符,将“’a’”直接换成你的变量,在任何语言下都万事无忧了。当然你也可以这么写:

(1, ‘Gingerboy has a new single out called Throwing Rocks. It’s great!’);

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘+apple +(>banana <orange)’ IN BOOLEAN MODE);
         返回必须同时包含“apple banana”或者必须同时包含“apple
orange”的记录。
         若同时包含“apple banana”和“apple orange”的记录,则“apple
banana”的权重高于“apple orange”的权重。

select id,title,name from achech_com.news where binary ucase(title)
like ucase(‘%a%’)

mysql> INSERT INTO `reviews` (`id`, `data`) VALUES 

    3、查询扩展检索: WITH QUERY EXPANSION

另一种办法,性能更高,MySQL全文检索

(2, ‘Hello all, I really like the new Madonna single. 

四、MySQL全文检索的条件限制
    
1、在MySQL5.6以下,只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索。
     2、相应字段建立FULLTEXT索引

  1. MySQL
    4.x版本及以上版本提供了全文检索支持,但是表的存储引擎类型必须为MyISAM,以下是建表SQL,注意其中显式设置了存储引擎类型

One of the hottest tracks currently playing…I’ve been listening to it all day’);

五、与全文检索相关的系统变量:
     ft_min_word_len = 全文检索的最小许可字符(默认4,通过 SHOW
VARIABLES LIKE ‘ft_min_word_len’ 可查看),
     中文通常是两个字就是一个词,所以做中文的话需要修改这个值为2最好。

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;   其中FULLTEXT(title, body)
给title和body这两列建立全文索引,之后检索的时候注意必须同时指定这两列。

mysql> INSERT INTO `reviews` (`id`, `data`)

六、总结事项
     1、预设搜寻是不分大小写,若要分大小写,columne 的 character
set要从utf8改成utf8_bin。

 

VALUES (3, ‘Have you heard the new band Hotter Than Hell?

     2、预设 MATCH…AGAINST 是以相关性排序,由高到低。

  1. 插入测试数据

They have five members and they burn their instruments when they play in concerts. 

     3、MATCH(title, content)里的字段必须和FULLTEXT(title,
content)里的字段一模一样。
          如果只要单查title或content一个字段,那得另外再建一个
FULLTEXT(title) 或
FULLTEXT(content),也因为如此,MATCH()的字段一定不能跨table,但是另外两种搜寻方式好像可以。
    
    
4、MySQL不支持中文全文索引,原因很简单:与英文不同,中文的文字是连着一起写的,中间没有MySQL能找到分词的地方,截至目前MySQL5.6版本是如此,但是有变通的办法,就是将整句的中文分词,并按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。

INSERT INTO articles (title,body) VALUES
    (‘MySQL Tutorial’,’DBMS stands for DataBase …’),
    (‘How To Use MySQL Well’,’After you went through a …’),
    (‘Optimizing MySQL’,’In this tutorial we will show …’),
    (‘1001 MySQL Tricks’,’1. Never run mysqld as root. 2. …’),
    (‘MySQL vs. YourSQL’,’In the following database comparison …’),
    (‘MySQL Security’,’When configured properly, MySQL …’);

These guys totally rock! Like, awesome, dude!’);

七、实验部分

  1. 全文检索测试

验证数据的正确录入:

◆ 步骤1 配置my.ini,在my.ini末尾添加如下:

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST (‘database’);   检索结果如下:

mysql> SELECT * FROM reviews;

# 修改全文检索的最小许可字符为2个字符或汉字
ft_min_word_len = 2

5        MySQL vs. YourSQL        In the following database comparison
…1        MySQL Tutorial               DBMS stands for DataBase …  
说明全文匹配时忽略大小写。

+—-+——————————————–+

完成后“重启 MySQL 服务”,并用 SHOW VARIABLES LIKE ‘ft_min_word_len’
查询下是否得到了正确的结果值为2,如下图:
图片 1

 

| id | data                                       |

◆ 步骤2 创建数据库(视情况可跳过此步)
CREATE DATABASE search DEFAULT CHARACTER SET utf8 COLLATE
utf8_general_ci;

  1. 可能遇到的困扰

+—-+——————————————–+

◆ 步骤3 创建数据表
CREATE TABLE `zzx_articles` (
`id` int(10) unsigned NOT NULL auto_increment, 
`title` char(254) default NULL COMMENT ‘标题’,
`content` text COMMENT ‘内容’,
`author` char(60) default NULL COMMENT ‘作者’,
`title_fc` char(254) default NULL COMMENT ‘标题的分词’,
`content_fc` text COMMENT ‘内容的分词’,
PRIMARY KEY  (`id`),
FULLTEXT KEY `zzx_title_fc` (`title_fc`),
FULLTEXT KEY `zzx_content_fc` (`content_fc`),
FULLTEXT KEY `zzx_title_con_fc` (`title_fc`,`content_fc`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

   到目前为止都很顺利,但是如果检索SQL改为下面会怎样呢?

|  1 | Gingerboy has a new single out called …  |

# 如果后期需要添全文加索引可以用如下语句:
alter table `zzx_articles` add fulltext
zzx_title_fc(`title_fc`); 
alter table `zzx_articles` add fulltext
zzx_con_fc(`content_fc`); 
alter table `zzx_articles` add fulltext
zzx_title_con_fc(`title_fc`,`content_fc`);

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST (‘well’);  
结果让人大跌眼镜,开始我也困惑了许久,后来去网上查了下才知道原来是这么回事:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图