Taco Steemers

A personal blog.
☼ / ☾

Notes on the mysql client

Category: Notes
Tags:

Here are some notes on the mysql client, for Linux and macOS. See also the mysql tag page.

Viewing bitfields

Bits and bitfields don't display well in terminals. Bitfield is the long name for the bit type:

mysql> desc my_table;
+---------------------+---------------+------+-----+---------------------+----------------+
| Field               | Type          | Null | Key | Default             | Extra          |
+---------------------+---------------+------+-----+---------------------+----------------+
...
| my_bit              | bit(1)        | NO   | MUL | NULL                |                |
...
+---------------------+---------------+------+-----+---------------------+----------------+

We can easily filter on the value of a bitfield (... WHERE my_bit = 1). If we want to visually inspect and compare bits we may need to do more work. Bits may not display at all, depending on our specific terminal and it's settings. That may lead us to believe that a bitfield is empty even though it is not.

To view the bit(s) we can convert the value to text (SELECT EXPORT_SET(my_bit, '1', '0', ',', 1) AS my_bit_s FROM ...). The last number is the amount of bits we want to convert and print. It is optional with a default and maximum of 64, and can be more than the size of the bitfield. The middle three inputs are what text to use for the export. Any text can be used for display purposes. This is the mysql documentation on EXPORT_SET.

Using differences in time

There are many good date and time functions in mysql. Here is an example of using a timestamp difference:

SELECT starttime, endtime FROM events WHERE TIMESTAMPDIFF(HOUR, starttime, endtime) > 23 and starttime > "2022-01-01 00:00:00";
+---------------------+---------------------+
| starttime           | endtime             |
+---------------------+---------------------+
| 2022-01-22 00:00:00 | 2022-01-23 00:00:00 |
| 2022-01-23 00:00:00 | 2022-01-24 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.01 sec)

Control characters and the pager

Sometimes a text can contain a control character, such as ^M, a representation of carriage return. Characters like these can mess up the mysql client's output in a really subtle manner, and won't be shown if we use the default settings. The application used to present text to use is called the pager. The pager is the application that helps display "a page worth of output", and what that means depends on the specific pager that is used. We can change which program we use as mysql's pager. The pager can be changed and reset while we are inside the mysql client, but only on Unix and in interactive mode. Interactive mode is when we use the mysql client to log in to the mysql server and type commands inside the mysql client, like I did at the top of this page.

For example, we can use pager less -FX to use less as the pager, and ask it to wrap long lines. We can use pager less -SFX if we want to have long lines truncated (cut off at the end) instead of wrapped to the next line. Finally, we can reset the pager to the default by simply typing pager.