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    arrow_json_extract_scalar_sql,
  9    date_add_interval_sql,
 10    datestrtodate_sql,
 11    format_time_lambda,
 12    isnull_to_is_null,
 13    json_keyvalue_comma_sql,
 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    parse_date_delta_with_interval,
 23    rename_func,
 24    simplify_literal,
 25    strposition_to_locate_sql,
 26)
 27from sqlglot.helper import seq_get
 28from sqlglot.tokens import TokenType
 29
 30
 31def _show_parser(*args: t.Any, **kwargs: t.Any) -> t.Callable[[MySQL.Parser], exp.Show]:
 32    def _parse(self: MySQL.Parser) -> exp.Show:
 33        return self._parse_show_mysql(*args, **kwargs)
 34
 35    return _parse
 36
 37
 38def _date_trunc_sql(self: MySQL.Generator, expression: exp.DateTrunc) -> str:
 39    expr = self.sql(expression, "this")
 40    unit = expression.text("unit")
 41
 42    if unit == "day":
 43        return f"DATE({expr})"
 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        self.unsupported(f"Unexpected interval unit: {unit}")
 59        return f"DATE({expr})"
 60
 61    return f"STR_TO_DATE({concat}, '{date_format}')"
 62
 63
 64def _str_to_date(args: t.List) -> exp.StrToDate:
 65    date_format = MySQL.format_time(seq_get(args, 1))
 66    return exp.StrToDate(this=seq_get(args, 0), format=date_format)
 67
 68
 69def _str_to_date_sql(
 70    self: MySQL.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
 71) -> str:
 72    date_format = self.format_time(expression)
 73    return f"STR_TO_DATE({self.sql(expression.this)}, {date_format})"
 74
 75
 76def _trim_sql(self: MySQL.Generator, expression: exp.Trim) -> str:
 77    target = self.sql(expression, "this")
 78    trim_type = self.sql(expression, "position")
 79    remove_chars = self.sql(expression, "expression")
 80
 81    # Use TRIM/LTRIM/RTRIM syntax if the expression isn't mysql-specific
 82    if not remove_chars:
 83        return self.trim_sql(expression)
 84
 85    trim_type = f"{trim_type} " if trim_type else ""
 86    remove_chars = f"{remove_chars} " if remove_chars else ""
 87    from_part = "FROM " if trim_type or remove_chars else ""
 88    return f"TRIM({trim_type}{remove_chars}{from_part}{target})"
 89
 90
 91def _date_add_sql(
 92    kind: str,
 93) -> t.Callable[[MySQL.Generator, exp.Expression], str]:
 94    def func(self: MySQL.Generator, expression: exp.Expression) -> str:
 95        this = self.sql(expression, "this")
 96        unit = expression.text("unit").upper() or "DAY"
 97        return f"DATE_{kind}({this}, {self.sql(exp.Interval(this=expression.expression.copy(), unit=unit))})"
 98
 99    return func
