MySQL.Database
A MySQL or MariaDB database's tables and views to navigate, or the rows of a query in its own SQL.
Group: Databases and lakes. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
MySQL.Database(server as text, database as text, optional options as nullable record) as table| Parameter | Type | Description |
|---|---|---|
server | text | The host, or host:port. |
database | text | The database's name. |
options | nullable record optional | Query: a SQL statement whose rows are the result. |
Returns
table. Without Query, a navigation table (Name, Data, Schema, Item, Kind) of the tables and views; with Query, the statement's rows.
Remarks
- DuckDB reads MySQL itself: the steps after a table fold into DuckDB's SQL, which reads only the columns and rows it needs.
- Sign in as a database user, with a user name and password.
- Credentials are never part of the M: the studio asks for a sign-in the first time this computer reads the source and keeps it on this computer only, for you.
Compared with Power Query
Of Power Query's options only Query is read; the others are accepted and change nothing.
Examples
A table
MySQL.Database("mysql.norwayanalytics.no", "shop"){[Schema = "shop", Item = "orders"]}[Data]Result: not shown here: it needs a MySQL server and a sign-in.
A query
MySQL.Database("mysql.norwayanalytics.no", "shop", [Query = "SELECT shop, SUM(amount) AS sales FROM orders GROUP BY shop"])Result: not shown here: it needs a MySQL server and a sign-in.