Students in my training classes usually prefer to use some kind of visual editor for MySQL. Typically this would be the software they’re using at work. Sometimes they just bring over their laptops with the software installed. Or they would use MySQL Workbench, which is what I usually have pre-installed on their desktops.
I see MySQL Workbench, SQLyog, Toad for MySQL, or several more.
I always humbly suggest they close down their software and open up a command line.
It isn’t fancy. It may not even be convenient (especially on Windows, in my opinion). And repeating your last command with a minor modification requires a lot of key stroking. Or you would copy+paste from some text editor. Most students will give it a shot, then go back to their favorite editor.
Well, again and again I reach the same conclusion:
Visual editors are not as trustworthy as the command line.
Time and again students show me something on their editor. Behavior seems strange to me. Opening up a console shows a completely different picture.
Things like:
- The visual editor would open a new connection for every new query (oh, so the @user_defined_variable I’ve just assigned turns NULL, or the TEMPORARY TABLE disappears).
- The visual editor will only show 1,000 results, via LIMIT 0,1000. “But the same query runs so much faster on my machine!”. Well, sure, a filesort of 1,000,000 rows that can satisfy the first 1,000 will quit early!
- The visual editor shows table definition graphically. “I didn’t realize the index did(n’t) cover this and that columns. I didn’t realize it only covered first n characters of my VARCHAR.”. That’s because you can’t beat SHOW CREATE TABLE, the definite table structure description.
- The visual editor allows for export/import/copy/transfer of tables and rows with just one click! “Why is it so complicated in the command line to purge 1,000,000 rows from a table?”. Ummm, did you realize the visual editor would typically use a naive approach of doing everything in one huge transaction?
- The visual editor is smart. But sometimes you don’t want smart. You just assume simple. I personally take great precaution with smart solutions. Luckily, with scripts you have so much greater control (i.e. command line options, “dry-run” mode, etc.) that I have greater confidence in them.
I do like it when a visual editor plays it both smart and safe, in such way that before doing its smart work it actually presents you with the query it’s going to issue. Which is why I always considered MySQL Query Browser (now replaced by Workbench) to be the visual editor of choice in my classes.
But, at the end of the day, I strongly believe: if you don’t know how to do it with command line, you can’t really know how it’s done.
To start, CLI cannot match the productivity gain of GUI tools and GUIs cannot match the flexibility of CLIs to a certain extent. Keeping the discussion limited to the academic usage, I think learning has to be fun and GUIs add this fun element. Students can easily start playing with GUIs and can visualize things under the hood. I think tools also make students curious to try out something new. They might end up discovering a concept all by themselves which they wouldn’t have otherwise unless reading a thick volume.
Regarding “The visual editor would open a new connection for every new query (oh, so the @user_defined_variable I’ve just assigned turns NULL, or the TEMPORARY TABLE disappears).”:
I use MySQL Workbench for my development work, and most of that work is in fairly sophisticated stored procedures. I do find that my temporary tables persist, as I’ve been able to query them independently of the output from my procedures.
I concur with your sentiment that people should know and be able to develop SQL via the command line; however, editing thousand-line plus procedures using the command line is cumbersome; a visual editor like MySQL Workbench is the way to go
@Ben,
Yes, no doubt it is unthinkable to create stored routines code directly with the command line. Visual editors are great for that.
BTW, an alternative is to use the SOURCE command from the command line client, such that I edit my stored routine code in my favorite text editor, then just SOURCE ‘/path/to/my/code.sql’