在 Hibernate Criteria 裡使用 SQL Function

想要執行類似以下的 SQL 語法。

select * from book order by length(title) asc;

在 Hibernate Criteria 裡要怎麼使用呢？在 Criteria、Property 與 Projections 裡怎麼也找不到相關的 API，最後 Google 才說，要用 @Formula 建立虛擬欄位。

Hibernate 提供的 @Formula 有兩種用途，第一是使用資料庫的運算，而非 JVM，第二是虛擬欄位，也就是不存在資料庫中的欄位，由其他欄位計算產生出來的結果。

在 Windows 上 import 大量的 SQL 到 Mysql，超慢

不管是用指令或者用 Client UI 都很慢，慢到一兩 MB 的 SQL 就要數個小時，一定有問題！

Google 得到的結果。

要在 SQL 裡加上兩行指令：

SET autocommit=0;
-- 原 SQL
-- 原 SQL
-- 原 SQL
-- 原 SQL
-- 原 SQL.......成千上萬行
COMMIT;

然後從命令列下指令，Client UI  還是慢。

mysql -u <user> -p<password> --default_character_set=utf8  <database> < BigBigBigSQL.sql

因為 SQL 檔是 UTF-8 編碼，所以要加上 --default_character_set=utf8，不然中文會變成亂碼。

就這樣兩行 code，馬上上天堂，匯入時間從一個晚上進步到五秒。
---
---
---

複製 MySQL Table（Copy MySQL Table）

語法分兩步驟，先建立 table，再複製資料。

create table table_new like table_copy;
insert into table_new select * from table_copy;

---

將查詢結果（select）寫到（update）資料表

需求是「代碼轉換」，要將 table 裡的「舊代碼」參照「代碼對照表」轉換成「新代碼」，舉例說明，要將舊代碼「A01」轉換成新代碼「A001」，而「A01」對照「A001」的訊息都寫在代碼對照表裡；或者要將 A table 裡的代碼寫到 B table 裡，A 與 B 之間有 Foreign Key。

PHP 中文變亂碼了

在本機表單輸入中文都沒有問題，上傳到測試環境就變成 ???。

依照在 Java 的經驗，頭開始痛起來。

幸好問題不難找，先到 phpmyadmin 直接輸入一筆資料看看，哇，亂碼，運氣真好，兇手是資料庫。

SQL 在數值計算時對 NULL 的處理

假設 table test 資料如下：

id    num_a    num_b
------------------------
1     2        1
2     3        (null)

執行以下語法：

select sum(num_a), sum(num_b) from test;

得到

sum(num_a)    sum(num_b)
------------------------
5             1

精確的 where 條件以改善 SQL 效能

最初的 SQL 如下，以 group by 方式取得欄位總和：

select ... sum(aField)  ... from ... where ... group by ... ;

當資料成千上萬筆後，效能就慢下來了。

因為需求是要取得加總的值，如果資料不存在或加總為零，則視為零，在這樣的前提下，可以在 where 加上必須大於零的條件（或小於零的狀況）。

select ... sum(aField)  ... from ... where ... and aField > 0 ... group by ... ;

如果資料庫中大多數的值為空或為零時，這樣的修正可以大大改進效能，改進的幅度視值為空或為零的比例而定。

移除 Subquery 以提昇 SQL 效能

Subquery 在資料量不大時很好用，但是遇到主 query 有成千上萬筆資料時，速度就嚴重慢下來了， 狀況類似 N+1 Select。

select .... (select sum(...) from ...) ... from ... ;

第一個改善方式就是將 subquery 打掉，改用 join 取代。

select ... sum(...) ... from ... join ... ;

但是萬一遇到無法以 join 取代 subquery 時，這時候就要拿記憶體來換了。