FireDAC 下的 Sqlite [9] - 关于排序

SQLite 内部是按二进制排序, 可以支持 ANSI; FrieDAC 通过 TFDSQLiteCollation 支持了 Unicode 排序, 并可通过其 OnCompare 事件自定义排序.

下面的例子, 测试了这两种排序的不同.

---

可把下面代码直接贴在空白窗体上, 以快速完成窗体设计:
object DBGrid1: TDBGrid
  Left = 0
  Top = 0
  Width = 297
  Height = 199
  Align = alLeft
  DataSource = DataSource1
  TabOrder = 0
  TitleFont.Charset = DEFAULT_CHARSET
  TitleFont.Color = clWindowText
  TitleFont.Height = -11
  TitleFont.Name = 'Tahoma'
  TitleFont.Style = []
end
object Button1: TButton
  Left = 303
  Top = 24
  Width = 110
  Height = 25
  Caption = 'SQLite '#20869#32622#25490#24207
  TabOrder = 1
  OnClick = Button1Click
end
object Button2: TButton
  Left = 303
  Top = 65
  Width = 110
  Height = 25
  Caption = 'FireDAC '#40664#35748#25490#24207
  TabOrder = 2
  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'
  LocaleName = 'zh-CN'
  Left = 240
  Top = 120
end

---

代码:

---

procedure TForm1.FormCreate(Sender: TObject);
var
  i: Integer;
  LCode: Integer;
begin
  {给 FDSQLiteCollation1 设定参数}
  FDSQLiteCollation1.DriverLink := FDPhysSQLiteDriverLink1;
//  FDSQLiteCollation1.CollationKind := scCompareString; //这是默认值(Unicode 不区分大小写, 在 Win 下是调用 WinAPI.CompareString); 使用其他选项需要自定义排序规则
  FDSQLiteCollation1.LocaleName := 'zh-CN';
  FDSQLiteCollation1.Flags := [sfIgnoreCase];
  FDSQLiteCollation1.CollationName := 'MyCollation';     //下面所有的调用全要依赖这个名称
  FDSQLiteCollation1.Active := True;

  FDConnection1.Params.Add('DriverID=SQLite');
//  FDConnection1.Params.Add('OpenMode=CreateUTF8'); //这是默认值, 可选 CreateUTF16(Unicode)

  {建立测试表, 三个字段 str(汉字), code(汉字对应的 Unicode 值), id(添加顺序)}
  FDConnection1.ExecSQL('CREATE TABLE MyTable(str string(10), code integer, id integer)');
//  FDConnection1.ExecSQL('CREATE TABLE MyTable(str string(10) COLLATE MyCollation, code integer, id integer)'); //用在表设计时

  {添加测试数据数据}
  for i := 0 to 99 do
  begin
    LCode := Random($9FA5-$4E00);
    FDConnection1.ExecSQL('INSERT INTO MyTable(str, code, id) VALUES(:1, :2, :3)', [WideChar($4E00 + LCode), LCode, i+1]);
  end;

  FDQuery1.Open('SELECT * FROM MyTable'); //无排序
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT * FROM MyTable ORDER BY str'); //SQLite 内置排序
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT * FROM MyTable ORDER BY str COLLATE MyCollation'); //FireDAC 默认排序
end;

---

测试效果图: