SQLite: MQL5 原生 SQL 数据库操纵·进阶篇
（2/3）· 从 MetaEditor 调试到事务批量提交，6 个被忽略的 SQLite 提速与落地细节
不少交易者把 SQLite 当成本地 CSV 的替代品，却在 EA 里逐条 INSERT 把回测结果写库，单次十万行优化数据能卡掉半分钟。更隐蔽的问题是，手写 SQL 字符串拼错只能在运行时崩，MetaEditor 里根本看不到哪列 bind 丢了。接上篇铺垫的建库与基础读写，这一篇直接进工程层。
◍ 用 MetaEditor 直连 sqlite 验证查询再写代码
在 MT5 里跑数据库相关逻辑，出错时所有操纵函数都会返回错误代码。四条底线先记牢：DatabaseFinalize() 销毁查询控柄后才能算干净；收工前必须用 DatabaseClose() 关库；每次执行都要查结果；一旦报错，先销毁查询再关库，顺序反了容易句柄泄漏。 最麻烦的是查询还没建起来时，你根本不知道错在哪。MetaEditor 本身能直接开 *.sqlite 文件做交互式 SQL，不用先写代码。以终端公用文件夹里的 company.sqlite 为例：双击导航器里的 COMPANY 表，状态栏会自动拼出 “SELECT * FROM COMPANY”，按 F9 或点执行就能跑，结果和错误都落在编辑器日志里。 先在编辑器把统计类查询（比如求和、平均）跑通，再搬进 MQL5 才稳。下面这段就是求和与平均工资的实跑代码，回测打印出来的硬数据是：Total salary=125000.0，Average salary=31250.0。 别把正态当圣经 编辑器里能跑通不代表 EA 里一定顺。DatabasePrepare 返回 INVALID_HANDLE 时，先打 GetLastError() 看码，再关库 return，别硬往下读。
Print("Some statistics:"); class=class="str">"cmt">//--- prepare a new query about the sum of salaries request=DatabasePrepare(db, "SELECT SUM(SALARY) FROM COMPANY"); if(request==INVALID_HANDLE) { Print("DB: ", filename, " request failed with code ", GetLastError()); DatabaseClose(db); class="kw">return; } class="kw">while(DatabaseRead(request)) { class="type">class="kw">double total_salary; DatabaseColumnDouble(request, class="num">0, total_salary); Print("Total salary=", total_salary); } class=class="str">"cmt">//--- remove the query after use DatabaseFinalize(request); class=class="str">"cmt">//--- prepare a new query about the average salary request=DatabasePrepare(db, "SELECT AVG(SALARY) FROM COMPANY"); if(request==INVALID_HANDLE) { Print("DB: ", filename, " request failed with code ", GetLastError()); ResetLastError(); DatabaseClose(db); class="kw">return; } class="kw">while(DatabaseRead(request)) { class="type">class="kw">double aver_salary; DatabaseColumnDouble(request, class="num">0, aver_salary); Print("Average salary=", aver_salary); } class=class="str">"cmt">//--- remove the query after use DatabaseFinalize(request);
「用 DatabaseReadBind 把整行塞进结构」
逐列调用 DatabaseColumnInteger、DatabaseColumnText 这类函数虽然能统一处理任意查询结果，但代码量会迅速膨胀。当查询返回表的字段顺序和类型提前确定时，这种逐列读取方式性价比很低。 DatabaseReadBind() 的思路是直接把当前记录整体映射到事先声明好的结构里。下面这段声明了 Person 结构，字段依次是 id(int)、name(string)、age(int)、address(string)、salary(double)，字段排列必须和 SQL 结果集的列顺序严格对应。 调用处用一个 for 循环配合 DatabaseReadBind(request, person) 作为条件，每次迭代自动把下一行读进 person，随后直接打印各成员即可，不再出现任何 ColumnXXX 调用。相比逐列读取，行数大约能砍掉一半，且字段访问变成了 person.salary 这种直观写法。 实际在 MT5 里跑时，若结果集某列类型与结构成员不匹配，绑定会失败并返回 false，循环自然终止。外汇与贵金属相关的本地数据缓存用这套逻辑处理批量历史报价时，注意 SQLite 返回 double 的精度问题，高风险品种 Tick 级数据建议先小批量验证。
class="kw">struct Person { class="type">int id; class="type">class="kw">string name; class="type">int age; class="type">class="kw">string address; class="type">class="kw">double salary; }; class=class="str">"cmt">//--- display obtained query results Person person; Print("Persons with salary > class="num">15000:"); for(class="type">int i=class="num">0; DatabaseReadBind(request, person); i++) Print(i, ": ", person.id, " ", person.name, " ", person.age, " ", person.address, " ", person.salary); class=class="str">"cmt">//--- remove the query after use DatabaseFinalize(request);
用事务锁把成交表批量写入压到极限
往 SQLite 里灌历史成交时，逐条 INSERT 会反复抢锁、刷盘，延迟随行数线性膨胀。把整批写操作包进 DatabaseTransactionBegin 和 DatabaseTransactionCommit 之间，数据库只在首尾各锁一次、落盘一次，中间命令纯内存排队。 官方示例里这套写法能把批量表操作提速数百倍，量级差异在万行以上成交回放时最明显。出错就调 DatabaseTransactionRollback 整批回退，比单行容错干净得多。 下面这段是从历史库捞成交并批量落表的骨架，注意高亮那行才是提速开关： [CODE] //--- auxiliary variables ulong deal_ticket; // deal ticket long order_ticket; // a ticket of an order a deal was executed by long position_ticket; // ID of a position a deal belongs to datetime time; // deal execution time long type ; // deal type long entry ; // deal direction string symbol; // a symbol a deal was executed for double volume; // operation volume double price; // price double profit; // financial result double swap; // swap double commission; // commission long magic; // Magic number (Expert Advisor ID) long reason; // deal execution reason or source //--- go through all deals and add them to the database bool failed=false; int deals=HistoryDealsTotal(); // --- lock the database before executing transactions DatabaseTransactionBegin(database); for(int i=0; i<deals; i++) { deal_ticket= HistoryDealGetTicket(i); order_ticket= HistoryDealGetInteger(deal_ticket, DEAL_ORDER); position_ticket=HistoryDealGetInteger(deal_ticket, DEAL_POSITION_ID); time= (datetime)HistoryDealGetInteger(deal_ticket, DEAL_TIME); type= HistoryDealGetInteger(deal_ticket, DEAL_TYPE); entry= HistoryDealGetInteger(deal_ticket, DEAL_ENTRY); symbol= HistoryDealGetString(deal_ticket, DEAL_SYMBOL); volume= HistoryDealGetDouble(deal_ticket, DEAL_VOLUME); price= HistoryDealGetDouble(deal_ticket, DEAL_PRICE); profit= HistoryDealGetDouble(deal_ticket, DEAL_PROFIT); swap= HistoryDealGetDouble(deal_ticket, DEAL_SWAP); [/CODE] 逐行拆一下：前面 14 行只是声明成交字段容器，deal_ticket 到 reason 覆盖票号、订单号、持仓 ID、时间、类型、方向、品种、量、价、利、息、费、魔法码、来源。 HistoryDealsTotal 取总数，DatabaseTransactionBegin(database) 才是关键——此后 for 循环里所有 HistoryDealGet* 读取加后续 INSERT 都不再单条锁库，直到你显式 Commit。外汇与贵金属行情跳空多、成交密集，回测落表不开事务可能卡到超时，开事务后写入耗时倾向下降 1~2 个数量级。
class=class="str">"cmt">//--- auxiliary variables class="type">class="kw">ulong deal_ticket; class=class="str">"cmt">// deal ticket class="type">long order_ticket; class=class="str">"cmt">// a ticket of an order a deal was executed by class="type">long position_ticket; class=class="str">"cmt">// ID of a position a deal belongs to class="type">class="kw">datetime time; class=class="str">"cmt">// deal execution time class="type">long type ; class=class="str">"cmt">// deal type class="type">long entry ; class=class="str">"cmt">// deal direction class="type">class="kw">string symbol; class=class="str">"cmt">// a symbol a deal was executed for class="type">class="kw">double volume; class=class="str">"cmt">// operation volume class="type">class="kw">double price; class=class="str">"cmt">// price class="type">class="kw">double profit; class=class="str">"cmt">// financial result class="type">class="kw">double swap; class=class="str">"cmt">// swap class="type">class="kw">double commission; class=class="str">"cmt">// commission class="type">long magic; class=class="str">"cmt">// Magic number(Expert Advisor ID) class="type">long reason; class=class="str">"cmt">// deal execution reason or source class=class="str">"cmt">//--- go through all deals and add them to the database class="type">bool failed=class="kw">false; class="type">int deals=HistoryDealsTotal(); class=class="str">"cmt">// --- lock the database before executing transactions DatabaseTransactionBegin(database); for(class="type">int i=class="num">0; i<deals; i++) { deal_ticket= HistoryDealGetTicket(i); order_ticket= HistoryDealGetInteger(deal_ticket, DEAL_ORDER); position_ticket=HistoryDealGetInteger(deal_ticket, DEAL_POSITION_ID); time= (class="type">class="kw">datetime)HistoryDealGetInteger(deal_ticket, DEAL_TIME); type= HistoryDealGetInteger(deal_ticket, DEAL_TYPE); entry= HistoryDealGetInteger(deal_ticket, DEAL_ENTRY); symbol= HistoryDealGetString(deal_ticket, DEAL_SYMBOL); volume= HistoryDealGetDouble(deal_ticket, DEAL_VOLUME); price= HistoryDealGetDouble(deal_ticket, DEAL_PRICE); profit= HistoryDealGetDouble(deal_ticket, DEAL_PROFIT); swap= HistoryDealGetDouble(deal_ticket, DEAL_SWAP);
◍ 用事务批写把成交入库提速五百倍
把历史成交逐笔写进本地 SQLite，若不用事务包裹，2737 笔成交的写入耗时会冲到 25818.9 毫秒，基本卡死主线程。 套上 DatabaseTransactionBegin / Commit 后，同一批数据只花 48.5 毫秒，加速比 532.8 倍。外汇与贵金属交易回测数据量大，这种延迟差异会直接拖垮 EA 的盘后统计。 下面这段是核心落库循环里的关键片段：先取每笔成交的佣金、魔术码、触发原因，再拼 SQL 插入，失败就回滚。 commission= HistoryDealGetDouble(deal_ticket, DEAL_COMMISSION); magic= HistoryDealGetInteger(deal_ticket, DEAL_MAGIC); reason= HistoryDealGetInteger(deal_ticket, DEAL_REASON); //--- add each deal to the table using the following query string request_text=StringFormat("INSERT INTO DEALS (ID,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON)" "VALUES (%d, %d, %d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %d, %d)", deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason); if(!DatabaseExecute(database, request_text)) { PrintFormat("%s: failed to insert deal #%d with code %d", __FUNCTION__, deal_ticket, GetLastError()); PrintFormat("i=%d: deal #%d %s", i, deal_ticket, symbol); failed=true; break; } } //--- check for transaction execution errors if(failed) { //--- roll back all transactions and unlock the database DatabaseTransactionRollback(database); PrintFormat("%s: DatabaseExecute() failed with code %d", __FUNCTION__, GetLastError()); return(false); } //--- all transactions have been performed successfully - record changes and unlock the database DatabaseTransactionCommit(database); 逐行看：前 three 行用 HistoryDealGetDouble / Integer 抓佣金、magic、reason 三个字段；StringFormat 拼出带 14 个列的 INSERT 语句，%G 对应浮点成交量与价格；DatabaseExecute 执行失败就打印错误并置 failed，循环 break。 外层 failed 判断里调 DatabaseTransactionRollback 整体回滚，成功路径上 DatabaseTransactionCommit 一次性落盘。开 MT5 把这段塞进你的历史扫描 EA，对比有无事务包裹的 Print 耗时即可验证。
commission= HistoryDealGetDouble(deal_ticket, DEAL_COMMISSION); magic= HistoryDealGetInteger(deal_ticket, DEAL_MAGIC); reason= HistoryDealGetInteger(deal_ticket, DEAL_REASON); class=class="str">"cmt">//--- add each deal to the table class="kw">using the following query class="type">class="kw">string request_text=StringFormat("INSERT INTO DEALS(ID,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON)" "VALUES(%d, %d, %d, %d, %d, %d, &class="macro">#x27;%s&class="macro">#x27;, %G, %G, %G, %G, %G, %d, %d)", deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason); if(!DatabaseExecute(database, request_text)) { PrintFormat("%s: failed to insert deal #%d with code %d", __FUNCTION__, deal_ticket, GetLastError()); PrintFormat("i=%d: deal #%d %s", i, deal_ticket, symbol); failed=true; class="kw">break; } } class=class="str">"cmt">//--- check for transaction execution errors if(failed) { class=class="str">"cmt">//--- roll back all transactions and unlock the database DatabaseTransactionRollback(database); PrintFormat("%s: DatabaseExecute() failed with code %d", __FUNCTION__, GetLastError()); class="kw">return(class="kw">false); } class=class="str">"cmt">//--- all transactions have been performed successfully - record changes and unlock the database DatabaseTransactionCommit(database);
「用一条 SQL 把成交拼成完整交易」
SQL 查询的实用价值在于，不用写循环遍历代码，就能直接对源数据排序、筛选和重组。在 MT5 的历史成交表里，开仓与平仓成交靠同一个 position_id 关联，对冲账户上这种成对结构尤其干净，一次 INNER JOIN 就能把两笔 deal 并成一条交易记录。 下面这段脚本先判断 DEALS 表存在，再执行一条 INSERT INTO … SELECT 把入场（entry=0）和出场（entry=1）按 position_id 自连接，抽取时间、品种、成交量、双边手续费等字段写进 TRADES 表。比起早年手搓 for 循环比对成交，单查询的耗时通常能压到微秒级。 实测某账户历史成交 2741 笔，脚本跑完首 10 条记录即呈现 ticket、position_ticket、time、type、entry、symbol、volume、price、profit 等字段。把附件 trades.sqlite 在 MetaEditor 打开，和你终端历史里的仓位逐条比对，就能验证配对是否漏单或错配。 开 MT5 挂上这段逻辑前，注意外汇和贵金属杠杆交易本身高风险，历史成交配对只反映已平仓结果，不代表任何未来收益倾向。
class=class="str">"cmt">//--- fill in the TRADES table class="kw">using an SQL query based on DEALS table data class="type">class="kw">ulong start=GetMicrosecondCount(); if(DatabaseTableExists(db, "DEALS")) { class=class="str">"cmt">//--- fill in the TRADES table if(!DatabaseExecute(db, "INSERT INTO TRADES(TIME_IN,TICKET,TYPE,VOLUME,SYMBOL,PRICE_IN,TIME_OUT,PRICE_OUT,COMMISSION,SWAP,PROFIT) " "SELECT " " d1.time as time_in," " d1.position_id as ticket," " d1.type as type," " d1.volume as volume," " d1.symbol as symbol," " d1.price as price_in," " d2.time as time_out," " d2.price as price_out," " d1.commission+d2.commission as commission," " d2.swap as swap," " d2.profit as profit " "FROM DEALS d1 " "INNER JOIN DEALS d2 ON d1.position_id=d2.position_id " "WHERE d1.entry=class="num">0 AND d2.entry=class="num">1")) { Print("DB: fillng the TRADES table failed with code ", GetLastError()); class="kw">return; } } class="type">class="kw">ulong transaction_time=GetMicrosecondCount()-start;
从成交明细看多品种同频挂单
上面这组 MT5 历史订单_dump 记录了 2019.09.05 22:39 到次日 08:00 的六笔动作，首行是账户初始化（余额 2000.00000，无品种、无仓位），其后五笔全是 0.10 手的标准化建仓。 细看时间戳：06:00:03 同时打出 USDCAD 多单（1.32320）与 USDCHF 空单（0.98697），07:00:00 又同步落 EURUSD 空（1.10348）和 AUDUSD 多（0.68203），08:00 再加一笔 USDCHF 空（0.98701）。这种整点批量触发，倾向是某个多品种网格或相关性对冲 EA 在按小时节拍扫单。 每笔 commission 固定在 -0.11 到 -0.18 之间、magic 统一为 3，说明不是手动操作。外汇与贵金属杠杆交易高风险，这类同频挂单在美元指数单边时会让相关性头寸同时浮亏，复制前建议先在策略测试器用 2019.09 这段数据跑一遍。