Class MySqlDialect
MySQL dialect: backtick-quoted identifiers, @name parameters, concat(...)
concatenation (the || operator is logical OR by default in MySQL), limit/offset
paging and the standard-deviation/variance aggregate name mapping.
Declared non-sealed so that NextORM.MariaDb can derive its dialect from it.
public class MySqlDialect : SqlDialectBase, ISqlDialect- Inheritance
-
MySqlDialect
- Implements
- Derived
- Inherited Members
- Extension Methods
Fields
Instance
Gets the shared MySQL dialect instance.
public static readonly MySqlDialect InstanceField Value
Properties
Iif
MySQL renders the portable iif as if(condition, whenTrue, whenFalse).
public override IIifRenderer Iif { get; }Property Value
IndexHints
MySQL/MariaDB support USE INDEX/FORCE INDEX/IGNORE INDEX.
public override IIndexHintRenderer? IndexHints { get; }Property Value
Lock
MySQL/MariaDB support the trailing row-locking clause.
public override ILockRenderer Lock { get; }Property Value
MySqlFunctions
MySQL and MariaDB (through the inherited dialect) render the native MySQL function surface.
public override IMySqlFunctions MySqlFunctions { get; }Property Value
NullSafeComparison
MySQL (and MariaDB) render the null-safe comparison over the native <=> equality primitive.
public override INullSafeComparisonRenderer? NullSafeComparison { get; }Property Value
RequireSubqueryAlias
A MySQL derived table (subquery in FROM) must have an alias.
public override bool RequireSubqueryAlias { get; }Property Value
RequiresQualifiedSelectStar
MySQL and MariaDB (through the inherited dialect) reject a bare * mixed with explicit
select expressions, so a dynamic-columns read qualifies the appended star with the source alias.
public override bool RequiresQualifiedSelectStar { get; }Property Value
ScalarFunctions
MySQL/MariaDB render the cross-provider scalar functions except translate, which neither has.
public override IScalarFunctions ScalarFunctions { get; }Property Value
SessionInfoFunctions
MySQL/MariaDB render the whole session/information family.
public override ISessionInfoFunctions SessionInfoFunctions { get; }Property Value
StringFormats
MySQL/MariaDB render CLR format specifiers through FORMAT/DATE_FORMAT.
public override IStringFormatFunctions? StringFormats { get; }Property Value
SupportsAnyValueAggregate
MySQL 5.7+/MariaDB render the arbitrary-value aggregate as ANY_VALUE(x).
public override bool SupportsAnyValueAggregate { get; }Property Value
SupportsApply
True when the provider can render CROSS APPLY/OUTER APPLY or their lateral
equivalent (CROSS JOIN LATERAL / LEFT JOIN LATERAL ... ON true). SQLite has no
lateral source, so the safe default is false.
public override bool SupportsApply { get; }Property Value
SupportsBase64Decode
MySQL decodes base64 through FROM_BASE64.
public override bool SupportsBase64Decode { get; }Property Value
SupportsBase64Encode
MySQL encodes base64 through TO_BASE64; its CRLF wrapping is stripped explicitly.
public override bool SupportsBase64Encode { get; }Property Value
SupportsBatch
MySQL/MariaDB batches through MySqlBatch.
public override bool SupportsBatch { get; }Property Value
SupportsBulkCopy
MySQL has a native bulk path: MySqlBulkCopy loads through LOAD DATA LOCAL INFILE,
which requires AllowLoadLocalInfile=true on the connection and local_infile enabled
on the server. Both are read-only preflight checks: a missing permission is never changed by
nextorm, the portable INSERT ... VALUES path is used in Auto,
and Native refuses by name instead of replaying through the portable
path after a failed load.
public override bool SupportsBulkCopy { get; }Property Value
SupportsCollation
MySQL/MariaDB support a per-expression COLLATE clause.
public override bool SupportsCollation { get; }Property Value
SupportsColumnDefault
Defaults to false; PostgreSQL, SQL Server, MySQL and MariaDB accept DEFAULT as a value.
public override bool SupportsColumnDefault { get; }Property Value
SupportsCreateTableAsSelect
MySQL/MariaDB support CREATE [TEMPORARY] TABLE ... AS SELECT.
public override bool SupportsCreateTableAsSelect { get; }Property Value
SupportsCreateTableAsSelectColumnList
MySQL/MariaDB accept a column list on CREATE TABLE ... AS SELECT.
public override bool SupportsCreateTableAsSelectColumnList { get; }Property Value
SupportsCreateTableAsSelectIfNotExists
MySQL/MariaDB accept IF NOT EXISTS on CREATE TABLE ... AS SELECT.
public override bool SupportsCreateTableAsSelectIfNotExists { get; }Property Value
SupportsCrossDatabase
MySQL qualifies a table with a database name (db.table
public override bool SupportsCrossDatabase { get; }Property Value
SupportsCryptGenRandom
MySQL generates random bytes through RANDOM_BYTES.
public override bool SupportsCryptGenRandom { get; }Property Value
SupportsDateArithmetic
True when the provider can render the date arithmetic surface of CommonFunctions
(date_add and end_of_month, also used for the DateTime.AddYears/
AddMonths/AddDays/... methods). The safe default is false; PostgreSQL,
SQL Server and ClickHouse opt in today.
public override bool SupportsDateArithmetic { get; }Property Value
SupportsDateTimeOffsetTranslation
MySQL stores a DateTimeOffset as a datetime(6) wall time and does not
preserve the original offset; the value is still produced and manipulated server-side.
public override bool SupportsDateTimeOffsetTranslation { get; }Property Value
SupportsDefaultValues
Defaults to false; every SQL provider except ClickHouse opts into an all-defaults insert.
public override bool SupportsDefaultValues { get; }Property Value
SupportsDeleteJoin
MySQL and MariaDB delete rows based on a join through the multi-table DELETE <alias> FROM ... JOIN form.
public override bool SupportsDeleteJoin { get; }Property Value
SupportsDirectApplyTableFunction
MySQL and MariaDB correlate a table function in FROM with the tables that precede it
implicitly, so a bounded correlated JSON_TABLE APPLY renders the function itself as the join
source. The derived wrapper cannot be used: MySQL rejects an outer reference inside a table function
under LATERAL and MariaDB has no LATERAL keyword at all.
public override bool SupportsDirectApplyTableFunction { get; }Property Value
SupportsFullJoin
True when the provider can render full join. MySQL/MariaDB implement right join
but not full join, so the two capabilities are separate.
public override bool SupportsFullJoin { get; }Property Value
SupportsFullText
True when the provider can render the full-text predicates contains and freetext
(contains<T>(T?, string?)). The safe default is false; SQL Server opts in.
public override bool SupportsFullText { get; }Property Value
SupportsGreatestLeast
True when the provider can render greatest(...)/least(...). The safe default is
false; PostgreSQL, MySQL/MariaDB, ClickHouse, SQL Server 2022+ and SQLite (through the
scalar max/min) opt in.
public override bool SupportsGreatestLeast { get; }Property Value
SupportsInlineHints
MySQL renders join/subquery/tables-in-scope hints as one /*+ ... */ comment.
public override bool SupportsInlineHints { get; }Property Value
SupportsInsertIgnore
MySQL skips conflicting rows with the INSERT IGNORE head.
public override bool SupportsInsertIgnore { get; }Property Value
SupportsLastInsertId
MySQL has no RETURNING; the generated identity is read back with a separate
SELECT LAST_INSERT_ID().
public override bool SupportsLastInsertId { get; }Property Value
SupportsNamedWindows
MySQL 8.0+ declares named windows (the WINDOW clause) and references them with OVER w.
public override bool SupportsNamedWindows { get; }Property Value
SupportsNativeDuration
MySQL has a native duration/time-of-day type (TIME), so a TimeSpan is stored natively.
public override bool SupportsNativeDuration { get; }Property Value
SupportsNthValue
MySQL 8.0+ supports nth_value(value, n) as a window function.
public override bool SupportsNthValue { get; }Property Value
SupportsOnDuplicateKey
Defaults to false; MySQL and MariaDB opt into ON DUPLICATE KEY UPDATE.
public override bool SupportsOnDuplicateKey { get; }Property Value
SupportsOrdinalComparison
MySQL/MariaDB can express ordinal comparison through the utf8mb4_bin collation.
public override bool SupportsOrdinalComparison { get; }Property Value
SupportsPercentRankCumeDist
MySQL 8.0+ (and MariaDB 10.2+) support the ANSI percent_rank/cume_dist window functions.
public override bool SupportsPercentRankCumeDist { get; }Property Value
SupportsQueryHints
MySQL 5.7+ (and MariaDB 10.2+) render statement-level hints as inline optimizer hints.
public override bool SupportsQueryHints { get; }Property Value
SupportsRangeColumns
True when the provider can store a Range<T> as a pair of scalar columns declared with RangeColumnsAttribute; false by default. See SupportsRangeColumns.
public override bool SupportsRangeColumns { get; }Property Value
SupportsRawSqlSource
MySQL/MariaDB support a raw SQL derived table (FROM (<sql>) AS alias).
public override bool SupportsRawSqlSource { get; }Property Value
SupportsRegex
MySQL 8.0.4+ matches with REGEXP_LIKE and replaces with REGEXP_REPLACE.
public override bool SupportsRegex { get; }Property Value
SupportsRollup
True when the provider can render GROUP BY ROLLUP (...). The safe default is false;
SQL Server, PostgreSQL, SQLite, MySQL/MariaDB and ClickHouse opt in (the latter group spells it
... WITH ROLLUP).
public override bool SupportsRollup { get; }Property Value
SupportsSavepoints
MySQL supports SAVEPOINT/ROLLBACK TO SAVEPOINT/RELEASE SAVEPOINT; MariaDB
inherits this value from its MySQL dialect base.
public override bool SupportsSavepoints { get; }Property Value
SupportsSelectWhereMinMax
MySQL 8.0+ renders SelectWhereMax/SelectWhereMin with a window function.
public override bool SupportsSelectWhereMinMax { get; }Property Value
SupportsStoredProcedures
MySqlConnector maps CommandType.StoredProcedure to CALL name(...).
public override bool SupportsStoredProcedures { get; }Property Value
SupportsStringAgg
True when the provider can render the string_agg(value, delimiter) aggregate. Defaults to
SupportsStringArrayAggregates; SQL Server (2017+) opts in on its own.
public override bool SupportsStringAgg { get; }Property Value
SupportsSubQueryHints
MySQL expresses the subquery hint through the same /*+ ... */ comment.
public override bool SupportsSubQueryHints { get; }Property Value
SupportsTableValuedParameters
MySQL/MariaDB emulate a table-valued parameter with a JSON document (JSON_TABLE).
public override bool SupportsTableValuedParameters { get; }Property Value
SupportsTextJson
True when the provider can render the JSON-as-text functions of CommonFunctions
(json_value, json_query, json_modify, isjson), where JSON is stored in
a normal text column rather than a native type. The safe default is false; SQL Server and
MySQL/MariaDB opt in.
public override bool SupportsTextJson { get; }Property Value
SupportsTruncate
MySQL and MariaDB have a native TRUNCATE TABLE.
public override bool SupportsTruncate { get; }Property Value
SupportsUpdateJoin
MySQL and MariaDB update rows based on a join through the multi-table UPDATE ... JOIN ... SET form.
public override bool SupportsUpdateJoin { get; }Property Value
Tuple
MySQL/MariaDB have no server-side row type, but the flat row constructor (a, b) is a valid
operand of a row comparison ((a, b) = (c, d)); element access is not offered.
public override ITupleRenderer? Tuple { get; }Property Value
Methods
Escape(string)
Quotes an identifier (alias, keyword).
public override string Escape(string keyword)Parameters
keywordstring
Returns
MakeAggregate(string)
Maps an aggregate function name to the provider specific one, e.g. stdev -> stddev.
public override string MakeAggregate(string name)Parameters
namestring
Returns
MakeBase64Decode(string)
Defaults to the unsupported-provider exception; reached only when SupportsBase64Decode is true.
public override string MakeBase64Decode(string value)Parameters
valuestring
Returns
MakeBase64Encode(string)
Defaults to the unsupported-provider exception; reached only when SupportsBase64Encode is true.
public override string MakeBase64Encode(string value)Parameters
valuestring
Returns
MakeCoalesce(string, string)
Renders coalesce(v1, v2) over the already-rendered operands.
public override string MakeCoalesce(string v1, string v2)Parameters
Returns
MakeCollate(string, string, KeywordCase)
Renders value COLLATE collation over the already-rendered value.
collation is the provider-native collation name, quoted where the provider
requires it (PostgreSQL). Only called when SupportsCollation is true.
public override string MakeCollate(string value, string collation, KeywordCase keywordCase = KeywordCase.Lower)Parameters
valuestringcollationstringkeywordCaseKeywordCase
Returns
MakeColumnReference(string)
Quotes a column alias when it is referenced from an outer query. Providers that emit quoted aliases (so they survive as case-sensitive identifiers) must quote the reference accordingly.
public override string MakeColumnReference(string name)Parameters
namestring
Returns
MakeConcat(IReadOnlyList<string>)
Renders a string concatenation over the already-rendered operands. Dialects whose infix
operator concatenates (SQLite, PostgreSQL, SQL Server) join the parts with
ConcatStringOperator; dialects where that operator means something else or does
not exist (MySQL/MariaDB, ClickHouse) render the concat function instead.
public override string MakeConcat(IReadOnlyList<string> parts)Parameters
partsIReadOnlyList<string>
Returns
MakeCryptGenRandom(string)
Defaults to the unsupported-provider exception; reached only when SupportsCryptGenRandom is true.
public override string MakeCryptGenRandom(string length)Parameters
lengthstring
Returns
MakeDateAdd(string, string, string)
Renders date addition (value + amount of field) over the already-rendered
arguments; field is a validated date-part name.
public override string MakeDateAdd(string field, string amount, string value)Parameters
Returns
MakeDateAdd(string, string, string, bool)
date_add rounds a fractional amount in the requested unit for day/hour/minute, so a
DateTimeOffset amount is scaled to the next finer unit; the sub-second
units already keep the fraction through the base implementation.
public override string MakeDateAdd(string field, string amount, string value, bool isDateTimeOffset)Parameters
Returns
MakeDateDiff(string, string, string)
Renders the difference between two already-rendered timestamps in field units;
field is a validated date-part name.
public override string MakeDateDiff(string field, string start, string end)Parameters
Returns
MakeDateFromParts(string, string, string)
Renders a date built from its already-rendered year/month/
day parts.
public override string MakeDateFromParts(string year, string month, string day)Parameters
Returns
MakeDatePart(string, string)
MySQL has dayofyear()/quarter()/weekofyear(); the generic extract()
spelling is avoided for these parts. weekofyear is ISO 8601 (EXTRACT(WEEK ...)
depends on default_week_format).
public override string MakeDatePart(string part, string value)Parameters
Returns
MakeDateTimeOffsetNow(bool)
Renders the UTC server clock at microsecond precision. MySQL/MariaDB do not store an offset, so a materialized DateTimeOffset canonicalises to +00:00.
public override string MakeDateTimeOffsetNow(bool utc)Parameters
utcbool
Returns
MakeDefaultValues(KeywordCase)
Renders the all-defaults row form; MySQL/MariaDB override it with () VALUES ().
public override string MakeDefaultValues(KeywordCase keywordCase = KeywordCase.Lower)Parameters
keywordCaseKeywordCase
Returns
MakeDurationType(DurationUnit?, int)
MySQL's native duration type is TIME, optionally with fractional-second precision.
public override string MakeDurationType(DurationUnit? unit, int precision = 0)Parameters
unitDurationUnit?precisionint
Returns
MakeEndOfMonth(string)
Renders the last day of the month of the already-rendered value.
public override string MakeEndOfMonth(string value)Parameters
valuestring
Returns
MakeFullText(string, string, string)
Renders the full-text predicate functionName (contains/freetext)
over the already-rendered column and search operands. Only
called when SupportsFullText is true.
public override string MakeFullText(string functionName, string column, string search)Parameters
Returns
MakeGrouping(string, GroupingType, KeywordCase)
Renders a grouping list with the given super-aggregate groupingType over the
already-rendered, comma-separated columns. The default is the ANSI
rollup (columns)/cube (columns) form; a provider that spells it
columns WITH ROLLUP overrides this.
public override string MakeGrouping(string columns, GroupingType groupingType, KeywordCase keywordCase = KeywordCase.Lower)Parameters
columnsstringgroupingTypeGroupingTypekeywordCaseKeywordCase
Returns
MakeInsertIgnoreInto(KeywordCase)
Renders the INSERT head that skips conflicting rows; MySQL/MariaDB override it with insert ignore into .
public override string MakeInsertIgnoreInto(KeywordCase keywordCase = KeywordCase.Lower)Parameters
keywordCaseKeywordCase
Returns
MakeIsJson(string, bool)
Renders the text-JSON validity test over the already-rendered value. When
asPredicate is true the result is a boolean predicate, otherwise a
value expression. Only called when SupportsTextJson is true.
public override string MakeIsJson(string value, bool asPredicate)Parameters
Returns
MakeLastInsertId(KeywordCase)
Renders the scalar query for the last generated identity; only reached through a dialect that set SupportsLastInsertId.
public override string MakeLastInsertId(KeywordCase keywordCase = KeywordCase.Lower)Parameters
keywordCaseKeywordCase
Returns
MakeNow(bool)
Renders the current local or UTC date/time.
public override string MakeNow(bool utc)Parameters
utcbool
Returns
MakeOnesComplement(string)
Renders the integer ones-complement (~) over the already-rendered operand.
MySQL's ~ yields an unsigned 64-bit result, so a dialect whose integer complement must
stay signed overrides this.
public override string MakeOnesComplement(string operand)Parameters
operandstring
Returns
MakeOrdinal(string, bool)
Renders a string operand normalised for ordinal comparison. A provider with a per-expression
binary collation emits value COLLATE <binary>; a provider whose native String
order is already ordinal (ClickHouse) returns the value unchanged. ignoreCase
additionally case-folds the operand. Only called when SupportsOrdinalComparison is
true.
public override string MakeOrdinal(string value, bool ignoreCase)Parameters
Returns
MakePage(Paging, StringBuilder, KeywordCase)
Appends the provider's paging clause (LIMIT/OFFSET, OFFSET/FETCH or TOP) for paging to sqlBuilder.
public override void MakePage(Paging paging, StringBuilder sqlBuilder, KeywordCase keywordCase = KeywordCase.Lower)Parameters
pagingPagingsqlBuilderStringBuilderkeywordCaseKeywordCase
MakeParam(string)
Renders a parameter placeholder, e.g. @name or $name.
public override string MakeParam(string name)Parameters
namestring
Returns
MakeQualifiedTableName(string?, string?, string?, string)
Renders the MySQL single-qualifier form database.table. A database and a schema are the
same concept here, so the two overrides are mutually exclusive; a linked server is unsupported.
public override string MakeQualifiedTableName(string? server, string? database, string? schema, string table)Parameters
Returns
MakeRegexMatch(string, string, bool)
Renders a regular-expression match predicate over the already-rendered value.
pattern is the raw pattern text (the dialect renders and escapes the string
literal itself) and ignoreCase selects
IgnoreCase. Reached only when
SupportsRegex is true.
public override string MakeRegexMatch(string value, string pattern, bool ignoreCase)Parameters
Returns
MakeRegexReplace(string, string, string, bool)
Renders a regular-expression replace over the already-rendered value,
replacing every match as Replace(string, string, string)
does. pattern and replacement are raw text (the dialect renders
and escapes the literals). Reached only when SupportsRegex is true.
A replacement backreference uses the provider's group syntax (C# $1, SQL \1); it is
not rewritten, so a replacement with a group reference is not portable as written.
public override string MakeRegexReplace(string value, string pattern, string replacement, bool ignoreCase)Parameters
Returns
MakeRepeat(string, string)
Renders value repeated count times. The primitive behind
the new string(char, n) translation and MakePad(string, string, string, bool).
public override string MakeRepeat(string value, string count)Parameters
Returns
MakeStringAgg(string, string)
Renders the string_agg(value, delimiter) aggregate over the already-rendered arguments.
public override string MakeStringAgg(string value, string delimiter)Parameters
Returns
MakeStringLength(string)
Renders a string length function over value (SQL Server len, others length).
public override string MakeStringLength(string value)Parameters
valuestring
Returns
MakeStringPosition(string, string)
Renders the one-based position of substring in value, or
0 when it is absent (charindex, instr, strpos, position).
protected override string MakeStringPosition(string value, string substring)Parameters
Returns
MakeStringPosition(string, string, string)
Renders the one-based position of substring in value
starting at the already-rendered zero-based start. The default composes the
two-argument primitive over the tail; a dialect with a native start argument overrides it.
protected override string MakeStringPosition(string value, string substring, string start)Parameters
Returns
MakeStringReverse(string)
Renders a character-wise reversal of value.
protected override string MakeStringReverse(string value)Parameters
valuestring
Returns
MakeStuff(string, string, string?, string)
Renders Remove(int)/Remove(int, int) and
Insert(int, string) as one stuff/overlay-style expression over
the already-rendered zero-based start. A count of null
removes through the end; newValue is spliced in at start otherwise (the
Insert translation passes the string "0" so nothing is removed).
public override string MakeStuff(string value, string start, string? count, string newValue)Parameters
Returns
MakeTextJsonFunction(string, IReadOnlyList<string>)
Renders a text-JSON function over the already-rendered args. The default
implementation wraps MakeTextJsonFunction(string), which is enough when the
provider's spelling is a plain function call; a provider whose form nests functions (MySQL
renders json_value as json_unquote(json_extract(...))) overrides this. Only called
when SupportsTextJson is true.
public override string MakeTextJsonFunction(string name, IReadOnlyList<string> args)Parameters
namestringargsIReadOnlyList<string>
Returns
MakeTypeName(Type)
SQL type name used when a CLR conversion has to be rendered as a database cast.
public override string MakeTypeName(Type type)Parameters
typeType
Returns
MakeUpdateJoin(string, string, string, string, string, string, string?, KeywordCase)
Renders the MySQL/MariaDB multi-table update: the join specification sits between UPDATE and
SET, and any filter follows the SET list. The SET list names the target through
its alias.
public override string MakeUpdateJoin(string target, string targetAlias, string assignments, string fromAndJoins, string usingSources, string joinConditions, string? whereSql, KeywordCase keywordCase = KeywordCase.Lower)Parameters
targetstringtargetAliasstringassignmentsstringfromAndJoinsstringusingSourcesstringjoinConditionsstringwhereSqlstringkeywordCaseKeywordCase
Returns
MakeUpsertValueReference(string, KeywordCase)
Renders the incoming value of a column (excluded.<column>); MySQL/MariaDB override it with VALUES(<column>). The qualifier is not cased by KeywordCase.
public override string MakeUpsertValueReference(string column, KeywordCase keywordCase = KeywordCase.Lower)Parameters
columnstringkeywordCaseKeywordCase
Returns
QuoteIdentifier(string)
MySQL quotes a physical identifier with backticks, doubling an embedded backtick.
public override string QuoteIdentifier(string name)Parameters
namestring
Returns
RenderQueryHints(string, IReadOnlyList<string>, string?, KeywordCase)
Renders the statement-level hints as a /*+ ... */ optimizer-hint comment immediately after
the top-level select (the position MySQL and MariaDB require; a WITH prefix and
subqueries are skipped). Neither has a maxrecursion option, so
maxRecursionOption is ignored.
public override string RenderQueryHints(string sql, IReadOnlyList<string> hints, string? maxRecursionOption, KeywordCase keywordCase = KeywordCase.Lower)Parameters
sqlstringhintsIReadOnlyList<string>maxRecursionOptionstringkeywordCaseKeywordCase
Returns
SupportsDatePart(string)
MySQL/MariaDB additionally accept the normalised weekday and epoch date parts.
public override bool SupportsDatePart(string part)Parameters
partstring