标签： mysql

MySQL创建全文索引

hotboy920
使用索引是数据库性能优化的必备技能之一。在MySQL数据库中，有四种索引：聚集索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FULLTEXT INDEX)。

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用“分词技术“等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里，我们就不追根究底其底层实现原理了，现在我们来看看在MySQL中如何创建并使用全文索引。

在MySQL中，创建全文索引相对比较简单。例如，我们有一个文章表(article)，其中有主键ID(id)、文章标题(title)、文章内容(content)三个字段。现在我们希望能够在title和content两个列上创建全文索引，article表及全文索引的创建SQL语句如下：

--创建article表
CREATE TABLE article (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	title VARCHAR(200),
	content TEXT,
	FULLTEXT (title, content) --在title和content列上创建全文索引
);

上面就是在创建表的同时建立全文索引的SQL示例。此外，如果我们想要给已经存在的表的指定字段创建全文索引，同样以article表为例，我们可以使用如下SQL语句进行创建：

--给现有的article表的title和content字段创建全文索引
--索引名称为fulltext_article
ALTER TABLE article
ADD FULLTEXT INDEX fulltext_article (title, content)

在MySQL中创建全文索引之后，现在就该了解如何使用了。众所周知，在数据库中进行模糊查询是使用LIKE关键字进行查询，例如：

复制
SELECT * FROM article WHERE content LIKE '%查询字符串%'

那么，我们使用全文索引也是这样用的吗？当然不是，我们必须使用特有的语法才能使用全文索引进行查询。例如，我们想要在article表的title和content列中全文检索指定的查询字符串，可以如下编写SQL语句：

复制
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

强烈注意：MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。本站将会在后续文章中对Sphinx以及Coreseek进行介绍。

备注1：目前，使用MySQL自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。

备注2：如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

mysql之模糊查询的方法

hotboy920

1.主要有两种方式：

like与SQL通配符  和  正则表达式模糊查询。

2.like与SQL ，必须使用like关键字

  • SQL的通配符如下：

% 百分号： 替代一个或者多个字符

_  下划线：仅替代一个字符

[字符列]  ：字符列中任何一个单一字符

[^字符列] 或者[!字符列]:  不在字符列中的任何一个单一字符

  • 用法如下

select 字段列表 from 表名 where 字段 like ‘Ne%’;     表示：查询以Ne开头的数据

select 字段列表 from 表名 where 字段 like ‘%Ne%’;  表示：查询含有Ne的数据

select 字段列表 from 表名 where 字段 like ‘L_ve’;     表示：查询含有 L+某一个字符+ve的数据

select 字段列表 from 表名 where 字段 like ’[abc]%’;  表示：查询a/b/c开头的数据

3.正则表达式模糊查询 必须使用regexp关键字

牢记如下规则：

点 . , 尖角符  ^ , 美元符 $ ,  字符集 [ ] ,  逻辑或 |  ， 星号 * ， 加号 + ， 问号 ？，大括号 { }

  • 语法如下：

select 字段列表 from 表名 where 字段 [not] regexp [binary] ‘正则表达式’;

  • 用法如下：

select 字段列表 from 表名 where 字段 regexp ‘j.’ ;   表示：查询 j 开头且为两个字符的数据

select 字段列表 from 表名 where 字段 regexp ‘158[0-9]{9}’;  表示：查询 158开头，11位的电话号码

。。。。。。

三、总结

  • 正则表达式的模式匹配比like 运算符的模式匹配更加强大、灵活。
实例：
下面介绍mysql中模糊查询的四种用法：

1，%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘%三%’
将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。
另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件
SELECT * FROM [user] WHERE u_name LIKE ‘%三%’ AND u_name LIKE ‘%猫%’
若使用 SELECT * FROM [user] WHERE u_name LIKE ‘%三%猫%’

虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。

2，_： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：
比如 SELECT * FROM [user] WHERE u_name LIKE ‘_三_’
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的；

再比如 SELECT * FROM [user] WHERE u_name LIKE ‘三__’; 只找出“三脚猫”这样name为三个字且第一个字是“三”的；

