JSON, generated columns and full-text search

json_read_session.py

"""Reading a JSON column: ->, ->> and JSON_EXTRACT on 20 orders, and what the binary storage keeps.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads orders.sql quietly, then runs json_read.sql. The documents are
fixed literals, so every run prints the same rows.
"""

from mysql_session import Server

with Server() as my:
    my.up()
    my.mysql("orders.sql", flags=("--skip-comments",))
    my.mysql("json_read.sql", flags=("-t", "-vv", "--skip-comments"))

$ mysql --skip-comments < orders.sql

$ mysql -t -vv --skip-comments < json_read.sql
--------------
SELECT id, delivery->>'$.pin_code' AS pin_text, delivery->'$.pin_code' AS pin_json, delivery->'$.items' AS items FROM orders LIMIT 3
--------------

+----+----------+----------+-----------------------------------------------------------------------------------+
| id | pin_text | pin_json | items                                                                             |
+----+----------+----------+-----------------------------------------------------------------------------------+
|  1 | 560001   | "560001" | [{"qty": 2, "sku": "A100"}, {"qty": 1, "sku": "B200"}]                            |
|  2 | 560002   | "560002" | [{"qty": 4, "sku": "C300"}]                                                       |
|  3 | 400001   | "400001" | [{"qty": 1, "sku": "A100"}, {"qty": 3, "sku": "D400"}, {"qty": 1, "sku": "E500"}] |
+----+----------+----------+-----------------------------------------------------------------------------------+
3 rows in set

--------------
SELECT JSON_EXTRACT(delivery, '$.pin_code') AS pin, JSON_TYPE(delivery->'$.pin_code') AS json_type, JSON_TYPE(delivery->'$.express') AS express_type, delivery->>'$.landmark' AS landmark FROM orders WHERE id IN (1, 3)
--------------

+----------+-----------+--------------+------------------+
| pin      | json_type | express_type | landmark         |
+----------+-----------+--------------+------------------+
| "560001" | STRING    | BOOLEAN      | NULL             |
| "400001" | STRING    | BOOLEAN      | Gateway of India |
+----------+-----------+--------------+------------------+
2 rows in set

--------------
SELECT COUNT(*) AS in_560001 FROM orders WHERE delivery->>'$.pin_code' = '560001'
--------------

+-----------+
| in_560001 |
+-----------+
|         4 |
+-----------+
1 row in set

--------------
SELECT CAST('{"zip": 1, "city": "Pune", "zip": 2}' AS JSON) AS normalised
--------------

+----------------------------+
| normalised                 |
+----------------------------+
| {"zip": 2, "city": "Pune"} |
+----------------------------+
1 row in set

--------------
SELECT id, JSON_STORAGE_SIZE(delivery) AS binary_bytes, LENGTH(CAST(delivery AS CHAR)) AS text_bytes FROM orders WHERE id <= 3
--------------

+----+--------------+------------+
| id | binary_bytes | text_bytes |
+----+--------------+------------+
|  1 |          142 |        125 |
|  2 |          110 |         99 |
|  3 |          203 |        181 |
+----+--------------+------------+
3 rows in set

Bye

json_read.sql

USE shop;
-- -> returns JSON (strings keep their quotes), ->> returns a plain utf8mb4 string; JSON_EXTRACT is -> in function form.
SELECT id, delivery->>'$.pin_code' AS pin_text, delivery->'$.pin_code' AS pin_json, delivery->'$.items' AS items FROM orders LIMIT 3;
SELECT JSON_EXTRACT(delivery, '$.pin_code') AS pin, JSON_TYPE(delivery->'$.pin_code') AS json_type, JSON_TYPE(delivery->'$.express') AS express_type, delivery->>'$.landmark' AS landmark FROM orders WHERE id IN (1, 3);
SELECT COUNT(*) AS in_560001 FROM orders WHERE delivery->>'$.pin_code' = '560001';
-- The stored form is binary and normalised: keys are sorted and a repeated key keeps only one value.
SELECT CAST('{"zip": 1, "city": "Pune", "zip": 2}' AS JSON) AS normalised;
SELECT id, JSON_STORAGE_SIZE(delivery) AS binary_bytes, LENGTH(CAST(delivery AS CHAR)) AS text_bytes FROM orders WHERE id <= 3;

orders.sql

-- The 20 orders every JSON session starts from: one JSON document of delivery details per order.
CREATE DATABASE shop;
USE shop;
CREATE TABLE orders (id BIGINT PRIMARY KEY, delivery JSON);
INSERT INTO orders VALUES
  (1, '{"pin_code": "560001", "city": "Bengaluru", "express": true, "items": [{"sku": "A100", "qty": 2}, {"sku": "B200", "qty": 1}]}'),
  (2, '{"pin_code": "560002", "city": "Bengaluru", "express": false, "items": [{"sku": "C300", "qty": 4}]}'),
  (3, '{"pin_code": "400001", "city": "Mumbai", "express": true, "landmark": "Gateway of India", "items": [{"sku": "A100", "qty": 1}, {"sku": "D400", "qty": 3}, {"sku": "E500", "qty": 1}]}'),
  (4, '{"pin_code": "110001", "city": "New Delhi", "express": false, "items": [{"sku": "B200", "qty": 5}]}'),
  (5, '{"pin_code": "600001", "city": "Chennai", "express": true, "items": [{"sku": "F600", "qty": 2}, {"sku": "A100", "qty": 2}]}'),
  (6, '{"pin_code": "700001", "city": "Kolkata", "express": false, "landmark": "Esplanade", "items": [{"sku": "C300", "qty": 1}]}'),
  (7, '{"pin_code": "560001", "city": "Bengaluru", "express": false, "items": [{"sku": "D400", "qty": 2}, {"sku": "E500", "qty": 2}]}'),
  (8, '{"pin_code": "411001", "city": "Pune", "express": true, "items": [{"sku": "A100", "qty": 3}]}'),
  (9, '{"pin_code": "500001", "city": "Hyderabad", "express": false, "items": [{"sku": "B200", "qty": 2}, {"sku": "F600", "qty": 1}]}'),
  (10, '{"pin_code": "380001", "city": "Ahmedabad", "express": false, "items": [{"sku": "E500", "qty": 4}]}'),
  (11, '{"pin_code": "560001", "city": "Bengaluru", "express": true, "items": [{"sku": "C300", "qty": 2}, {"sku": "A100", "qty": 1}, {"sku": "B200", "qty": 1}]}'),
  (12, '{"pin_code": "400001", "city": "Mumbai", "express": false, "items": [{"sku": "F600", "qty": 6}]}'),
  (13, '{"pin_code": "110001", "city": "New Delhi", "express": true, "landmark": "India Gate", "items": [{"sku": "D400", "qty": 1}, {"sku": "A100", "qty": 1}]}'),
  (14, '{"pin_code": "600001", "city": "Chennai", "express": false, "items": [{"sku": "E500", "qty": 3}]}'),
  (15, '{"pin_code": "302001", "city": "Jaipur", "express": false, "items": [{"sku": "B200", "qty": 1}, {"sku": "C300", "qty": 1}]}'),
  (16, '{"pin_code": "560002", "city": "Bengaluru", "express": true, "items": [{"sku": "A100", "qty": 5}]}'),
  (17, '{"pin_code": "700001", "city": "Kolkata", "express": true, "items": [{"sku": "D400", "qty": 2}, {"sku": "F600", "qty": 2}]}'),
  (18, '{"pin_code": "411001", "city": "Pune", "express": false, "items": [{"sku": "C300", "qty": 3}]}'),
  (19, '{"pin_code": "500001", "city": "Hyderabad", "express": true, "items": [{"sku": "E500", "qty": 1}, {"sku": "B200", "qty": 2}]}'),
  (20, '{"pin_code": "560001", "city": "Bengaluru", "express": false, "items": [{"sku": "F600", "qty": 1}]}');

