<<續>>
MySQL的函數大列9大類:

1. 流程控制函數
2. 字串函數
3. 數值函數
4. 日期時間函數
5. 全文檢索函數
6. 型別轉換函數
7. XML函數
8. 其他函數
9. 用在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

5. 全文檢索函數
   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

<<中篇完>>