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