mysql_session.py

"""Throwaway MySQL 8.4.11 servers for the examples of this lesson.

Every session creates a server of its own: `mysqld --initialize-insecure` makes a new data directory inside a new
folder in the examples folder, the server starts on it, the session runs `mysql` client scripts and MySQL's programs
against it and prints what they printed, and then the server is stopped and the folder deleted, also when something
fails. The server is the pinned MySQL 8.4.11 from the mise installations next to the Python that runs this file;
Server(basedir=...) runs the programs of another unpacked MySQL tarball instead (dump_defaults.py uses it).

Safety: the server listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system
reports as unused; the X Protocol listener is off (--mysqlx=OFF); its Unix socket, temporary files, error log and
import/export folder all stay inside the run folder. Right after the first start the session gives root a password made
up for the run (never printed) and connects only with a client option file that holds it, so no password ever appears
on a command line. Every program starts with --no-defaults or --defaults-file, so no option file of the computer
(/etc/my.cnf, ~/.my.cnf) can change a result, and HOME points at the run folder, so ~/.mylogin.cnf is not read either.
A printed mysqld command shows the options that matter to the lesson; the session also passes, on the command line,
the folders it keeps inside the run folder (--basedir, --tmpdir, --log-error, --pid-file, --secure-file-priv) and
the port, which is why performance_schema.variables_info reports those variables as COMMAND_LINE.
learn:verify's run sandbox cannot read the home folder, where mise keeps MySQL, so these sessions are recorded with
LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with these substitutions so that a record never shows the computer it
ran on and is the same on every run: the run folder is shown as ".", the folder of MySQL's programs is left out of
their paths and MySQL's own folder is shown as <basedir>, the computer's name is shown as "lab-host" and the operating-system user's name as "learner", the time a
statement took (the client's "(0.01 sec)") is left out, the time stamp SET PERSIST writes into mysqld-auto.cnf is
shown as <when it was set>, error-log lines lose their timestamp and thread number, and a session can name more values
that change between runs with my.mask(pattern, replacement), which then prints in their place. A client table whose cells
change is drawn again so that its borders still line up.
The sessions never print the password, and the run's port prints as <port>. A session stops with status 1 when a command that should have worked
fails, or when a script reports a different number of errors than the session expects, so a run that went wrong is
never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

MYSQL_VERSION = "8.4.11"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
PINNED = INSTALLS / "mysql" / MYSQL_VERSION
OS_USER = pwd.getpwuid(os.geteuid()).pw_name
HOST = socket.gethostname().split(".")[0]
# 2026-10-17T16:50:01.123456Z 0 [ERROR] ... -> [ERROR] ...
LOG_PREFIX = re.compile(r"^\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d\.\d+Z \d+ ", re.M)


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway server: initialise, start; stopped and deleted when the with block ends.

    options: extra mysqld options for every start (for example "--sql-mode=" or "--character-set-server=latin1").
    basedir: the folder of an unpacked MySQL tarball to run instead of the pinned 8.4.11.
    masks: (pattern, replacement) pairs, as for mask().
    """

    def __init__(self, options=(), redo=None, basedir=None, masks=()):
        self.options = [str(o) for o in options]
        self.redo = redo
        self.basedir = Path(basedir).resolve() if basedir else PINNED
        self.bin = self.basedir / "bin"
        self.masks = []
        for pattern, replacement in masks:
            self.mask(pattern, replacement)
        self.proc = None

    def mask(self, pattern, replacement):
        """From now on every printed line shows text matching the regular expression as the replacement: for a value
        that changes from run to run (an uptime, a time stamp), so that the recorded output stays the same."""
        self.masks.append((re.compile(pattern), replacement))

    # ---- the folder ------------------------------------------------------------------------------------------------
    def __enter__(self):
        if not (self.bin / "mysqld").exists():
            sys.exit(f"no MySQL server program in {self.basedir}" if self.basedir != PINNED
                     else f"MySQL {MYSQL_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="my", dir=".")).resolve()
        for sub in ("tmp", "files"):
            (self.base / sub).mkdir(mode=0o700)
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        self.client_cnf = self.base / "client.cnf"
        self._write_client_cnf(password=None)
        self.env = {key: value for key, value in os.environ.items()
                    if not key.startswith("MYSQL") and key not in ("TZ", "HOME", "LANG", "LC_ALL")}
        self.env.update({"HOME": str(self.base), "TZ": "UTC", "LANG": "en_US.UTF-8",
                         "MYSQL_TEST_LOGIN_FILE": str(self.base / "no-login-paths.cnf")})
        for pattern in ("*.sql", "*.cnf", "*.csv", "*.txt"):
            for f in Path(".").glob(pattern):
                shutil.copy(f, self.base / f.name)
        return self

    def __exit__(self, *exc):
        if self.proc and self.proc.poll() is None:
            self.stop(quiet=True)
        shutil.rmtree(self.base, ignore_errors=True)

    def _write_client_cnf(self, password):
        lines = ["[client]", "user=root", "host=127.0.0.1", f"port={self.port}", "protocol=TCP"]
        if password:
            lines.append(f"password={password}")
        self.client_cnf.write_text("\n".join(lines) + "\n")
        self.client_cnf.chmod(0o600)

    def server_options(self):
        b = self.base
        return ["--no-defaults", f"--basedir={self.basedir}", f"--datadir={b / 'data'}", f"--tmpdir={b / 'tmp'}",
                f"--log-error={b / 'mysqld.err'}", f"--pid-file={b / 'mysqld.pid'}",
                f"--secure-file-priv={b / 'files'}",
                *([f"--innodb-redo-log-capacity={self.redo}"] if self.redo else [])]

    # ---- the server ------------------------------------------------------------------------------------------------
    def initialize(self, show=False):
        """mysqld --initialize-insecure: a new data directory whose root account has no password yet."""
        args = ["mysqld", *self.server_options(), "--initialize-insecure"]
        code, out = self._call(args)
        if show:
            _show(["mysqld", "--no-defaults", "--initialize-insecure", "--datadir=./data"], out)
        if code != 0:
            print(self.log())
            sys.exit(f"mysqld --initialize-insecure ended with status {code}")

    def start(self, extra=(), defaults_file=None, expect_fail=False, show=False):
        """Starts mysqld on 127.0.0.1 and waits until it answers. With expect_fail, prints the error-log lines of a
        start that must fail, and returns them."""
        log = self.base / "mysqld.err"
        before = log.read_text() if log.exists() else ""
        opts = self.server_options()
        if defaults_file:
            opts[0] = f"--defaults-file={self.base / defaults_file}"
        args = [str(self.bin / "mysqld"), *opts, "--bind-address=127.0.0.1", f"--port={self.port}",
                "--socket=mysql.sock", "--mysqlx=OFF", *self.options, *[str(e) for e in extra]]
        if show:
            shown = ["mysqld", opts[0].replace(str(self.base) + "/", ""), "--datadir=./data", "--bind-address=127.0.0.1",
                     "--port=$PORT", "--socket=mysql.sock", "--mysqlx=OFF", *self.options, *extra]
            print("$ " + " ".join(_arg(a) if a != "--port=$PORT" else a for a in shown) + " &")
            print()
        with open(self.base / "mysqld.out", "a") as out:
            self.proc = subprocess.Popen(args, stdout=out, stderr=subprocess.STDOUT, cwd=self.base, env=self.env)
        for _ in range(600):
            if self.proc.poll() is not None:
                break
            if self._ping():
                break
            time.sleep(0.1)
        if self.proc.poll() is not None:
            new = (log.read_text() if log.exists() else "")[len(before):]
            lines = [l for l in self.clean(LOG_PREFIX.sub("", new)).splitlines() if "[ERROR]" in l]
            if expect_fail:
                print("\n".join(lines))
                print(f"(mysqld ended with status {self.proc.returncode})")
                print()
                self.proc = None
                return lines
            print("\n".join(lines))
            sys.exit(f"mysqld ended with status {self.proc.returncode} while starting")
        if expect_fail:
            sys.exit("mysqld started, but the session expected it to fail")
        if not self._ping():
            sys.exit("mysqld did not answer within 60 seconds")
        return []

    def _ping(self):
        code, _ = self._call(["mysqladmin", f"--defaults-file={self.client_cnf}", "--connect-timeout=2", "ping"])
        return code == 0

    def up(self, extra=(), defaults_file=None, show=False):
        """Initialise, start (with start()'s options), and give root the run's password, quietly."""
        self.initialize()
        self.start(extra=extra, defaults_file=defaults_file, show=show)
        return self.login()

    def login(self):
        """Gives root the run's password (never printed) on a server started from a new data directory, and makes
        every later client of the session use it."""
        self.silent(f"ALTER USER 'root'@'localhost' IDENTIFIED BY '{self.password}'")
        self._write_client_cnf(self.password)
        return self

    def stop(self, quiet=False):
        """A clean shutdown (mysqladmin shutdown), then waits until the process has ended."""
        code, out = self._call(["mysqladmin", f"--defaults-file={self.client_cnf}", "shutdown"])
        if code != 0 and not quiet:
            sys.exit(f"mysqladmin shutdown failed: {out}")
        try:
            self.proc.wait(timeout=120)
        except subprocess.TimeoutExpired:
            self.proc.terminate()
            self.proc.wait(timeout=60)
        self.proc = None

    def restart(self, extra=(), defaults_file=None, show=False):
        self.stop()
        self.start(extra=extra, defaults_file=defaults_file, show=show)

    # ---- running things --------------------------------------------------------------------------------------------
    def _call(self, args, stdin=None, merge=True):
        """Runs one of MySQL's programs in the run folder: (status, cleaned output). merge=False keeps only what it
        wrote to standard output (its warnings on standard error are dropped)."""
        args = [str(a) for a in args]
        done = subprocess.run([str(self.bin / args[0]), *args[1:]], stdout=subprocess.PIPE,
                              stderr=subprocess.STDOUT if merge else subprocess.DEVNULL,
                              text=True, cwd=self.base, env=self.env, input=stdin)
        return done.returncode, self.clean(done.stdout)

    def output(self, *args, connect=False, status=0):
        """Runs one of MySQL's programs without printing anything and returns its standard output, for example the
        SQL that mysql_tzinfo_to_sql writes, which the session saves with write_file(..., show=False) and runs next."""
        args = [str(a) for a in args]
        run = [args[0], f"--defaults-file={self.client_cnf}", *args[1:]] if connect else args
        code, out = self._call(run, merge=False)
        if code != status:
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The substitutions of the docstring. A table of the client whose cells change is drawn again with the
        new column widths, so its borders still line up."""
        lines, out, i = text.split("\n"), [], 0
        while i < len(lines):
            if lines[i].startswith("+-"):
                j = i
                while j < len(lines) and lines[j][:1] in ("+", "|"):
                    j += 1
                block = lines[i:j]
                done = [self._clean(l) for l in block]
                out.extend(done if done == block else _redraw(block, self._clean))
                i = j
            else:
                out.append(self._clean(lines[i]))
                i += 1
        return "\n".join(out)

    def _clean(self, text):
        text = text.replace(str(self.bin) + "/", "").replace(str(self.basedir), "<basedir>")
        text = text.replace(str(self.base) + "/", "./").replace(str(self.base), ".")
        text = re.sub(r" \(\d+\.\d\d sec\)", "", text)
        text = re.sub(r'"Timestamp": \d+', '"Timestamp": <when it was set>', text)
        text = re.sub(rf"\b{re.escape(HOST)}\b", "lab-host", text)
        text = re.sub(rf"\b{re.escape(OS_USER)}\b", "learner", text)
        text = re.sub(rf"(?<![\d.]){self.port}(?![\d.])", "<port>", text)
        for pattern, replacement in self.masks:
            text = pattern.sub(replacement, text)
        return text

    def log(self, pattern=None):
        """The error log without timestamps and thread numbers, optionally only the lines matching a pattern."""
        text = self.clean(LOG_PREFIX.sub("", (self.base / "mysqld.err").read_text()))
        if pattern:
            text = "\n".join(l for l in text.splitlines() if re.search(pattern, l))
        return text

    def show_log(self, pattern, note=None):
        """Prints the error-log lines that match a pattern, as grep would."""
        print(f"$ grep -E {_arg(pattern)} mysqld.err" + (f"   # {note}" if note else ""))
        print(self.log(pattern))
        print()

    def query(self, sql, db=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        args = ["mysql", f"--defaults-file={self.client_cnf}", "-N", "-B", *([db] if db else [])]
        code, out = self._call(args, stdin=sql)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only for the run's password)."""
        self.query(sql)

    def mysql(self, script, errors=0, status=None, db=None, flags=("-t", "-vvv", "--skip-comments"), note=None, force=None):
        """Runs a client script from standard input and prints the command and its output. `errors` is how many
        ERROR lines the script must print; with errors > 0 the client runs on past an error (--force) and then ends
        with status 0, without --force it stops at the first error with status 1. The default flags print each
        statement (-vvv) as a table (-t) without the script's comments (--skip-comments)."""
        force = errors > 0 if force is None else force
        shown = ["mysql", *flags, *(["--force"] if force else []), *([db] if db else [])]
        args = ["mysql", f"--defaults-file={self.client_cnf}", *shown[1:]]
        code, out = self._call(args, stdin=(self.base / script).read_text())
        if note:
            print(f"-- {note}")
        print("$ " + " ".join(_arg(a) for a in shown) + f" < {script}")
        if out.strip():
            print(out.rstrip("\n"))
        print()
        found = sum(1 for line in out.splitlines() if line.startswith("ERROR "))
        expected_status = (1 if errors and not force else 0) if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")
        return out

    def shell(self, *args, status=0, shown=None, connect=False):
        """Runs one of MySQL's programs and prints the command and its output. connect=True gives a client program
        (mysqladmin, mysqlshow, mysql) the run's client option file, which the printed command leaves out."""
        args = [str(a) for a in args]
        run = [args[0], f"--defaults-file={self.client_cnf}", *args[1:]] if connect else args
        code, out = self._call(run)
        _show(shown or args, out)
        if code != status:
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def sql(self, statement, flags=("-t",), errors=0, db=None, note=None):
        """Runs one statement with mysql -e and prints the command and its output."""
        shown = ["mysql", *flags, *([db] if db else []), "-e", statement]
        code, out = self._call(["mysql", f"--defaults-file={self.client_cnf}", *shown[1:]])
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if line.startswith("ERROR "))
        if found != errors or (code != 0) != (errors > 0):
            sys.exit(f"{statement}: {found} errors and status {code}; the session expects {errors}")
        return out

    def write_file(self, path, text, show=True):
        """Writes a text file into the run folder (an option file, a script) and prints it, as cat would."""
        (self.base / path).write_text(textwrap.dedent(text).lstrip("\n"))
        if show:
            self.show_file(path)

    def show_file(self, path):
        print(f"$ cat {path}")
        print(self.clean((self.base / path).read_text()).rstrip("\n"))
        print()

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the run (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"$ ls -p {folder}" + (f" | grep '^{prefix}'" if prefix else ""))
        print(textwrap.fill("  ".join(names), width=78, break_on_hyphens=False))
        print()
        return names


