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.
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.
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.
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.
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.