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