def _redraw(block, clean):
    """Draws a client table again after its cells were cleaned. The columns come from the first border line; the row
    after it is the header (left-aligned); a column whose data cells were padded on the left (numbers) stays
    right-aligned (an empty cell says nothing about its column)."""
    cuts = [k for k, ch in enumerate(block[0]) if ch == "+"]
    rows = [None if line.startswith("+") else [line[x + 1:y] for x, y in zip(cuts, cuts[1:])] for line in block]
    header = next(k for k, r in enumerate(rows) if r is not None)
    ncol = len(cuts) - 1
    right = [any(r is not None and k != header and r[c].strip() and r[c].startswith("  ") for k, r in enumerate(rows))
             for c in range(ncol)]
    cells = [None if r is None else [clean(x.strip()) for x in r] for r in rows]
    widths = [max(len(r[c]) for r in cells if r is not None) for c in range(ncol)]
    drawn = []
    for k, r in enumerate(cells):
        if r is None:
            drawn.append("+" + "+".join("-" * (w + 2) for w in widths) + "+")
        else:
            drawn.append("|" + "|".join(" " + (t.rjust(w) if right[c] and k != header else t.ljust(w)) + " "
                                        for c, (t, w) in enumerate(zip(r, widths))) + "|")
    return drawn


def _arg(a):
    """A command-line argument as a reader would type it: quotes only where the shell needs them."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()

json_table_session.py

"""JSON_TABLE over the items of 20 orders, then a JSON_SCHEMA_VALID check constraint that refuses two bad documents.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads orders.sql quietly, then runs json_table.sql. Two of its
INSERT statements break the constraint, so the session expects exactly two errors.
"""

from mysql_session import Server

with Server() as my:
    my.up()
    my.mysql("orders.sql", flags=("--skip-comments",))
    my.mysql("json_table.sql", errors=2, flags=("-t", "-vv", "--skip-comments"))

$ mysql --skip-comments < orders.sql

$ mysql -t -vv --skip-comments --force < json_table.sql
--------------
SELECT o.id, jt.line, jt.sku, jt.qty
  FROM orders o,
       JSON_TABLE(o.delivery, '$.items[*]' COLUMNS (line FOR ORDINALITY, sku VARCHAR(32) PATH '$.sku', qty INT PATH '$.qty')) AS jt
 WHERE o.id <= 3
--------------

+----+------+------+------+
| id | line | sku  | qty  |
+----+------+------+------+
|  1 |    1 | A100 |    2 |
|  1 |    2 | B200 |    1 |
|  2 |    1 | C300 |    4 |
|  3 |    1 | A100 |    1 |
|  3 |    2 | D400 |    3 |
|  3 |    3 | E500 |    1 |
+----+------+------+------+
6 rows in set

--------------
SELECT jt.sku, SUM(jt.qty) AS units, COUNT(*) AS order_lines
  FROM orders o,
       JSON_TABLE(o.delivery, '$.items[*]' COLUMNS (sku VARCHAR(32) PATH '$.sku', qty INT PATH '$.qty')) AS jt
 GROUP BY jt.sku ORDER BY units DESC
--------------

+------+-------+-------------+
| sku  | units | order_lines |
+------+-------+-------------+
| A100 |    15 |           7 |
| B200 |    12 |           6 |
| F600 |    12 |           5 |
| C300 |    11 |           5 |
| E500 |    11 |           5 |
| D400 |     8 |           4 |
+------+-------+-------------+
6 rows in set

--------------
ALTER TABLE orders ADD CONSTRAINT delivery_shape CHECK (JSON_SCHEMA_VALID(
  '{"type": "object", "required": ["pin_code", "items"],
    "properties": {"pin_code": {"type": "string", "pattern": "^[0-9]{6}$"},
                   "items": {"type": "array", "minItems": 1}}}', delivery))
--------------

Query OK, 20 rows affected
Records: 20  Duplicates: 0  Warnings: 0

--------------
INSERT INTO orders VALUES (21, '{"pin_code": "411001", "city": "Pune", "items": [{"sku": "A100", "qty": 1}]}')
--------------

Query OK, 1 row affected

--------------
INSERT INTO orders VALUES (22, '{"pin_code": 411001, "city": "Pune", "items": [{"sku": "A100", "qty": 1}]}')
--------------

ERROR 3819 (HY000) at line 18: Check constraint 'delivery_shape' is violated.
--------------
INSERT INTO orders VALUES (23, '{"pin_code": "411001", "city": "Pune", "items": []}')
--------------

ERROR 3819 (HY000) at line 19: Check constraint 'delivery_shape' is violated.
--------------
SELECT COUNT(*) AS orders_now FROM orders
--------------

+------------+
| orders_now |
+------------+
|         21 |
+------------+
1 row in set

Bye

json_table.sql

USE shop;
-- JSON_TABLE turns the $.items array of each order into rows, one per element.
SELECT o.id, jt.line, jt.sku, jt.qty
  FROM orders o,
       JSON_TABLE(o.delivery, '$.items[*]' COLUMNS (line FOR ORDINALITY, sku VARCHAR(32) PATH '$.sku', qty INT PATH '$.qty')) AS jt
 WHERE o.id <= 3;
-- Once the array is rows, ordinary SQL works on it.
SELECT jt.sku, SUM(jt.qty) AS units, COUNT(*) AS order_lines
  FROM orders o,
       JSON_TABLE(o.delivery, '$.items[*]' COLUMNS (sku VARCHAR(32) PATH '$.sku', qty INT PATH '$.qty')) AS jt
 GROUP BY jt.sku ORDER BY units DESC;
-- A schema in a CHECK constraint keeps malformed documents out. All 20 stored orders already satisfy it.
ALTER TABLE orders ADD CONSTRAINT delivery_shape CHECK (JSON_SCHEMA_VALID(
  '{"type": "object", "required": ["pin_code", "items"],
    "properties": {"pin_code": {"type": "string", "pattern": "^[0-9]{6}$"},
                   "items": {"type": "array", "minItems": 1}}}', delivery));
INSERT INTO orders VALUES (21, '{"pin_code": "411001", "city": "Pune", "items": [{"sku": "A100", "qty": 1}]}');
INSERT INTO orders VALUES (22, '{"pin_code": 411001, "city": "Pune", "items": [{"sku": "A100", "qty": 1}]}');
INSERT INTO orders VALUES (23, '{"pin_code": "411001", "city": "Pune", "items": []}');
SELECT COUNT(*) AS orders_now FROM orders;

orders.sql

-- The 20 orders every JSON session starts from: one JSON document of delivery details per order.
CREATE DATABASE shop;
USE shop;
CREATE TABLE orders (id BIGINT PRIMARY KEY, delivery JSON);
INSERT INTO orders VALUES
  (1, '{"pin_code": "560001", "city": "Bengaluru", "express": true, "items": [{"sku": "A100", "qty": 2}, {"sku": "B200", "qty": 1}]}'),
  (2, '{"pin_code": "560002", "city": "Bengaluru", "express": false, "items": [{"sku": "C300", "qty": 4}]}'),
  (3, '{"pin_code": "400001", "city": "Mumbai", "express": true, "landmark": "Gateway of India", "items": [{"sku": "A100", "qty": 1}, {"sku": "D400", "qty": 3}, {"sku": "E500", "qty": 1}]}'),
  (4, '{"pin_code": "110001", "city": "New Delhi", "express": false, "items": [{"sku": "B200", "qty": 5}]}'),
  (5, '{"pin_code": "600001", "city": "Chennai", "express": true, "items": [{"sku": "F600", "qty": 2}, {"sku": "A100", "qty": 2}]}'),
  (6, '{"pin_code": "700001", "city": "Kolkata", "express": false, "landmark": "Esplanade", "items": [{"sku": "C300", "qty": 1}]}'),
  (7, '{"pin_code": "560001", "city": "Bengaluru", "express": false, "items": [{"sku": "D400", "qty": 2}, {"sku": "E500", "qty": 2}]}'),
  (8, '{"pin_code": "411001", "city": "Pune", "express": true, "items": [{"sku": "A100", "qty": 3}]}'),
  (9, '{"pin_code": "500001", "city": "Hyderabad", "express": false, "items": [{"sku": "B200", "qty": 2}, {"sku": "F600", "qty": 1}]}'),
  (10, '{"pin_code": "380001", "city": "Ahmedabad", "express": false, "items": [{"sku": "E500", "qty": 4}]}'),
  (11, '{"pin_code": "560001", "city": "Bengaluru", "express": true, "items": [{"sku": "C300", "qty": 2}, {"sku": "A100", "qty": 1}, {"sku": "B200", "qty": 1}]}'),
  (12, '{"pin_code": "400001", "city": "Mumbai", "express": false, "items": [{"sku": "F600", "qty": 6}]}'),
  (13, '{"pin_code": "110001", "city": "New Delhi", "express": true, "landmark": "India Gate", "items": [{"sku": "D400", "qty": 1}, {"sku": "A100", "qty": 1}]}'),
  (14, '{"pin_code": "600001", "city": "Chennai", "express": false, "items": [{"sku": "E500", "qty": 3}]}'),
  (15, '{"pin_code": "302001", "city": "Jaipur", "express": false, "items": [{"sku": "B200", "qty": 1}, {"sku": "C300", "qty": 1}]}'),
  (16, '{"pin_code": "560002", "city": "Bengaluru", "express": true, "items": [{"sku": "A100", "qty": 5}]}'),
  (17, '{"pin_code": "700001", "city": "Kolkata", "express": true, "items": [{"sku": "D400", "qty": 2}, {"sku": "F600", "qty": 2}]}'),
  (18, '{"pin_code": "411001", "city": "Pune", "express": false, "items": [{"sku": "C300", "qty": 3}]}'),
  (19, '{"pin_code": "500001", "city": "Hyderabad", "express": true, "items": [{"sku": "E500", "qty": 1}, {"sku": "B200", "qty": 2}]}'),
  (20, '{"pin_code": "560001", "city": "Bengaluru", "express": false, "items": [{"sku": "F600", "qty": 1}]}');

generated_session.py

"""VIRTUAL and STORED generated columns over a JSON document: an index on a virtual column, and the cost of a stored one.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads orders_2000.sql (2,000 orders made by arithmetic, no RAND()), then
runs generated.sql. One ALTER TABLE asks for the INPLACE algorithm on a STORED column and is refused, so the session
expects one error.
"""

from mysql_session import Server

with Server() as my:
    my.up()
    my.mysql("orders_2000.sql", flags=("--skip-comments",))
    my.mysql("generated.sql", errors=1, flags=("-t", "-vv", "--skip-comments"))

$ mysql --skip-comments < orders_2000.sql
Table	Op	Msg_type	Msg_text
shop.orders	analyze	status	OK

$ mysql -t -vv --skip-comments --force < generated.sql
--------------
EXPLAIN SELECT id FROM orders WHERE delivery->>'$.pin_code' = '560001'
--------------

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2000 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning

--------------
ALTER TABLE orders ADD COLUMN pin_code VARCHAR(10) AS (delivery->>'$.pin_code') VIRTUAL, ALGORITHM=INSTANT
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
ALTER TABLE orders ADD INDEX idx_pin (pin_code)
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
EXPLAIN SELECT id FROM orders WHERE pin_code = '560001'
--------------

+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_pin       | idx_pin | 43      | const |  250 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning

--------------
EXPLAIN SELECT id FROM orders WHERE delivery->>'$.pin_code' = '560001'
--------------

+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_pin       | idx_pin | 43      | const |  250 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning

--------------
ALTER TABLE orders ADD COLUMN city_name VARCHAR(40) AS (delivery->>'$.city') STORED, ALGORITHM=INPLACE
--------------

ERROR 1845 (0A000) at line 10: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
--------------
ALTER TABLE orders ADD COLUMN city_name VARCHAR(40) AS (delivery->>'$.city') STORED
--------------

Query OK, 2000 rows affected
Records: 2000  Duplicates: 0  Warnings: 0

--------------
SELECT COLUMN_NAME, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders' ORDER BY ORDINAL_POSITION
--------------

+-------------+-------------------+
| COLUMN_NAME | EXTRA             |
+-------------+-------------------+
| id          |                   |
| delivery    |                   |
| pin_code    | VIRTUAL GENERATED |
| city_name   | STORED GENERATED  |
+-------------+-------------------+
4 rows in set

Bye

generated.sql

USE shop;
-- Without an index on the pin code, every lookup reads all 2,000 documents.
EXPLAIN SELECT id FROM orders WHERE delivery->>'$.pin_code' = '560001';
-- A VIRTUAL generated column holds no data in the row: adding it only changes the table definition.
ALTER TABLE orders ADD COLUMN pin_code VARCHAR(10) AS (delivery->>'$.pin_code') VIRTUAL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_pin (pin_code);
EXPLAIN SELECT id FROM orders WHERE pin_code = '560001';
EXPLAIN SELECT id FROM orders WHERE delivery->>'$.pin_code' = '560001';
-- A STORED column writes its value into every row, so the server has to rebuild the table.
ALTER TABLE orders ADD COLUMN city_name VARCHAR(40) AS (delivery->>'$.city') STORED, ALGORITHM=INPLACE;
ALTER TABLE orders ADD COLUMN city_name VARCHAR(40) AS (delivery->>'$.city') STORED;
SELECT COLUMN_NAME, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders' ORDER BY ORDINAL_POSITION;

orders_2000.sql

-- 2,000 orders made by arithmetic (no RAND()): the same documents on every run. Eight pin codes, 250 orders each.
CREATE DATABASE shop;
USE shop;
CREATE TABLE orders (id BIGINT PRIMARY KEY, delivery JSON);
SET SESSION cte_max_recursion_depth = 2000;
INSERT INTO orders
WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 2000)
SELECT i, JSON_OBJECT(
         'pin_code', ELT(1 + (i * 7) % 8, '560001', '560002', '400001', '110001', '600001', '700001', '411001', '500001'),
         'city', ELT(1 + (i * 7) % 8, 'Bengaluru', 'Bengaluru', 'Mumbai', 'New Delhi', 'Chennai', 'Kolkata', 'Pune', 'Hyderabad'),
         'items', JSON_ARRAY(JSON_OBJECT('sku', CONCAT('A', 100 + i % 5), 'qty', 1 + i % 4)))
  FROM n;
-- The optimizer plans from index statistics; refresh them so the plans below do not depend on timing.
ANALYZE TABLE orders;

functional_session.py

"""A functional index on a JSON field: when the optimizer uses it, and the collation that decides.

