mysql: three things you (probably) didn't know you could do with the mysql command
Tags: mysql, sql, database
Published: 2023-06-20 15:20:30

the `mysql` command is nice and simple: you run it and get a prompt, you enter a query and get your results. no bells or whistles.

except there are _few_ bells and whistles.

in this post we're going to look at three features of the `mysql` command that you (probably) didn't know existed but (probably) want to use.

## the flyover
the three features that we'll be covering are:

* <a hame="inline editing" href="#inline-editing">inline editing with a real text editor</a>
* <a hame="setting a custom prompt" href="#setting-a-custom-prompt">setting a custom prompt</a>
* <a hame="logging output to a file" href="#logging-output-to-a-file">logging output to a file</a>


## inline editing
we've all been in that situation where we're working on a complex query so we compose it outside of mysql in a text editor and the paste it to run. it works, but it's not great.

fortunately, we can open up an editor _inside_ the mysql shell and compose our queries there. we do this with an escape code command:

```
\e
```

running this will pop up our default editor, where we can compose our sql statement. after we save, we are dumped back to the mysql prompt and can run our query by entering either `;` or `\g`.

if we open up the editor again with `\e`, we will see the query we already composed. our query persists after running.

mysql saves these queries in the `/tmp` directory. if we look there, we will see a file named something like `/tmp/sqlEJWUWT`.

### using existing query files
if we already have a query in a file, we can run it from within mysql using the `\.` escape code like so:

```
\. /path/to/file
```

that's the `\.` escape code followed by the path to our our query file.  note that when we do this, our new file becomes the file that `\e` opens.


## setting a custom prompt
when we run the `mysql` command, we get dumped into a shell with the familiar prompt:

```
mysql> 
```

as far as prompts go, it's accurate and short. those are good things, but it could definitely be better and more informative. fortunately, we can spruce it up with the command:

```
\R <our new prompt>
```

we can use this to create a new prompt out of any string literal we choose:

```sql
mysql> \R give it a whirl!> 
PROMPT set to 'give it a whirl!> '
give it a whirl!> 
```

unfortunately, string literals make for boring, not-very-useful prompts. we would probably like to have some dynamic data in there; the name of the database we're using, for instance.

we can do that by leveraging some pre-defined escape codes that mysql provides. let's look at an example:

```sql
mysql> \R (\U)[\d]>
PROMPT set to '(\U)[\d]>'
(ghorwood@localhost)[my_fancy_db]>
```

here we have used the `\R` escape code to tell mysql we're setting a new prompt. the prompt itself uses the `\U` code for 'full username' (including host), and the `\d` code for 'database'. we then add a few string literal brackets to spruce it up, and that's our new prompt!

mysql provides [a bunch of escape codes we can use in prompts](https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html). here's a truncated list of the most useful ones:

|    |    |
| -- | -- |
| `\c` | a counter that increments for each statement you issue |
| `\D` | the full current date. ie. `Mon Jun 01 12:34:56 2023` |
| `\d` | the current database being used |
| `\h` | the server hostname |
| `\n` | a newline character |
| `\R` | the current time in 24-hour format |
| `\U` | your full user name in username@hostname format |
| `\u` | your user name |


we can combine any and as many of these escape codes we want with any string literal characters we desire (including emojis) to create our prompt.

if we issue the `\R` command with no prompt, we revert back to the standard `mysql>`.

### persisting our custom prompt
of course, having to set our custom prompt every time we start `mysql` is a pain. we would much prefer that we can set our prompt as the default.

the best way to do this is to modify `my.cnf`, mysql's configuration file.

on linux-like systems, this is usually located in `/etc/mysql`. what we are looking for is the config file that has the configuration block for `[mysql]`. since many distros have `my.cnf` that includes other configuration files to keep things 'tidy', this may take a bit of looking.

once we have found our `[mysql]` configuration block, we can add our new prompt under it. it will looks something like this:

```
[mysql]
prompt="(\U)[\d]> "
```

note here that we've enclosed the prompt in double quotes. this isn't strictly necessary, but mysql will trim trailing whitespace if we do not do this, and we would like to have a space between the `>` character and our cursor.

## logging output to a file
reading queries in from files is great, but we will probably at some point want to write our results _out_ to a file as well. 

mysql has this covered with the `\T` escape command. the 'T' here is short for `tee` as in the [linux tee command](https://linuxize.com/post/linux-tee-command/). when we say 'tee' in this context, we're saying that the output of our command is going to two different destinations; the terminal and a file. it's like a tee pipe splitting a flow of water to separate places.

we can use `\T` to set our destination file like so:

```
\T /path/to/file
```

once we do that, the output of our mysql session will go to both our terminal where we can see it, and the file we specified.

we can turn off writing to our file with

```
\t
```

## conclusion
there are certainly a lot of [other, more powerful, clients out there](https://dev.mysql.com/doc/mysql-shell/8.0/en/) than the standard `mysql` command. but that doesn't mean the default client is without useful features and, once we know what it is capable of, it can be an excellent day-to-day tool.