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