Some other interesting things about the mysql client: It is build from the same mysql-server repository as MySQL Server. The source is in client/mysql.cc. In addition to the server version it also reports 14.14 as its version. The previous version (14.13) was around the time of MySQL 5.1, so this version is mostly meaningless.
If you start it it identifies itself as "MySQL monitor", not to be confused with MySQL Enterprise Monitor.
The version of the client is not tightly coupled with the server, in most situations a 5.6 client works fine with a 5.7 server and vice versa. Note that there might be some minor annoyances if you use an older client with a newer server. For example: the 5.6 client doesn't know about the new hint syntax, and considers the hint to be just a comment. And comments are stripped by default, which results in the situation that the hint is not sent to the server.
But there are some situations where the MySQL client has some limitations.
The first one is that the 'pager' option doesn't work on Windows. The pager command is very useful (e.g. less, grep, etc). And cmd.exe isn't the best terminal emulator ever.. using a third party terminal emulator or PowerShell fixes that somewhat. And with PowerShell there are some other issues you might run into: MySQL uses UTF-8, and PowerShell uses UTF-16. While both can do charset conversions, this often makes things more difficult (for example: Bug #74817).
And if you're working with spatial data, images or stored procedures then the mysql client is often not very helpful. The graphical client, MySQL Workbench, is often much better suited in these cases. It has syntax highlighting, a spatial viewer and an image viewer. It allows you to edit a SQL script and then execute it and edit it again and run it again. I you try to do this with the history of the mysql client then the formatting gets lost. For working with SQL procedures, triggers, events, etc the solution is to edit it with your favourite editor and then source it. But for images and spatial data you often really have to use Workbench or something like QGIS.
Besides the CLI vs GUI difference there are some more differences in how most people use both tools. For Workbench it is installed on a the client workstation and then uses a remote connection to the server. Workbench supports both the native SSL/TLS protocol and can tunnel through SSH.
The mysql client supports SSL/TLS, but doesn't support SSH tunnelling. Which is ok, because you can just run it on the server.
This also has implications on configuration: The mysql client only needs to know how to connect to the local server. Workbench needs configuration for every server. This makes the mysql client more useful if you are managing a large set of machines.
One of the more annoying situations with the mysql client is that you quickly want to select a row from a table or run that select query which was reported as being slow. So you ssh to the server and run the query... and then you suddenly get a lot of 'weird' characters on you screen. This happens if you have binary columns (BLOB, varbinary, geometry) to store IP addresses, locations, binary UUID's, photos, etc.
I made a patch to fix that. With the patch binary data is printed with hex literals (e.g. 0x08080404 for the binary version of 8.8.4.4). So this doesn't break your terminal anymore and also allows you to copy the value to the subsequent query.
mysql> select * from t1;
+----+------------------------------------+
| id | ip |
+----+------------------------------------+
| 1 | 0x00000000000000000000000000000001 |
| 2 | 0x7F000001 |
| 3 | 0x08080808 |
| 4 | 0x08080404 |
+----+------------------------------------+
4 rows in set (0.00 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ip` varbinary(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
This might raise the question: why not display them as an IP address instead? I did make a patch to do that. The patch triggers this display if the column is varbinary with a length which matches an IPv4 or IPv6 address. But we might store IP addresses in columns with other names and we might store values which are not an IP, but have the same length. This would require a lot of configuration and configuration options. And this would need more work for geometry types, binary UUID's etc. So for now I decided not to take that route.
It would be nice if the server would allow you to define an 'ip6' datatype which is just an alias for varbinary(16), but would be sent to the client. This could also be done with something like "SELECT c1::ip6" in the query. Or the server really has to define UUID, and IP types. Or user defined types. Or both.
mysql> select id,hex(ip),ip from t1\G
*************************** 1. row ***************************
id: 1
hex(ip): 00000000000000000000000000000001
ip: INET6_ATON('::1')
*************************** 2. row ***************************
id: 2
hex(ip): 7F000001
ip: INET6_ATON('127.0.0.1')
2 rows in set (0.00 sec)
Also somewhat belonging in this list: I made a patch in 2015 which replaces the drawing characters (+ for corners, - for horizontal lines, | for vertical lines) with unicode drawing characters.
mysql> DESC mysql.func;
╭───────┬──────────────────────────────┬──────┬─────┬─────────┬───────╮
│ Field │ Type │ Null │ Key │ Default │ Extra │
├───────┼──────────────────────────────┼──────┼─────┼─────────┼───────┤
│ name │ char(64) │ NO │ PRI │ │ │
│ ret │ tinyint(1) │ NO │ │ 0 │ │
│ dl │ char(128) │ NO │ │ │ │
│ type │ enum('function','aggregate') │ NO │ │ NULL │ │
╰───────┴──────────────────────────────┴──────┴─────┴─────────┴───────╯
4 rows in set (0.00 sec)
I also made a patch to report the runtime with more detail (e.g 0.004 instead of 0.00).
mysql> select sleep(0.123); +--------------+ | sleep(0.123) | +--------------+ | 0 | +--------------+ 1 row in set (0.123 sec)
I also once made a patch to set the terminal title.
And what about the future? I don't know, the mysql client might be replaced with MySQL Shell (mysqlsh), but for that to happen mysqlsh needs many improvements. MySQL Workbench could replace some of it if it gets the capability to easily connect to many similar servers without much configuration. But should it? iTerm2 (macOS) now allows you to display images in the terminal, so if more terminal emulators would get this feature then it might make sense to get a image and geometry viewer in the client..
Please leave a comment with your experience with the mysql client and which features you would like to see.