Two MySQL Pro Tricks

Chances are you use some form of gui when you interact with MySQL. Imaright? I don't. I prefer to use the command line tools for pretty much everything that I do. MySQL is no exceptions. That being said, these two tricks are very helpful, even if you normally use a gui...in fact, the second trick will work in the gui's as well, 'cause it is really just some really cool sql.

The first thing that we are looking at is how we end a sql statement. Typically, you use the ';' to end your statements like this:

SELECT * FROM table WHERE type="device";

A pro trick is to use "\G" like this: DESCRIBE table\G

So what is the difference? Well, "\G" gives a much easier to read print out of the data then the traditional tabular form.

mysql> select * from cms_phone_features where entry_id=4766\G 
*************************** 1. row *************************** 
entry_id: 4766
row_id: 18973
row_order: 0
style: Touch Screen
keyboard: Virtual
camera_front: 1.3-megapixel
camera_rear: 3.2-megapixel
1 row in set (0.00 sec)

The second trick that we are going to show today is some cool sql. So here is the scenario: You are dealing with a very large database with a massive amount of tables. Now, all you really want to deal with are a subset of these tables. They all happen to be prefixed 'ds_'. So how can we list just these tables?

show tables like 'ds%'\G

The 'like' term will list tables like whatever string you follow with. The string must be in '' and the % is a wildcard that matches anything.

For this and other tricks of the trade, check out my website:
http://weatheredwatcher.com