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 a LEFT JOIN instead of the FULL OUTER JOIN other 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 with YEAR(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

0sql never connects to MySQL. The fields above are carried as metadata for your own application, which runs the SQL it gets back. 0sql never uses them. If a password does land in datasources.yml, zsql deploy strips it before building the archive.

Notes

  • Expressions are MySQL SQL. group_concat, if() and date_format are fine in expression.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.

Next Steps