100
101
102def _ts_or_ds_to_date_sql(self: MySQL.Generator, expression: exp.TsOrDsToDate) -> str:
103    time_format = expression.args.get("format")
104    if time_format:
105        return _str_to_date_sql(self, expression)
106    return f"DATE({self.sql(expression, 'this')})"
107
108
109def _remove_ts_or_ds_to_date(
110    to_sql: t.Optional[t.Callable[[MySQL.Generator, exp.Expression], str]] = None,
111    args: t.Tuple[str, ...] = ("this",),
112) -> t.Callable[[MySQL.Generator, exp.Func], str]:
113    def func(self: MySQL.Generator, expression: exp.Func) -> str:
114        expression = expression.copy()
115
116        for arg_key in args:
117            arg = expression.args.get(arg_key)
118            if isinstance(arg, exp.TsOrDsToDate) and not arg.args.get("format"):
119                expression.set(arg_key, arg.this)
120
121        return to_sql(self, expression) if to_sql else self.function_fallback_sql(expression)
122
123    return func
124
125
126class MySQL(Dialect):
127    # https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
128    IDENTIFIERS_CAN_START_WITH_DIGIT = True
129
130    TIME_FORMAT = "'%Y-%m-%d %T'"
131    DPIPE_IS_STRING_CONCAT = False
132    SUPPORTS_USER_DEFINED_TYPES = False
133    SUPPORTS_SEMI_ANTI_JOIN = False
134
135    # https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions
136    TIME_MAPPING = {
137        "%M": "%B",
138        "%c": "%-m",
139        "%e": "%-d",
140        "%h": "%I",
141        "%i": "%M",
142        "%s": "%S",
143        "%u": "%W",
144        "%k": "%-H",
145        "%l": "%-I",
146        "%T": "%H:%M:%S",
147        "%W": "%a",
148    }
149
150    class Tokenizer(tokens.Tokenizer):
151        QUOTES = ["'", '"']
152        COMMENTS = ["--", "#", ("/*", "*/")]
153        IDENTIFIERS = ["`"]
154        STRING_ESCAPES = ["'", '"', "\\"]
155        BIT_STRINGS = [("b'", "'"), ("B'", "'"), ("0b", "")]
156        HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", "")]
157
158        KEYWORDS = {
159            **tokens.Tokenizer.KEYWORDS,
160            "CHARSET": TokenType.CHARACTER_SET,
161            "ENUM": TokenType.ENUM,
162            "FORCE": TokenType.FORCE,
163            "IGNORE": TokenType.IGNORE,
164            "LOCK TABLES": TokenType.COMMAND,
165            "LONGBLOB": TokenType.LONGBLOB,
166            "LONGTEXT": TokenType.LONGTEXT,
167            "MEDIUMBLOB": TokenType.MEDIUMBLOB,
168            "TINYBLOB": TokenType.TINYBLOB,
169            "TINYTEXT": TokenType.TINYTEXT,
170            "MEDIUMTEXT": TokenType.MEDIUMTEXT,
171            "MEDIUMINT": TokenType.MEDIUMINT,
172            "MEMBER OF": TokenType.MEMBER_OF,
173            "SEPARATOR": TokenType.SEPARATOR,
174            "START": TokenType.BEGIN,
175            "SIGNED": TokenType.BIGINT,
176            "SIGNED INTEGER": TokenType.BIGINT,
177            "UNLOCK TABLES": TokenType.COMMAND,
178            "UNSIGNED": TokenType.UBIGINT,
179            "UNSIGNED INTEGER": TokenType.UBIGINT,
180            "YEAR": TokenType.YEAR,
181            "_ARMSCII8": TokenType.INTRODUCER,
182            "_ASCII": TokenType.INTRODUCER,
183            "_BIG5": TokenType.INTRODUCER,
184            "_BINARY": TokenType.INTRODUCER,
185            "_CP1250": TokenType.INTRODUCER,
186            "_CP1251": TokenType.INTRODUCER,
187            "_CP1256": TokenType.INTRODUCER,
188            "_CP1257": TokenType.INTRODUCER,
189            "_CP850": TokenType.INTRODUCER,
190            "_CP852": TokenType.INTRODUCER,
191            "_CP866": TokenType.INTRODUCER,
192            "_CP932": TokenType.INTRODUCER,
193            "_DEC8": TokenType.INTRODUCER,
194            "_EUCJPMS": TokenType.INTRODUCER,
195            "_EUCKR": TokenType.INTRODUCER,
196            "_GB18030": TokenType.INTRODUCER,
197            "_GB2312": TokenType.INTRODUCER,
198            "_GBK": TokenType.INTRODUCER,
199            "_GEOSTD8": TokenType.INTRODUCER,
200            "_GREEK": TokenType.INTRODUCER,
201            "_HEBREW": TokenType.INTRODUCER,
202            "_HP8": TokenType.INTRODUCER,
203            "_KEYBCS2": TokenType.INTRODUCER,
204            "_KOI8R": TokenType.INTRODUCER,
205            "_KOI8U": TokenType.INTRODUCER,
206            "_LATIN1": TokenType.INTRODUCER,
207            "_LATIN2": TokenType.INTRODUCER,
208            "_LATIN5": TokenType.INTRODUCER,
209            "_LATIN7": TokenType.INTRODUCER,
210            "_MACCE": TokenType.INTRODUCER,
211            "_MACROMAN": TokenType.INTRODUCER,
212            "_SJIS": TokenType.INTRODUCER,
213            "_SWE7": TokenType.INTRODUCER,
214            "_TIS620": TokenType.INTRODUCER,
215            "_UCS2": TokenType.INTRODUCER,
216            "_UJIS": TokenType.INTRODUCER,
217            # https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
218            "_UTF8": TokenType.INTRODUCER,
219            "_UTF16": TokenType.INTRODUCER,
220            "_UTF16LE": TokenType.INTRODUCER,
221            "_UTF32": TokenType.INTRODUCER,
222            "_UTF8MB3": TokenType.INTRODUCER,
223            "_UTF8MB4": TokenType.INTRODUCER,
224            "@@": TokenType.SESSION_PARAMETER,
225        }
226
227        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
228
229    class Parser(parser.Parser):
230        FUNC_TOKENS = {
231            *parser.Parser.FUNC_TOKENS,
232            TokenType.DATABASE,
233            TokenType.SCHEMA,
234            TokenType.VALUES,
235        }
236
237        CONJUNCTION = {
238            **parser.Parser.CONJUNCTION,
239            TokenType.DAMP: exp.And,
240            TokenType.XOR: exp.Xor,
241            TokenType.DPIPE: exp.Or,
242        }
243
244        # MySQL uses || as a synonym to the logical OR operator
245        # https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html#operator_or
246        BITWISE = parser.Parser.BITWISE.copy()
247        BITWISE.pop(TokenType.DPIPE)
248
249        TABLE_ALIAS_TOKENS = (
250            parser.Parser.TABLE_ALIAS_TOKENS - parser.Parser.TABLE_INDEX_HINT_TOKENS
251        )
252
253        RANGE_PARSERS = {
254            **parser.Parser.RANGE_PARSERS,
255            TokenType.MEMBER_OF: lambda self, this: self.expression(
256                exp.JSONArrayContains,
257                this=this,
258                expression=self._parse_wrapped(self._parse_expression),
259            ),
260        }
261
262        FUNCTIONS = {
263            **parser.Parser.FUNCTIONS,
264            "DATE": lambda args: exp.TsOrDsToDate(this=seq_get(args, 0)),
265            "DATE_ADD": parse_date_delta_with_interval(exp.DateAdd),
266            "DATE_FORMAT": format_time_lambda(exp.TimeToStr, "mysql"),
267            "DATE_SUB": parse_date_delta_with_interval(exp.DateSub),
268            "INSTR": lambda args: exp.StrPosition(substr=seq_get(args, 1), this=seq_get(args, 0)),
269            "ISNULL": isnull_to_is_null,
270            "LOCATE": locate_to_strposition,
271            "MONTHNAME": lambda args: exp.TimeToStr(
272                this=exp.TsOrDsToDate(this=seq_get(args, 0)),
273                format=exp.Literal.string("%B"),
274            ),
275            "STR_TO_DATE": _str_to_date,
276            "TO_DAYS": lambda args: exp.paren(
277                exp.DateDiff(
278                    this=exp.TsOrDsToDate(this=seq_get(args, 0)),
279                    expression=exp.TsOrDsToDate(this=exp.Literal.string("0000-01-01")),
280                    unit=exp.var("DAY"),
281                )
282                + 1
283            ),
284            "DAY": lambda args: exp.Day(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
285            "DAYOFMONTH": lambda args: exp.DayOfMonth(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
286            "DAYOFWEEK": lambda args: exp.DayOfWeek(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
287            "DAYOFYEAR": lambda args: exp.DayOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
288            "MONTH": lambda args: exp.Month(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
289            "WEEK": lambda args: exp.Week(
290                this=exp.TsOrDsToDate(this=seq_get(args, 0)), mode=seq_get(args, 1)
291            ),
292            "WEEKOFYEAR": lambda args: exp.WeekOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
293            "YEAR": lambda args: exp.Year(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
294        }
295
296        FUNCTION_PARSERS = {
297            **parser.Parser.FUNCTION_PARSERS,
298            "CHAR": lambda self: self._parse_chr(),
299            "GROUP_CONCAT": lambda self: self.expression(
300                exp.GroupConcat,
301                this=self._parse_lambda(),
302                separator=self._match(TokenType.SEPARATOR) and self._parse_field(),
303            ),
304            # https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values
305            "VALUES": lambda self: self.expression(
306                exp.Anonymous, this="VALUES", expressions=[self._parse_id_var()]
307            ),
308        }
309
310        STATEMENT_PARSERS = {
311            **parser.Parser.STATEMENT_PARSERS,
312            TokenType.SHOW: lambda self: self._parse_show(),
313        }
314
315        SHOW_PARSERS = {
316            "BINARY LOGS": _show_parser("BINARY LOGS"),
317            "MASTER LOGS": _show_parser("BINARY LOGS"),
318            "BINLOG EVENTS": _show_parser("BINLOG EVENTS"),
319            "CHARACTER SET": _show_parser("CHARACTER SET"),
320            "CHARSET": _show_parser("CHARACTER SET"),
321            "COLLATION": _show_parser("COLLATION"),
322            "FULL COLUMNS": _show_parser("COLUMNS", target="FROM", full=True),
323            "COLUMNS": _show_parser("COLUMNS", target="FROM"),
324            "CREATE DATABASE": _show_parser("CREATE DATABASE", target=True),
325            "CREATE EVENT": _show_parser("CREATE EVENT", target=True),
326            "CREATE FUNCTION": _show_parser("CREATE FUNCTION", target=True),
327            "CREATE PROCEDURE": _show_parser("CREATE PROCEDURE", target=True),
328            "CREATE TABLE": _show_parser("CREATE TABLE", target=True),
329            "CREATE TRIGGER": _show_parser("CREATE TRIGGER", target=True),
330            "CREATE VIEW": _show_parser("CREATE VIEW", target=True),
331            "DATABASES": _show_parser("DATABASES"),
332            "ENGINE": _show_parser("ENGINE", target=True),
333            "STORAGE ENGINES": _show_parser("ENGINES"),
334            "ENGINES": _show_parser("ENGINES"),
335            "ERRORS": _show_parser("ERRORS"),
336            "EVENTS": _show_parser("EVENTS"),
337            "FUNCTION CODE": _show_parser("FUNCTION CODE", target=True),
338            "FUNCTION STATUS": _show_parser("FUNCTION STATUS"),
339            "GRANTS": _show_parser("GRANTS", target="FOR"),
340            "INDEX": _show_parser("INDEX", target="FROM"),
341            "MASTER STATUS": _show_parser("MASTER STATUS"),
342            "OPEN TABLES": _show_parser("OPEN TABLES"),
343            "PLUGINS": _show_parser("PLUGINS"),
344            "PROCEDURE CODE": _show_parser("PROCEDURE CODE", target=True),
345            "PROCEDURE STATUS": _show_parser("PROCEDURE STATUS"),
346            "PRIVILEGES": _show_parser("PRIVILEGES"),
347            "FULL PROCESSLIST": _show_parser("PROCESSLIST", full=True),
348            "PROCESSLIST": _show_parser("PROCESSLIST"),
349            "PROFILE": _show_parser("PROFILE"),
350            "PROFILES": _show_parser("PROFILES"),
351            "RELAYLOG EVENTS": _show_parser("RELAYLOG EVENTS"),
352            "REPLICAS": _show_parser("REPLICAS"),
353            "SLAVE HOSTS": _show_parser("REPLICAS"),
354            "REPLICA STATUS": _show_parser("REPLICA STATUS"),
355            "SLAVE STATUS": _show_parser("REPLICA STATUS"),
356            "GLOBAL STATUS": _show_parser("STATUS", global_=True),
357            "SESSION STATUS": _show_parser("STATUS"),
358            "STATUS": _show_parser("STATUS"),
359            "TABLE STATUS": _show_parser("TABLE STATUS"),
360            "FULL TABLES": _show_parser("TABLES", full=True),
361            "TABLES": _show_parser("TABLES"),
362            "TRIGGERS": _show_parser("TRIGGERS"),
363            "GLOBAL VARIABLES": _show_parser("VARIABLES", global_=True),
364            "SESSION VARIABLES": _show_parser("VARIABLES"),
365            "VARIABLES": _show_parser("VARIABLES"),
366            "WARNINGS": _show_parser("WARNINGS"),
367        }
368
369        SET_PARSERS = {
370            **parser.Parser.SET_PARSERS,
371            "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"),
372            "PERSIST_ONLY": lambda self: self._parse_set_item_assignment("PERSIST_ONLY"),
373            "CHARACTER SET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
374            "CHARSET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
375            "NAMES": lambda self: self._parse_set_item_names(),
376        }
377
378        CONSTRAINT_PARSERS = {
379            **parser.Parser.CONSTRAINT_PARSERS,
380            "FULLTEXT": lambda self: self._parse_index_constraint(kind="FULLTEXT"),
381            "INDEX": lambda self: self._parse_index_constraint(),
382            "KEY": lambda self: self._parse_index_constraint(),
383            "SPATIAL": lambda self: self._parse_index_constraint(kind="SPATIAL"),
384        }
385
386        SCHEMA_UNNAMED_CONSTRAINTS = {
387            *parser.Parser.SCHEMA_UNNAMED_CONSTRAINTS,
388            "FULLTEXT",
389            "INDEX",
390            "KEY",
391            "SPATIAL",
392        }
393
394        PROFILE_TYPES = {
395            "ALL",
396            "BLOCK IO",
397            "CONTEXT SWITCHES",
398            "CPU",
399            "IPC",
400            "MEMORY",
401            "PAGE FAULTS",
402            "SOURCE",
403            "SWAPS",
404        }
405
406        TYPE_TOKENS = {
407            *parser.Parser.TYPE_TOKENS,
408            TokenType.SET,
409        }
410
411        ENUM_TYPE_TOKENS = {
412            *parser.Parser.ENUM_TYPE_TOKENS,
413            TokenType.SET,
414        }
415
416        LOG_DEFAULTS_TO_LN = True
417
418        def _parse_primary_key_part(self) -> t.Optional[exp.Expression]:
419            this = self._parse_id_var()
420            if not self._match(TokenType.L_PAREN):
421                return this
422
423            expression = self._parse_number()
424            self._match_r_paren()
425            return self.expression(exp.ColumnPrefix, this=this, expression=expression)
426
427        def _parse_index_constraint(
428            self, kind: t.Optional[str] = None
429        ) -> exp.IndexColumnConstraint:
430            if kind:
431                self._match_texts({"INDEX", "KEY"})
432
433            this = self._parse_id_var(any_token=False)
434            index_type = self._match(TokenType.USING) and self._advance_any() and self._prev.text
435            schema = self._parse_schema()
436
437            options = []
438            while True:
439                if self._match_text_seq("KEY_BLOCK_SIZE"):
440                    self._match(TokenType.EQ)
441                    opt = exp.IndexConstraintOption(key_block_size=self._parse_number())
442                elif self._match(TokenType.USING):
443                    opt = exp.IndexConstraintOption(using=self._advance_any() and self._prev.text)
444                elif self._match_text_seq("WITH", "PARSER"):
445                    opt = exp.IndexConstraintOption(parser=self._parse_var(any_token=True))
446                elif self._match(TokenType.COMMENT):
447                    opt = exp.IndexConstraintOption(comment=self._parse_string())
448                elif self._match_text_seq("VISIBLE"):
449                    opt = exp.IndexConstraintOption(visible=True)
450                elif self._match_text_seq("INVISIBLE"):
451                    opt = exp.IndexConstraintOption(visible=False)
452                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
453                    self._match(TokenType.EQ)
454                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
455                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
456                    self._match(TokenType.EQ)
457                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
458                elif self._match_text_seq("SECONDARY_ENGINE_ATTRIBUTE"):
459                    self._match(TokenType.EQ)
460                    opt = exp.IndexConstraintOption(secondary_engine_attr=self._parse_string())
461                else:
462                    opt = None
463
464                if not opt:
465                    break
466
467                options.append(opt)
468
469            return self.expression(
470                exp.IndexColumnConstraint,
471                this=this,
472                schema=schema,
473                kind=kind,
474                index_type=index_type,
475                options=options,
476            )
477
478        def _parse_show_mysql(
479            self,
480            this: str,
481            target: bool | str = False,
482            full: t.Optional[bool] = None,
483            global_: t.Optional[bool] = None,
484        ) -> exp.Show:
485            if target:
486                if isinstance(target, str):
487                    self._match_text_seq(target)
488                target_id = self._parse_id_var()
489            else:
490                target_id = None
491
492            log = self._parse_string() if self._match_text_seq("IN") else None
493
494            if this in {"BINLOG EVENTS", "RELAYLOG EVENTS"}:
495                position = self._parse_number() if self._match_text_seq("FROM") else None
496                db = None
497            else:
498                position = None
499                db = None
500
501                if self._match(TokenType.FROM):
502                    db = self._parse_id_var()
503                elif self._match(TokenType.DOT):
504                    db = target_id
505                    target_id = self._parse_id_var()
506
507            channel = self._parse_id_var() if self._match_text_seq("FOR", "CHANNEL") else None
508
509            like = self._parse_string() if self._match_text_seq("LIKE") else None
510            where = self._parse_where()
511
512            if this == "PROFILE":
513                types = self._parse_csv(lambda: self._parse_var_from_options(self.PROFILE_TYPES))
514                query = self._parse_number() if self._match_text_seq("FOR", "QUERY") else None
515                offset = self._parse_number() if self._match_text_seq("OFFSET") else None
516                limit = self._parse_number() if self._match_text_seq("LIMIT") else None
517            else:
518                types, query = None, None
519                offset, limit = self._parse_oldstyle_limit()
520
521            mutex = True if self._match_text_seq("MUTEX") else None
522            mutex = False if self._match_text_seq("STATUS") else mutex
523
524            return self.expression(
525                exp.Show,
526                this=this,
527                target=target_id,
528                full=full,
529                log=log,
530                position=position,
531                db=db,
532                channel=channel,
533                like=like,
534                where=where,
535                types=types,
536                query=query,
537                offset=offset,
538                limit=limit,
539                mutex=mutex,
540                **{"global": global_},  # type: ignore
541            )
542
543        def _parse_oldstyle_limit(
544            self,
545        ) -> t.Tuple[t.Optional[exp.Expression], t.Optional[exp.Expression]]:
546            limit = None
547            offset = None
548            if self._match_text_seq("LIMIT"):
549                parts = self._parse_csv(self._parse_number)
550                if len(parts) == 1:
551                    limit = parts[0]
552                elif len(parts) == 2:
553                    limit = parts[1]
554                    offset = parts[0]
555
556            return offset, limit
557
558        def _parse_set_item_charset(self, kind: str) -> exp.Expression:
559            this = self._parse_string() or self._parse_id_var()
560            return self.expression(exp.SetItem, this=this, kind=kind)
561
562        def _parse_set_item_names(self) -> exp.Expression:
563            charset = self._parse_string() or self._parse_id_var()
564            if self._match_text_seq("COLLATE"):
565                collate = self._parse_string() or self._parse_id_var()
566            else:
567                collate = None
568
569            return self.expression(exp.SetItem, this=charset, collate=collate, kind="NAMES")
570
571        def _parse_type(self, parse_interval: bool = True) -> t.Optional[exp.Expression]:
572            # mysql binary is special and can work anywhere, even in order by operations
573            # it operates like a no paren func
574            if self._match(TokenType.BINARY, advance=False):
575                data_type = self._parse_types(check_func=True, allow_identifiers=False)
576
577                if isinstance(data_type, exp.DataType):
578                    return self.expression(exp.Cast, this=self._parse_column(), to=data_type)
579
580            return super()._parse_type(parse_interval=parse_interval)
581
582        def _parse_chr(self) -> t.Optional[exp.Expression]:
583            expressions = self._parse_csv(self._parse_conjunction)
584            kwargs: t.Dict[str, t.Any] = {"this": seq_get(expressions, 0)}
585
586            if len(expressions) > 1:
587                kwargs["expressions"] = expressions[1:]
588
589            if self._match(TokenType.USING):
590                kwargs["charset"] = self._parse_var()
591
592            return self.expression(exp.Chr, **kwargs)
593
594    class Generator(generator.Generator):
595        LOCKING_READS_SUPPORTED = True
596        NULL_ORDERING_SUPPORTED = False
597        JOIN_HINTS = False
598        TABLE_HINTS = True
599        DUPLICATE_KEY_UPDATE_WITH_SET = False
600        QUERY_HINT_SEP = " "
601        VALUES_AS_TABLE = False
602        NVL2_SUPPORTED = False
603
604        TRANSFORMS = {
605            **generator.Generator.TRANSFORMS,
606            exp.CurrentDate: no_paren_current_date_sql,
607            exp.DateDiff: _remove_ts_or_ds_to_date(
608                lambda self, e: self.func("DATEDIFF", e.this, e.expression), ("this", "expression")
609            ),
610            exp.DateAdd: _remove_ts_or_ds_to_date(_date_add_sql("ADD")),
611            exp.DateStrToDate: datestrtodate_sql,
612            exp.DateSub: _remove_ts_or_ds_to_date(_date_add_sql("SUB")),
613            exp.DateTrunc: _date_trunc_sql,
614            exp.Day: _remove_ts_or_ds_to_date(),
615            exp.DayOfMonth: _remove_ts_or_ds_to_date(rename_func("DAYOFMONTH")),
616            exp.DayOfWeek: _remove_ts_or_ds_to_date(rename_func("DAYOFWEEK")),
617            exp.DayOfYear: _remove_ts_or_ds_to_date(rename_func("DAYOFYEAR")),
618            exp.GroupConcat: lambda self, e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""",
619            exp.ILike: no_ilike_sql,
620            exp.JSONExtractScalar: arrow_json_extract_scalar_sql,
621            exp.JSONKeyValue: json_keyvalue_comma_sql,
622            exp.Max: max_or_greatest,
623            exp.Min: min_or_least,
624            exp.Month: _remove_ts_or_ds_to_date(),
625            exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"),
626            exp.NullSafeNEQ: lambda self, e: self.not_sql(self.binary(e, "<=>")),
627            exp.Pivot: no_pivot_sql,
628            exp.Select: transforms.preprocess(
629                [
630                    transforms.eliminate_distinct_on,
631                    transforms.eliminate_semi_and_anti_joins,
632                    transforms.eliminate_qualify,
633                ]
634            ),
635            exp.StrPosition: strposition_to_locate_sql,
636            exp.StrToDate: _str_to_date_sql,
637            exp.StrToTime: _str_to_date_sql,
638            exp.Stuff: rename_func("INSERT"),
639            exp.TableSample: no_tablesample_sql,
640            exp.TimestampAdd: date_add_interval_sql("DATE", "ADD"),
641            exp.TimestampSub: date_add_interval_sql("DATE", "SUB"),
642            exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
643            exp.TimeStrToTime: lambda self, e: self.sql(exp.cast(e.this, "datetime", copy=True)),
644            exp.TimeToStr: _remove_ts_or_ds_to_date(
645                lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e))
646            ),
647            exp.Trim: _trim_sql,
648            exp.TryCast: no_trycast_sql,
649            exp.TsOrDsAdd: _date_add_sql("ADD"),
650            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
651            exp.Week: _remove_ts_or_ds_to_date(),
652            exp.WeekOfYear: _remove_ts_or_ds_to_date(rename_func("WEEKOFYEAR")),
653            exp.Year: _remove_ts_or_ds_to_date(),
654        }
655
656        UNSIGNED_TYPE_MAPPING = {
657            exp.DataType.Type.UBIGINT: "BIGINT",
658            exp.DataType.Type.UINT: "INT",
659            exp.DataType.Type.UMEDIUMINT: "MEDIUMINT",
660            exp.DataType.Type.USMALLINT: "SMALLINT",
661            exp.DataType.Type.UTINYINT: "TINYINT",
662            exp.DataType.Type.UDECIMAL: "DECIMAL",
663        }
664
665        TIMESTAMP_TYPE_MAPPING = {
666            exp.DataType.Type.TIMESTAMP: "DATETIME",
667            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
668            exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
669        }
670
671        TYPE_MAPPING = {
672            **generator.Generator.TYPE_MAPPING,
673            **UNSIGNED_TYPE_MAPPING,
674            **TIMESTAMP_TYPE_MAPPING,
675        }
676
677        TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT)
678        TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT)
679        TYPE_MAPPING.pop(exp.DataType.Type.TINYTEXT)
680        TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB)
681        TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB)
682        TYPE_MAPPING.pop(exp.DataType.Type.TINYBLOB)
683
684        PROPERTIES_LOCATION = {
685            **generator.Generator.PROPERTIES_LOCATION,
686            exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
687            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
688        }
689
690        LIMIT_FETCH = "LIMIT"
691
692        # MySQL doesn't support many datatypes in cast.
693        # https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast
694        CAST_MAPPING = {
695            exp.DataType.Type.BIGINT: "SIGNED",
696            exp.DataType.Type.BOOLEAN: "SIGNED",
697            exp.DataType.Type.INT: "SIGNED",
698            exp.DataType.Type.TEXT: "CHAR",
699            exp.DataType.Type.UBIGINT: "UNSIGNED",
700            exp.DataType.Type.VARCHAR: "CHAR",
701        }
702
703        TIMESTAMP_FUNC_TYPES = {
704            exp.DataType.Type.TIMESTAMPTZ,
705            exp.DataType.Type.TIMESTAMPLTZ,
706        }
707
708        def datatype_sql(self, expression: exp.DataType) -> str:
709            # https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
710            result = super().datatype_sql(expression)
711            if expression.this in self.UNSIGNED_TYPE_MAPPING:
712                result = f"{result} UNSIGNED"
713            return result
714
715        def limit_sql(self, expression: exp.Limit, top: bool = False) -> str:
716            # MySQL requires simple literal values for its LIMIT clause.
717            expression = simplify_literal(expression.copy())
718            return super().limit_sql(expression, top=top)
719
720        def offset_sql(self, expression: exp.Offset) -> str:
721            # MySQL requires simple literal values for its OFFSET clause.
722            expression = simplify_literal(expression.copy())
723            return super().offset_sql(expression)
724
725        def xor_sql(self, expression: exp.Xor) -> str:
726            if expression.expressions:
727                return self.expressions(expression, sep=" XOR ")
728            return super().xor_sql(expression)
729
730        def jsonarraycontains_sql(self, expression: exp.JSONArrayContains) -> str:
731            return f"{self.sql(expression, 'this')} MEMBER OF({self.sql(expression, 'expression')})"
732
733        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
734            if expression.to.this in self.TIMESTAMP_FUNC_TYPES:
735                return self.func("TIMESTAMP", expression.this)
736
737            to = self.CAST_MAPPING.get(expression.to.this)
738
739            if to:
740                expression = expression.copy()
741                expression.to.set("this", to)
742            return super().cast_sql(expression)
743
744        def show_sql(self, expression: exp.Show) -> str:
745            this = f" {expression.name}"
746            full = " FULL" if expression.args.get("full") else ""
747            global_ = " GLOBAL" if expression.args.get("global") else ""
748
749            target = self.sql(expression, "target")
750            target = f" {target}" if target else ""
751            if expression.name in {"COLUMNS", "INDEX"}:
752                target = f" FROM{target}"
753            elif expression.name == "GRANTS":
754                target = f" FOR{target}"
755
756            db = self._prefixed_sql("FROM", expression, "db")
757
758            like = self._prefixed_sql("LIKE", expression, "like")
759            where = self.sql(expression, "where")
760
761            types = self.expressions(expression, key="types")
762            types = f" {types}" if types else types
763            query = self._prefixed_sql("FOR QUERY", expression, "query")
764
765            if expression.name == "PROFILE":
766                offset = self._prefixed_sql("OFFSET", expression, "offset")
767                limit = self._prefixed_sql("LIMIT", expression, "limit")
768            else:
769                offset = ""
770                limit = self._oldstyle_limit_sql(expression)
771
772            log = self._prefixed_sql("IN", expression, "log")
773            position = self._prefixed_sql("FROM", expression, "position")
774
775            channel = self._prefixed_sql("FOR CHANNEL", expression, "channel")
776
777            if expression.name == "ENGINE":
778                mutex_or_status = " MUTEX" if expression.args.get("mutex") else " STATUS"
779            else:
780                mutex_or_status = ""
781
782            return f"SHOW{full}{global_}{this}{target}{types}{db}{query}{log}{position}{channel}{mutex_or_status}{like}{where}{offset}{limit}"
783
784        def _prefixed_sql(self, prefix: str, expression: exp.Expression, arg: str) -> str:
785            sql = self.sql(expression, arg)
786            return f" {prefix} {sql}" if sql else ""
787
788        def _oldstyle_limit_sql(self, expression: exp.Show) -> str:
789            limit = self.sql(expression, "limit")
790            offset = self.sql(expression, "offset")
791            if limit:
792                limit_offset = f"{offset}, {limit}" if offset else limit
793                return f" LIMIT {limit_offset}"
794            return ""
795
796        def chr_sql(self, expression: exp.Chr) -> str:
797            this = self.expressions(sqls=[expression.this] + expression.expressions)
798            charset = expression.args.get("charset")
799            using = f" USING {self.sql(charset)}" if charset else ""
800            return f"CHAR({this}{using})"