sqlglot.dialects.mysql

   1from __future__ import annotations
   2
   3import typing as t
   4
   5from sqlglot import exp, generator, parser, tokens, transforms
   6from sqlglot.dialects.dialect import (
   7    Dialect,
   8    NormalizationStrategy,
   9    arrow_json_extract_sql,
  10    date_add_interval_sql,
  11    datestrtodate_sql,
  12    build_formatted_time,
  13    isnull_to_is_null,
  14    locate_to_strposition,
  15    max_or_greatest,
  16    min_or_least,
  17    no_ilike_sql,
  18    no_paren_current_date_sql,
  19    no_pivot_sql,
  20    no_tablesample_sql,
  21    no_trycast_sql,
  22    build_date_delta,
  23    build_date_delta_with_interval,
  24    rename_func,
  25    strposition_to_locate_sql,
  26    unit_to_var,
  27    trim_sql,
  28    timestrtotime_sql,
  29)
  30from sqlglot.helper import seq_get
  31from sqlglot.tokens import TokenType
  32
  33
  34def _show_parser(*args: t.Any, **kwargs: t.Any) -> t.Callable[[MySQL.Parser], exp.Show]:
  35    def _parse(self: MySQL.Parser) -> exp.Show:
  36        return self._parse_show_mysql(*args, **kwargs)
  37
  38    return _parse
  39
  40
  41def _date_trunc_sql(self: MySQL.Generator, expression: exp.DateTrunc) -> str:
  42    expr = self.sql(expression, "this")
  43    unit = expression.text("unit").upper()
  44
  45    if unit == "WEEK":
  46        concat = f"CONCAT(YEAR({expr}), ' ', WEEK({expr}, 1), ' 1')"
  47        date_format = "%Y %u %w"
  48    elif unit == "MONTH":
  49        concat = f"CONCAT(YEAR({expr}), ' ', MONTH({expr}), ' 1')"
  50        date_format = "%Y %c %e"
  51    elif unit == "QUARTER":
  52        concat = f"CONCAT(YEAR({expr}), ' ', QUARTER({expr}) * 3 - 2, ' 1')"
  53        date_format = "%Y %c %e"
  54    elif unit == "YEAR":
  55        concat = f"CONCAT(YEAR({expr}), ' 1 1')"
  56        date_format = "%Y %c %e"
  57    else:
  58        if unit != "DAY":
  59            self.unsupported(f"Unexpected interval unit: {unit}")
  60        return self.func("DATE", expr)
  61
  62    return self.func("STR_TO_DATE", concat, f"'{date_format}'")
  63
  64
  65# All specifiers for time parts (as opposed to date parts)
  66# https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
  67TIME_SPECIFIERS = {"f", "H", "h", "I", "i", "k", "l", "p", "r", "S", "s", "T"}
  68
  69
  70def _has_time_specifier(date_format: str) -> bool:
  71    i = 0
  72    length = len(date_format)
  73
  74    while i < length:
  75        if date_format[i] == "%":
  76            i += 1
  77            if i < length and date_format[i] in TIME_SPECIFIERS:
  78                return True
  79        i += 1
  80    return False
  81
  82
  83def _str_to_date(args: t.List) -> exp.StrToDate | exp.StrToTime:
  84    mysql_date_format = seq_get(args, 1)
  85    date_format = MySQL.format_time(mysql_date_format)
  86    this = seq_get(args, 0)
  87
  88    if mysql_date_format and _has_time_specifier(mysql_date_format.name):
  89        return exp.StrToTime(this=this, format=date_format)
  90
  91    return exp.StrToDate(this=this, format=date_format)
  92
  93
  94def _str_to_date_sql(
  95    self: MySQL.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
  96) -> str:
  97    return self.func("STR_TO_DATE", expression.this, self.format_time(expression))
  98
  99
 100def _unix_to_time_sql(self: MySQL.Generator, expression: exp.UnixToTime) -> str:
 101    scale = expression.args.get("scale")
 102    timestamp = expression.this
 103
 104    if scale in (None, exp.UnixToTime.SECONDS):
 105        return self.func("FROM_UNIXTIME", timestamp, self.format_time(expression))
 106
 107    return self.func(
 108        "FROM_UNIXTIME",
 109        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)),
 110        self.format_time(expression),
 111    )
 112
 113
 114def date_add_sql(
 115    kind: str,
 116) -> t.Callable[[generator.Generator, exp.Expression], str]:
 117    def func(self: generator.Generator, expression: exp.Expression) -> str:
 118        return self.func(
 119            f"DATE_{kind}",
 120            expression.this,
 121            exp.Interval(this=expression.expression, unit=unit_to_var(expression)),
 122        )
 123
 124    return func
 125
 126
 127def _ts_or_ds_to_date_sql(self: MySQL.Generator, expression: exp.TsOrDsToDate) -> str:
 128    time_format = expression.args.get("format")
 129    return _str_to_date_sql(self, expression) if time_format else self.func("DATE", expression.this)
 130
 131
 132def _remove_ts_or_ds_to_date(
 133    to_sql: t.Optional[t.Callable[[MySQL.Generator, exp.Expression], str]] = None,
 134    args: t.Tuple[str, ...] = ("this",),
 135) -> t.Callable[[MySQL.Generator, exp.Func], str]:
 136    def func(self: MySQL.Generator, expression: exp.Func) -> str:
 137        for arg_key in args:
 138            arg = expression.args.get(arg_key)
 139            if isinstance(arg, exp.TsOrDsToDate) and not arg.args.get("format"):
 140                expression.set(arg_key, arg.this)
 141
 142        return to_sql(self, expression) if to_sql else self.function_fallback_sql(expression)
 143
 144    return func
 145
 146
 147class MySQL(Dialect):
 148    PROMOTE_TO_INFERRED_DATETIME_TYPE = True
 149
 150    # https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
 151    IDENTIFIERS_CAN_START_WITH_DIGIT = True
 152
 153    # We default to treating all identifiers as case-sensitive, since it matches MySQL's
 154    # behavior on Linux systems. For MacOS and Windows systems, one can override this
 155    # setting by specifying `dialect="mysql, normalization_strategy = lowercase"`.
 156    #
 157    # See also https://dev.mysql.com/doc/refman/8.2/en/identifier-case-sensitivity.html
 158    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_SENSITIVE
 159
 160    TIME_FORMAT = "'%Y-%m-%d %T'"
 161    DPIPE_IS_STRING_CONCAT = False
 162    SUPPORTS_USER_DEFINED_TYPES = False
 163    SUPPORTS_SEMI_ANTI_JOIN = False
 164    SAFE_DIVISION = True
 165
 166    # https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions
 167    TIME_MAPPING = {
 168        "%M": "%B",
 169        "%c": "%-m",
 170        "%e": "%-d",
 171        "%h": "%I",
 172        "%i": "%M",
 173        "%s": "%S",
 174        "%u": "%W",
 175        "%k": "%-H",
 176        "%l": "%-I",
 177        "%T": "%H:%M:%S",
 178        "%W": "%A",
 179    }
 180
 181    class Tokenizer(tokens.Tokenizer):
 182        QUOTES = ["'", '"']
 183        COMMENTS = ["--", "#", ("/*", "*/")]
 184        IDENTIFIERS = ["`"]
 185        STRING_ESCAPES = ["'", '"', "\\"]
 186        BIT_STRINGS = [("b'", "'"), ("B'", "'"), ("0b", "")]
 187        HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", "")]
 188
 189        KEYWORDS = {
 190            **tokens.Tokenizer.KEYWORDS,
 191            "CHARSET": TokenType.CHARACTER_SET,
 192            # The DESCRIBE and EXPLAIN statements are synonyms.
 193            # https://dev.mysql.com/doc/refman/8.4/en/explain.html
 194            "EXPLAIN": TokenType.DESCRIBE,
 195            "FORCE": TokenType.FORCE,
 196            "IGNORE": TokenType.IGNORE,
 197            "KEY": TokenType.KEY,
 198            "LOCK TABLES": TokenType.COMMAND,
 199            "LONGBLOB": TokenType.LONGBLOB,
 200            "LONGTEXT": TokenType.LONGTEXT,
 201            "MEDIUMBLOB": TokenType.MEDIUMBLOB,
 202            "TINYBLOB": TokenType.TINYBLOB,
 203            "TINYTEXT": TokenType.TINYTEXT,
 204            "MEDIUMTEXT": TokenType.MEDIUMTEXT,
 205            "MEDIUMINT": TokenType.MEDIUMINT,
 206            "MEMBER OF": TokenType.MEMBER_OF,
 207            "SEPARATOR": TokenType.SEPARATOR,
 208            "START": TokenType.BEGIN,
 209            "SIGNED": TokenType.BIGINT,
 210            "SIGNED INTEGER": TokenType.BIGINT,
 211            "UNLOCK TABLES": TokenType.COMMAND,
 212            "UNSIGNED": TokenType.UBIGINT,
 213            "UNSIGNED INTEGER": TokenType.UBIGINT,
 214            "YEAR": TokenType.YEAR,
 215            "_ARMSCII8": TokenType.INTRODUCER,
 216            "_ASCII": TokenType.INTRODUCER,
 217            "_BIG5": TokenType.INTRODUCER,
 218            "_BINARY": TokenType.INTRODUCER,
 219            "_CP1250": TokenType.INTRODUCER,
 220            "_CP1251": TokenType.INTRODUCER,
 221            "_CP1256": TokenType.INTRODUCER,
 222            "_CP1257": TokenType.INTRODUCER,
 223            "_CP850": TokenType.INTRODUCER,
 224            "_CP852": TokenType.INTRODUCER,
 225            "_CP866": TokenType.INTRODUCER,
 226            "_CP932": TokenType.INTRODUCER,
 227            "_DEC8": TokenType.INTRODUCER,
 228            "_EUCJPMS": TokenType.INTRODUCER,
 229            "_EUCKR": TokenType.INTRODUCER,
 230            "_GB18030": TokenType.INTRODUCER,
 231            "_GB2312": TokenType.INTRODUCER,
 232            "_GBK": TokenType.INTRODUCER,
 233            "_GEOSTD8": TokenType.INTRODUCER,
 234            "_GREEK": TokenType.INTRODUCER,
 235            "_HEBREW": TokenType.INTRODUCER,
 236            "_HP8": TokenType.INTRODUCER,
 237            "_KEYBCS2": TokenType.INTRODUCER,
 238            "_KOI8R": TokenType.INTRODUCER,
 239            "_KOI8U": TokenType.INTRODUCER,
 240            "_LATIN1": TokenType.INTRODUCER,
 241            "_LATIN2": TokenType.INTRODUCER,
 242            "_LATIN5": TokenType.INTRODUCER,
 243            "_LATIN7": TokenType.INTRODUCER,
 244            "_MACCE": TokenType.INTRODUCER,
 245            "_MACROMAN": TokenType.INTRODUCER,
 246            "_SJIS": TokenType.INTRODUCER,
 247            "_SWE7": TokenType.INTRODUCER,
 248            "_TIS620": TokenType.INTRODUCER,
 249            "_UCS2": TokenType.INTRODUCER,
 250            "_UJIS": TokenType.INTRODUCER,
 251            # https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
 252            "_UTF8": TokenType.INTRODUCER,
 253            "_UTF16": TokenType.INTRODUCER,
 254            "_UTF16LE": TokenType.INTRODUCER,
 255            "_UTF32": TokenType.INTRODUCER,
 256            "_UTF8MB3": TokenType.INTRODUCER,
 257            "_UTF8MB4": TokenType.INTRODUCER,
 258            "@@": TokenType.SESSION_PARAMETER,
 259        }
 260
 261        COMMANDS = {*tokens.Tokenizer.COMMANDS, TokenType.REPLACE} - {TokenType.SHOW}
 262
 263    class Parser(parser.Parser):
 264        FUNC_TOKENS = {
 265            *parser.Parser.FUNC_TOKENS,
 266            TokenType.DATABASE,
 267            TokenType.SCHEMA,
 268            TokenType.VALUES,
 269        }
 270
 271        CONJUNCTION = {
 272            **parser.Parser.CONJUNCTION,
 273            TokenType.DAMP: exp.And,
 274            TokenType.XOR: exp.Xor,
 275        }
 276
 277        DISJUNCTION = {
 278            **parser.Parser.DISJUNCTION,
 279            TokenType.DPIPE: exp.Or,
 280        }
 281
 282        TABLE_ALIAS_TOKENS = (
 283            parser.Parser.TABLE_ALIAS_TOKENS - parser.Parser.TABLE_INDEX_HINT_TOKENS
 284        )
 285
 286        RANGE_PARSERS = {
 287            **parser.Parser.RANGE_PARSERS,
 288            TokenType.MEMBER_OF: lambda self, this: self.expression(
 289                exp.JSONArrayContains,
 290                this=this,
 291                expression=self._parse_wrapped(self._parse_expression),
 292            ),
 293        }
 294
 295        FUNCTIONS = {
 296            **parser.Parser.FUNCTIONS,
 297            "CHAR_LENGTH": exp.Length.from_arg_list,
 298            "CHARACTER_LENGTH": exp.Length.from_arg_list,
 299            "CONVERT_TZ": lambda args: exp.ConvertTimezone(
 300                source_tz=seq_get(args, 1), target_tz=seq_get(args, 2), timestamp=seq_get(args, 0)
 301            ),
 302            "DATE": lambda args: exp.TsOrDsToDate(this=seq_get(args, 0)),
 303            "DATE_ADD": build_date_delta_with_interval(exp.DateAdd),
 304            "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "mysql"),
 305            "DATE_SUB": build_date_delta_with_interval(exp.DateSub),
 306            "DAY": lambda args: exp.Day(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 307            "DAYOFMONTH": lambda args: exp.DayOfMonth(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 308            "DAYOFWEEK": lambda args: exp.DayOfWeek(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 309            "DAYOFYEAR": lambda args: exp.DayOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 310            "FROM_UNIXTIME": build_formatted_time(exp.UnixToTime, "mysql"),
 311            "ISNULL": isnull_to_is_null,
 312            "LOCATE": locate_to_strposition,
 313            "MAKETIME": exp.TimeFromParts.from_arg_list,
 314            "MONTH": lambda args: exp.Month(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 315            "MONTHNAME": lambda args: exp.TimeToStr(
 316                this=exp.TsOrDsToDate(this=seq_get(args, 0)),
 317                format=exp.Literal.string("%B"),
 318            ),
 319            "STR_TO_DATE": _str_to_date,
 320            "TIMESTAMPDIFF": build_date_delta(exp.TimestampDiff),
 321            "TO_DAYS": lambda args: exp.paren(
 322                exp.DateDiff(
 323                    this=exp.TsOrDsToDate(this=seq_get(args, 0)),
 324                    expression=exp.TsOrDsToDate(this=exp.Literal.string("0000-01-01")),
 325                    unit=exp.var("DAY"),
 326                )
 327                + 1
 328            ),
 329            "WEEK": lambda args: exp.Week(
 330                this=exp.TsOrDsToDate(this=seq_get(args, 0)), mode=seq_get(args, 1)
 331            ),
 332            "WEEKOFYEAR": lambda args: exp.WeekOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 333            "YEAR": lambda args: exp.Year(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
 334        }
 335
 336        FUNCTION_PARSERS = {
 337            **parser.Parser.FUNCTION_PARSERS,
 338            "CHAR": lambda self: self.expression(
 339                exp.Chr,
 340                expressions=self._parse_csv(self._parse_assignment),
 341                charset=self._match(TokenType.USING) and self._parse_var(),
 342            ),
 343            "GROUP_CONCAT": lambda self: self._parse_group_concat(),
 344            # https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values
 345            "VALUES": lambda self: self.expression(
 346                exp.Anonymous, this="VALUES", expressions=[self._parse_id_var()]
 347            ),
 348            "JSON_VALUE": lambda self: self._parse_json_value(),
 349        }
 350
 351        STATEMENT_PARSERS = {
 352            **parser.Parser.STATEMENT_PARSERS,
 353            TokenType.SHOW: lambda self: self._parse_show(),
 354        }
 355
 356        SHOW_PARSERS = {
 357            "BINARY LOGS": _show_parser("BINARY LOGS"),
 358            "MASTER LOGS": _show_parser("BINARY LOGS"),
 359            "BINLOG EVENTS": _show_parser("BINLOG EVENTS"),
 360            "CHARACTER SET": _show_parser("CHARACTER SET"),
 361            "CHARSET": _show_parser("CHARACTER SET"),
 362            "COLLATION": _show_parser("COLLATION"),
 363            "FULL COLUMNS": _show_parser("COLUMNS", target="FROM", full=True),
 364            "COLUMNS": _show_parser("COLUMNS", target="FROM"),
 365            "CREATE DATABASE": _show_parser("CREATE DATABASE", target=True),
 366            "CREATE EVENT": _show_parser("CREATE EVENT", target=True),
 367            "CREATE FUNCTION": _show_parser("CREATE FUNCTION", target=True),
 368            "CREATE PROCEDURE": _show_parser("CREATE PROCEDURE", target=True),
 369            "CREATE TABLE": _show_parser("CREATE TABLE", target=True),
 370            "CREATE TRIGGER": _show_parser("CREATE TRIGGER", target=True),
 371            "CREATE VIEW": _show_parser("CREATE VIEW", target=True),
 372            "DATABASES": _show_parser("DATABASES"),
 373            "SCHEMAS": _show_parser("DATABASES"),
 374            "ENGINE": _show_parser("ENGINE", target=True),
 375            "STORAGE ENGINES": _show_parser("ENGINES"),
 376            "ENGINES": _show_parser("ENGINES"),
 377            "ERRORS": _show_parser("ERRORS"),
 378            "EVENTS": _show_parser("EVENTS"),
 379            "FUNCTION CODE": _show_parser("FUNCTION CODE", target=True),
 380            "FUNCTION STATUS": _show_parser("FUNCTION STATUS"),
 381            "GRANTS": _show_parser("GRANTS", target="FOR"),
 382            "INDEX": _show_parser("INDEX", target="FROM"),
 383            "MASTER STATUS": _show_parser("MASTER STATUS"),
 384            "OPEN TABLES": _show_parser("OPEN TABLES"),
 385            "PLUGINS": _show_parser("PLUGINS"),
 386            "PROCEDURE CODE": _show_parser("PROCEDURE CODE", target=True),
 387            "PROCEDURE STATUS": _show_parser("PROCEDURE STATUS"),
 388            "PRIVILEGES": _show_parser("PRIVILEGES"),
 389            "FULL PROCESSLIST": _show_parser("PROCESSLIST", full=True),
 390            "PROCESSLIST": _show_parser("PROCESSLIST"),
 391            "PROFILE": _show_parser("PROFILE"),
 392            "PROFILES": _show_parser("PROFILES"),
 393            "RELAYLOG EVENTS": _show_parser("RELAYLOG EVENTS"),
 394            "REPLICAS": _show_parser("REPLICAS"),
 395            "SLAVE HOSTS": _show_parser("REPLICAS"),
 396            "REPLICA STATUS": _show_parser("REPLICA STATUS"),
 397            "SLAVE STATUS": _show_parser("REPLICA STATUS"),
 398            "GLOBAL STATUS": _show_parser("STATUS", global_=True),
 399            "SESSION STATUS": _show_parser("STATUS"),
 400            "STATUS": _show_parser("STATUS"),
 401            "TABLE STATUS": _show_parser("TABLE STATUS"),
 402            "FULL TABLES": _show_parser("TABLES", full=True),
 403            "TABLES": _show_parser("TABLES"),
 404            "TRIGGERS": _show_parser("TRIGGERS"),
 405            "GLOBAL VARIABLES": _show_parser("VARIABLES", global_=True),
 406            "SESSION VARIABLES": _show_parser("VARIABLES"),
 407            "VARIABLES": _show_parser("VARIABLES"),
 408            "WARNINGS": _show_parser("WARNINGS"),
 409        }
 410
 411        PROPERTY_PARSERS = {
 412            **parser.Parser.PROPERTY_PARSERS,
 413            "LOCK": lambda self: self._parse_property_assignment(exp.LockProperty),
 414        }
 415
 416        SET_PARSERS = {
 417            **parser.Parser.SET_PARSERS,
 418            "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"),
 419            "PERSIST_ONLY": lambda self: self._parse_set_item_assignment("PERSIST_ONLY"),
 420            "CHARACTER SET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
 421            "CHARSET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
 422            "NAMES": lambda self: self._parse_set_item_names(),
 423        }
 424
 425        CONSTRAINT_PARSERS = {
 426            **parser.Parser.CONSTRAINT_PARSERS,
 427            "FULLTEXT": lambda self: self._parse_index_constraint(kind="FULLTEXT"),
 428            "INDEX": lambda self: self._parse_index_constraint(),
 429            "KEY": lambda self: self._parse_index_constraint(),
 430            "SPATIAL": lambda self: self._parse_index_constraint(kind="SPATIAL"),
 431        }
 432
 433        ALTER_PARSERS = {
 434            **parser.Parser.ALTER_PARSERS,
 435            "MODIFY": lambda self: self._parse_alter_table_alter(),
 436        }
 437
 438        SCHEMA_UNNAMED_CONSTRAINTS = {
 439            *parser.Parser.SCHEMA_UNNAMED_CONSTRAINTS,
 440            "FULLTEXT",
 441            "INDEX",
 442            "KEY",
 443            "SPATIAL",
 444        }
 445
 446        PROFILE_TYPES: parser.OPTIONS_TYPE = {
 447            **dict.fromkeys(("ALL", "CPU", "IPC", "MEMORY", "SOURCE", "SWAPS"), tuple()),
 448            "BLOCK": ("IO",),
 449            "CONTEXT": ("SWITCHES",),
 450            "PAGE": ("FAULTS",),
 451        }
 452
 453        TYPE_TOKENS = {
 454            *parser.Parser.TYPE_TOKENS,
 455            TokenType.SET,
 456        }
 457
 458        ENUM_TYPE_TOKENS = {
 459            *parser.Parser.ENUM_TYPE_TOKENS,
 460            TokenType.SET,
 461        }
 462
 463        # SELECT [ ALL | DISTINCT | DISTINCTROW ] [ <OPERATION_MODIFIERS> ]
 464        OPERATION_MODIFIERS = {
 465            "HIGH_PRIORITY",
 466            "STRAIGHT_JOIN",
 467            "SQL_SMALL_RESULT",
 468            "SQL_BIG_RESULT",
 469            "SQL_BUFFER_RESULT",
 470            "SQL_NO_CACHE",
 471            "SQL_CALC_FOUND_ROWS",
 472        }
 473
 474        LOG_DEFAULTS_TO_LN = True
 475        STRING_ALIASES = True
 476        VALUES_FOLLOWED_BY_PAREN = False
 477        SUPPORTS_PARTITION_SELECTION = True
 478
 479        def _parse_primary_key_part(self) -> t.Optional[exp.Expression]:
 480            this = self._parse_id_var()
 481            if not self._match(TokenType.L_PAREN):
 482                return this
 483
 484            expression = self._parse_number()
 485            self._match_r_paren()
 486            return self.expression(exp.ColumnPrefix, this=this, expression=expression)
 487
 488        def _parse_index_constraint(
 489            self, kind: t.Optional[str] = None
 490        ) -> exp.IndexColumnConstraint:
 491            if kind:
 492                self._match_texts(("INDEX", "KEY"))
 493
 494            this = self._parse_id_var(any_token=False)
 495            index_type = self._match(TokenType.USING) and self._advance_any() and self._prev.text
 496            expressions = self._parse_wrapped_csv(self._parse_ordered)
 497
 498            options = []
 499            while True:
 500                if self._match_text_seq("KEY_BLOCK_SIZE"):
 501                    self._match(TokenType.EQ)
 502                    opt = exp.IndexConstraintOption(key_block_size=self._parse_number())
 503                elif self._match(TokenType.USING):
 504                    opt = exp.IndexConstraintOption(using=self._advance_any() and self._prev.text)
 505                elif self._match_text_seq("WITH", "PARSER"):
 506                    opt = exp.IndexConstraintOption(parser=self._parse_var(any_token=True))
 507                elif self._match(TokenType.COMMENT):
 508                    opt = exp.IndexConstraintOption(comment=self._parse_string())
 509                elif self._match_text_seq("VISIBLE"):
 510                    opt = exp.IndexConstraintOption(visible=True)
 511                elif self._match_text_seq("INVISIBLE"):
 512                    opt = exp.IndexConstraintOption(visible=False)
 513                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
 514                    self._match(TokenType.EQ)
 515                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
 516                elif self._match_text_seq("SECONDARY_ENGINE_ATTRIBUTE"):
 517                    self._match(TokenType.EQ)
 518                    opt = exp.IndexConstraintOption(secondary_engine_attr=self._parse_string())
 519                else:
 520                    opt = None
 521
 522                if not opt:
 523                    break
 524
 525                options.append(opt)
 526
 527            return self.expression(
 528                exp.IndexColumnConstraint,
 529                this=this,
 530                expressions=expressions,
 531                kind=kind,
 532                index_type=index_type,
 533                options=options,
 534            )
 535
 536        def _parse_show_mysql(
 537            self,
 538            this: str,
 539            target: bool | str = False,
 540            full: t.Optional[bool] = None,
 541            global_: t.Optional[bool] = None,
 542        ) -> exp.Show:
 543            if target:
 544                if isinstance(target, str):
 545                    self._match_text_seq(target)
 546                target_id = self._parse_id_var()
 547            else:
 548                target_id = None
 549
 550            log = self._parse_string() if self._match_text_seq("IN") else None
 551
 552            if this in ("BINLOG EVENTS", "RELAYLOG EVENTS"):
 553                position = self._parse_number() if self._match_text_seq("FROM") else None
 554                db = None
 555            else:
 556                position = None
 557                db = None
 558
 559                if self._match(TokenType.FROM):
 560                    db = self._parse_id_var()
 561                elif self._match(TokenType.DOT):
 562                    db = target_id
 563                    target_id = self._parse_id_var()
 564
 565            channel = self._parse_id_var() if self._match_text_seq("FOR", "CHANNEL") else None
 566
 567            like = self._parse_string() if self._match_text_seq("LIKE") else None
 568            where = self._parse_where()
 569
 570            if this == "PROFILE":
 571                types = self._parse_csv(lambda: self._parse_var_from_options(self.PROFILE_TYPES))
 572                query = self._parse_number() if self._match_text_seq("FOR", "QUERY") else None
 573                offset = self._parse_number() if self._match_text_seq("OFFSET") else None
 574                limit = self._parse_number() if self._match_text_seq("LIMIT") else None
 575            else:
 576                types, query = None, None
 577                offset, limit = self._parse_oldstyle_limit()
 578
 579            mutex = True if self._match_text_seq("MUTEX") else None
 580            mutex = False if self._match_text_seq("STATUS") else mutex
 581
 582            return self.expression(
 583                exp.Show,
 584                this=this,
 585                target=target_id,
 586                full=full,
 587                log=log,
 588                position=position,
 589                db=db,
 590                channel=channel,
 591                like=like,
 592                where=where,
 593                types=types,
 594                query=query,
 595                offset=offset,
 596                limit=limit,
 597                mutex=mutex,
 598                **{"global": global_},  # type: ignore
 599            )
 600
 601        def _parse_oldstyle_limit(
 602            self,
 603        ) -> t.Tuple[t.Optional[exp.Expression], t.Optional[exp.Expression]]:
 604            limit = None
 605            offset = None
 606            if self._match_text_seq("LIMIT"):
 607                parts = self._parse_csv(self._parse_number)
 608                if len(parts) == 1:
 609                    limit = parts[0]
 610                elif len(parts) == 2:
 611                    limit = parts[1]
 612                    offset = parts[0]
 613
 614            return offset, limit
 615
 616        def _parse_set_item_charset(self, kind: str) -> exp.Expression:
 617            this = self._parse_string() or self._parse_unquoted_field()
 618            return self.expression(exp.SetItem, this=this, kind=kind)
 619
 620        def _parse_set_item_names(self) -> exp.Expression:
 621            charset = self._parse_string() or self._parse_unquoted_field()
 622            if self._match_text_seq("COLLATE"):
 623                collate = self._parse_string() or self._parse_unquoted_field()
 624            else:
 625                collate = None
 626
 627            return self.expression(exp.SetItem, this=charset, collate=collate, kind="NAMES")
 628
 629        def _parse_type(
 630            self, parse_interval: bool = True, fallback_to_identifier: bool = False
 631        ) -> t.Optional[exp.Expression]:
 632            # mysql binary is special and can work anywhere, even in order by operations
 633            # it operates like a no paren func
 634            if self._match(TokenType.BINARY, advance=False):
 635                data_type = self._parse_types(check_func=True, allow_identifiers=False)
 636
 637                if isinstance(data_type, exp.DataType):
 638                    return self.expression(exp.Cast, this=self._parse_column(), to=data_type)
 639
 640            return super()._parse_type(
 641                parse_interval=parse_interval, fallback_to_identifier=fallback_to_identifier
 642            )
 643
 644        def _parse_group_concat(self) -> t.Optional[exp.Expression]:
 645            def concat_exprs(
 646                node: t.Optional[exp.Expression], exprs: t.List[exp.Expression]
 647            ) -> exp.Expression:
 648                if isinstance(node, exp.Distinct) and len(node.expressions) > 1:
 649                    concat_exprs = [
 650                        self.expression(exp.Concat, expressions=node.expressions, safe=True)
 651                    ]
 652                    node.set("expressions", concat_exprs)
 653                    return node
 654                if len(exprs) == 1:
 655                    return exprs[0]
 656                return self.expression(exp.Concat, expressions=args, safe=True)
 657
 658            args = self._parse_csv(self._parse_lambda)
 659
 660            if args:
 661                order = args[-1] if isinstance(args[-1], exp.Order) else None
 662
 663                if order:
 664                    # Order By is the last (or only) expression in the list and has consumed the 'expr' before it,
 665                    # remove 'expr' from exp.Order and add it back to args
 666                    args[-1] = order.this
 667                    order.set("this", concat_exprs(order.this, args))
 668
 669                this = order or concat_exprs(args[0], args)
 670            else:
 671                this = None
 672
 673            separator = self._parse_field() if self._match(TokenType.SEPARATOR) else None
 674
 675            return self.expression(exp.GroupConcat, this=this, separator=separator)
 676
 677        def _parse_json_value(self) -> exp.JSONValue:
 678            this = self._parse_bitwise()
 679            self._match(TokenType.COMMA)
 680            path = self._parse_bitwise()
 681
 682            returning = self._match(TokenType.RETURNING) and self._parse_type()
 683
 684            return self.expression(
 685                exp.JSONValue,
 686                this=this,
 687                path=self.dialect.to_json_path(path),
 688                returning=returning,
 689                on_condition=self._parse_on_condition(),
 690            )
 691
 692    class Generator(generator.Generator):
 693        INTERVAL_ALLOWS_PLURAL_FORM = False
 694        LOCKING_READS_SUPPORTED = True
 695        NULL_ORDERING_SUPPORTED = None
 696        JOIN_HINTS = False
 697        TABLE_HINTS = True
 698        DUPLICATE_KEY_UPDATE_WITH_SET = False
 699        QUERY_HINT_SEP = " "
 700        VALUES_AS_TABLE = False
 701        NVL2_SUPPORTED = False
 702        LAST_DAY_SUPPORTS_DATE_PART = False
 703        JSON_TYPE_REQUIRED_FOR_EXTRACTION = True
 704        JSON_PATH_BRACKETED_KEY_SUPPORTED = False
 705        JSON_KEY_VALUE_PAIR_SEP = ","
 706        SUPPORTS_TO_NUMBER = False
 707        PARSE_JSON_NAME: t.Optional[str] = None
 708        PAD_FILL_PATTERN_IS_REQUIRED = True
 709        WRAP_DERIVED_VALUES = False
 710        VARCHAR_REQUIRES_SIZE = True
 711        SUPPORTS_MEDIAN = False
 712
 713        TRANSFORMS = {
 714            **generator.Generator.TRANSFORMS,
 715            exp.ArrayAgg: rename_func("GROUP_CONCAT"),
 716            exp.CurrentDate: no_paren_current_date_sql,
 717            exp.DateDiff: _remove_ts_or_ds_to_date(
 718                lambda self, e: self.func("DATEDIFF", e.this, e.expression), ("this", "expression")
 719            ),
 720            exp.DateAdd: _remove_ts_or_ds_to_date(date_add_sql("ADD")),
 721            exp.DateStrToDate: datestrtodate_sql,
 722            exp.DateSub: _remove_ts_or_ds_to_date(date_add_sql("SUB")),
 723            exp.DateTrunc: _date_trunc_sql,
 724            exp.Day: _remove_ts_or_ds_to_date(),
 725            exp.DayOfMonth: _remove_ts_or_ds_to_date(rename_func("DAYOFMONTH")),
 726            exp.DayOfWeek: _remove_ts_or_ds_to_date(rename_func("DAYOFWEEK")),
 727            exp.DayOfYear: _remove_ts_or_ds_to_date(rename_func("DAYOFYEAR")),
 728            exp.GroupConcat: lambda self,
 729            e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""",
 730            exp.ILike: no_ilike_sql,
 731            exp.JSONExtractScalar: arrow_json_extract_sql,
 732            exp.Length: rename_func("CHAR_LENGTH"),
 733            exp.Max: max_or_greatest,
 734            exp.Min: min_or_least,
 735            exp.Month: _remove_ts_or_ds_to_date(),
 736            exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"),
 737            exp.NullSafeNEQ: lambda self, e: f"NOT {self.binary(e, '<=>')}",
 738            exp.Pivot: no_pivot_sql,
 739            exp.Select: transforms.preprocess(
 740                [
 741                    transforms.eliminate_distinct_on,
 742                    transforms.eliminate_semi_and_anti_joins,
 743                    transforms.eliminate_qualify,
 744                    transforms.eliminate_full_outer_join,
 745                    transforms.unnest_generate_date_array_using_recursive_cte,
 746                ]
 747            ),
 748            exp.StrPosition: strposition_to_locate_sql,
 749            exp.StrToDate: _str_to_date_sql,
 750            exp.StrToTime: _str_to_date_sql,
 751            exp.Stuff: rename_func("INSERT"),
 752            exp.TableSample: no_tablesample_sql,
 753            exp.TimeFromParts: rename_func("MAKETIME"),
 754            exp.TimestampAdd: date_add_interval_sql("DATE", "ADD"),
 755            exp.TimestampDiff: lambda self, e: self.func(
 756                "TIMESTAMPDIFF", unit_to_var(e), e.expression, e.this
 757            ),
 758            exp.TimestampSub: date_add_interval_sql("DATE", "SUB"),
 759            exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
 760            exp.TimeStrToTime: lambda self, e: timestrtotime_sql(
 761                self,
 762                e,
 763                include_precision=not e.args.get("zone"),
 764            ),
 765            exp.TimeToStr: _remove_ts_or_ds_to_date(
 766                lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e))
 767            ),
 768            exp.Trim: trim_sql,
 769            exp.TryCast: no_trycast_sql,
 770            exp.TsOrDsAdd: date_add_sql("ADD"),
 771            exp.TsOrDsDiff: lambda self, e: self.func("DATEDIFF", e.this, e.expression),
 772            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
 773            exp.UnixToTime: _unix_to_time_sql,
 774            exp.Week: _remove_ts_or_ds_to_date(),
 775            exp.WeekOfYear: _remove_ts_or_ds_to_date(rename_func("WEEKOFYEAR")),
 776            exp.Year: _remove_ts_or_ds_to_date(),
 777        }
 778
 779        UNSIGNED_TYPE_MAPPING = {
 780            exp.DataType.Type.UBIGINT: "BIGINT",
 781            exp.DataType.Type.UINT: "INT",
 782            exp.DataType.Type.UMEDIUMINT: "MEDIUMINT",
 783            exp.DataType.Type.USMALLINT: "SMALLINT",
 784            exp.DataType.Type.UTINYINT: "TINYINT",
 785            exp.DataType.Type.UDECIMAL: "DECIMAL",
 786        }
 787
 788        TIMESTAMP_TYPE_MAPPING = {
 789            exp.DataType.Type.TIMESTAMP: "DATETIME",
 790            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
 791            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
 792        }
 793
 794        TYPE_MAPPING = {
 795            **generator.Generator.TYPE_MAPPING,
 796            **UNSIGNED_TYPE_MAPPING,
 797            **TIMESTAMP_TYPE_MAPPING,
 798        }
 799
 800        TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT)
 801        TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT)
 802        TYPE_MAPPING.pop(exp.DataType.Type.TINYTEXT)
 803        TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB)
 804        TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB)
 805        TYPE_MAPPING.pop(exp.DataType.Type.TINYBLOB)
 806
 807        PROPERTIES_LOCATION = {
 808            **generator.Generator.PROPERTIES_LOCATION,
 809            exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
 810            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
 811        }
 812
 813        LIMIT_FETCH = "LIMIT"
 814
 815        LIMIT_ONLY_LITERALS = True
 816
 817        CHAR_CAST_MAPPING = dict.fromkeys(
 818            (
 819                exp.DataType.Type.LONGTEXT,
 820                exp.DataType.Type.LONGBLOB,
 821                exp.DataType.Type.MEDIUMBLOB,
 822                exp.DataType.Type.MEDIUMTEXT,
 823                exp.DataType.Type.TEXT,
 824                exp.DataType.Type.TINYBLOB,
 825                exp.DataType.Type.TINYTEXT,
 826                exp.DataType.Type.VARCHAR,
 827            ),
 828            "CHAR",
 829        )
 830        SIGNED_CAST_MAPPING = dict.fromkeys(
 831            (
 832                exp.DataType.Type.BIGINT,
 833                exp.DataType.Type.BOOLEAN,
 834                exp.DataType.Type.INT,
 835                exp.DataType.Type.SMALLINT,
 836                exp.DataType.Type.TINYINT,
 837                exp.DataType.Type.MEDIUMINT,
 838            ),
 839            "SIGNED",
 840        )
 841
 842        # MySQL doesn't support many datatypes in cast.
 843        # https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast
 844        CAST_MAPPING = {
 845            **CHAR_CAST_MAPPING,
 846            **SIGNED_CAST_MAPPING,
 847            exp.DataType.Type.UBIGINT: "UNSIGNED",
 848        }
 849
 850        TIMESTAMP_FUNC_TYPES = {
 851            exp.DataType.Type.TIMESTAMPTZ,
 852            exp.DataType.Type.TIMESTAMPLTZ,
 853        }
 854
 855        # https://dev.mysql.com/doc/refman/8.0/en/keywords.html
 856        RESERVED_KEYWORDS = {
 857            "accessible",
 858            "add",
 859            "all",
 860            "alter",
 861            "analyze",
 862            "and",
 863            "as",
 864            "asc",
 865            "asensitive",
 866            "before",
 867            "between",
 868            "bigint",
 869            "binary",
 870            "blob",
 871            "both",
 872            "by",
 873            "call",
 874            "cascade",
 875            "case",
 876            "change",
 877            "char",
 878            "character",
 879            "check",
 880            "collate",
 881            "column",
 882            "condition",
 883            "constraint",
 884            "continue",
 885            "convert",
 886            "create",
 887            "cross",
 888            "cube",
 889            "cume_dist",
 890            "current_date",
 891            "current_time",
 892            "current_timestamp",
 893            "current_user",
 894            "cursor",
 895            "database",
 896            "databases",
 897            "day_hour",
 898            "day_microsecond",
 899            "day_minute",
 900            "day_second",
 901            "dec",
 902            "decimal",
 903            "declare",
 904            "default",
 905            "delayed",
 906            "delete",
 907            "dense_rank",
 908            "desc",
 909            "describe",
 910            "deterministic",
 911            "distinct",
 912            "distinctrow",
 913            "div",
 914            "double",
 915            "drop",
 916            "dual",
 917            "each",
 918            "else",
 919            "elseif",
 920            "empty",
 921            "enclosed",
 922            "escaped",
 923            "except",
 924            "exists",
 925            "exit",
 926            "explain",
 927            "false",
 928            "fetch",
 929            "first_value",
 930            "float",
 931            "float4",
 932            "float8",
 933            "for",
 934            "force",
 935            "foreign",
 936            "from",
 937            "fulltext",
 938            "function",
 939            "generated",
 940            "get",
 941            "grant",
 942            "group",
 943            "grouping",
 944            "groups",
 945            "having",
 946            "high_priority",
 947            "hour_microsecond",
 948            "hour_minute",
 949            "hour_second",
 950            "if",
 951            "ignore",
 952            "in",
 953            "index",
 954            "infile",
 955            "inner",
 956            "inout",
 957            "insensitive",
 958            "insert",
 959            "int",
 960            "int1",
 961            "int2",
 962            "int3",
 963            "int4",
 964            "int8",
 965            "integer",
 966            "intersect",
 967            "interval",
 968            "into",
 969            "io_after_gtids",
 970            "io_before_gtids",
 971            "is",
 972            "iterate",
 973            "join",
 974            "json_table",
 975            "key",
 976            "keys",
 977            "kill",
 978            "lag",
 979            "last_value",
 980            "lateral",
 981            "lead",
 982            "leading",
 983            "leave",
 984            "left",
 985            "like",
 986            "limit",
 987            "linear",
 988            "lines",
 989            "load",
 990            "localtime",
 991            "localtimestamp",
 992            "lock",
 993            "long",
 994            "longblob",
 995            "longtext",
 996            "loop",
 997            "low_priority",
 998            "master_bind",
 999            "master_ssl_verify_server_cert",
1000            "match",
1001            "maxvalue",
1002            "mediumblob",
1003            "mediumint",
1004            "mediumtext",
1005            "middleint",
1006            "minute_microsecond",
1007            "minute_second",
1008            "mod",
1009            "modifies",
1010            "natural",
1011            "not",
1012            "no_write_to_binlog",
1013            "nth_value",
1014            "ntile",
1015            "null",
1016            "numeric",
1017            "of",
1018            "on",
1019            "optimize",
1020            "optimizer_costs",
1021            "option",
1022            "optionally",
1023            "or",
1024            "order",
1025            "out",
1026            "outer",
1027            "outfile",
1028            "over",
1029            "partition",
1030            "percent_rank",
1031            "precision",
1032            "primary",
1033            "procedure",
1034            "purge",
1035            "range",
1036            "rank",
1037            "read",
1038            "reads",
1039            "read_write",
1040            "real",
1041            "recursive",
1042            "references",
1043            "regexp",
1044            "release",
1045            "rename",
1046            "repeat",
1047            "replace",
1048            "require",
1049            "resignal",
1050            "restrict",
1051            "return",
1052            "revoke",
1053            "right",
1054            "rlike",
1055            "row",
1056            "rows",
1057            "row_number",
1058            "schema",
1059            "schemas",
1060            "second_microsecond",
1061            "select",
1062            "sensitive",
1063            "separator",
1064            "set",
1065            "show",
1066            "signal",
1067            "smallint",
1068            "spatial",
1069            "specific",
1070            "sql",
1071            "sqlexception",
1072            "sqlstate",
1073            "sqlwarning",
1074            "sql_big_result",
1075            "sql_calc_found_rows",
1076            "sql_small_result",
1077            "ssl",
1078            "starting",
1079            "stored",
1080            "straight_join",
1081            "system",
1082            "table",
1083            "terminated",
1084            "then",
1085            "tinyblob",
1086            "tinyint",
1087            "tinytext",
1088            "to",
1089            "trailing",
1090            "trigger",
1091            "true",
1092            "undo",
1093            "union",
1094            "unique",
1095            "unlock",
1096            "unsigned",
1097            "update",
1098            "usage",
1099            "use",
1100            "using",
1101            "utc_date",
1102            "utc_time",
1103            "utc_timestamp",
1104            "values",
1105            "varbinary",
1106            "varchar",
1107            "varcharacter",
1108            "varying",
1109            "virtual",
1110            "when",
1111            "where",
1112            "while",
1113            "window",
1114            "with",
1115            "write",
1116            "xor",
1117            "year_month",
1118            "zerofill",
1119        }
1120
1121        def array_sql(self, expression: exp.Array) -> str:
1122            self.unsupported("Arrays are not supported by MySQL")
1123            return self.function_fallback_sql(expression)
1124
1125        def arraycontainsall_sql(self, expression: exp.ArrayContainsAll) -> str:
1126            self.unsupported("Array operations are not supported by MySQL")
1127            return self.function_fallback_sql(expression)
1128
1129        def dpipe_sql(self, expression: exp.DPipe) -> str:
1130            return self.func("CONCAT", *expression.flatten())
1131
1132        def extract_sql(self, expression: exp.Extract) -> str:
1133            unit = expression.name
1134            if unit and unit.lower() == "epoch":
1135                return self.func("UNIX_TIMESTAMP", expression.expression)
1136
1137            return super().extract_sql(expression)
1138
1139        def datatype_sql(self, expression: exp.DataType) -> str:
1140            if (
1141                self.VARCHAR_REQUIRES_SIZE
1142                and expression.is_type(exp.DataType.Type.VARCHAR)
1143                and not expression.expressions
1144            ):
1145                # `VARCHAR` must always have a size - if it doesn't, we always generate `TEXT`
1146                return "TEXT"
1147
1148            # https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
1149            result = super().datatype_sql(expression)
1150            if expression.this in self.UNSIGNED_TYPE_MAPPING:
1151                result = f"{result} UNSIGNED"
1152
1153            return result
1154
1155        def jsonarraycontains_sql(self, expression: exp.JSONArrayContains) -> str:
1156            return f"{self.sql(expression, 'this')} MEMBER OF({self.sql(expression, 'expression')})"
1157
1158        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
1159            if expression.to.this in self.TIMESTAMP_FUNC_TYPES:
1160                return self.func("TIMESTAMP", expression.this)
1161
1162            to = self.CAST_MAPPING.get(expression.to.this)
1163
1164            if to:
1165                expression.to.set("this", to)
1166            return super().cast_sql(expression)
1167
1168        def show_sql(self, expression: exp.Show) -> str:
1169            this = f" {expression.name}"
1170            full = " FULL" if expression.args.get("full") else ""
1171            global_ = " GLOBAL" if expression.args.get("global") else ""
1172
1173            target = self.sql(expression, "target")
1174            target = f" {target}" if target else ""
1175            if expression.name in ("COLUMNS", "INDEX"):
1176                target = f" FROM{target}"
1177            elif expression.name == "GRANTS":
1178                target = f" FOR{target}"
1179
1180            db = self._prefixed_sql("FROM", expression, "db")
1181
1182            like = self._prefixed_sql("LIKE", expression, "like")
1183            where = self.sql(expression, "where")
1184
1185            types = self.expressions(expression, key="types")
1186            types = f" {types}" if types else types
1187            query = self._prefixed_sql("FOR QUERY", expression, "query")
1188
1189            if expression.name == "PROFILE":
1190                offset = self._prefixed_sql("OFFSET", expression, "offset")
1191                limit = self._prefixed_sql("LIMIT", expression, "limit")
1192            else:
1193                offset = ""
1194                limit = self._oldstyle_limit_sql(expression)
1195
1196            log = self._prefixed_sql("IN", expression, "log")
1197            position = self._prefixed_sql("FROM", expression, "position")
1198
1199            channel = self._prefixed_sql("FOR CHANNEL", expression, "channel")
1200
1201            if expression.name == "ENGINE":
1202                mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS"
1203            else:
1204                mutex_or_status = ""
1205
1206            return f"SHOW{full}{global_}{this}{target}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}"
1207
1208        def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
1209            dtype = self.sql(expression, "dtype")
1210            if not dtype:
1211                return super().altercolumn_sql(expression)
1212
1213            this = self.sql(expression, "this")
1214            return f"MODIFY COLUMN {this} {dtype}"
1215
1216        def _prefixed_sql(self, prefix: str, expression: exp.Expression, arg: str) -> str:
1217            sql = self.sql(expression, arg)
1218            return f" {prefix} {sql}" if sql else ""
1219
1220        def _oldstyle_limit_sql(self, expression: exp.Show) -> str:
1221            limit = self.sql(expression, "limit")
1222            offset = self.sql(expression, "offset")
1223            if limit:
1224                limit_offset = f"{offset}, {limit}" if offset else limit
1225                return f" LIMIT {limit_offset}"
1226            return ""
1227
1228        def chr_sql(self, expression: exp.Chr) -> str:
1229            this = self.expressions(sqls=[expression.this] + expression.expressions)
1230            charset = expression.args.get("charset")
1231            using = f" USING {self.sql(charset)}" if charset else ""
1232            return f"CHAR({this}{using})"
1233
1234        def timestamptrunc_sql(self, expression: exp.TimestampTrunc) -> str:
1235            unit = expression.args.get("unit")
1236
1237            # Pick an old-enough date to avoid negative timestamp diffs
1238            start_ts = "'0000-01-01 00:00:00'"
1239
1240            # Source: https://stackoverflow.com/a/32955740
1241            timestamp_diff = build_date_delta(exp.TimestampDiff)([unit, start_ts, expression.this])
1242            interval = exp.Interval(this=timestamp_diff, unit=unit)
1243            dateadd = build_date_delta_with_interval(exp.DateAdd)([start_ts, interval])
1244
1245            return self.sql(dateadd)
1246
1247        def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
1248            from_tz = expression.args.get("source_tz")
1249            to_tz = expression.args.get("target_tz")
1250            dt = expression.args.get("timestamp")
1251
1252            return self.func("CONVERT_TZ", dt, from_tz, to_tz)
1253
1254        def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
1255            self.unsupported("AT TIME ZONE is not supported by MySQL")
1256            return self.sql(expression.this)