sqlglot.dialects.mysql

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