Tag: MySQL

MAMP MySQL 啟動失敗

Posted by – October 7, 2017

原本好好的 MySQL 突然啟動失敗....

我知道現在比較流行 Docker,不過我還有很多舊專案放在 MAMP 裡,也是要維護一下的。

這 MySQL 無法啟動這問題毫無頭緒,還在思考怎麼查 log 時就 google 到解法了

刪除 MAMP/db/mysql56 下的 ib_logfile*

我看我的資料夾有 ib_logfile0 和 ib_logfile1 兩個檔案,刪掉再重啟 MAMP 就正常了

MySQL: GROUP_CONCAT function

Posted by – December 11, 2013

在 MySQL 上經常遇到如下的資料:

id    value

 1     A

 1     B

 1     C

 2     A

 2     C

這種情況在要輸出報表的時候就覺得很麻煩,一旦 Group By 起來,就失去了 value 的內容。不想丟失 value 的內容也不想出現多筆重複的 id 就得多下幾次 sql 才能取得完整的資料。

今天又是一個偶然在 StackOverflow 上看到 GROUP_CONCAT() 這個沒見過的指令,馬上試試有什麼效果。

SELECT id, GROUP_CONCAT(value) as values FROM table GROUP BY id ORDER BY id

就能得到以下的結果:

id=1, values=A,B,C
id=2, values=A,C

神方便啊,在 MySQL 的文件中有完整的 GROUP_CONCAT 用法,包括可以排序,改變分隔符號。例如:

SELECT id, GROUP_CONCAT(value ORDER BY value DESC SEPARATOR ': ') as values FROM table GROUP BY id ORDER BY id

結果就變這樣:

id=1, values=C: B: A

id=2, values=C: A

製作報表就靠它了。

MySQL: How To Count Rows But Still Show All The Rows?

Posted by – October 21, 2013

之前寫過 MySQL: Displaying Rows With COUNT 0 With Group By,用於強制顯示 Count() 為 0 的資料。最近又看到另一種寫法,因為我自己很少這麼寫,所以留個筆記。

SELECT col1,
       (SELECT COUNT(*) 
          FROM table1 t1 
         WHERE t1.id = t2.t1_id) as Total
  FROM table2 t2
GROUP BY t2.id;

把子查詢放在 SELECT 敘述。試了一下,這種寫法的查詢速度會比單純的 left join 慢上許多,實際應用要視資料筆數來決定。

MySQL: Convert Negative Value to Zero

Posted by – May 15, 2013

在 MySQL 裡經常會變動一些關於計數的數值。例如

UPDATE table SET count = count + 1

碰到減一的情況時,為了避免計數變成負值,可以多加一個判斷。

UPDATE table SET count = GREATEST(count - 1, 0)

如果是碰到空值的話,再多一步。

UPDATE table SET count = GREATEST(IFNULL(count, 0) - 1, 0)

簡單。

MySQL: On Duplicate Key Update Do Nothing

Posted by – May 10, 2013

在 MySQL 資料庫中 Insert 資料時常會有些例外情況需要處理,像是碰到 UNIQUE 鍵值重複時,如果不處理就會出現錯誤。

最近碰到一個需求是如果 UNIQUE 值重複時... 就什麼也不做。

有兩種解法,INSERT IGNORE 和 REPLACE。不過各自有缺點,前者是忽略錯誤訊息,但仍有可能出現警告,尤其是當以下情況發生時:

  • 插入值遇到 PRIMARY KEY 或 UNIQUE 重複。
  • 在 NOT NULL 欄位插入空值。

而 REPLACE 實際上在碰到重複值的行為是先 DELETEINSERT,顯而易見的問題是 auto-increment ID 會變動.... 外來鍵馬上掛掉。而且多一步 DELETE 對效率有負面影響。

第三種解法是 ON DUPLICATE KEY UPDATE,當值重複時觸發一個 UPDATE 行為。

INSERT INTO table_tags 
   SET tag = 'tag' 
    ON DUPLICATE KEY UPDATE tag = tag;

碰到重複鍵時結果是

Query OK, 0 rows affected (0.07 sec)

什麼也沒做,讚啦。

雖然什麼事都沒做,不過流程上需要 insert id 去跑下一步.... 理論上沒有實際進行 INSERT 行為就沒有 insert id,不過還好一樣可以趁著觸發 UPDATE 的時候做個手腳。將上句改為...

INSERT INTO table_tags 
   SET tag = 'tag' 
    ON DUPLICATE KEY UPDATE tag = tag,
                            tag_id = LAST_INSERT_ID(tag_id);

就可以取得 insert id,碰到重複值也能取得現有資料的 id。

How to create MySQL configuration file (my.cnf) on MAMP?

Posted by – March 14, 2013

因為想修改 MySQL 的一些設定,卻遍尋不著 MAMP 的 my.cnf 寫在哪,後來才在一篇日文的教學裡找到蛛絲馬跡。

用以下指令可以找到 MAMP 裡的 .cnf,一開始並沒有出現 my.cnf,而是些預設的範本。

