2009-05-31--06-02 MySQL学习笔记04

1.数据库名

在文件系统中，MySQL的数据存储区以目录方式表示MySQL数据库，所以数据库名必须与目录名一致。包含特殊字符或者全部由数字或保留字组成的数据库名必须用符号“`”引起来。

mysql> create database `...`;
Query OK, 1 row affected (0.01 sec)

mysql> create database `123456`;
Query OK, 1 row affected (0.01 sec)

mysql> create database `database`;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 123456 |
| ... |
| database |
| ggyy |
| mysql |
| test |
+--------------------+
7 rows in set (0.00 sec)

[@more@]mysql> use ...
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| ... |
+------------+
1 row in set (0.00 sec)

mysql> use 123456
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| 123456 |
+------------+
1 row in set (0.00 sec)

mysql> use database
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| database |
+------------+
1 row in set (0.00 sec)

mysql> drop database `...`;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database `123456`;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database `database`;
Query OK, 0 rows affected (0.00 sec)


2.关于NULL和空值的补充说明

在MySQL中，空值与NULL不同，它不受NOT NULL约束的限制。

mysql> create table namelist
-> (
-> fname varchar(15) not null,
-> lname varchar(15) default 'Li',
-> tel smallint(11) unsigned not null
-> );
Query OK, 0 rows affected (0.18 sec)

mysql> desc namelist;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| fname | varchar(15) | NO | | NULL | |
| lname | varchar(15) | YES | | Li | |
| tel | smallint(11) unsigned | NO | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into namelist (fname) values ('Ning');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
+-------+-------+-----+
1 row in set (0.00 sec)

mysql> insert into namelist (fname) values (NUll);
ERROR 1048 (23000): Column 'fname' cannot be null

但这里说的空值是指插入记录时不值定任何值，用“''”指定的空字符串则不属于这种情况。

mysql> insert into namelist values ('', '', '');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
| | | 0 |
+-------+-------+-----+
2 rows in set (0.00 sec)

如果一个字段没有指定DEFAULT修饰符，MySQL会根据这个字段是NULL还是NOT NULL自动设置默认值。如果字段可以为NULL，默认值为NULL；如果字段指定指定了NOT NULL，MySQL对于数值类型插入0（如前面例子中的tel列），字符串类型插入空字符串，时间戳类型插入当前的日期和时间，ENUM类型插入允许值集合的第一个值。
从下面的例子可以看到，MySQL为NOT NULL的varchar列fname插入了空字符串，这和用“''”指定的空字符串相同：

mysql> insert into namelist (lname, tel) values ('', '');
Query OK, 1 row affected, 2 warnings (0.44 sec)

mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
| | | 0 |
| | | 0 |
+-------+-------+-----+
3 rows in set (0.00 sec)

mysql> select * from namelist where fname='';
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| | | 0 |
| | | 0 |
+-------+-------+-----+
2 rows in set (0.41 sec)


3.FULLTEXT索引

FULLTEXT索引用于全文检索。可以为一或两个纯文本字符串列（称为资料库）添加该索引，索引列可以是CHAR, VARCHAR或TEXT类型，但不能为BLOB类型，对索引列进行查询时返回含有与搜索字符串相似的部分的记录。FULLTEXT索引仅可用于MyISAM表，而创建表的默认类型为InnoDB，所以要用type明确指定。
下面创建一个存储英语日常用语的表并向其中插入若干条记录，该表有两个列，问句和答句，FULLTEXT索引建立在这两个列上（个人理解，即确定了全文检索的范围，将两列的内容作为一个整体来生成索引）：

mysql> create table oraleng
-> (
-> ask text,
-> answer text,
-> fulltext index (ask, answer)
-> )
-> type = myisam;
Query OK, 0 rows affected, 1 warning (0.15 sec)

mysql> insert into oraleng values ('How do you do?', 'How do you do?');
Query OK, 1 row affected (0.06 sec)

mysql> insert into oraleng values ('How are you?', 'Fine.Thank you.');
Query OK, 1 row affected (0.42 sec)

mysql> insert into oraleng values ('What's your name?', 'My name is Jack Sparro
w.');
Query OK, 1 row affected (0.41 sec)

mysql> insert into oraleng values ('Where are you from?', 'I'm from maldives.')
;
Query OK, 1 row affected (0.00 sec)

mysql> insert into oraleng values ('What's the weather like?', 'It's fine.');
Query OK, 1 row affected (0.00 sec)

mysql> insert into oraleng values ('What time is it now?', 'It's seven o'clock
.');
Query OK, 1 row affected (0.00 sec)

mysql> insert into oraleng values ('What day is it today?', 'It's Wednesday.');

Query OK, 1 row affected (0.00 sec)

mysql> select * from oraleng;
+--------------------------+--------------------------+
| ask | answer |
+--------------------------+--------------------------+
| How do you do? | How do you do? |
| How are you? | Fine.Thank you. |
| What's your name? | My name is Jack Sparrow. |
| Where are you from? | I'm from maldives. |
| What's the weather like? | It's fine. |
| What time is it now? | It's seven o'clock. |
| What day is it today? | It's Wednesday. |
+--------------------------+--------------------------+
7 rows in set (0.00 sec)

相对于在创建表时产生索引，输入数据后使用CREATE FULLTEXT INDEX或者ALTER TABLE语句向表中添加索引速度更快。

match()和against()函数返回一个代表相似度的值，例如：

mysql> select match(ask, answer) against ('weather') as score, ask,answer from o
raleng;
+------------------+--------------------------+--------------------------+
| score | ask | answer |
+------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 1.75147557258606 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+------------------+--------------------------+--------------------------+
7 rows in set (0.00 sec)

如果将其作为where后面的条件，就可以只返回含有与搜索字符串相似的部分的记录：

mysql> select ask, answer from oraleng where match (ask) against ('weather');
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
mysql> select ask, answer from oraleng where match (ask, answer) against ('weath
er');
+--------------------------+------------+
| ask | answer |
+--------------------------+------------+
| What's the weather like? | It's fine. |
+--------------------------+------------+
1 row in set (0.40 sec)

出现频率高的词，返回的值就较小：

mysql> select match(ask, answer) against ('fine') as score, ask,answer from oral
eng;
+-------------------+--------------------------+--------------------------+
| score | ask | answer |
+-------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0.905873239040375 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 0.895689904689789 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+-------------------+--------------------------+--------------------------+
7 rows in set (0.00 sec)

如果搜索字符串中包含两个关键词，返回的值大约为分别搜索它们时返回值的和：

mysql> select match(ask, answer) against ('weather fine') as score, ask,answer f
rom oraleng;
+-------------------+--------------------------+--------------------------+
| score | ask | answer |
+-------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0.905873239040375 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 2.64716553688049 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+-------------------+--------------------------+--------------------------+
7 rows in set (0.01 sec)

MATCH()函数是在一些列参数的基础上计算这个值的，如：

每一行词的个数
每一行唯一词的个数
集合中所有词的总个数
包含一个特定词的行数

并不是所有的词都会加入索引，一些默认的全文停止字如from, the, what, you...就会被忽略，在所有的记录中出现频率大于50%（即包含该词的行数超过总行数的一半）的词也会被忽略，另外长度不符合要求的词同样会被忽略。通过修改服务器配置变量ft_stopword_file，ft_min_word_len和ft_max_word_len可以分别控制停止词列表、最小单词长度和最大单词长度，它们的默认值如下：

mysql> select @@ft_stopword_file, @@ft_min_word_len, @@ft_max_word_len;
+--------------------+-------------------+-------------------+
| @@ft_stopword_file | @@ft_min_word_len | @@ft_max_word_len |
+--------------------+-------------------+-------------------+
| (built-in) | 4 | 84 |
+--------------------+-------------------+-------------------+
1 row in set (0.00 sec)

其中，ft_stopword_file默认指定为MySQL内置的全文停止字表，其内容可以在MySQL参考手册中查到。
修改这些服务器变量后，需要重启MySQL服务器使修改生效，而FULLTEXT索引也需要重建。一种较快的重建方法是使用带有QUICK参数的REPAIR TABLE命令。

4.0.1以上的版本，还可以执行FULLTEXT索引的布尔搜索：

mysql> select ask, answer from oraleng where match (ask, answer) against ('fine'
);
+--------------------------+-----------------+
| ask | answer |
+--------------------------+-----------------+
| How are you? | Fine.Thank you. |
| What's the weather like? | It's fine. |
+--------------------------+-----------------+
2 rows in set (0.01 sec)

mysql> select ask, answer from oraleng where match (ask, answer) against ('+fine
-weather' in boolean mode);
+--------------+-----------------+
| ask | answer |
+--------------+-----------------+
| How are you? | Fine.Thank you. |
+--------------+-----------------+
1 row in set (0.00 sec)