A throwaway MySQL 8.4.11 server (mysql_session.py) loads orders_2000.sql, then runs functional.sql.
"""

from mysql_session import Server

with Server() as my:
    my.up()
    my.mysql("orders_2000.sql", flags=("--skip-comments",))
    my.mysql("functional.sql", flags=("-t", "-vv", "--skip-comments"))

$ mysql --skip-comments < orders_2000.sql
Table	Op	Msg_type	Msg_text
shop.orders	analyze	status	OK

$ mysql -t -vv --skip-comments < functional.sql
--------------
CREATE INDEX idx_pin_fn ON orders ((CAST(delivery->>'$.pin_code' AS CHAR(10))))
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
EXPLAIN SELECT id FROM orders WHERE delivery->>'$.pin_code' = '560001'
--------------

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2000 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning

--------------
EXPLAIN SELECT id FROM orders WHERE CAST(delivery->>'$.pin_code' AS CHAR(10)) = '560001'
--------------

+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_pin_fn    | idx_pin_fn | 43      | const |  250 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning

--------------
DROP INDEX idx_pin_fn ON orders
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
CREATE INDEX idx_pin_fn ON orders ((CAST(delivery->>'$.pin_code' AS CHAR(10)) COLLATE utf8mb4_bin))
--------------

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

--------------
EXPLAIN SELECT id FROM orders WHERE delivery->>'$.pin_code' = '560001'
--------------

+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_pin_fn    | idx_pin_fn | 43      | const |  250 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning

Bye

functional.sql

USE shop;
-- A functional index stores the value of an expression. ->> returns LONGTEXT, which cannot be indexed whole, so cast it.
CREATE INDEX idx_pin_fn ON orders ((CAST(delivery->>'$.pin_code' AS CHAR(10))));
-- The index is on a CAST string with the connection's collation, but ->> yields a utf8mb4_bin string: no match.
EXPLAIN SELECT id FROM orders WHERE delivery->>'$.pin_code' = '560001';
-- Writing the indexed expression in the query matches it exactly.
EXPLAIN SELECT id FROM orders WHERE CAST(delivery->>'$.pin_code' AS CHAR(10)) = '560001';
-- Or give the index the collation that ->> produces, and the plain query matches.
DROP INDEX idx_pin_fn ON orders;
CREATE INDEX idx_pin_fn ON orders ((CAST(delivery->>'$.pin_code' AS CHAR(10)) COLLATE utf8mb4_bin));
EXPLAIN SELECT id FROM orders WHERE delivery->>'$.pin_code' = '560001';

orders_2000.sql

-- 2,000 orders made by arithmetic (no RAND()): the same documents on every run. Eight pin codes, 250 orders each.
CREATE DATABASE shop;
USE shop;
CREATE TABLE orders (id BIGINT PRIMARY KEY, delivery JSON);
SET SESSION cte_max_recursion_depth = 2000;
INSERT INTO orders
WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 2000)
SELECT i, JSON_OBJECT(
         'pin_code', ELT(1 + (i * 7) % 8, '560001', '560002', '400001', '110001', '600001', '700001', '411001', '500001'),
         'city', ELT(1 + (i * 7) % 8, 'Bengaluru', 'Bengaluru', 'Mumbai', 'New Delhi', 'Chennai', 'Kolkata', 'Pune', 'Hyderabad'),
         'items', JSON_ARRAY(JSON_OBJECT('sku', CONCAT('A', 100 + i % 5), 'qty', 1 + i % 4)))
  FROM n;
-- The optimizer plans from index statistics; refresh them so the plans below do not depend on timing.
ANALYZE TABLE orders;

mvi_session.py

"""A multi-valued index on a JSON array of tags: 1,000 products, three ways to ask, and the plan of each.

