Here at the ol’ job, we use MySQL (something that I have blogged about before) and, naturally, have phpMyAdmin installed. I seldom use it as I prefer a a nice, CLI interface. It does provide a few amenities that have log me in even when I don’t strictly speaking need it. Some of these are the editing feature (yes, I am lazy enough that sometimes I would rather not sit down and write out an UPDATE query), the printing (which is much nicer than out-of-the-box lp or lpr on *nix machines), and dumping stuff to CSV or Excel (which is nice for one-off reports that I occasionally have to run).
The last couple of days, while working on some reconciliation type reports that get a little involved, I decided to take advantage of the Unix philosophy (a tool for every job, do one thing and do it well, etc.) and make my life quicker and easier from the MySQL command line. So, here is a look at the various tweaks I’ve made.
The first thing to look at is paging. The client doesn’t do any out of the box. After jacking off with a handful of pagers (less, more, most, and w3m) I decided on w3m for reasons that will soon become fairly clear. To make mysql page, simply run the command:
pager w3m
or more, less, most, etc. Whatever command you want to be the pager. This setting can be made permanent
Next up, we have printing. This is why I chose w3m. less and most provided no way that I could see to pass the piped-in text off to a printer. If some pager coniusseur would care to correct me on this score, I am all ears.
Set:
keymap C-p SAVE_SCREEN “| a2ps -r test.txt -1 -r -f 7pts”
keymap q EXIT
keymap C-p SAVE_SCREEN "| a2ps -1 -r -f 7pts"
keymap q EXIT
The second item maps q to exit without confirmation. Out of the box, w3m always prompts. I hate being prompted. Remove at your liking. The first line maps the sequence Control + P to a SAVE_SCREEN command (which is used to dump pages to files) and then pipes it to a2ps. You can look up the options for a2ps, but the end result is that, since no output file was specified, a2ps prettifies the text handed it and sends it off to the printer.
Finally, we have that little problem of dumping to Excel. We do not have to dump straight to .xls or .xlsx format. CSV will do, despite being a poor format in general. MySQL can do this part natively by running a query like so:
select * from foo
into outfile 'someplace.csv'
fields terminated by ',' lines terminated by '\n';
This is nice, but, speaking for myself, I usually review the results before dumping them out, just to be sure it looks roughly the way I want or expect. Another good way to do this, is to simply put the query into a file and run it like this:
mysql -uuser database < query.sql
When in noninteractive mode, the mysql client outputs the records in a tab delimited format. Piping this through sed and into a text file will create a simplistic CSV or opening it in tab-delimited form with a spreadsheet app (like Excel or OpenOffice Calc) will allow it to be exported to a more friendly format.
