1 月 13 2008
在 MySQL 中處理時間資料
以前，我會習慣用 PHP 的 date() 把時間字串整理好再放進資料庫，不然就是直接存 UNIX timestamp 。
直到前一陣子，我才發現 MySQL 有些巧妙的函式，方便我們處理理時間型態的資料。
如果在 TABLE_NAME 中，有個名為 TIME_COL 的欄位，其型態為 (unsigned) int，儲存的資料為 UNIX timestamp 。
那麼，在 MySQL 中，有些好用的 function 可供我們進行資料的操作：
- 新增一筆資料，其 TIME_COL 為現在時間：
INSERT INTO `TABLE_NAME` (`TIME_COL`) VALUES ( UNIX_TIMESTAMP( NOW() ) );
- 這串 SQL 語法可以找出 TIME_COL 所紀錄的時間在五分鐘內的資料：
SELECT * FROM `TABLE_NAME` WHERE `TIME_COL` > UNIX_TIMESTAMP( NOW() - INTERVAL 5 MINUTE );
- 這串 SQL 語法可以讓所有資料的 TIME_COL 值往後延 2 個月：
UPDATE `TABLE_NAME` SET `TIME_COL`=UNIX_TIMESTAMP( FROM_UNIXTIME(`TIME_COL`, '%Y-%m-%d %H:%i:%s') + INTERVAL 2 MONTH ) WHERE 1;
如果 TIME_COL 欄位的型態為 datetime ，事情處理起來會更容易：
- 新增一筆資料，其 TIME_COL 為現在時間：
INSERT INTO `TABLE_NAME` (`TIME_COL`) VALUES ( NOW() );
- 這串 SQL 語法可以找出 TIME_COL 所紀錄的時間在五分鐘內的資料：
SELECT * FROM `TABLE_NAME` WHERE `TIME_COL` > NOW() - INTERVAL 5 MINUTE;
- 這串 SQL 語法可以讓所有資料的 TIME_COL 值往後延 2 個月：
UPDATE `TABLE_NAME` SET `TIME_COL`=(`TIME_COL` + INTERVAL 2 MONTH) WHERE 1;
雖然 datetime 跟 timestamp 的資料顯示出來都是一樣的，但兩者是不同的，詳情可以參考 MySQL 官方手冊對 timestamp 資料型態的說明 。
3 月 9 2008
在 MySQL 中處理網路 IP 位址型的資料..
一、兩年以前，我會直接在 MySQL 中，以 varchar 型態，直接把 IP address 存入。
直到我在某次進行系統開發時，翻過 MySQL 的 Operator and Function Reference ，才發現這兩個好用的 functions ：
透過這兩個 functions ，我開始用 unsigned int 型態來存 IP address。
跟以往的 varchar 比較，使用 unsigned int ，搭配這兩個 functions 有著以下這兩項優勢：
尤其是第二項，以往使用 varchar 型態儲存 IP address 時，我必須先取出資料，再使用字串處理函式，甚至是正規表示式對字串進行切割。
而今，運用 INET_ATON() ，把原本的 IP address 轉成數字存成 unsigned int 之後，我只要把區段的頭、尾 IP address 用 INET_ATON() 轉換成數字，就可以利用大於、小於，甚至是 MySQL 的 between 來作比對或判斷了。
如果遇到的是以 CIDR 表示的區段，使用 PHP 開發程式或系統的人也可以選用 Pear 的 Net_IPv4 套件，呼叫 parseAddress() ，取得該區段的頭、尾 IP 等資訊，再運用 INET_ATON() ，一樣可以利用大於、小於、MySQL 的 between 來作比對或判斷。
By Joe Horn • Database, Network 0 • Tags: IP address, MySQL