My requirement is to store the entire results of the query
```
SELECT * FROM document
WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429)
```
to an Excel file.
Exporting results of a Mysql query to excel?
Re: Exporting results of a Mysql query to excel?
The typical way to achieve this is to export to CSV and then load the CSV into Excel.
## TL;DR:
-
For a **server-side** Excel-friendly CSV file from a `SELECT` query, run this:
`SELECT ... FROM someTable WHERE etc
INTO OUTFILE 'someTableExport.csv' CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY '\r\n';
```
-
For a **server-side** Excel-friendly CSV file use `mysqldump` like so:
```
mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables table1 table2 table3
```
-
For a **client-side** Excel-friendly CSV file using `mysqlsh` (MySQL Shell) like so:
```
mysqlsh --user="mysqlUserName" --host="serverHostName" --port=3306 --schema="databaseName"
# Once connected, run this:
util.exportTable("tableName", "file:///C:/Users/You/Desktop/test.csv", { dialect: "csv", fieldsEscapedBy: ""})
```
## First, a caution about Excel:
## Excel's vs. MySQL's default CSV formats:
Remember that Excel has its own underdocumented ideas about how CSV files should be formatted and these stand in-contrast to [MySQL's own ideas about CSV files](https://dev.mysql.com/doc/refman/8.0/en/load-data.html); though Excel is largely compliant [with RFC 4180](https://www.rfc-editor.org/rfc/rfc4180) you still need to prod and poke MySQL and its associated tooling to generate CSV files that Excel won't misinterpret:
Excel
MySQL (default)
MySQL (when configured)
SQL `NULL`
Zero-length value
Literal `\N`
Literal `NULL`
Text values that don't contain commas, quotes, or line-breaks
Not enclosed
Not enclosed
Enclosed in `"`
Text values that contain commas, quotes, or line-breaks
Enclosed in `"`
Not enclosed
Enclosed in `"`
Non-text values
Not enclosed
Not enclosed
Not enclosed
Line-breaks and tabs in text values
Literal
Escaped as `[\r]\n`
Literal
Double-quotes in text values
Doubled-up `""`
Escaped as `\"`
Doubled-up `""`
Field separator
`,`
`\t` (Tab)
`,`
Record separator
`\r\n`
`\n`
`\r\n`
Commas inside non-quoted text values
(Results in broken table data)
Not escaped
Will always be quoted if the value contains a comma
UTF-8 support
- **Excel 2007-2013:** - Requires leading UTF-8 BOM
- **Excel 2016+:** - Handles *BOM-less* UTF-8 with some prodding
Use `utf8mb4`.
Do not specify the [older *broken* `utf8` or `utf8mb3` encodings](https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql).
Use `utf8mb4`
As per the table above, MySQL *can* generate Excel-friendly CSV files, excepting that SQL `NULL`s will always be interpreted by Excel as literal text, though it's trivial to use PowerQuery or even just *Find-and-Replace* in Excel to replace them with empty cells.
## Excel and *special* CSV text markers
- [If any of your text values start with `=` then you'll need to manually fudge those as even when quoted Excel will interpret the text following `=` as a formula instead of literal text](https://stackoverflow.com/questions/34595812/is-a-csv-with-equal-sign-valid).
- Also watch out for [leading text like `sep=` too](https://superuser.com/questions/773644/what-is-the-sep-metadata-you-can-add-to-csvs).
## Excel and UTF-8 encoding:
Surprisingly, it wasn't until Excel was 31 years old (Excel 2016) when Excel added built-in support for UTF-8 encoding in files *without needing a BOM*, but it still defaults to importing and exporting CSV files using your system-default non-Unicode encoding (e.g. `Windows-1252`).
- When importing CSV into Excel, be sure to select Codepage `65001` for correct UTF-8 handling as Excel still defaults to non-Unicode-based codepages for some reason.
-
Note that *opening* a CSV file in Excel won't display the *Text Import Wizard*. (As of Excel 2021) you need to copy-and-paste CSV text into Excel and use the popup menu to use the legacy (*frozen-in-1994*) wizard, or use Data > From Text/CSV on the ribbon to use the newer (but less flexible, imo) PowerQuery-based CSV import wizard:
-
Excel 2007-2013
Excel 2016+
## Your options:
Option
`SELECT INTO OUTFILE`
`mysqldump --tab`
`mysqldump > file.csv`
`mysqlsh`
MySQL Workbench
Server-side CSV
**True**
**True**
**True**
**True**
Broken
Remote (client-side) CSV
False
False
False
**True**
Broken
MySQL Server version support
All versions
All versions
All versions
Only 5.7 and later
All versions
## Option 1: Exporting an Excel-friendly CSV using `INTO OUTFILE`:
- You can do a **server-side** CSV export by using [the `INTO OUTFILE` clause of a `SELECT` query](https://dev.mysql.com/doc/refman/8.0/en/select-into.html).
- Because this is "normal" SQL that's executed by the MySQL server this works regardless of whatever MySQL client tool you're using, so you don't need to install MySQL Workbench.
- ...but because this is a server-side export you need to have permission to write to the server's filesystem which you might not have, in which case consider using specialty export tools like `mysqldump` (see below).
- MySQL's `OUTFILE` clause [has a number of **optional** subclauses](https://dev.mysql.com/doc/refman/8.0/en/load-data.html#:%7E:text=INSERT%20Statements%E2%80%9D.-,Field%20and%20Line%20Handling,-For%20both%20the) *that must be specified* for some-level of compatibility with Excel's own CSV reader:
- `FIELDS...`
- `TERMINATED BY` (default: `'\t'`, for Excel use `','`)
- `[OPTIONALLY] ENCLOSED BY` (default: `''`, should be `'"'` **with** the `OPTIONALLY` keyword)
- `ESCAPED BY` (default: `'\\'`, for Excel use `''`)
- `LINES...`
- `TERMINATED BY` (default: `'\n'`, for Excel use `'\r\n'`)
- `STARTING BY` (default: `''`, for Excel you can omit this or use the MySQL default).
- **Do not** use `ENCLOSED BY` (without the preceding `OPTIONALLY` keyword) as that will enquote all values, regardless of type (i.e. it will enquote `int` values which will cause Excel (by default) to interpret them as text (strings) instead of numbers).
- Note that there is no option to instruct MySQL to output SQL `NULL`s as empty-fields, and so Excel will interpet them as unquoted strings of text (i.e. as `"NULL"`), so you'll want to do a Find-and-Replace in Excel after importing the file.
- If your `INTO OUTFILE` filename (e.g. `'someTableExport.csv'` above) is not an absolute path then it will be saved into your database's `datadir` directory. Run `SHOW VARIABLES LIKE 'datadir';` to get the path. Note that you might not necessarily have read/write permission for new files under that directory.
So your query (`SELECT * FROM document WHERE documentid...`) would look something like this:
`SELECT
*
FROM
document
WHERE
documentid IN ( SELECT documentid FROM TaskResult WHERE taskResult = 2429 )
INTO
OUTFILE 'someTableExport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY ''
LINES TERMINATED BY '\r\n';
```
## Option 2: Exporting an Excel-friendly CSV using `mysqldump`:
- To store dump into CSV file using the `--tab` command-line option, [as per `mysqldump`'s documentation](http://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_tab).
- **Unfortunately** `mysqldump`'s `--tab=` option **won't work** for remote MySQL servers: this is because `--tab="fileName.csv"` can only represent a path on the server.
- While you *can* use `stdout` redirection to generate a local file (i.e. `mysqldump --etc > output.csv`) you cannot use the `--fields-terminated-by` and other format options with `stdout`, making it useless for Excel-compatible output. So if you're remote and cannot `ssh`-in then you will need to use **MySQL Shell** (`mysqlsh`) instead (see below).
- Note that `mysqldump` does not support `SELECT` queries for dumping data: it **does** support simple `WHERE`-style filters with the `--where=` option but this doesn't support things like filtering using an `INNER JOIN` (though as a workaround you could `SELECT` into a new table, then run `mysqldump` on that new table. Note that you cannot use `TEMPORARY TABLE` with `mysqldump` [as Temporary Tables are connection-scoped](https://stackoverflow.com/questions/6567730/is-mysql-temporary-table-a-shared-resource)).
In the OP's case, due to limitations inherent in how the `--where=` command-line option works, they'll want to export both tables (`document` and `TaskResult`) and apply their filter logic in Excel PowerQuery or similar. Perform the export like so:
```
mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables document TaskResult
```
-
The above command-line should work *without modification* in Windows' `cmd.exe`, macOS's `zsh`, and `bash` on Linux - provided `mysqldump` is in your `PATH`.
-
The use of hex-encoded chars means sidestepping the hassle of figurig out how to pass double-quotes and line-breaks as literals in your shell and terminal (`0x22` is `"`, `0x2C` is `,`, and `0x0D0A` is `\r\n`).
-
**Avoid** using the `--password=` (aka `-p`) option on
the `mysqldump` command-line, as it will mean your password will be saved in plaintext to your terminal or console history file, [which is an obvious massive security risk](https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html).
- So if you're in an interactive command-line session need to specify a password then `mysqldump` will prompt you for the password immediately when the program runs so it won't be saved to your history file.
- If you want to run `mysqldump` in a non-interactive context (e.g. from within a web-application, daemon, or other process) then there's (normally) no history file to worry about, [but you should still consider alternative approaches](https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html) before resorting to handling passwords in an insecure way.
-
If you don't specify an absolute path but use a short (unqualified) filename like `INTO OUTFILE 'output.csv'` or `INTO OUTFILE './output.csv'` theb it will store the output file to the directory specified by `SHOW VARIABLES LIKE 'datadir';`.
## Option 3: Exporting an Excel-friendly CSV using *MySQL Workbench*:
Unfortunately, **you can't** (unless you don't have any double-quotes anywhere in your data): As of late 2022 [MySQL Workbench has an open bug regarding its own CSV export feature: output files *never* escape double-quote characters in text](https://bugs.mysql.com/bug.php?id=94735), so pretty much all CSV-handling software out there will report a malformed CSV file or import data to the wrong columns - so this makes it completely unsuitable for use with Excel.
## Option 4: Exporting an Excel-friendly CSV using *MySQL Shell* (aka `mysqlsh`):
- This is probably the simplest option, but you [might need to install the MySQL Shell](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html) as it doesn't come in-box in most MySQL installations.
- MySQL Shell supports connecting to MySQL Server 5.7 and later (but not older versions). If you're still using MySQL Server 5.6 or earlier (then you *really* should update to 5.7 or later anyway) you'll have to stick with `mysqldump` run locally on the MySQL Server itself (but you can use an `ssh` session, of course).
- The new "MySQL X" protocol (`mysqlx://user@host/schema`) is not supported by MySQL 5.7, but `mysqlsh` supports non-X connections with old-style command-line parameters.
- [Install MySQL Shell](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html) if it isn't already installed.
- If you start *MySQL Shell* without any command-line arguments (e.g. because you use the Start Menu shortcut on Windows)) then use the `\connect` command to connect.
-
For MySQL 5.7 use `\connect mysql://username@hostname`
-
For MySQL 8.0+ there are a variety of ways to connect, including the "MySQL X" protocol as well as "Classic" connections. [Consult the docs for more info](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-commands.html#:%7E:text=Connect%2C%20Reconnect%2C%20and%20Disconnect%20Commands).
-
If your username contains literal `@` characters then you need to percent-encode them (e.g. if you're using Azure MySQL then your *full* username will be like `username%[email protected]`).
-
Immediately after you submit the `\connect` command you will be prompted for your password interactively.
- If you can start `mysqlsh` with arguments, then you can run `mysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName"` directly without using the `\connect` command.
- Once connected, [run the `util.exportTable(tableName, outputUri, options)` command](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-table-export.html) with the following arguments:
- `tableName`: your table name.
- Unfortunately there doesn't seem to be a way to apply a `WHERE` filter or export the results of a `SELECT` query, (though as with `mysqldump` you could always save your query results to a new `TABLE`, then export that table, then `DROP TABLE` when you're done with it. Remember that `TEMPORARY TABLE` won't work here as tables created in one session aren't visible from any other session - and `mysqlsh` will have its own session.
- `outputUri`: To save the file locally use a `file:///` URI.
- On Windows you can use a forward-slash as a directory-name separator instead of a backslash. e.g. `file:///C:/Users/Me/Desktop/export.csv`.
- `options`: To ensure compatibility with Excel specify `{ dialect: "csv", fieldsEscapedBy: ""}`.
- The `dialect: "csv"` option sets Excel-compatible defaults for all-but-one of the `OUTFILE` parameters, so you must also specify `fieldsEscapedBy: ""`, otherwise SQL `NULL` will be be rendered as `\N` (literally) while double-quotes and line-breaks inside text values will be backslash-escaped, which Excel doesn't support.
## TL;DR:
-
For a **server-side** Excel-friendly CSV file from a `SELECT` query, run this:
`SELECT ... FROM someTable WHERE etc
INTO OUTFILE 'someTableExport.csv' CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY '\r\n';
```
-
For a **server-side** Excel-friendly CSV file use `mysqldump` like so:
```
mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables table1 table2 table3
```
-
For a **client-side** Excel-friendly CSV file using `mysqlsh` (MySQL Shell) like so:
```
mysqlsh --user="mysqlUserName" --host="serverHostName" --port=3306 --schema="databaseName"
# Once connected, run this:
util.exportTable("tableName", "file:///C:/Users/You/Desktop/test.csv", { dialect: "csv", fieldsEscapedBy: ""})
```
## First, a caution about Excel:
## Excel's vs. MySQL's default CSV formats:
Remember that Excel has its own underdocumented ideas about how CSV files should be formatted and these stand in-contrast to [MySQL's own ideas about CSV files](https://dev.mysql.com/doc/refman/8.0/en/load-data.html); though Excel is largely compliant [with RFC 4180](https://www.rfc-editor.org/rfc/rfc4180) you still need to prod and poke MySQL and its associated tooling to generate CSV files that Excel won't misinterpret:
Excel
MySQL (default)
MySQL (when configured)
SQL `NULL`
Zero-length value
Literal `\N`
Literal `NULL`
Text values that don't contain commas, quotes, or line-breaks
Not enclosed
Not enclosed
Enclosed in `"`
Text values that contain commas, quotes, or line-breaks
Enclosed in `"`
Not enclosed
Enclosed in `"`
Non-text values
Not enclosed
Not enclosed
Not enclosed
Line-breaks and tabs in text values
Literal
Escaped as `[\r]\n`
Literal
Double-quotes in text values
Doubled-up `""`
Escaped as `\"`
Doubled-up `""`
Field separator
`,`
`\t` (Tab)
`,`
Record separator
`\r\n`
`\n`
`\r\n`
Commas inside non-quoted text values
(Results in broken table data)
Not escaped
Will always be quoted if the value contains a comma
UTF-8 support
- **Excel 2007-2013:** - Requires leading UTF-8 BOM
- **Excel 2016+:** - Handles *BOM-less* UTF-8 with some prodding
Use `utf8mb4`.
Do not specify the [older *broken* `utf8` or `utf8mb3` encodings](https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql).
Use `utf8mb4`
As per the table above, MySQL *can* generate Excel-friendly CSV files, excepting that SQL `NULL`s will always be interpreted by Excel as literal text, though it's trivial to use PowerQuery or even just *Find-and-Replace* in Excel to replace them with empty cells.
## Excel and *special* CSV text markers
- [If any of your text values start with `=` then you'll need to manually fudge those as even when quoted Excel will interpret the text following `=` as a formula instead of literal text](https://stackoverflow.com/questions/34595812/is-a-csv-with-equal-sign-valid).
- Also watch out for [leading text like `sep=` too](https://superuser.com/questions/773644/what-is-the-sep-metadata-you-can-add-to-csvs).
## Excel and UTF-8 encoding:
Surprisingly, it wasn't until Excel was 31 years old (Excel 2016) when Excel added built-in support for UTF-8 encoding in files *without needing a BOM*, but it still defaults to importing and exporting CSV files using your system-default non-Unicode encoding (e.g. `Windows-1252`).
- When importing CSV into Excel, be sure to select Codepage `65001` for correct UTF-8 handling as Excel still defaults to non-Unicode-based codepages for some reason.
-
Note that *opening* a CSV file in Excel won't display the *Text Import Wizard*. (As of Excel 2021) you need to copy-and-paste CSV text into Excel and use the popup menu to use the legacy (*frozen-in-1994*) wizard, or use Data > From Text/CSV on the ribbon to use the newer (but less flexible, imo) PowerQuery-based CSV import wizard:
-
Excel 2007-2013
Excel 2016+
## Your options:
Option
`SELECT INTO OUTFILE`
`mysqldump --tab`
`mysqldump > file.csv`
`mysqlsh`
MySQL Workbench
Server-side CSV
**True**
**True**
**True**
**True**
Broken
Remote (client-side) CSV
False
False
False
**True**
Broken
MySQL Server version support
All versions
All versions
All versions
Only 5.7 and later
All versions
## Option 1: Exporting an Excel-friendly CSV using `INTO OUTFILE`:
- You can do a **server-side** CSV export by using [the `INTO OUTFILE` clause of a `SELECT` query](https://dev.mysql.com/doc/refman/8.0/en/select-into.html).
- Because this is "normal" SQL that's executed by the MySQL server this works regardless of whatever MySQL client tool you're using, so you don't need to install MySQL Workbench.
- ...but because this is a server-side export you need to have permission to write to the server's filesystem which you might not have, in which case consider using specialty export tools like `mysqldump` (see below).
- MySQL's `OUTFILE` clause [has a number of **optional** subclauses](https://dev.mysql.com/doc/refman/8.0/en/load-data.html#:%7E:text=INSERT%20Statements%E2%80%9D.-,Field%20and%20Line%20Handling,-For%20both%20the) *that must be specified* for some-level of compatibility with Excel's own CSV reader:
- `FIELDS...`
- `TERMINATED BY` (default: `'\t'`, for Excel use `','`)
- `[OPTIONALLY] ENCLOSED BY` (default: `''`, should be `'"'` **with** the `OPTIONALLY` keyword)
- `ESCAPED BY` (default: `'\\'`, for Excel use `''`)
- `LINES...`
- `TERMINATED BY` (default: `'\n'`, for Excel use `'\r\n'`)
- `STARTING BY` (default: `''`, for Excel you can omit this or use the MySQL default).
- **Do not** use `ENCLOSED BY` (without the preceding `OPTIONALLY` keyword) as that will enquote all values, regardless of type (i.e. it will enquote `int` values which will cause Excel (by default) to interpret them as text (strings) instead of numbers).
- Note that there is no option to instruct MySQL to output SQL `NULL`s as empty-fields, and so Excel will interpet them as unquoted strings of text (i.e. as `"NULL"`), so you'll want to do a Find-and-Replace in Excel after importing the file.
- If your `INTO OUTFILE
So your query (`SELECT * FROM document WHERE documentid...`) would look something like this:
`SELECT
*
FROM
document
WHERE
documentid IN ( SELECT documentid FROM TaskResult WHERE taskResult = 2429 )
INTO
OUTFILE 'someTableExport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY ''
LINES TERMINATED BY '\r\n';
```
## Option 2: Exporting an Excel-friendly CSV using `mysqldump`:
- To store dump into CSV file using the `--tab` command-line option, [as per `mysqldump`'s documentation](http://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_tab).
- **Unfortunately** `mysqldump`'s `--tab=` option **won't work** for remote MySQL servers: this is because `--tab="fileName.csv"` can only represent a path on the server.
- While you *can* use `stdout` redirection to generate a local file (i.e. `mysqldump --etc > output.csv`) you cannot use the `--fields-terminated-by` and other format options with `stdout`, making it useless for Excel-compatible output. So if you're remote and cannot `ssh`-in then you will need to use **MySQL Shell** (`mysqlsh`) instead (see below).
- Note that `mysqldump` does not support `SELECT` queries for dumping data: it **does** support simple `WHERE`-style filters with the `--where=
In the OP's case, due to limitations inherent in how the `--where=` command-line option works, they'll want to export both tables (`document` and `TaskResult`) and apply their filter logic in Excel PowerQuery or similar. Perform the export like so:
```
mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables document TaskResult
```
-
The above command-line should work *without modification* in Windows' `cmd.exe`, macOS's `zsh`, and `bash` on Linux - provided `mysqldump` is in your `PATH`.
-
The use of hex-encoded chars means sidestepping the hassle of figurig out how to pass double-quotes and line-breaks as literals in your shell and terminal (`0x22` is `"`, `0x2C` is `,`, and `0x0D0A` is `\r\n`).
-
**Avoid** using the `--password=
the `mysqldump` command-line, as it will mean your password will be saved in plaintext to your terminal or console history file, [which is an obvious massive security risk](https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html).
- So if you're in an interactive command-line session need to specify a password then `mysqldump` will prompt you for the password immediately when the program runs so it won't be saved to your history file.
- If you want to run `mysqldump` in a non-interactive context (e.g. from within a web-application, daemon, or other process) then there's (normally) no history file to worry about, [but you should still consider alternative approaches](https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html) before resorting to handling passwords in an insecure way.
-
If you don't specify an absolute path but use a short (unqualified) filename like `INTO OUTFILE 'output.csv'` or `INTO OUTFILE './output.csv'` theb it will store the output file to the directory specified by `SHOW VARIABLES LIKE 'datadir';`.
## Option 3: Exporting an Excel-friendly CSV using *MySQL Workbench*:
Unfortunately, **you can't** (unless you don't have any double-quotes anywhere in your data): As of late 2022 [MySQL Workbench has an open bug regarding its own CSV export feature: output files *never* escape double-quote characters in text](https://bugs.mysql.com/bug.php?id=94735), so pretty much all CSV-handling software out there will report a malformed CSV file or import data to the wrong columns - so this makes it completely unsuitable for use with Excel.
## Option 4: Exporting an Excel-friendly CSV using *MySQL Shell* (aka `mysqlsh`):
- This is probably the simplest option, but you [might need to install the MySQL Shell](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html) as it doesn't come in-box in most MySQL installations.
- MySQL Shell supports connecting to MySQL Server 5.7 and later (but not older versions). If you're still using MySQL Server 5.6 or earlier (then you *really* should update to 5.7 or later anyway) you'll have to stick with `mysqldump` run locally on the MySQL Server itself (but you can use an `ssh` session, of course).
- The new "MySQL X" protocol (`mysqlx://user@host/schema`) is not supported by MySQL 5.7, but `mysqlsh` supports non-X connections with old-style command-line parameters.
- [Install MySQL Shell](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html) if it isn't already installed.
- If you start *MySQL Shell* without any command-line arguments (e.g. because you use the Start Menu shortcut on Windows)) then use the `\connect` command to connect.
-
For MySQL 5.7 use `\connect mysql://username@hostname`
-
For MySQL 8.0+ there are a variety of ways to connect, including the "MySQL X" protocol as well as "Classic" connections. [Consult the docs for more info](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-commands.html#:%7E:text=Connect%2C%20Reconnect%2C%20and%20Disconnect%20Commands).
-
If your username contains literal `@` characters then you need to percent-encode them (e.g. if you're using Azure MySQL then your *full* username will be like `username%[email protected]`).
-
Immediately after you submit the `\connect` command you will be prompted for your password interactively.
- If you can start `mysqlsh` with arguments, then you can run `mysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName"` directly without using the `\connect` command.
- Once connected, [run the `util.exportTable(tableName, outputUri, options)` command](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-table-export.html) with the following arguments:
- `tableName`: your table name.
- Unfortunately there doesn't seem to be a way to apply a `WHERE` filter or export the results of a `SELECT` query, (though as with `mysqldump` you could always save your query results to a new `TABLE`, then export that table, then `DROP TABLE` when you're done with it. Remember that `TEMPORARY TABLE` won't work here as tables created in one session aren't visible from any other session - and `mysqlsh` will have its own session.
- `outputUri`: To save the file locally use a `file:///` URI.
- On Windows you can use a forward-slash as a directory-name separator instead of a backslash. e.g. `file:///C:/Users/Me/Desktop/export.csv`.
- `options`: To ensure compatibility with Excel specify `{ dialect: "csv", fieldsEscapedBy: ""}`.
- The `dialect: "csv"` option sets Excel-compatible defaults for all-but-one of the `OUTFILE` parameters, so you must also specify `fieldsEscapedBy: ""`, otherwise SQL `NULL` will be be rendered as `\N` (literally) while double-quotes and line-breaks inside text values will be backslash-escaped, which Excel doesn't support.