3，[ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’ 将找出“张三”、“李三”、“王三”（而不是“张李王三”）；
如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE ‘老[1-9]’ 将找出“老1”、“老2”、……、“老9”；

4，[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘[^张李王]三’ 将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
SELECT * FROM [user] WHERE u_name LIKE ‘老[^1-4]’; 将排除“老1”到“老4”，寻找“老5”、“老6”、……

5，查询内容包含通配符时
由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数：
function sqlencode(str) str=replace(str,”‘;”,”‘;’;”)
str=replace(str,”[“,”[[]”) ‘;此句一定要在最先 str=replace(str,”_”,”[_]”) str=replace(str,”%”,”[%]”) sqlencode=str end function

Mysql模糊查询正常情况下在数据量小的时候，速度还是可以的，但是不容易看出查询的效率，在数据量达到百万级，千万级的甚至亿级时 mysql查询的效率是很关键的，也是很重要的。

一、一般情况下 like 模糊查询的写法：前后模糊匹配

这个SQL语句，如果用explain解释的话，我们很容易就能发觉它是没有走索引搜索，而是对全表进行了扫描，这显然是很慢的，还有卡库的可能。

如果将上面的SQL语句改成下面的写法：

就是把‘keyword’前面的%去掉了，这样的写法用explain解释看到，SQL语句使用了索引，这样就可以大大的提高查询的效率。

有时候，我们在做模糊查询的时候，并非要想查询的关键词都在开头，所以如果不是特别的要求，”keywork%”并不合适所有的模糊查询。

二、模糊查询高效的方法：

1、LOCATE（’substr’,str,pos）方法

解释：返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。如果pos存在，返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。

实例：

备注：keyword是要搜索的内容，business为被匹配的字段，查询出所有存在keyword的数据

2、POSITION(‘substr’ IN `field`)方法

其实我们就可以把这个方法当做是locate（）方法的别名，因为它和locate（）方法的作用是一样的。

实例：

3、INSTR(`str`,’substr’)方法

格式：

实例：

除了上述的方法外，还有一个函数FIND_IN_SET，这个方法比较特殊，他所查询的必须要是以“，”分隔开。

4、FIND_IN_SET(str1,str2):

返回str2中str1所在的位置索引，其中str2必须以”,”分割开。

格式：

实例：

MySQL出现Incorrect integer value: ” for column ‘id’ at row 1解决方法

hotboy920

用Navicat for MySQL还原数据库备份时，出现Incorrect integer value: ” for column ‘id’ at row 1的错误;

网上查资料发现5以上的版本如果是空值应该要写NULL
这种问题一般mysql 5.x上出现。

使用Select version();查看，

我用的是mysql5.0.37，而创建备份的MySQL数据库版本是5.6

官方解释说：得知新版本mysql对空值插入有”bug”,
要在安装mysql的时候去除默认勾选的enable strict SQL mode
那么如果我们安装好了mysql怎么办了，解决办法是更改mysql中的配置 my.ini

my.ini中查找sql-mode，

默认为sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，

将其修改为sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，重启mysql后即可

那么如果是虚拟主机或者是空间怎么办了。如果你能让空间商帮你改那是最好。如果不能，那你就只能程序改改。都规范一点。空值就写 null

用mysql建了个表：
create table file_table ( id int auto_increment primary key, filename varchar(50) not null,filepath varchar(50) not null,update_time date);
  • 1

然后用mysql写如下的插入语句:

$query = "Insert into file_table". " values ('','$name','$filepath',now())";
  • 1

出现如下错误：
Incorrect integer value: ” for column ‘id’ at row 1

解决办法：
查了一下是因为用了高版本的mysql导致的，发现高版本的mysql如果是空值应该要写NULL或者0，所以插入语句应写成：

$query = "Insert into file_table". " values (NULL,'$name','$filepath',now())";
  • 1

或者

$query = "Insert into file_table". " values (0,'$name','$filepath',now())";

MySQL Installer is running in Community mode 的解决办法

hotboy920

 更新时间：2018年06月15日 15:15:05   投稿：mdxy-dxy
这篇文章主要介绍了MySQL Installer is running in Community mode 的解决办法,需要的朋友可以参考下

今天在运行登录远程桌面的时候发现了这个提示

关于 “ MySQL Installer is running in Community mode ” 的解决办法

在项目开发完成后，需求是将项目部署到云服务器上，其中使用到了MySQL服务器，我们使用的是基于最新版本的MySQL服务，在安装完成后，系统能正常运行，但是第二天出现了如下一个提示框，如下图：

给个人人都看得懂的如下图：

解决办法:

这个是新版本MySQL服务自带的一个定时任务，每天23:59:59执行的任务，我们只需要在本地系统的“任务计划程序”中将这个定时任务干掉就OK了。
开始 -> 在 “ 搜索程序和文件 ” 框中，输入 “ 任务 ” 然后回车，然后选择 “ 任务计划程序 ” ，如下图：

打开 任务计划程序 Dialog后，在 计划程序库中找到 MySQL，在这儿就可以看到，MySQL的定时任务了，在右下角工具栏中选择 “ 禁用 ” 该任务计划即可，如下图：

温馨提示：尽量不要直接删掉这个MySQL定时服务器，如果到后期需要业务扩张的时候，还能用到，可以仿照到这个进行定时任务的创建工作，这个是非常用的。

第二种方法：

解决方案：

1、先关闭错误框

2、打开控制面板  -> 使用小图标查看 -> 打开管理工具 -> 双击打开任务计划程序 ->（展开并找到Mysql的定时Installer） -> 右键  -> 禁用

如图：

打开控制面板

使用小图标查看

双击打开任务计划程序

（展开并找到Mysql的定时Installer） -> 右键  -> 禁用

这篇文章就介绍到这了，主要就是需要禁止mysql的任务计划即可。