Overview
Last month we looked at MySQL
Administrator, this month I try out another of the MySQL tools: MySQL Query
Browser. One nice thing about writing a regular monthly column (or a book!) is
that it is an incentive to try out new tools I may not use in the work
environment. I can avoid nappy-changing duties at home by saying I am working
hard to earn enough for those same nappies, rather than just trying out the
tool for the fun of it! Finding new topics can be taxing, (please feel free to
add requests to the
forum). It's not every month I realize I've missed an obvious topic like MySQL
Backups. However, I was pleasantly surprised upon realizing that I hadn't
yet tried out MySQL Query Browser.
The 'gamma' version of
MySQL Query Browser was released in November, although beta versions were
available long before that. MySQL Query Browser (I will start calling it MyQB
from now) is a tool that allows you to execute queries and develop SQL scripts.
That does not sound too exciting, which is probably why I took so long to try
it, but it has some handy features that may come in useful.
Features
Upon opening the
application, MyQB will ask for connection details (default schema is the
default database you would like to connect to. Once you are in, (connection
details will be saved for next time), experienced MySQL users will feel
comforted by the command-line-like sparseness of the interface (see screenshot). Here the test database
has a number of tables already, listed in the window on the right of the screen
(called the Object Browser). Other areas to note include the space
around the white area waiting for your input, called the Query Toolbar,
the large blank grey area (called the Result Area, soon to be filled
with the results of your queries), the area in the bottom right called the Information
Browser, and the area just below the Query Toolbar, called the Advanced
Toolbar. Perhaps not the best choice of names, but they are all relatively
easy to understand as you will soon see.
Executing queries
The easiest way to enter
a query is simply to type it in. For example, enter the following SQL in the
text area of the Query Browser, and press Execute:
CREATE TABLE myqb_data (id INT, f1 VARCHAR(10), PRIMARY KEY (id)). Unfortunately,
the Object Browser does not update itself automatically, and I had to
right-click on the database name and then choose Refresh Schema. Execute
a few INSERT statements. You will see that the Go Back and Go Next buttons are
now clickable (see screenshot - MyQB
models itself on the browser interface), allowing you to easily access all
previous queries (don't worry; they aren't executed until you actually press Execute.
The MySQL documentation claims that only valid queries are stored, but my
version seemed to store valid and invalid queries, which in my opinion is an
improvement, as I am often annoyed by the lack of this feature in phpMyAdmin,
which I use daily.
Another way of entering
queries is with the Advanced Toolbar. This can be simpler. As an example,
suppose you want to SELECT all the id's from the myqb_data table.
You can click on the arrow next to the myqb_data table in the Object Browser,
which will display all fields in the table. Then click the SELECT button from
the Advanced Toolbar. Hovering over the button tells you that clicking on a
column name in the schema browser (another term for the Object Browser) will
add it as a SELECT column. Try this now by clicking the id field. The
whole query is built in just one click, as SELECT m.id FROM test.myqb_data m (see the screenshot).
The other buttons work in a similar way, contextually adding the field you
click on depending on what button you have previously selected. You can also
BEGIN, COMMIT and ROLLBACK transactions
Of course, no
browser-like interface can be complete without the wonders of tabs. You can
create new Result Area tabs with Ctrl-T, or choosing New Tab and Execute
from the options next to the Execute button. You can also split a tab with the Split Tab and Execute option. If a result set is too large, F12
will maximize the display area.
You can also edit tables
by right-clicking on a table listed in the Object Browser, and using the MySQL
Table Editor (a feature of both MySQL Query Browser and MySQL Administrator).
Some more features: Comparing, Explaining
and Creating Views
If you have two result
sets in two tabs, you can use the Compare button to highlight the
differences between them. For some reason though, this only works if the two
tabs are split vertically, not horizontally. Next to the compare button is the Explain
button, which explains the current query (used for query optimizing - see the
article Optimizing
MySQL Queries and Indexes for more details if you are not sure.)
I tested MyQB with MySQL
4.1, but if you are using MySQL 5.0, you can also use it to create views. How?
As simple as entering and executing the query that defines the view, and then
pressing the Create View button.
Editing Result Sets
If each record can be
uniquely identified (i.e. has a primary key), you can also edit the result set,
or add records. To do so, click Start Editing, and then click on the
records to edit or right click on choose Add Row. Apply Changes
and Discard Changes should do just what they claim, though I had a few
problems with this functionality on my Mandrake Linux installation.
MyQB also allows you to
conveniently save entire result sets (choosing File/Export Resultset and
then a format from one of CSV, HTML, XML or Excel) or just the highlighted
field. There are also special ways for handling Blob and Text fields. I could
only view, load to or save from disk. The documentation mentions being able to
edit and clear, so perhaps this will available to you.
The Information Browser
None of the above is
particularly exciting or useful, but one area that is, is the Information
Browser. It is effectively your reference manual. Not sure how to use the HANDLER
statement? You can skim through a paper book, rush off to mysql.com and start
searching, or, easiest of all, click on HANDLER in the Information
Browser (it is the third Data Manipulation statement under Syntax). There is
also a function reference (see screenshot), a
list of parameters, and a list of all queries in your transactions.
Master-detail views
MyQB lets you view data
represented in a one-to-many relationship (called here a master-detail view).
This is quite useful for rapidly going through alternatives without needing to
rerun the queries, or deal with a large result set. To test this, create the
following two tables, and sample data:
CREATE TABLE `car_manufacturer` (
`id` int(11) default NULL,
`make` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
#
# Dumping data for table `car_manufacturer`
#
INSERT INTO `car_manufacturer` VALUES (1, 'Toyota');
INSERT INTO `car_manufacturer` VALUES (2, 'Honda');
CREATE TABLE `car_model` (
`id` int(11) default NULL,
`manufacturer_ID` varchar(50) default NULL,
`model` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
#
# Dumping data for table `car_model`
#
INSERT INTO `car_model` VALUES (1, '1', 'Corolla');
INSERT INTO `car_model` VALUES (2, '1', 'Tazz');
INSERT INTO `car_model` VALUES (3, '2', 'Jazz');
For each car
manufacturer, there may be many car models. We can get a quick overview of
models for each manufacturer by creating a master-detail view, as follows:
-
First, execute
the following query:
SELECT * FROM car_manufacturer
-
Then
right-click on the result area and choose the Split Tab Vertically option.
-
Add a
parameter called id (using the Parameter tab of the Information browser)
-
Create a query
that uses this parameter, as follows:
SELECT
* FROM car_model WHERE manufacturer_id =:id.
-
Now, when you
click on Toyota, you will see the two models
appear in the right of the split screen. When you click on Honda, the right side
refreshes itself, and the Honda model appears (see screenshot).
The Script Editor
You
may be thinking so far that MyQB is nothing more than a manual and a few marginally
useful buttons to press. I know I was! However, MyQB also contains a Script
Editor, which allows you to work with scripts containing multiple SQL
statements. To open a script, use Ctrl-O or choose Load Script
from the file menu. Note that SQL in the loaded script needs to contain the
semicolon at the end of each statement. Commands available include:
-
Execute (run
the whole script)
-
Continue (Run
the whole script, or continue from where you left off, stopping for errors)
-
Step Over
(running the next command, executing but not descending into functions)
-
Step Into
(running the next command, descending into functions)
-
Run Until
Return (which runs the script until the current function returns)
-
Stop (which terminates
running of the script if you are in the middle).
You
can also edit queries directly from a development tool, but this was only
available in the Windows version, so I have not tested it.
Conclusion
Being
easy-to-use, graphical, and considering only a minority of Linux MySQL users
are uncomfortable on the command line, it does seem that this application has
been designed primarily for Windows users. In addition, my Mandrake Linux
version seemed to be missing a few features, and seg-faulted twice (when
attempting to edit on an empty result set). It does makes life easier for those
who find the standard MySQL command line intimidating, and has some handy
features that may entice others as well. Overall, I was disappointed, and
probably will not use it again, although I realize I am not the target market.
More resources
»
See All Articles by Columnist Ian Gilfillan
» 
Comment and Contribute
