Thursday, March 26, 2009

Another command line tip

Encouraged by Baron Schwartz tip on result set comparison, here are a few more, on the same vein.
First, you can send a result set to a file. Probably you will say "yeah, I know, using SELECT INTO OUTFILE". Correct. Except that you can't rewrite to an existing file, if you want to, and you will get a raw output, not the well formatted one that you usually see on the command line. For example:

mysql > select 1 into outfile '/tmp/f1.txt';
mysql > \! cat /tmp/f1.txt
1

mysql > select 1 into outfile '/tmp/f1.txt';
ERROR 1086 (HY000): File '/tmp/f1.txt' already exists

BTW, \! command is a handy shortcut for executing a shell command.
Let's see what happens with the alternative method:

mysql > pager cat > /tmp/f1.txt
mysql > select 1;
\! cat /tmp/f1.txt
+---+
| 1 |
+---+
| 1 |
+---+

Now, Using the above trick, you can check the differences between two result sets visually:

mysql > pager cat > /tmp/f1.txt
mysql > select "one" union select "two" union select "three";
3 rows in set (0.00 sec)

mysql > pager cat > /tmp/f2.txt

mysql > select "one" union select "two" union select "Three";
3 rows in set (0.00 sec)

mysql > nopager

mysql > \! vimdiff -o /tmp/f[12].txt

And here is what you get:

Not only you'll know that there is something different, but you will also know exactly what.
This trick is part of a collection of command line advice that I have meant to write for long time. I will publish it all before the Users Conference. I just couldn't resist with this one!

No comments:

Vote on Planet MySQL