MySQL Adapter
Generate MySQL SQL from your model.
What the adapter emits
- Identifiers in
`backticks`; bare column names in expressions are lowercased. - No
FULL JOIN. When a query blends measures from two facts, the per-fact subqueries are combined with aLEFT JOINinstead of theFULL OUTER JOINother dialects get. Groups that exist only on the right-hand fact are not returned. - MySQL has no
DATE_TRUNC, so grain truncation is built from MySQL’s own date functions; parts withYEAR(x),MONTH(x),DAYOFWEEK(x). - CTEs for the per-fact subqueries and window functions where a decorator needs them; both require MySQL 8.0 or later.
Configuration
mysql_db:
adapter: mysql
name: MySQL Database
tier: warm
host: db.example.com
port: 3306
database: analytics
username: analyst
# password: never here; zsql deploy strips it if present
Connection fields
The server never connects to MySQL: it has no database driver in it, and the only keys the deployed model keeps are adapter, tier and the name. Your application runs the statement it gets back, with its own credentials.
zsql on this machine does connect, read-only, for zsql catalog, zsql sample and zsql run. It takes username and a password; --schema on a catalog read names the database. TLS is not wired up for this driver yet. Secrets go in .zsql or ZSQL_DS_<KEY>_<FIELD>, never in datasources.yml; zsql deploy strips password from the file if present. See Connecting to a warehouse.
Notes
- Expressions are MySQL SQL.
group_concat,if()anddate_formatare fine inexpression.sql. - Blends are left joins. Put the fact whose groups must all appear first in the spec’s projections, or model a conformed dimension table so each fact’s measure can be served from a path that reaches it.