The session makes the data itself. random.Random(42) gives every product 2 to 4 tags from a pool of forty, and the
INSERT statements go to items_load.sql, which a throwaway MySQL 8.4.11 server (mysql_session.py) runs quietly. Then
mvi.sql creates the index and shows the plans. The tags never change from run to run.
"""

import json
import random

from mysql_session import Server

POOL = ["sale", "promo", "new", "eco", "gift", "bulk", "import", "fragile", "vegan", "handmade",
        "cotton", "steel", "wood", "glass", "organic", "festive", "summer", "winter", "kids", "office",
        "travel", "kitchen", "garden", "sports", "pet", "premium", "budget", "refurbished", "combo", "limited",
        "seasonal", "local", "halal", "recyclable", "monsoon", "diwali", "wedding", "school", "outdoor", "compact"]

rng = random.Random(42)
rows = [(i, json.dumps(rng.sample(POOL, rng.randint(2, 4)))) for i in range(1, 1001)]
load = ["CREATE DATABASE shop;", "USE shop;", "CREATE TABLE items (id BIGINT PRIMARY KEY, tags JSON);"]
load += [f"INSERT INTO items VALUES {', '.join(f'({i}, {chr(39)}{tags}{chr(39)})' for i, tags in rows[k:k + 100])};"
         for k in range(0, 1000, 100)]
load.append("ANALYZE TABLE items;")

with Server() as my:
    my.up()
    my.write_file("items_load.sql", "\n".join(load) + "\n", show=False)
    my.mysql("items_load.sql", flags=("--skip-comments",))
    my.mysql("mvi.sql", flags=("-t", "-v", "--skip-comments"))

$ mysql --skip-comments < items_load.sql
Table	Op	Msg_type	Msg_text
shop.items	analyze	status	OK

$ mysql -t -v --skip-comments < mvi.sql
--------------
EXPLAIN SELECT * FROM items WHERE 'promo' MEMBER OF (tags)
--------------

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | items | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
--------------
CREATE INDEX idx_tags ON items ((CAST(tags AS CHAR(16) ARRAY)))
--------------

--------------
EXPLAIN SELECT * FROM items WHERE 'promo' MEMBER OF (tags)
--------------

+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | items | NULL       | ref  | idx_tags      | idx_tags | 67      | const |   63 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
--------------
EXPLAIN SELECT * FROM items WHERE JSON_CONTAINS(tags, '["promo", "sale"]')
--------------

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | items | NULL       | range | idx_tags      | idx_tags | 67      | NULL |  125 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
--------------
EXPLAIN SELECT * FROM items WHERE JSON_OVERLAPS(tags, '["promo", "sale"]')
--------------

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | items | NULL       | range | idx_tags      | idx_tags | 67      | NULL |  125 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
--------------
SELECT SUM(JSON_LENGTH(tags)) AS tag_entries,
       SUM('promo' MEMBER OF (tags)) AS has_promo,
       SUM(JSON_CONTAINS(tags, '["promo", "sale"]')) AS has_both,
       SUM(JSON_OVERLAPS(tags, '["promo", "sale"]')) AS has_either
  FROM items
--------------

+-------------+-----------+----------+------------+
| tag_entries | has_promo | has_both | has_either |
+-------------+-----------+----------+------------+
|        3007 |        63 |        3 |        122 |
+-------------+-----------+----------+------------+

mvi.sql

USE shop;
-- Without an index, every product's tag array is read.
EXPLAIN SELECT * FROM items WHERE 'promo' MEMBER OF (tags);
-- One index entry for every tag of every product.
CREATE INDEX idx_tags ON items ((CAST(tags AS CHAR(16) ARRAY)));
EXPLAIN SELECT * FROM items WHERE 'promo' MEMBER OF (tags);
EXPLAIN SELECT * FROM items WHERE JSON_CONTAINS(tags, '["promo", "sale"]');
EXPLAIN SELECT * FROM items WHERE JSON_OVERLAPS(tags, '["promo", "sale"]');
-- The three functions answer different questions on the same index.
SELECT SUM(JSON_LENGTH(tags)) AS tag_entries,
       SUM('promo' MEMBER OF (tags)) AS has_promo,
       SUM(JSON_CONTAINS(tags, '["promo", "sale"]')) AS has_both,
       SUM(JSON_OVERLAPS(tags, '["promo", "sale"]')) AS has_either
  FROM items;

partial_json_session.py

"""Partial JSON updates in the binary log: the same JSON_SET logged as a few bytes and as the whole document.

