MySql-常用函数

常用函数

Chloe 对很多数据库函数支持很丰富,具体用法如下:

IQuery<User> q = context.Query<User>();

var space = new char[] { ' ' };

DateTime startTime = DateTime.Now;
DateTime endTime = DateTime.Now.AddDays(1);

var ret = q.Select(a => new
{
    Id = a.Id,

    String_Length = (int?)a.Name.Length,//LENGTH(`Users`.`Name`)
    Substring = a.Name.Substring(0),//SUBSTRING(`Users`.`Name`,0 + 1,LENGTH(`Users`.`Name`))
    Substring1 = a.Name.Substring(1),//SUBSTRING(`Users`.`Name`,1 + 1,LENGTH(`Users`.`Name`))
    Substring1_2 = a.Name.Substring(1, 2),//SUBSTRING(`Users`.`Name`,1 + 1,2)
    ToLower = a.Name.ToLower(),//LOWER(`Users`.`Name`)
    ToUpper = a.Name.ToUpper(),//UPPER(`Users`.`Name`)
    IsNullOrEmpty = string.IsNullOrEmpty(a.Name),//CASE WHEN (`Users`.`Name` IS NULL OR `Users`.`Name` = N'') THEN 1 ELSE 0 END = 1
    Contains = (bool?)a.Name.Contains("s"),//`Users`.`Name` LIKE CONCAT('%',N's','%')
    Trim = a.Name.Trim(),//TRIM(`Users`.`Name`)
    TrimStart = a.Name.TrimStart(space),//LTRIM(`Users`.`Name`)
    TrimEnd = a.Name.TrimEnd(space),//RTRIM(`Users`.`Name`)
    StartsWith = (bool?)a.Name.StartsWith("s"),//`Users`.`Name` LIKE CONCAT(N's','%')
    EndsWith = (bool?)a.Name.EndsWith("s"),//`Users`.`Name` LIKE CONCAT('%',N's')

    DiffYears = Sql.DiffYears(startTime, endTime),//TIMESTAMPDIFF(YEAR,?P_0,?P_1)
    DiffMonths = Sql.DiffMonths(startTime, endTime),//TIMESTAMPDIFF(MONTH,?P_0,?P_1)
    DiffDays = Sql.DiffDays(startTime, endTime),//TIMESTAMPDIFF(DAY,?P_0,?P_1)
    DiffHours = Sql.DiffHours(startTime, endTime),//TIMESTAMPDIFF(HOUR,?P_0,?P_1)
    DiffMinutes = Sql.DiffMinutes(startTime, endTime),//TIMESTAMPDIFF(MINUTE,?P_0,?P_1)
    DiffSeconds = Sql.DiffSeconds(startTime, endTime),//TIMESTAMPDIFF(SECOND,?P_0,?P_1)
    //DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime),//MySql 不支持 Millisecond
    //DiffMicroseconds = Sql.DiffMicroseconds(startTime, endTime),//ex

    Now = DateTime.Now,//NOW()
    UtcNow = DateTime.UtcNow,//UTC_TIMESTAMP()
    Today = DateTime.Today,//CURDATE()
    Date = DateTime.Now.Date,//DATE(NOW())
    Year = DateTime.Now.Year,//YEAR(NOW())
    Month = DateTime.Now.Month,//MONTH(NOW())
    Day = DateTime.Now.Day,//DAY(NOW())
    Hour = DateTime.Now.Hour,//HOUR(NOW())
    Minute = DateTime.Now.Minute,//MINUTE(NOW())
    Second = DateTime.Now.Second,//SECOND(NOW())
    Millisecond = DateTime.Now.Millisecond,//?P_2 AS `Millisecond`
    DayOfWeek = DateTime.Now.DayOfWeek,//(DAYOFWEEK(NOW()) - 1)

    //Byte_Parse = byte.Parse("1"),//不支持
    Int_Parse = int.Parse("1"),//CAST(N'1' AS SIGNED)
    Int16_Parse = Int16.Parse("11"),//CAST(N'11' AS SIGNED)
    Long_Parse = long.Parse("2"),//CAST(N'2' AS SIGNED)
    //Double_Parse = double.Parse("3"),//N'3' 不支持,否则可能会成为BUG
    //Float_Parse = float.Parse("4"),//N'4' 不支持,否则可能会成为BUG
    //Decimal_Parse = decimal.Parse("5"),//不支持
    Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//N'D544BC4C-739E-4CD3-A3D3-7BF803FCE179'

    Bool_Parse = bool.Parse("1"),//CAST(N'1' AS SIGNED)
    DateTime_Parse = DateTime.Parse("2014-1-1"),//CAST(N'2014-1-1' AS DATETIME)
}).ToList();

上述的函数可以用于 Select 方法,亦可用在 Where 或其它方法的 lambda 表达式树中,最终都会翻译成数据库函数。