只谈MySQL (第31天) 系统函数(中三)

<<续>>
MySQL的函数大列9大类:

流程控制函数字串函数数值函数日期时间函数全文检索函数型别转换函数XML函数其他函数用在Group By场合下的函数
今天介绍3. 数值函数, 4. 日期时间函数, 5. 全文检索函数
"割"成三篇才能放完...
<<续4. 日期时间函数>>

TIMESTAMPADD()(v5.0.0)例子说明比较快

[code]mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
-> '2003-01-02 00:01:00' --> 第一个参数是单位, 第二个参数是要增加数值, 第三个参数是被加的日期时间
mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
-> '2003-01-09'

[/code]

TIMESTAMPDIFF()(v5.0.0): 还是用例子来说明比较快

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3 --> 第一个参数是单位, 第二个、第三个参数是要计算差的日期参数
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885

TO_DAYS(): 传回日期参数是由西元零年1月1日起的第几天了

UTC_DATE()(v4.1.1): 传回UTC日期

UTC_TIME()(v4.1.1): 传回UTC时间

UTC_TIMESTAMP()(v4.1.1): 传回UTC日期时间

WEEK(): 传回日期参数于所在的年份内的週数, 例如WEEK('2009-11-12');传回45

WEEKDAY(): 传回日期参数所在的週内第几天, 由星期一(第0天)起算到星期日(第6天), 例如DAYWEEK('2009-11-12');是星期四..传回3

WEEKOFYEAR()(v4.1.1): 传回日期参数在该年份内的日曆週数, 由0算到第53週, 例如WEEKOF(YEAR('2009-11-12');回传46

YEAR(): 传回日期参数所在的年份

YEARWEEK(): 传回日期参数所在的年份及週数, 例如YEARWEEK('2009-11-12');传回200945

全文检索函数
MySQL提供了相当丰富的全文检索功能, 这是个可当成单独研究的课题, 因此我在这裏只以一个简单的MySQL的全文检索例子来大致介绍

mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body) <-- 用FULLTEXT()建立索引键及全文检索的关联
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> 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 ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) <-- MATCH()及AGAINST()都是MySQL的全文检索函数
-> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
上面的例子就是在title和body栏位中找到有'database'关键字的资料录出来, 更多的例子:
mysql> SELECT COUNT() FROM articles
-> WHERE MATCH (title,body)
-> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT() |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT
-> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
-> AS count
-> FROM articles;
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.03 sec)
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body)
-> AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)

关于MySQL的全文检索功能, 就先讨论到这裏, 以后再专文讨论其全部的函数, 有兴趣的网有可以参考
http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

<<中篇完>>