A throwaway MySQL 8.4.11 server (mysql_session.py) starts with --binlog-row-value-options=PARTIAL_JSON and
--binlog-row-image=MINIMAL, the pair that saves the most. partial_json.sql updates one order with the option on and one
with it switched off for the session. Then mysqlbinlog -vv reads the binary log and the session prints the lines of the
two update events, as grep and cut would, and the size of each event. Time stamps and checksums change from run to run
and so do the positions in the log (what the server wrote before depends on the run), so they are masked; the size of
an event, the difference of its two positions, does not change.
"""

import re

from mysql_session import Server

with Server(options=("--binlog-row-value-options=PARTIAL_JSON", "--binlog-row-image=MINIMAL")) as my:
    my.mask(r"#\d{6}\s+\d{1,2}:\d\d:\d\d", "#<date> <time>")
    my.mask(r"CRC32 0x[0-9a-f]+", "CRC32 <crc>")
    my.mask(r"table id \d+", "table id <n>")
    my.up()
    my.mysql("partial_json.sql", flags=("-t", "-v", "--skip-comments"))
    log = my.output("mysqlbinlog", "-vv", "--base64-output=DECODE-ROWS", "./data/binlog.000001").splitlines()
    print("$ mysqlbinlog -vv --base64-output=DECODE-ROWS data/binlog.000001 | grep -B1 -A5 Update_rows | cut -c1-100")
    sizes = []
    for i, line in enumerate(log):
        if re.search(r"\tUpdate_rows", line):
            start = int(re.search(r"# at (\d+)", log[i - 1]).group(1))
            end = int(re.search(r"end_log_pos (\d+)", line).group(1))
            sizes.append((re.search(r"\t(Update_rows\w*)", line).group(1), end - start))
            print("# at <position>")
            print(re.sub(r"end_log_pos \d+", "end_log_pos <position>", line)[:100])
            for shown in log[i + 1:i + 6]:
                print(shown[:100])
            print("--")
    print()
    print("$ # the size of an event is its end_log_pos minus its \"# at\" position")
    for name, size in sizes:
        print(f"{name}: {size} bytes")

$ mysql -t -v --skip-comments < partial_json.sql
--------------
CREATE DATABASE shop
--------------

--------------
CREATE TABLE orders (id BIGINT PRIMARY KEY, delivery JSON)
--------------

--------------
INSERT INTO orders VALUES
  (1, JSON_OBJECT('pin_code', '560001', 'city', 'Bengaluru', 'note', REPEAT('deliver after 6 pm; ', 30))),
  (2, JSON_OBJECT('pin_code', '560001', 'city', 'Bengaluru', 'note', REPEAT('deliver after 6 pm; ', 30)))
--------------

--------------
SHOW VARIABLES WHERE Variable_name IN ('binlog_format', 'binlog_row_image', 'binlog_row_value_options')
--------------

+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| binlog_format            | ROW          |
| binlog_row_image         | MINIMAL      |
| binlog_row_value_options | PARTIAL_JSON |
+--------------------------+--------------+
--------------
UPDATE orders SET delivery = JSON_SET(delivery, '$.pin_code', '560002') WHERE id = 1
--------------

--------------
SET SESSION binlog_row_value_options = ''
--------------

--------------
UPDATE orders SET delivery = JSON_SET(delivery, '$.pin_code', '560002') WHERE id = 2
--------------

--------------
SELECT id, delivery->>'$.pin_code' AS pin_code, JSON_STORAGE_SIZE(delivery) AS bytes FROM orders
--------------

+----+----------+-------+
| id | pin_code | bytes |
+----+----------+-------+
|  1 | 560002   |   661 |
|  2 | 560002   |   661 |
+----+----------+-------+

$ mysqlbinlog -vv --base64-output=DECODE-ROWS data/binlog.000001 | grep -B1 -A5 Update_rows | cut -c1-100
# at <position>
#<date> <time> server id 1  end_log_pos <position> CRC32 <crc> 	Update_rows_partial: table id <n> fl
### UPDATE `shop`.`orders`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @2=JSON_REPLACE(@2, '$.pin_code', '560002') /* JSON meta=4 nullable=1 is_null=0 */
--
# at <position>
#<date> <time> server id 1  end_log_pos <position> CRC32 <crc> 	Update_rows: table id <n> flags: STM
### UPDATE `shop`.`orders`
### WHERE
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @2='{"city": "Bengaluru", "note": "deliver after 6 pm; deliver after 6 pm; deliver after 6 pm;
--

$ # the size of an event is its end_log_pos minus its "# at" position
Update_rows_partial: 73 bytes
Update_rows: 711 bytes

partial_json.sql

CREATE DATABASE shop;
USE shop;
CREATE TABLE orders (id BIGINT PRIMARY KEY, delivery JSON);
-- Two orders with the same 600-character note, so the document is much larger than the field that changes.
INSERT INTO orders VALUES
  (1, JSON_OBJECT('pin_code', '560001', 'city', 'Bengaluru', 'note', REPEAT('deliver after 6 pm; ', 30))),
  (2, JSON_OBJECT('pin_code', '560001', 'city', 'Bengaluru', 'note', REPEAT('deliver after 6 pm; ', 30)));
SHOW VARIABLES WHERE Variable_name IN ('binlog_format', 'binlog_row_image', 'binlog_row_value_options');
-- Order 1: the server was started with binlog_row_value_options=PARTIAL_JSON.
UPDATE orders SET delivery = JSON_SET(delivery, '$.pin_code', '560002') WHERE id = 1;
-- Order 2: the same change with the option switched off for this session.
SET SESSION binlog_row_value_options = '';
UPDATE orders SET delivery = JSON_SET(delivery, '$.pin_code', '560002') WHERE id = 2;
SELECT id, delivery->>'$.pin_code' AS pin_code, JSON_STORAGE_SIZE(delivery) AS bytes FROM orders;

fts_session.py

"""InnoDB full-text search over 300 products: natural-language and boolean mode, the minimum token size and stopwords.