$ find /Applications/MAMP/ -name my*.cnf
/Applications/MAMP//Library/support-files/my-huge.cnf
/Applications/MAMP//Library/support-files/my-innodb-heavy-4G.cnf
/Applications/MAMP//Library/support-files/my-large.cnf
/Applications/MAMP//Library/support-files/my-medium.cnf
/Applications/MAMP//Library/support-files/my-small.cnf

接著用以下指令,會看到一大排資訊。

$ /Applications/MAMP/Library/bin/mysql --help

其中可以找到這麼一行... 這一行就是 MySQL 讀取 my.cnf 的順序。

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /Applications/MAMP/conf/my.cnf ~/.my.cnf

接著把我需要的設定寫在其中一個就可以了。為了方便記憶和下次再找檔案,我會建議寫在這裡。

/Applications/MAMP/conf/my.cnf

PHP: Finding the year quarter for a date

Posted by – November 29, 2012

在工商業應用中,時間紀錄是系統中非常基本的東西,不過時間的表示方法就五花八門了。最近碰到一個需求是同一個時間欄位可能是 Y-m-d (ex. 2012-11-29)、quarter (Q4 2012)、month (Nov 2012),或是 year (2012),甚至是 TBA (to be announced)。

其中轉換成季 (quarter) 會用到一點點技巧,在此筆記如下:

<?
$timestamp = mktime(0, 0, 0, 11, 29, 2012);
echo ceil(date("m", $timestamp)/3);
?>

只要前面再加個 'Q' 就是第幾季了。
在資料庫部份如何分辨客戶是儲存哪種型態,我的做法是多開一個 date_type 的欄位。

CREATE TABLE `calendar` (
	`c_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`c_date` date NOT NULL,
	`c_date_type` char(1) NOT NULL DEFAULT 'd',
	PRIMARY KEY (`c_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

然後用以下 function 來轉換日期

function date_converter($date_type, $date){

	$timestamp = strtotime($date);

	switch($date_type){
		case 'd':
			return $date;
			break;
		case 'm':
			return date("Y-m-d", mktime(23, 59, 59, date('m', $timestamp) + 1, 0, date('Y', $timestamp)));
			break;
		case 'q':
			$the_quarter = ceil(date("m", $timestamp)/3);
			return date("Y-m-d", mktime(23, 59, 59, $the_quarter * 3 + 1, 0, date('Y', $timestamp)));
			break;
		case 'y':
			return date("Y-m-d", mktime(23, 59, 59, 12, 31, date('Y', $timestamp)));
			break;
		case 't':
			return '2099-12-31';
			break;
	}
}

最後將轉換後的日期與日期型態一份存入資料庫,讀取時根據型態做切換就可以了。

Updated:

後來又碰到一個需求,碰到資料庫日期相同時,例如季和月兩種日期都是記錄最後一天,Q1 與 March 在資料庫裡都是 3 / 31,排序的時候就有可能出現 Q1, March, Q1, March 交錯的現象。解決方法是在 SELECT ... ORDER BY ... 加個自訂排序,用前述的 date_type 來排列。

SELECT * FROM `calendar` ORDER BY `c_date` ASC, FIELD(`c_date_type`, `d`, `m`, `q`, `y`, `t`)

如此就可以避免不同型態日期交錯的問題。

 

How to use the MAMP Mysql command line client in a terminal

Posted by – September 20, 2012

這篇是寫給自己的筆記,因為我老忘了路徑....

Mac 用 Terminal 登入 MAMP MySQL 的路徑如下

/Applications/MAMP/Library/bin/mysql -uroot -p

MySQL: 限制存取單一資料庫

Posted by – April 16, 2012

從客戶那邊拿到一份巨大的資料庫備份,解壓縮後的 .sql 文字檔接近  1GB。由於檔案很大,想要編輯或檢視內容都很不方便,就想說直接匯進測試用的資料庫。用了 source 指令後成功地匯入了,當下看起來也很正常。不過後來只要重開測試主機就會出現完全連不上資料庫的情況,而且屢試不爽,推測大概是客戶連同紀錄資料庫自身狀態的 information_schema 也一併匯出了。

問了客戶的工程師才知道他們匯入時會加指令來限制匯到哪個資料庫。語法如下:

mysql -u USERNAME -p PASSWORD --force --one-database DATABASE

登入 mysql 後再使用 source 匯入,此時若是碰到操作非指定 DB 的 statements 都會被自動略過。

總算順利搞定資料庫,繼續開發工作。又學到了一課。

後來去翻閱 MySQL 官方文件,其實只要加個 -o 就可以了,參考這篇

MySQL: Import large file using SOURCE command

Posted by – March 26, 2012

在 MySQL 匯入 sql 資料大都時候都是用 phpMyAdmin 這個軟體來代勞,不過有時候碰到非常巨大的 sql 檔案,動輒數百 MB,甚至更大時就超過了 phpMyAdmin 處理能力。

這時候比較方便的做法是用命令列來匯入,指令是 SOURCE

mysql> use exampledb;
Database changed

mysql> source C:\data.sql;

等待 MySQL 執行完畢就完工了,無痛匯入巨量的 sql 資料。