MYSQL入门学习之一：基本操作
1、登录数据库 www.2cto.com
命令：mysql -u username –p （mysql -h主机地址 -u用户名 －p用户密码）
描述：输入命令回车，即可输入密码了。密码验证通过即可进入数据库了。
2、查看数据状态
命令：show database;
描述：该命令会显示现存的所有数据库。
3、建立与删除数据库
命令：create database db;
描述：db可以替换为要建立数据库的名称。
命令：drop database db;
描述：该命令彻底删除名称为db的数据库，不论数据库内是否存有数据，所以切记谨慎使用。
4、选用某个数据库
命令：use db；
描述：该命令使的当前会话进入db命名的数据库，可以进行建立修改删除表格和插入数据等操作。
命令：select database();
描述：查看当前正在使用的数据库名称
5、查看当前系统状态
命令：select now();
描述：得知现在的日期和时间
命令：select user();
描述：得知当前登录mysql的用户
命令：select version();
描述：得知当前mysql的版本
6、创建表格 www.2cto.com
命令：create table tablename (filed1,filed2,filed3,...,filedN);
描述：建立名称为tablename的表格，字段名称分别由filedN指定。
声明字段属性（filedN）的基本语法为：
字段名称 数据类型与大小 是否可以为空 是否为主键 默认值 备注
其中字段名称、数据类型与大小是一定要有的，其他可以不写。
各项的规定可以简单介绍如下：
字段名称：使用英文字母、数字和下划线比较好。不区分大小写，长度不应超过64。
数据类型与大小：例如 int、int unsigned、char(10)等。
是否为空：not null 表示这个字段不允许为空，如果没有指定，表示可以为空。
是否为主键：如果语句中包含primary key,即表示为主键。
默认值：如果写了 default 'abc'即表示该字段的默认值为 abc，如果插入新数据没有指定该字段的值，则会以默认值插入。
备注：在备注中，如果插入auto_increment语句，表示该字段在插入新数据时会自动增加（限于整型）。而在插入新数据时，该字段指定为NULL即可。
示例：create table abc (number int not null primary key auto_increment,name char(10) default 'unknow');
7、删除表格
命令：drop table tablename;
描述：删除名称为 tablename 的表格，如同删除数据库命令一样，要谨慎使用。
8、显示表格状态
命令：show tables;
描述：显示数据库中建立了那些表格。
命令：desc(describe) tablename;
描述：显示名称为tablename的表格的结构。
9、表名称与结构的修改
命令：alter table tablename add fieldN+1
描述：添加一个字段进入表格，fieldN+1与上面描述写法一致。
命令：alter table oldtablename rename newtablename;
描述：修改表oldtablename名称为newtablename。
命令：alter table tablename modify filedN;
描述：修改表tablename中字段filedN的属性，filedN中指明需要改的字段名称和新的属性。
命令：alter table tablename change oldfiledN newfiledN;
描述：改变表tablename字段filedN 为filedN+1；oldfiledN只需指明需要改的字段名称，而newfiledN中需要指明新的字段名称和类型。
如：alter table newname change follow newfollow varchar(40);
命令：alter table tablename drop filedN;
描述：删除表tablename中的filedN字段。
10、创建数据库
Mysql> Create Database 数据库名;
11、创建用户并授权
Mysql> grant 权限 on 数据库名.* to 用户名@登录主机 identified by "密码";
如：grant all on test.* to test@localhost identified by "test";
12、修改密码
mysqladmin -u用户名 -p旧密码 password 新密码
13、删除用户
Mysql> use mysql;delete from user where user="zaho" host="localhost";
14、把sql文件导入数据库
Mysql -uroot -p databasename < d:\datasource\databasename.sql
如：mysql -utest -ptest test < h:\tmp\create.sql
MYSQL入门学习之四：MYSQL的数据类型
一、整型 www.2cto.com
整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种整数类型。MySQL数据库除了支持这两种类型外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。
各种整数类型的取值范围、存储的字节数如下：
整型 字节数 无符号数的取值范围 有符号数的取值范围
TINYINT 1 0~255 -128~127
SMALLINT 2 0~65535 -32768~12767
MEDIUMINT 3 0~16777215 -8388608~8388607
INT 4 0~4294967295 -2147483648~2147483647
INTEGER 4 0~4294967295 -2147483648~2147483647
BIGINT 8 0~18446744073709551615 -9223372036954775808~9223372036854775807
二、浮点数类型和定点数类型
MySQL使用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数(FLOAT类型)和双精度浮点数(DOUBLE类型)。定点数类型就是DECIMAL型。
FLOAT型、DOUBLE型、DECIMAL型的取值范围、存储的字节数如下：
小数类型 字节数 负数取值范围 无符号取值范围
FLOAT 4 -3.402823466E+38~ 0和1.175494351E-38~
-1.175494351E-38 3.402823466E+38
DOUBLE 8 1.7976931348623157E+308~ 0和2.2250738585072014E~
-2.2250738585072014E-308 1.7976931348623157E+308
DECIMAL(M,D) M+2 DOUBLE型 同DOUBLE型
或DEC(M,D)
M:最大长度（包括小数部分，但不包括小数点）
D:小数点后保留长度
对于浮点数和定点数，当插入值的精度高于实际定义的精度，系统会自动进行四舍五入处理。其目的是为了使该值的精度达到要求。浮点数进行四舍五入不会报警，定点数出现警告。
在未指定精度的情况下，浮点数和定点数有其默认的精度。FLOAT类型和DOUBLE类型默认会保存实际精度。这个精度与操作系统和硬件的精度有关。DECIMAL类型默认整数位为10, 小数位为0，即默认为整数。 www.2cto.com
在MySQL中，定点数精度比浮点数要高。而且，浮点数会出现误差。如果要对数据的精度要求比较高，应该选择定点数。
三、日期和时间类型
1、DATETIME类型
DATETIME类型表示同时包含日期和时间信息的值。MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'～'9999-12-31 23:59:59'。
2、DATE类型
DATE类型表示只有日期值而没有时间值的值。MySQL用'YYYY-MM-DD'格式检索和显示DATE值。支持的范围为'1000-01-01'～'9999-12-31'。
3、TIME类型
TIME值可以使用多种表示格式。
'D HH:MM:SS.fraction'格式的字符串。还可以使用下面任何一种“非严格”语法'HH:MM:SS.fraction'、'HH:MM:SS'、'HH:MM'、'D HH:MM:SS'、'D HH:MM'、'D HH'或'SS'。这里D表示日，可以取0～34的值。请注意MySQL不保存分数。
'HHMMSS'格式的没有间隔符的字符串，被假定为是有意义的时间。例如，'101112'被理解为'10:11:12'，但'109712'是不合法的(它有一个没有意义的分钟部分)，其将变为'00:00:00'。
HHMMSS格式的数值，被假定为是有意义的时间。例如，101112被理解为'10:11:12'。下面的格式也可以理解：SS、MMSS、HHMMSS、HHMMSS.fraction。请注意MySQL不保存分数。
4、YEAR类型
YEAR类型是一个单字节类型，用于表示年。MySQL以YYYY格式检索和显示YEAR值。范围为1901～2155。
可以指定各种格式的YEAR值。
四位字符串，范围为'1901'～'2155'。
四位数字，范围为1901～2155。
两位字符串，范围为'00'～'99'。'00'～'69'和'70'～'99'范围的值分别被转换为2000～2069和1970～1999范围的YEAR值。
两位整数，范围为1～99。1～69和70～99范围的值分别被转换为2001～2069和1970～1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为不能直接将零指定为数字并将它解释为2000。必须将它指定为一个字符串'0'或'00'或它被解释为0000。
5、TIMESTAMP类型
TIMESTAMP类型使用４个字节来表示日期和时间。TIMESTAMP类型的范围从1970-01-001 08:00:01到2038-01-19 11:14:07。MySQL中也是以'YYYY-MM-DD HH:MM:SS'的形式显示TIMESTAMP类型的值。从其形式可以看出，TIMESTAMP类型与DATETIME类型显示的格式一样的。给TIMESTAMP类型的字段赋值的表示方法基本与DATETIME类型相同。值的注意的是，TIMESTAMP类型范围比较小，没有DATETIME类型的范围大。因此，输入值时要保证在TIMESTAMP类型时有效范围内。
四、字符串类型
字符串类型是在数据库中存储字符串的数据类型。
1、CHAR类型和VARCHAR类型
CHAR类型和VARCHAR类型都是在创建表时指定最大长度，其基本形式如下：
字符串类型(M)
例如，CHAR(4)就是指数据类型为CHAR类型，其最大长度为4。
CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。
例如，CHAR(100)就是指定CHAR类型的长度为100。
VARCHAR类型的长度是可变的，在创建时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100。但是，不是每条记录都要占100个位置。而是在这个最大值范围内，使用多少分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1。这样，可以有效的节约系统的空间。
2、TEXT类型
TEXT类型是一种特殊的字符串类型。TEXT只能保存字符数据，如文章等。TEXT类型包含TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
类型 允许的长度 存储空间
TINYTEXT 0~255字节 值的长度+2个字节
TEXT 0~65535字节 值的长度+2个字节
MEDIUMTEXT 0~167772150字节 值的长度+3个字节
LONGTEXT 0~4294967295字节 值的长度+4个字节
从表可以看出，各种TEXT类型的区别在于允许的长度和存储空间不同。因此在这几种TEXT中，根据需求选取既能满足需要以最节约空间的类型即可。
3、ENUM类型(枚举类型) www.2cto.com
ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围就以列表的形式指定了。
属性名 ENUM('值1', '值2',...., '值n');
其中， '属性名'参数指定字段名称；'值n'参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。
ENUm类型的值只能列表中的一个元素。其取值列表中最多能有65535个值。列表中的每个值都有一个顺序排序的编号，MySQL中存入的是这个编号，而不是列表中的值。
如果ENUm类型加上了NOT NULL属性，其默认值为取值列表的第1个元素。如果不加NOT NULL属性，ENUm类型将允许插入NULL，而且NULL为默认值。
CREATE TABLE IF NOT EXISTS `test`.`enum_tbl`(
`a` ENUM('male','female'),
`b` ENUM('true','false') NOT NULL
);
INSERT INTO `test`.`enum_tbl`
VALUES('male', 'true'),(NULL, 'false'), (NULL, NULL),(20, 20);
SELECT * FROM `enum_tbl`;
4、SET类型
基本形式如下：
属性名 set('值1','值2','值3'...'值n');
其中，'属性名'参数指定字段名称；'值n'参数列表中的第n个值,这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。
SET类型的值可以取列表中一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是64个元素构成的组合。列表中的每一个值都有一个顺序排列的编号。MySQL中存入的是这个编号，而不是列表中的值。
插入记录时，SET字段里的元素顺序无关紧要。存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示。 www.2cto.com
CREATE TABLE IF NOT EXISTS `test`.`set_tbl`(
`a` SET('a','b','c','d','e','f','g')
);
INSERT INTO `test`.`set_tbl` VALUES('f'),('a,b,c'),('d,e,a');
INSERT INTO `test`.`set_tbl` VALUES('h');
SELECT * FROM `set_tbl`;
五、二进制类型
二进制类型是在数据库中存储二进制数据的数据类型。
二进制类型 取值范围
BINARY(M) 字节数为M，允许长度为0~M的定长二进制字符串
VARBINARY(M) 允许长度为0~M的变长二进制字符串，字节数为值的长度加一
BIT(M) M位二进制数据，M最大值为64
TINYBLOB 可变长二进制数据，最多255个字节
BLOB 可变长二进制数据，最多(2[16]-1)个字节
MEDIUMBLOB 可变长二进制数据，最多(2[24]-1)个字节
LONGBLOB 可变长二进制数据，最多(2[32]-1)个字节
1、BINARY和VARBINARY类型
BINARY和VARBINARY类型都是在创建表时指定了最大长度，其基本形式如下 ：
字符串类型(M)
这与CHAR类型和VARCHAR类型相似。
例如，BINARY(10)就是指数据类型为BINARY类型，其最大长度为10。
BINARY类型的长度是固定的，在创建表是就指定了。不足最大长度的空间由"\0"补全。例如，BINARY(50)就是指定BINARY类型的长度为50。
VARBINARY类型的长度是可变的，在创建表时指定了最大长度。指定好了VARBINARY类型的最大值以后，基长度可以在0到最大长度之间。例如，VARBINARY(50)的最大字节长度是50。但是，不是每条记录的字节长度都是50。在这个最大范围内，使用多少分配多少。VARBINARY类型实际占用的空间为实际长度加一。这样，可以有效的节约系统的空间。
2、BIT类型
BIT类型也是创建表时指定了最大长度，其基本形式如下：
BIT(M)
其中，'M'指定了该二进制的数的最大字节长度为M，M的最大值为64。例如，BIT(4)就是数据类型BIT类型，长度为4.若字段的类型BIT(4)，存储的数据是从0到15。因为，变成二进制以后，15的值为1111，其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度。因此大于等于16的数是不能插入到BIT(4)类型的字段中的。在查询BIT类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。
3、BLOB类型
BLOB类型是一种特殊的二进制类型。BLOB可以用来保存数据量很大的二进制数据，如图片等。BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。这几种BLOB类型最大的区别就是能够保存的最大长度不同。LONGBLOB的长度最大，TINYBLOB的长度最小。
BLOB类型与TEXT类型很类似。不同点在于BLOB类型用于存储二进制数据，BLOB类型数据是根据其二进制编码进行比较和排序。而TEXT类型是文本模式进行比较和排序的。
MYSQL入门学习之五：MYSQL的字符集
MySQL的字符集支持(Character Set Support)有两个方面：字符集(Character set)和排序方式(Collation)。 www.2cto.com
对于字符集的支持细化到四个层次:服务器(server)，数据库(database)，数据表(table)和连接(connection)。
一、MySQL默认字符集
MySQL对于字符集的指定可以细化到一个数据库，一张表，一列，应该用什么字符集。
但是，传统的程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配置，那么，默认的配置从何而来呢？
(1)编译MySQL 时，指定了一个默认的字符集，这个字符集是 latin1；
(2)安装MySQL 时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的；
(3)启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时 character_set_server 被设定为这个默认的字符集；
(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server；
(5)当选定了一个数据库时，character_set_database 被设定为这个数据库默认的字符集；
(6)在这个数据库里创建一张表时，表默认的字符集被设定为 character_set_database，也就是这个数据库默认的字符集；
(7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集； www.2cto.com
简单的总结一下，如果什么地方都不修改，那么所有的数据库的所有表的所有栏位的都用latin1存储，不过我们如果安装 MySQL，一般都会选择多语言支持，也就是说，安装程序会自动在配置文件中把default_character_set 设置为 UTF-8，这保证了缺省情况下，所有的数据库的所有表的所有栏位的都用 UTF-8 存储。
二、查看字符集
1、查看MySQL数据库服务器和数据库字符集
mysql> show variables like '%char%';
+--------------------------+------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------+
| character_set_client | latin1 |-- 客户端字符集
| character_set_connection | latin1 |
| character_set_database | latin1 |-- 数据库字符集
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |-- 服务器字符集
| character_set_system | utf8 |
| character_sets_dir | D:\APMServ5.2.6\MySQL5.1\share\charsets\ |
+--------------------------+------------------------------------------+
2、查看MySQL数据表（table）的字符集
mysql> show table status from test like '%test%';
+---------------+--------+---------+------------+ +-------------------+
| Name | Engine | Version | Row_format | | Collation |
+---------------+--------+---------+------------+ +-------------------+
| test_char | MyISAM | 10 | Fixed | | hebrew_general_ci |
| test_inn | InnoDB | 10 | Compact | | latin1_swedish_ci |
| test_inn2 | InnoDB | 10 | Compact | | utf8_general_ci |
| test_priority | MyISAM | 10 | Dynamic | | latin1_swedish_ci |
| test_trans | InnoDB | 10 | Compact | | latin1_swedish_ci |
| test_view | NULL | NULL | NULL | | NULL |
+---------------+--------+---------+------------+ +-------------------+ www.2cto.com
3、查看MySQL数据列（column）的字符集
mysql> show full columns from v9_picture;
+-------------+-----------------------+----------------+
| Field | Type | Collation |
+-------------+-----------------------+----------------+
| id | mediumint(8) unsigned | NULL |
| catid | smallint(5) unsigned | NULL |
| typeid | smallint(5) unsigned | NULL |
| title | char(80) | gbk_chinese_ci |
| style | char(24) | gbk_chinese_ci |
| thumb | char(100) | gbk_chinese_ci |
| keywords | char(40) | gbk_chinese_ci |
| description | char(255) | gbk_chinese_ci |
| posids | tinyint(1) unsigned | NULL |
| url | char(100) | gbk_chinese_ci |
| listorder | tinyint(3) unsigned | NULL |
| status | tinyint(2) unsigned | NULL |
| sysadd | tinyint(1) unsigned | NULL |
| islink | tinyint(1) unsigned | NULL |
| username | char(20) | gbk_chinese_ci |
| inputtime | int(10) unsigned | NULL |
| updatetime | int(10) unsigned | NULL |
+-------------+-----------------------+----------------+
三、设置及修改字符集
1、创建数据库指定数据库的字符集
mysql> create database mydb character set gb2312;
mysql> use mydb
mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | gb2312 |
+------------------------+--------+
2、修改数据库的字符集
（1）、通过SQL修改
mysql> alter database mydb character set utf8;
mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| character_set_database | utf8 |
+------------------------+-------+
（2）、通过配置文件修改
修改文件$MYSQL_HOME\data\mydb\db.opt的内容
default-character-set=utf8
default-collation=utf8_general_ci
为
default-character-set=latin1
default-collation=latin1_swedish_ci
重启MYSQL后生效
3、通过SQL修改字符集
mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
mysql> set character_set_system=utf8;
mysql> set collation_connection=utf8;
mysql> set collation_database=utf8;
mysql> set collation_server=utf8;
MYSQL入门学习之二：使用正则表达式搜索
MYSQL入门学习之二：使用正则表达式搜索
一、正则表达式介绍
www.2cto.com
正则表达式是用来匹配文本的特殊的串（字符集合）。
二、使用MySQL正则表达式
1、MySQL仅仅支持多数正则表达式实现的一个很小的子集。
2、LIKE匹配整个列值；而REGEXP匹配列值的子串，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。看下面的例子：
www.2cto.com
[sql]
mysql>select username from v9_admin where username like 'space';
Emptyset (0.00 sec)
mysql>select username from v9_admin where username REGEXP 'space';
+-----------+
|username |
+-----------+
|warmspace |
+-----------+
1 rowin set (0.02 sec)
3、默认MySQL中的正则表达式匹配不区分大小写，可使用BINARY关键字区分大小写，如
[sql]
mysql>select username from v9_admin
-> where username REGEXP BINARY'SPACCE';
Emptyset (0.04 sec)
4、进行OR匹配：为搜索两个串之一，使用|，如下所示：
[sql]
mysql>select roleid from v9_admin_role
-> where roleid REGEXP '1|3';
+--------+
|roleid |
+--------+
| 1 |
| 3 |
+--------+
2 rowsin set (0.00 sec)
5、匹配几个字符之一：匹配任何单一字符。如下所示：
[sql]
mysql>select name from v9_collection_node
-> where name REGEXP '005-[123]';
+-----------------+
|name |
+-----------------+
|101-02-01-005-2 |
|101-02-01-005-1 |
|101-02-01-005-3 |
+-----------------+
3 rowsin set (0.00 sec)
正如所见，[]是另一种形式的OR语句。'005-[123]'与’005-[1|2|3]’是等价的。
匹配除给定字符外的字符：
[sql]
mysql>select name from v9_collection_node
-> where name REGEXP '005-[^123]';
+-------------------+
|name |
+-------------------+
|101-02-01-005-4.2 |
|101-02-01-005-4 |
|101-02-01-005-5 |
+-------------------+
3 rowsin set (0.00 sec)
6、可使用-来定义一个范围。如[1-9],[a-b]。
[sql]
mysql>select name from v9_collection_node
-> where name REGEXP '005-[1-3]';
+-----------------+
|name |
+-----------------+
|101-02-01-005-2 |
|101-02-01-005-1 |
|101-02-01-005-3 |
+-----------------+
3 rowsin set (0.00 sec)
7、为了匹配特殊字符，必须用\\为前导，例如\\-,\\.等。这种处理即转义（escaping）。
多数正则表达式实现使用单个反斜杠转义特殊字符，但MYSQL要求两个（MYSQL自己解释一个，正则表达式库解释另一个）。
\\也用来引用元字符（具有特殊含义的字符），如下表：
8、为了更方便工作，可以使用预定义的字符集，称为字符类（character class）。
[sql]
mysql> select name fromv9_collection_node
-> where name REGEXP '[[:digit:]]';
+-------------------+
| name |
+-------------------+
| 101-10-01-002-1 |
| 101-02-01-005-4.2 |
| 101-02-01-005-2 |
| 101-02-01-005-1 |
| 101-02-01-005-3 |
| 101-02-01-005-4 |
| 101-02-01-005-5 |
| 101-10-01-002-2 |
| 101-11-04-001-1 |
+-------------------+
9 rowsin set (0.00 sec)
9、使用正则表达式重复元字符匹配多个实例：
[sql]
mysql> select name fromv9_collection_node
-> where name REGEXP '1{2}';
+-----------------+
| name |
+-----------------+
| 101-11-04-001-1 |
+-----------------+
1 rowin set (0.00 sec)
10、定位符（为了匹配特定位置的文本）：
[sql]
mysql> select name from v9_collection_node
-> where name REGEXP '2$';
+-------------------+
| name |
+-------------------+
| 101-02-01-005-4.2 |
| 101-02-01-005-2 |
| 101-10-01-002-2 |
+-------------------+
3 rows in set (0.00 sec)
11、使REGEXP起类似LIKE的作用，它们的区别在于LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式。
12、简单的正则表达式测试：可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0或1（匹配）。
[sql]
mysql> select 'hello' REGEXP'[0-9]';
+------------------------+
| 'hello' REGEXP '[0-9]' |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
MySQL入门学习之三：全文本搜索
一、理解全文本搜索
www.2cto.com
1、MyISAM支持全文本搜索，而InnoDB不支持。
2、在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样MySQL可以快速有效地决定哪些词匹配，哪些词不匹配，它们匹配的频率，等等。
二、使用全文本搜索
1、为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。
在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索。
2、一般在创建表时启用全文本搜索。
[sql]
create table productnotes
(
note_id int not nullauto_increment,
note_text text null,
primary key(note_id),
fulltext(note_text)
)engine=MyISAM;
在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。
3、不要在导入数据时使用FULLTEXT。
www.2cto.com
4、进行全文本搜索
Match()指定被搜索的列，Against()指定要使用的搜索表达式。
[sql]
mysql> select * from productnotes
-> whereMatch(note_text) Against('designed');
+---------+---------------------------------------------------------------------
------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
------------------------------------------------------+
| 6 | LimsLink isdesigned to interface output from chromatography data sy
stems (CDSs) to LIMS. |
| 5 | This line ofproprietary reagents, containers, and automation tools
is designed for genomics and drug discovery research. |
+---------+---------------------------------------------------------------------
------------------------------------------------------+
2 rows in set (0.03 sec)
5、传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
6、除非使用BINARY方式，否则全文本搜索不区分大小写。
[sql]
mysql> select * from productnotes
-> where BINARYMatch(note_text) Against('line');
+---------+---------------------------------------------------------------------
------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
------------------------------------------------------+
| 5 | This line ofproprietary reagents, containers, and automation tools
is designed for genomics and drug discovery research. |
+---------+---------------------------------------------------------------------
------------------------------------------------------+
1 row in set (0.05 sec)
7、全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。
等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。文本中词先前的行的等级值比词靠后的行的等级值高。
[sql]
mysql> select note_id, Match(note_text) Against('This line')as rank,note_text
-> fromproductnotes
-> whereMatch(note_text) Against('This line');
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
| note_id | rank | note_text
|
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
| 5 |0.81339610830754 | This line of proprietary reagents,. containers, a
nd automation tools is designed. for genomics and drugdiscovery .research. |
| 7 |0.76517958501676 | specificities include both alpha–beta and beta–
beta. This line from chromatography .data systems (CDSs) and toLIMS. |
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
8、查询扩展 www.2cto.com
在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索。
首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次，MySQL检查这些匹配行并选择所有有用的词；
再次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
表中的行越多，使用查询扩展返回的结果越好。
查询扩展功能在MySQL4.1.1中引入。
[sql]
mysql> select note_id, Match(note_text) Against('This line')as rank,note_text
-> fromproductnotes
-> where Match(note_text)Against('This line' with query expansion);
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
| note_id | rank | note_text
|
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
| 5 | 0.81339610830754| This line of proprietary reagents,. containers, a
nd automation tools is designed. for genomics and drugdiscovery .research. |
| 7 |0.76517958501676 | specificities include both alpha–beta and beta–
beta. This line from chromatography .data systems (CDSs) and toLIMS. |
| 3 | 0 | Human S-100. monoclonal.and polyclonal specifici
ties include both alpha–beta and beta–beta isoforms. |
| 6 | 0 | LimsLink is .designed to interfaceoutput. from c
hromatography .data systems (CDSs) and to LIMS. |
| 1 | 0 | PepTool allows users tostore, manage. analyze, a
nd visualize protein data. |
+---------+------------------+--------------------------------------------------
----------------------------------------------------------------------------+
5 rows in set (0.00 sec)
9、布尔文本搜索（boolean mode）
以布尔方式，可以提供关于如下内容的细节：
要匹配的词； www.2cto.com
要排斥的词；
排列提示；（指定某些词比其他词更重要）
表达式分组；
另外一些内容。
[sql]
mysql> select note_id,note_text
-> fromproductnotes
-> whereMatch(note_text) Against('line' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| 5 | This line ofproprietary reagents,. containers, and automation tools
is designed. for genomicsand drug discovery .research. |
| 7 | specificitiesinclude both alpha–beta and beta–beta. This line fro
m chromatography .data systems (CDSs) and to LIMS. |
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
2 rows in set (0.00 sec)
即使没有FULLTEXT索引也可以使用布尔文本搜索。但是非常缓慢。
mysql> select note_id,note_text/*匹配line且不包含systems*/
-> fromproductnotes
-> whereMatch(note_text) Against('line -systems*' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| 5 | This line ofproprietary reagents,. containers, and automation tools
is designed. forgenomics and drug discovery .research. |
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select note_id,note_text/*匹配line且匹配systems*/
-> fromproductnotes
-> whereMatch(note_text) Against('+line +systems' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------+
| 7 | specificitiesinclude both alpha–beta and beta–beta. This line fro
m chromatography .data systems (CDSs) and to LIMS. |
+---------+---------------------------------------------------------------------
---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select note_id,note_text/*匹配line或匹配systems*/
-> fromproductnotes
-> whereMatch(note_text) Against('line systems' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| 5 | This line ofproprietary reagents,. containers, and automation tools
is designed. forgenomics and drug discovery .research. |
| 6 | LimsLink is.designed to interface output. from chromatography .data
systems (CDSs) and toLIMS. |
| 7 | specificitiesinclude both alpha–beta and beta–beta. This line fro
m chromatography .data systems (CDSs) and to LIMS. |
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select note_id,note_text/*匹配短语*/
-> fromproductnotes
-> whereMatch(note_text) Against('"This line"' in boolean mode);
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| note_id | note_text
|
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
| 5 | This line ofproprietary reagents,. containers, and automation tools
is designed. forgenomics and drug discovery .research. |
| 7 | specificitiesinclude both alpha–beta and beta–beta. This line fro
m chromatography .data systems (CDSs) and to LIMS. |
+---------+---------------------------------------------------------------------
---------------------------------------------------------+
2 rows in set (0.00 sec)
10、使用说明
l 在索引全文本数据时，短词被忽略且从索引中排除。短词的定义为那些具有3个或脸上以下字符的词（如果需要，这个数目可以更新）。
l MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表。
l MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
l 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
l 忽略词中的单引号。如，don’t索引为dont。
l 不具有词分隔符的语言不能恰当地返回全文本搜索结果。
本文引用自http://blog.****.net/xiaoyangger/article/details/8476175