mysql linq 分页查询语句_sql查询语句如何解析成分页查询？

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Text.RegularExpressions;usingSystem.Threading.Tasks;namespaceMySqlTest

{classProgram

{static void Main(string[] args)

{string sql = "select id,code,name,modifytime,storeid from retail_cashier where profileid=@profileid and storeId=@storeId order by id";var parts = MysqlPageHelper.BuildPageQuery(sql, 1, 20);

Console.WriteLine("sql：{0}；",parts.Sql);

Console.WriteLine("SqlCount：{0}", parts.SqlCount);

Console.WriteLine("SqlPage：{0}",parts.SqlPage);

Console.WriteLine("SqlOrderBy：{0}", parts.SqlOrderBy);

Console.ReadKey();

}

}public classMysqlPageHelper

{public Regex RegexColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?)|\)(?)|.?)*(?(depth)(?!))\)|.)*?)(?

RegexOptions.IgnoreCase| RegexOptions.Multiline | RegexOptions.Singleline |RegexOptions.Compiled);public Regex RegexDistinct = new Regex(@"\ADISTINCT\s",

RegexOptions.IgnoreCase| RegexOptions.Multiline | RegexOptions.Singleline |RegexOptions.Compiled);public Regex RegexOrderBy =

newRegex(@"\bORDER\s+BY\s+(?!.*?(?:\)|\s+)AS\s)(?:\((?>\((?)|\)(?)|.?)*(?(depth)(?!))\)|[\[\]`""\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?)|\)(?)|.?)*(?(depth)(?!))\)|[\[\]`""\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*",

RegexOptions.RightToLeft| RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline |RegexOptions.Compiled);private string _sqlSelectRemoved = null;public void SplitSql(string sql, long skip, longtake)

{this.Sql =sql;this.SqlCount = null;this.SqlOrderBy = null;//从中提取列

var m = RegexColumns.Match(this.Sql);if (!m.Success)throw new Exception("无法解析分页查询的SQL语句");//sql语句中columns替换成count(*)

var g = m.Groups[1];this._sqlSelectRemoved = this.Sql.Substring(g.Index);if (RegexDistinct.IsMatch(this._sqlSelectRemoved))this.SqlCount = this.Sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ")" + sql.Substring(g.Index +g.Length);else

this.SqlCount = this.Sql.Substring(0, g.Index) + "COUNT(*)" + sql.Substring(g.Index +g.Length);//sql语句最后order by

m = RegexOrderBy.Match(this.SqlCount);if(m.Success)

{

g= m.Groups[0];this.SqlOrderBy =g.ToString();this.SqlCount = this.SqlCount.Substring(0, g.Index) + this.SqlCount.Substring(g.Index +g.Length);

}//分页读取数据

SqlPage = string.Format("{0}\nLIMIT {1} OFFSET {2}", this.Sql, take, skip);

}public static MysqlPageHelper BuildPageQuery(string sql, long skip, longtake)

{var page=newMysqlPageHelper();

page.SplitSql(sql,skip,take);returnpage;

}public string Sql { get; private set; }public string SqlPage { get;private set; }public string SqlCount { get;private set; }public string SqlOrderBy { get;private set; }

}

}