random.Random(11) builds 300 product names and descriptions from fixed word lists (no text from any outside source);
the INSERT statements go to products_load.sql, which a throwaway MySQL 8.4.11 server (mysql_session.py) runs quietly
after CREATE TABLE ... FULLTEXT (name, description). Then fts.sql runs the searches.
"""

import random

from mysql_session import Server

BRANDS = ["Nilgiri", "Kaveri", "Sahyadri", "Aravalli", "Vindhya", "Deccan", "Malabar", "Konkan"]
KINDS = ["headphones", "earbuds", "speaker", "charger", "cable", "power bank", "keyboard", "mouse", "webcam",
         "monitor", "router", "tripod", "backpack", "bottle", "lamp", "TV mount", "AC adapter", "laptop stand",
         "microphone", "hub"]
FEATURES = ["wireless", "wired", "bluetooth", "USB-C", "USB-A", "noise cancelling", "waterproof", "portable",
            "foldable", "rechargeable", "ergonomic", "compact", "mechanical", "gaming", "fast charging"]
EXTRAS = ["a carry pouch", "a braided cable", "a two year warranty", "an LED indicator", "a soft grip",
          "a quick start guide", "long battery life", "a travel case", "a metal stand", "a matte finish"]
ENDINGS = ["Ships in the box with the manual.", "Works with most phones and laptops.", "Built for daily use at home.",
           "A good fit for the office desk.", "Tested for a year of use in the lab.", "The cable is one metre long."]

rng = random.Random(11)
rows = []
for i in range(1, 301):
    kind, first, second = rng.choice(KINDS), rng.choice(FEATURES), rng.choice(FEATURES)
    name = f"{rng.choice(BRANDS)} {first} {kind}"
    description = f"{second.capitalize()} {kind} with {rng.choice(EXTRAS)} and {rng.choice(EXTRAS)}. {rng.choice(ENDINGS)}"
    rows.append(f"({i}, '{name}', '{description}')")
load = ["CREATE DATABASE shop;", "USE shop;",
        "CREATE TABLE products (id BIGINT PRIMARY KEY, name VARCHAR(200), description TEXT, FULLTEXT (name, description));"]
load += [f"INSERT INTO products VALUES {', '.join(rows[k:k + 50])};" for k in range(0, 300, 50)]

with Server() as my:
    my.up()
    my.write_file("products_load.sql", "\n".join(load) + "\n", show=False)
    my.mysql("products_load.sql", flags=("--skip-comments",))
    my.mysql("fts.sql", flags=("-t", "-v", "--skip-comments"))

$ mysql --skip-comments < products_load.sql

$ mysql -t -v --skip-comments < fts.sql
--------------
SELECT id, name, ROUND(MATCH(name, description) AGAINST('wireless headphones'), 2) AS score
  FROM products
 WHERE MATCH(name, description) AGAINST('wireless headphones')
 ORDER BY score DESC LIMIT 5
--------------

+-----+---------------------------------+-------+
| id  | name                            | score |
+-----+---------------------------------+-------+
|  87 | Konkan wireless headphones      |  6.15 |
|  11 | Nilgiri USB-C headphones        |  5.33 |
|  69 | Nilgiri rechargeable headphones |  5.33 |
| 140 | Nilgiri USB-C headphones        |  5.33 |
| 148 | Nilgiri foldable headphones     |  5.33 |
+-----+---------------------------------+-------+
--------------
SELECT id, name, LEFT(description, 32) AS description_starts
  FROM products WHERE MATCH(name, description) AGAINST('+wireless -usb*' IN BOOLEAN MODE) LIMIT 4
--------------

+-----+---------------------------+----------------------------------+
| id  | name                      | description_starts               |
+-----+---------------------------+----------------------------------+
|   5 | Aravalli wireless speaker | Wireless speaker with an LED ind |
|  14 | Konkan wireless charger   | Wireless charger with a travel c |
| 271 | Deccan wireless speaker   | Wireless speaker with a quick st |
|   7 | Kaveri portable monitor   | Wireless monitor with a travel c |
+-----+---------------------------+----------------------------------+
--------------
SELECT (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('wireless' IN BOOLEAN MODE)) AS wireless,
       (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('+wireless -usb*' IN BOOLEAN MODE)) AS wireless_no_usb,
       (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('+"noise cancelling"' IN BOOLEAN MODE)) AS phrase
--------------

+----------+-----------------+--------+
| wireless | wireless_no_usb | phrase |
+----------+-----------------+--------+
|       37 |              32 |     36 |
+----------+-----------------+--------+
--------------
SHOW VARIABLES LIKE 'innodb_ft_min_token_size'
--------------

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 3     |
+--------------------------+-------+
--------------
SELECT COUNT(*) AS default_stopwords FROM information_schema.INNODB_FT_DEFAULT_STOPWORD
--------------

+-------------------+
| default_stopwords |
+-------------------+
|                36 |
+-------------------+
--------------
SELECT (SELECT COUNT(*) FROM products WHERE name LIKE '%TV%') AS like_tv,
       (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('tv' IN BOOLEAN MODE)) AS match_tv,
       (SELECT COUNT(*) FROM products WHERE description LIKE '% the %') AS like_the,
       (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('the' IN BOOLEAN MODE)) AS match_the
--------------

+---------+----------+----------+-----------+
| like_tv | match_tv | like_the | match_the |
+---------+----------+----------+-----------+
|      19 |        0 |      205 |         0 |
+---------+----------+----------+-----------+

fts.sql

USE shop;
-- Natural-language mode ranks rows by relevance; the words are matched anywhere in name or description.
SELECT id, name, ROUND(MATCH(name, description) AGAINST('wireless headphones'), 2) AS score
  FROM products
 WHERE MATCH(name, description) AGAINST('wireless headphones')
 ORDER BY score DESC LIMIT 5;
-- Boolean mode: + must be there, - must not be, a trailing * matches any word that starts so.
SELECT id, name, LEFT(description, 32) AS description_starts
  FROM products WHERE MATCH(name, description) AGAINST('+wireless -usb*' IN BOOLEAN MODE) LIMIT 4;
SELECT (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('wireless' IN BOOLEAN MODE)) AS wireless,
       (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('+wireless -usb*' IN BOOLEAN MODE)) AS wireless_no_usb,
       (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('+"noise cancelling"' IN BOOLEAN MODE)) AS phrase;
-- Words under innodb_ft_min_token_size (3) and stopwords are never indexed.
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
SELECT COUNT(*) AS default_stopwords FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;
SELECT (SELECT COUNT(*) FROM products WHERE name LIKE '%TV%') AS like_tv,
       (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('tv' IN BOOLEAN MODE)) AS match_tv,
       (SELECT COUNT(*) FROM products WHERE description LIKE '% the %') AS like_the,
       (SELECT COUNT(*) FROM products WHERE MATCH(name, description) AGAINST('the' IN BOOLEAN MODE)) AS match_the;

ngram_session.py

"""The ngram full-text parser on 20 Japanese sentences, against the default parser on the same text.

