Tag: MySQL

MySQL: Add time value to a date value

Posted by – January 15, 2012

在改 MySQL 查詢時碰到需要設定未來時間的情況,翻了一下 MySQL 的文件找到指定的方法。

ADDDATE() DATE_ADD() 來增加時間,像這樣...

SELECT ADDDATE( NOW(), INTERVAL 15 HOUR);

就可以得到距離現在 15 個小時之後的時間。HOUR 可以替換成 YEAR、MONTH、DAY 等等,完整的單位列表可參考這裡。ADDDATE() 與 DATE_ADD() 的差別只有名稱不同,用法與效果相同。
如果是要減去時間,就用 SUBDATE() DATE_SUB()

MySQL: Displaying Rows With COUNT 0 With Group By

Posted by – October 14, 2011

MySQL 裡用 Count 和 Group By 來做統計是常用的寫法。舉例來說,以下兩個 table,一個紀錄公司行號的資料,一個紀錄員工資料。

Company
id company
1 Google
2 Microsoft
Employee
id employee company
1 Jack Google
2 Eric Microsoft

More

MySQL Reset Auto Increment Number

Posted by – July 3, 2011

Database 裡在開新 table 時,常會使用俱有自動增值屬性 (auto increment) 的欄位來當 primary key,而常見的命名也不外乎是 ID 或是 post_id 之類的。每當插入一筆新資料,ID 就會自動加一,是很方便的功能。不過在刪除資料時,ID 值並不會重設,留下很多空的序列,像是 1, 2, 4, 7, 12 這樣,尤其是系統開發階段更是如此。

除了將 table 刪除,重匯資料以外,MySQL 官網上也有提到如何重設 auto-increment 的指標值。只要輸入下列 query 就能指定指標值:

ALTER TABLE tbl AUTO_INCREMENT = 1;

當然數字可以依需求更改。

若是要在插入資料時就指定數值,要輸入下列 query:

SET insert_id = 40;
INSERT INTO tbl VALUES ('blah', '...');

馬上接著的 insert into 就會是指定的 ID 值了,這個語法是一次性的,並不會影響其他插入的資料。

或是先入插入資料,再用 UPDATE 也可以更改 ID 值。

INSERT INTO tbl VALUES ('blah', '...');
UPDATE tbl SET id = 40;

若是需要知道最後一筆新資料的 ID 值,可以用以下 query:

SELECT LAST_INSERT_ID();

 

MySQL 字串連接 concat

Posted by – June 30, 2011

在 MySQL 資料庫中想產生一組獨一無二的 key,這樣可以做為比對的依據。打算直接在資料庫上操作新增欄位,至於 key 的產生是很直覺地想用 md5() 這個 function 來處理。但是因為要拿什麼資料來雜湊呢? 想說就用其他欄位加一加,再加上時間來湊。很直覺地寫下這樣的 query

SELECT md5( id + name + now() )

可惜這樣的結果會因為 id 是 int,而且 name 是 string 而產生非預期的結果。解決方法是把所有的欄位轉成 string 來處理,這時候需要 concat() 來組合字串。

SELECT md5( concat( id, name, now() ) )

這樣就可以得到正確的 md5 值了。

deaf03c84d18a56b70e0b7d6438ada3e

[Excel] Convert Date and UNIX time

Posted by – June 2, 2011

這個問題是最近工作上碰到的,把 SQL 裡的 time 欄位輸出,整理成 Excel 給 PM,不過報表裡的時間都是 UNIX time 格式,除非有超強的心算,不然一般人是沒辦法直接看著 UNIX time 換成算時間日期的。

UNIX time 是以 1970 年 1 月 1 日零點 (UTC) 為起點,每過一秒加 1 的時間計算方式,更詳細的介紹請參考維基百科。根據這個公式,本文撰文時刻是 1306971424

Excel 內部的時間計算方式和 UNIX time 有異曲同工之妙,不過起點是 1900 年 1 月 1 日,日期是整數,時間是小數,詳情請參考這篇如何在 Excel 中使用日期與時間。翻遍 Excel 2010 的 Help 也找不到和 UNIX 或 POSIX 相關的 function,看來只能土法轉換了。

轉換公式 =Unixtime / 86400 + 25569

More