Category: Database
-
TablePlus 訂閱一年… 到期啦
忘了當初從哪裡得知 TablePlus 這個軟體的，只記得當時還是開發中的免費軟體。用起來還很陽春，但試用後覺得很有潛力。 後來 TablePlus 轉型成訂閱制的軟體，買了一年的服務。 TablePlus 最初是 macOS 上一款現代化的 Database 管理工具，支援 MySQL、Postgres、Oracle、Microsft SQL、SQLite 等多種關聯與非關聯式資料庫。輔以更親近新式的操作介面。現在也推出 Windows、Linux 版本了，連 Apple M1 處理器也有支援，開發團隊更新非常認真。 不過因為生活與工作環境的變化，我後來就有點少用這款了。這幾天剛好手上有個小 case 想用 MySQL 開發測試。又重啟了 TablePlus，然後發現金鑰即將在七天後過期… 有點難過，沒用夠本的感覺。 雖然續約只需要 39 鎂，但只能啟用一台電腦，對我來還是不太夠用。決定先不續約了。
-
MAMP MySQL 啟動失敗
原本好好的 MySQL 突然啟動失敗…. 我知道現在比較流行 Docker，不過我還有很多舊專案放在 MAMP 裡，也是要維護一下的。 這 MySQL 無法啟動這問題毫無頭緒，還在思考怎麼查 log 時就 google 到解法了 刪除 MAMP/db/mysql56 下的 ib_logfile* 我看我的資料夾有 ib_logfile0 和 ib_logfile1 兩個檔案，刪掉再重啟 MAMP 就正常了
-
將 Sequel Pro 的表導出成 Laravel migration 文件
在 Laravel 學院看到一篇有趣的文，筆記下。 将 Sequel Pro 中已存在的数据表导出为 Laravel 迁移文件 對於用 Mac 的開發者來說頗為實用。Sequel Pro 這款 MySQL GUI 也是我用過最好用、最順手的 DB 管理工具，沒有之一，輕巧高效，可惜支援的 DB 種類不多。
-
Truncate all tables in Oracle database
今天開發團隊終於決定要把開發與測試用的 Oracle 資料庫清空重建了。在長達一年多的開發時間裡，這台測試 DB 是非常重要的開發工具。但長久以來承受了各式各樣亂七八糟的資料，也難以保證測試結果是否符合預期，所以大夥決定將它清空。 不過 Oracle 自己的 GUI 管理工具 SQL Developer 真是頗為難用。 想一次清空 (truncate) 是沒有全選 -> 右鍵 -> 清空 這麼方便的事。但是近百個 table 又不想一個一個清… 就下指令吧。 begin for t in (select table_name from user_tables) loop execute immediate ‘ truncate table ‘||t.table_name; end loop; end; 這樣完成清除資料表的任務。
-
MySQL: GROUP_CONCAT function
在 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,…
-
MySQL: How To Count Rows But Still Show All The Rows?
之前寫過 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
在 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
在 MySQL 資料庫中 Insert 資料時常會有些例外情況需要處理，像是碰到 UNIQUE 鍵值重複時，如果不處理就會出現錯誤。 最近碰到一個需求是如果 UNIQUE 值重複時… 就什麼也不做。 有兩種解法，INSERT IGNORE 和 REPLACE。不過各自有缺點，前者是忽略錯誤訊息，但仍有可能出現警告，尤其是當以下情況發生時： 插入值遇到 PRIMARY KEY 或 UNIQUE 重複。 在 NOT NULL 欄位插入空值。 而 REPLACE 實際上在碰到重複值的行為是先 DELETE 再 INSERT，顯而易見的問題是 auto-increment ID 會變動…. 外來鍵馬上掛掉。而且多一步 DELETE 對效率有負面影響。 第三種解法是 ON DUPLICATE KEY UPDATE，當值重複時觸發一個 UPDATE 行為。 INSERT INTO table_tags SET tag = ‘tag’ ON DUPLICATE KEY UPDATE tag…
-
How to create MySQL configuration file (my.cnf) on MAMP?
因為想修改 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
在工商業應用中，時間紀錄是系統中非常基本的東西，不過時間的表示方法就五花八門了。最近碰到一個需求是同一個時間欄位可能是 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…
-
How to use the MAMP Mysql command line client in a terminal
這篇是寫給自己的筆記，因為我老忘了路徑…. Mac 用 Terminal 登入 MAMP MySQL 的路徑如下 /Applications/MAMP/Library/bin/mysql -uroot -p
-
MySQL: 限制存取單一資料庫
從客戶那邊拿到一份巨大的資料庫備份，解壓縮後的 .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
在 MySQL 匯入 sql 資料大都時候都是用 phpMyAdmin 這個軟體來代勞，不過有時候碰到非常巨大的 sql 檔案，動輒數百 MB，甚至更大時就超過了 phpMyAdmin 處理能力。 這時候比較方便的做法是用命令列來匯入，指令是 SOURCE。 mysql> use exampledb; Database changed mysql> source C:\data.sql; 等待 MySQL 執行完畢就完工了，無痛匯入巨量的 sql 資料。
-
MySQL: Add time value to a date value
在改 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
MySQL 裡用 Count 和 Group By 來做統計是常用的寫法。舉例來說，以下兩個 table，一個紀錄公司行號的資料，一個紀錄員工資料。 Company id company 1 Google 2 Microsoft Employee id employee company 1 Jack Google 2 Eric Microsoft
-
MySQL Reset Auto Increment Number
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 值了，這個語法是一次性的，並不會影響其他插入的資料。 或是先入插入資料，再用…
-
MySQL 字串連接 concat
在 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