A throwaway MySQL 8.4.11 server (mysql_session.py) runs ngram_load.sql: the same 20 fixed sentences go into texts_cjk
(FULLTEXT ... WITH PARSER ngram) and texts_plain (the built-in parser). Then ngram.sql searches both for 京都 (Kyoto).
"""

from mysql_session import Server

with Server() as my:
    my.up()
    my.mysql("ngram_load.sql", flags=("--skip-comments",))
    my.mysql("ngram.sql", flags=("-t", "-v", "--skip-comments"))

$ mysql --skip-comments < ngram_load.sql
Table	Op	Msg_type	Msg_text
shop.texts_cjk	analyze	status	OK
shop.texts_plain	analyze	status	OK

$ mysql -t -v --skip-comments < ngram.sql
--------------
SHOW VARIABLES LIKE 'ngram_token_size'
--------------

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
--------------
SELECT id, body FROM texts_cjk WHERE MATCH(body) AGAINST('京都')
--------------

+----+--------------------------------------------------+
| id | body                                             |
+----+--------------------------------------------------+
|  1 | 京都は古い都です                                 |
|  2 | 東京都は日本の首都です                           |
|  4 | 京都のお寺はとても静かです                       |
| 10 | 京都駅から奈良まで電車で行きます                 |
| 16 | 東京都庁は新宿にあります                         |
| 20 | 京都の秋は紅葉が美しいです                       |
+----+--------------------------------------------------+
--------------
SELECT COUNT(*) AS default_parser_hits FROM texts_plain WHERE MATCH(body) AGAINST('京都')
--------------

+---------------------+
| default_parser_hits |
+---------------------+
|                   0 |
+---------------------+
--------------
EXPLAIN SELECT id FROM texts_cjk WHERE MATCH(body) AGAINST('京都')
--------------

+----+-------------+-----------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+
| id | select_type | table     | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-----------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | texts_cjk | NULL       | fulltext | body          | body | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-----------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+

ngram.sql

USE shop;
SHOW VARIABLES LIKE 'ngram_token_size';
SELECT id, body FROM texts_cjk WHERE MATCH(body) AGAINST('京都');
-- The default parser cuts words at spaces and punctuation, so a Japanese sentence is one long word.
SELECT COUNT(*) AS default_parser_hits FROM texts_plain WHERE MATCH(body) AGAINST('京都');
EXPLAIN SELECT id FROM texts_cjk WHERE MATCH(body) AGAINST('京都');

ngram_load.sql

-- Twenty short Japanese sentences, written for this lesson. 東京都 (the Tokyo prefecture) contains the two characters 京都.
CREATE DATABASE shop;
USE shop;
CREATE TABLE texts_cjk (id BIGINT PRIMARY KEY, body TEXT, FULLTEXT (body) WITH PARSER ngram);
CREATE TABLE texts_plain (id BIGINT PRIMARY KEY, body TEXT, FULLTEXT (body));
INSERT INTO texts_cjk VALUES
  (1, '京都は古い都です'), (2, '東京都は日本の首都です'), (3, '大阪は食べ物で有名です'),
  (4, '京都のお寺はとても静かです'), (5, '東京タワーは赤い塔です'), (6, '北海道は冬に雪が多いです'),
  (7, '福岡のラーメンはおいしいです'), (8, '奈良には大きな仏像があります'), (9, '東京駅は大きな駅です'),
  (10, '京都駅から奈良まで電車で行きます'), (11, '沖縄の海はとてもきれいです'), (12, '名古屋は日本の真ん中にあります'),
  (13, '札幌でビールを飲みました'), (14, '広島の夜景は美しいです'), (15, '神戸は港の町です'),
  (16, '東京都庁は新宿にあります'), (17, '金沢の兼六園は有名な庭です'), (18, '仙台は杜の都と呼ばれます'),
  (19, '横浜の中華街へ行きました'), (20, '京都の秋は紅葉が美しいです');
INSERT INTO texts_plain SELECT * FROM texts_cjk;
ANALYZE TABLE texts_cjk, texts_plain;