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