http://www.developer.com/

Back to article

Secrets of the MySQL Client Command Line


February 5, 2008

Graphically oriented MySQL administration tools abound, but for true speed and no-frills management perhaps nothing is more effective than the command-line driven MySQL client. However, the inordinate amount of typing due to executing common queries, determining what database you're currently logged into, and monitoring database server performance quickly can become tedious. In this article, I'll unveil a few of MySQL's command-line hidden features that can eliminate those extra keystrokes and along the way make you a much more efficient user of this powerful client.

Changing the Client Prompt

If I had a dollar for every time I ran show tables just as a reminder of which database was currently selected, I'd be writing this article from an exotic beach locale. The fact is, when regularly jumping among an IDE, shell prompt, and MySQL client prompt it can be very easy to forget your spot. Eliminate this problem in its entirety by changing MySQL's prompt by using the prompt command:

mysql>prompt mysql (\d)>

Once executed, the currently selected database will appear in the prompt, like so:

mysql (corporate)>

It's also easy to lose track of which database server you're logged in to, not to mention which account you're using. To fix this, modify your prompt using the \u and \h options:

mysql>prompt mysql (\u@\h)>

This produces a prompt similar to the following:

mysql (root@www.wjgilmore.com)>

To render the change permanent, add the command to your .my.cnf file like so:

[mysql] prompt=mysql \\d>

The database, user, and host options are just a few of many available to you. See the MySQL documentation for more information.

Use Shell Aliases to Execute Common MySQL Commands

Suppose your new web application is really starting to take off, with new users registering by the hour. Like a stock IPO, chances are you and your colleagues are regularly peeking the user count. But, constantly logging in to and out of MySQL just to execute a simple query is aggravating. However, if you're otherwise logged in to the server, you can create a shell alias that will log in to the MySQL server, select the appropriate database, and execute a specific command:

%>alias usrcount="mysql -u appadmin -p myapplication
                        -e \"select count(id) from users\""

In this example, you're logging in to the local MySQL server using the appadmin user account, selecting the myapplication database, and passing in the SELECT query for execution using the little-known -e option. Now, any time you want to determine how many users are in the system, just execute the following command and provide the appadmin user password:

%>usrcount
Enter password:
+-------------+
| count(id)   |
+-------------+
|         348 |
+-------------+

To make the alias permanent, add it to your .bashrc or similar shell configuration file.

Rendering Query Results in Vertical Format

Easily reviewing SELECT query results in a shell window can be particularly difficult when a table consists of numerous columns. Remedying this inconvenience is easily done by appending the \G switch to the query:

mysql>SELECT * from users WHERE id=1\G

Executing this query with the \G switch attached formats the output like so:

mysql test>select * from users where id=1\G
*************************** 1. row *********
       id: 1
     name: Jason
    email: jason@example.com
telephone: (614)999-9999
     city: Columbus
1 row in set (0.00 sec)

Create a CSV File From Query Results

If you're using a database professionally, chances are a colleague has asked you to dump data from the database into an Excel file to perform further analysis. Did you know you can modify a SELECT query to do all of the CSV formatting for you, and place the data in a textfile? All you need to do is identify how the fields and lines should be terminated. For example, to dump a table named users to a CSV file named users.csv, execute this command:

mysql>SELECT * FROM users INTO OUTFILE '/home/jason/users.csv'
   FIELDS TERMINATED BY '\t\' ->LINES TERMINATED BY '\n';

Inserting Batch Data

Suppose you're writing an e-commerce application that will be used to sell a wide variety of products. It makes sense to categorize these products; therefore, you'll be using a table named categories to manage the category names:

mysql test>create table categories (
   ->id integer not null auto_increment,
   ->name varchar(35) not null,
   ->primary key(id));

During breaks from writing the application, you've been adding to a textfile (categories.txt containing a list of categories. This file looks something like this:

Candy
Fruit
Coffee
Tea
Pop
Vegetables

The time has come to add these categories to the categories table. You could do so manually, but this is time-consuming and error prone. The sane solution is to use MySQL's LOAD DATA INFILE command:

mysql>LOAD DATA INFILE '/home/jason/categories.txt'
      INTO TABLE categories ->LINES TERMINATED BY '\n'

If your input file has multiple items per row, you can tell MySQL to insert each into a separate column by delimiting them with a tab and using FIELDS TERMINATED BY '\t':

mysql>LOAD DATA INFILE '/home/jason/categories.txt'
      INTO TABLE categories ->FIELDS TERMINATED BY '\t'
      LINES TERMINATED BY '\n'

Disable the Annoying Error Beep

If you've ever seen the movie Dumb and Dumber, you might recall the scene where Lloyd (Jim Carrey) and Harry (Jeff Daniels) attempt to outdo each other in terms of who can make the most annoying sound in the world. The MySQL client's obnoxious error beep would have fared quite well in such a competition. If you're not familiar with it, just execute a malformed query from within the client for an earful. Although it has nothing to do with efficiency, I couldn't pass up the opportunity to work this tip for disabling it into this tutorial. To temporarily disable the beep, pass --no-beep along when logging into the server:

%>mysql -u root -p --no-beep

To permanently disable this annoyance, add no-beep to the [client] section of your .my.cnf file.

Conclusion

I hope this compilation of MySQL client "secrets" helps you use this powerful tool much more efficiently! If you'd like to share a tip, email me at jasonATwjgilmore.com!

About the Author

W. Jason Gilmore is a freelance web developer, consultant, and technical writer. He's the author of several books, including the best-selling Beginning PHP and MySQL 5: Novice to Professional, Second Edition (Apress, 2006. 913pp.).

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date