README.md

Expressions

This library does not have many expressions/functions/operators from MySQL built-in.

However, you can create your own custom expressions

---

Asterisks (*) denote expressions not natively from MySQL.

• Aggregate

  • avg(number|bigint|null)
  • count()
  • max(expr)
  • min(expr)
  • (number|null)/bigIntSum(bigint|null)
  • sumAsDecimal(number|null, { maxDigitCount, fractionalDigitCount })*

• Cast

  • castAsDateTime(Date|string, 0|1|2|3)

  • castAsDouble(bigint)*

    MySQL 5.7 does not have CAST(x AS DOUBLE). However, (x + 0e0) has the same effect.

  • castAsSignedInteger(number|bigint)

  • castAsUnsignedInteger(number|bigint)

• Comparison

  • strCmp(string, string)

  • coalesce(expr, ...)

  • greatest(expr, expr, ...)

  • interval(number|bigint, number|bigint, ...)

  • least(expr, expr, ...)

  • like(string, string)

  • between(expr, expr, expr)

  • eq(expr, expr)

  • gt(expr, expr)

  • gtEq(expr, expr)

  • in(expr, ...)

  • isFalse(expr)

  • isNotFalse(expr)

  • isNotNull(expr)

  • isNotTrue(expr)

  • isNull(expr)

  • isTrue(expr)

  • lt(expr)

  • ltEq(expr)

  • notBetween(expr, expr, expr)

  • notEq(expr, expr) (in MySQL, both <> and != are valid)

  • notIn(expr, ...)

  • nullSafeEq(expr, expr)

  • nullSafeNotEq(expr, expr)*

    Internally, NOT (a <=> b)

• Control-flow

  • if(boolean, expr, expr)
  • case()/case(expr)

• Date-time

  • timestampAdd(TemporalUnit, bigint, Date)
  • timestampDiff(TemporalUnit, Date, Date)
  • utcTimestamp(0|1|2|3)

• Information

  • database()
  • foundRows()

• Math

  • ceil(number)
  • floor(number)
  • rand()/rand(bigint)
  • round(number)

• String

  • ascii(string)
  • bin(bigint|number
  • bitLength(string)
  • char(number, ...)
  • charLength(string)
  • concat(string, ...)
  • concatWs(string, string|null, ...)
  • elt(number, string, ...)
  • exportSet(number, string, string)
  • field(string, string, ...)
  • findInSet(string, string)
  • format(number, number)
  • fromBase64(string)
  • hex(bigint|number|string)

• Subquery

  • exists(query)

• Arithmetic

  • add(number, ...)/bigIntAdd(bigint, ...)

  • addAsDecimal({ maxDigitCount, fractionalDigitCount }, number, ...)*

    Like add(number, ...) but casts to DECIMAL, performs addition, then casts to DOUBLE

  • div(number, number)/bigIntDiv(bigint, bigint)

  • integerDiv(bigint, bigint)

  • mod(number, number),bigIntMod(bigint, bigint)

  • mul(number, ...)/bigIntMul(bigint, ...)

  • neg(number)/bigIntNeg(bigint)

  • sub(number, ...)/bigIntSub(bigint, ...)

• Logical

  • and(boolean, ...)

  • isNotNullAnd(expr, (expr) => boolean)*

    Equivalent to (expr IS NOT NULL) AND (boolean).

    This function narrows the type of expr from T|null to T.

  • not(boolean)

  • or(boolean, ...)

  • xor(boolean, boolean)