使用EF Core 3.x+Mysql 時,如下EF表達式 生成的語句: SELECT .`id`, .`account`, ... FROM AS WHERE LOCATE(CONVERT( 'test' USING UTF8MB4) COLLATE utf8mb4_bin, .`account` ...
使用EF Core 3.x+Mysql 時,如下EF表達式
var list=await ctx.Set<User>().where(v=>v.Account.Contains("test")).ToListAsync();
生成的語句:
SELECT
`b1`.`id`,
`b1`.`account`,
...
FROM
`basis_user` AS `b1`
WHERE
LOCATE(CONVERT( 'test' USING UTF8MB4) COLLATE utf8mb4_bin,
`b1`.`account`) > 0
其中 LOCATE(CONVERT( 'test' USING UTF8MB4) COLLATE utf8mb4_bin,`b1`.`account`) > 0
是資料庫函數操作,數量量大時,會非常慢.
解決辦法:攔截EF生成的語句,將上述函數操作,替換成like
偽代碼如下:
1:定義攔截器
public class FmtCommandInterceptor : DbCommandInterceptor, IDbCommandInterceptor
{
public static readonly Regex Regex_Replace_MySql_Like
= new Regex(@"LOCATE\(CONVERT\('(?<v>.+?)' USING utf8mb4\) COLLATE utf8mb4_bin, (?<k>`.+`?)\) > 0",
RegexOptions.Compiled | RegexOptions.IgnoreCase);
public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
command.CommandText = Regex_Replace_MySql_Like.Replace(command.CommandText, " ${k} like '%${v}%' ");
return base.ReaderExecuting(command, eventData, result);
}
public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
{
command.CommandText = Regex_Replace_MySql_Like.Replace(command.CommandText, " ${k} like '%${v}%' ");
return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
}
}
2:註冊攔截器
services
.AddDbContextPool<yearDbContext>(o =>
{
o.UseMySql(Configuration.GetConnectionString("youdbConfigName"), mySqlOptions =>
{
mySqlOptions.ServerVersion(new Version(5, 6, 40), ServerType.MySql);
}).AddInterceptors(new FmtCommandInterceptor());
})
有其它場景時,也可以優化上面的正則替換方法,達到替換執行語句的效果
大家如果有其它辦法處理這類問題,也麻煩分享一下