Delphi FireDAC SQLite Unicode 排序與自定義比較（九）：TFDSQLiteCollation 實現中文拼音/筆畫排序

前言

SQLite 內建僅支援二進制排序，中文亂序嚴重。FireDAC TFDSQLiteCollation 元件封裝 SQLite sqlite3_create_collation API，支援系統 Locale（如 zh-CN）與完全自定義比較函數，輕鬆實現拼音、筆畫、部首等中文排序。

表單元件配置

object DBGrid1: TDBGrid; Left = 0; Top = 0; Width = 297; Height = 199; Align = alLeft; DataSource = DataSource1; end
object Button1: TButton; Left = 303; Top = 24; Width = 110; Height = 25; Caption = 'SQLite 二進制排序'; OnClick = Button1Click; end
object Button2: TButton; Left = 303; Top = 65; Width = 110; Height = 25; Caption = 'FireDAC Unicode 排序'; OnClick = Button2Click; end
object FDConnection1: TFDConnection; Left = 34; Top = 24; end
object FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink; Left = 143; Top = 24; end
object FDGUIxWaitCursor1: TFDGUIxWaitCursor; Provider = 'Forms'; Left = 260; Top = 24; end
object FDQuery1: TFDQuery; Connection = FDConnection1; Left = 32; Top = 88; end
object DataSource1: TDataSource; DataSet = FDQuery1; Left = 132; Top = 88; end
object FDSQLiteCollation1: TFDSQLiteCollation;
  DriverLink = FDPhysSQLiteDriverLink1
  CollationName = 'MyCollation'   // SQL 中使用 COLLATE MyCollation
  LocaleName = 'zh-CN'            // 系統 Locale 排序
  Left = 240; Top = 120; end

核心代碼

procedure TForm1.Button1Click(Sender: TObject);
begin
  // 預設二進制排序
  FDQuery1.Open('SELECT * FROM TestTable ORDER BY Name');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  // 使用 FireDAC 註冊的 Unicode 排序
  FDQuery1.Open('SELECT * FROM TestTable ORDER BY Name COLLATE MyCollation');
end;

進階：自定義比較函數

設定 FDSQLiteCollation1.OnCompare 事件，實現任意排序邏輯（如按拼音首字母、筆畫數、自定義權重）：

procedure TForm1.FDSQLiteCollation1Compare(Sender: TObject; const ALeft, ARight: string; var AResult: Integer);
var
  PLeft, PRight: string;
begin
  // 範例：轉拼音首字母比較（需自行實現 GetPinyinFirstChar）
  PLeft  := GetPinyinFirstChar(ALeft);
  PRight := GetPinyinFirstChar(ARight);
  if PLeft < PRight then AResult := -1
  else if PLeft > PRight then AResult := 1
  else AResult := 0;
end;

效果對比

結語

TFDSQLiteCollation 以聲明式方式解決 SQLite 中文排序難題：LocaleName 一行搞定系統標準排序，OnCompare 開放無限自定義空間。無需修改 SQL 結構，僅在 ORDER BY 子句加上 COLLATE MyCollation 即可生效。