Posted on 5 Comments

mysql cmdline tricks: output control (\G, pager, tee)

Compared to SQL*Plus (Oracle’s cmdline client) which can as I understand produce complete paginated reports, the ‘mysql’ tool has fairly simple output formatting. I’m not generally fussed about that, as there are perfectly good other tools do make snazzy reports with. Some very cross platform (like web based), output PDF, not a problem. You can even write your own quickly these days.

But the cmdline tool has its use. I always teach my students the basics of it, and insist that some exercises are done using this tool. Familiarity (which involves some practice) helps there. Why? If all else fails, you generally do have mysql available on a machine. It’s not hindered by remote ssh logins, or anything else. And, of course, for quickly checking something. And that’s where the proficiency helps again. So, normally, you get this type of ASCII table:

mysql> select "Hello, world!" AS foo;
 --------------- 
| foo           |
 --------------- 
| Hello, world! | 
 --------------- 

which is nice. But for some commands, this type of formatting would make the output unreadable. Key examples are SHOW CREATE TABLE, SHOW CREATE TRIGGER, and SHOW WARNINGS (particularly after EXPLAIN). Now check this out:

mysql> select "Hello, world!" AS foo \G
*************************** 1. row ***************************
foo: Hello, world!

If, instead of a semicolon you specify \G, the output is formatted vertically. A separator line above each row, and then each column prefixed by its name/alias followed by the value of that column within the row. It’s much more readable for cases where you get a one-row result set with only a few columns, at least one of which might be longer than the width of your console.

The above also teaches us another lesson: the semicolon is not actually part of the SQL command. It’s merely a delimiter that tells the client code to send the query to the server. If you’re in a programming or scripting language using the MySQL API, you wouldn’t include it in the query string.

A related trick is to use the “pager” command. If you specify pager less on the mysql command prompt, the output will thereafter be run through less. So then you can scroll through a larger result set, search it, etc. (see man less).

Final one for this post, there’s “tee”. This command followed by a filename (or program/pipe!) lets you log everything you do, queries (what you type) as well as output. Very handy, particularly during training and testing, or when you need to make a bugreport or support request.

Posted on 5 Comments

5 thoughts on “mysql cmdline tricks: output control (\G, pager, tee)

  1. Funny how a week after I finish writing the MySQL Magazine article about the mysql commandline options (you can do all that using the mysql command line, or put it in an option file), both Peter and you write about some of them. 🙂

    Although my focuses more on the commands from the shell commandline, and also goes into things like getting HTML output, and avoiding the “table-style” format of output when you want to avoid it.

  2. “the semicolon is not actually part of the SQL command. It’s merely a delimiter that tells the client code to send the query to the server. If you’re in a programming or scripting language using the MySQL API, you wouldn’t include it in the query string.”

    Well, yes and no. Even in pure SQL (not SP context), the semi-colon is interpreted by the server to delimit SQL statements. It can be used to send a batch of statements (and if applicable, receive a batch of results too) in just one roundtrip.

    Of course you won’t notice this meaning of semi-colon unless you set the default mysql client delimiter to something other than the semicolon.

    http://rpbouman.blogspot.com/2008/02/most-misunderstood-character-in-mysqls.html

    Best regards,

    Roland Bouman

  3. Another useful thing I found out (in Linux though, don’t know if it works in Windows) was using “\! cmd ” to run a command on the shell. For instance, if you were running a file called mark.sql, by running “>mysql source mark.sql” and you wanted to edit it, you could do so by entering “\! vim mark.sql” Of course you can always do this in separate sessions, but I find it useful.
    Mark Nessfield
    http://www.mysql.oxford123.co.uk/blogs

  4. Does the cmdline client enable multi-query for its connection? If not, the server won’t even accept it, right?

  5. “Does the cmdline client enable multi-query for its connection? If not, the server won’t even accept it, right?”

    Like described in the article, it accepts it, yes:

    mysql> delimiter go
    mysql> select 1; select 2;
    -> go
    +—+
    | 1 |
    +—+
    | 1 |
    +—+
    1 row in set (0.00 sec)

    +—+
    | 2 |
    +—+
    | 2 |
    +—+
    1 row in set (0.00 sec)

    mysql>

    Roland

Comments are closed.