November 26, 2014
Hot Topics:

The Other 5 Command-line Timesavers for MySQL Tasks, Page 2

  • October 28, 2010
  • By Jason Gilmore
  • Send Email »
  • More Articles »

6. Change the Prompt

More than once I've attempted to view or modify a schema only to be told that the table didn't exist. Sheer panic ensued over the possibility I mistakenly deleted it, until I realized that I was logged into the wrong database server. By modifying the mysql client prompt to reflect the name of the database you're currently using, you can eliminate this confusion entirely. To change your prompt interactively, log in to the mysql client and execute the following command:

mysql>prompt [\d]>
[dev_wjgilmore_com]>

 

Because you'll probably want the change to be permanent, add the following line to your configuration file:

prompt = [d]>

 

You're not limited to including just the database name. Among other information, you can include the current time and date, hostname, and username. Consult the MySQL manual for more information.

7. Prevent Catastrophes with Safe Updates

In the previous tip I mentioned the panic of suspecting a table has accidentally been deleted. More than one administrator has fallen victim to a similarly serious gaffe, not because of accidentally executing the DROP TABLE command but rather because of the far more insidious omission of a WHERE clause when executing the UPDATE command. For instance, suppose you wanted to modify a user's username by executing the following command:

mysql>UPDATE users set User = 'wjgilmore' WHERE User = 'wjgilmore-temp';

 

However, in your haste to head out to lunch you enter:

mysql>UPDATE users set User = 'wjgilmore';

 

Naturally, executing the latter command will result in every value in the users table's User column being set to wjgilmore. Avoid such catastrophic errors by adding the following line to your configuration file:

safe-updates

 

8. Use the Command Documentation

Many users are aware of the mysql client's built-in documentation, which presents a list of useful commands when help is executed:

mysql>help
...
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
...

However, the documentation goes much deeper than this. For instance, if you can't remember what the DESCRIBE command is used for, pass the command name to help:

mysql>help describe;
Name: 'DESCRIBE'
Description:
Syntax:
{DESCRIBE | DESC} tbl_name [col_name | wild]

DESCRIBE provides information about the columns in a table. It is a
shortcut for SHOW COLUMNS FROM. These statements also display
information for views. (See [HELP SHOW COLUMNS].)
...

9. Use the Pager

Sometimes you want to peruse a table's rows, but there are too many to view in a single screen. Rather than grapple with using the LIMIT clause, enable the client pager and set it to your system's paging utility:

mysql>pager more
PAGER set to 'more'

 

To disable the pager, execute the nopager command.

10. Dump Output to a File

You'll occasionally need to dump SQL result output to a text file. You could use the SELECT INTO OUTFILE command, or from within the mysql client you can enable the tee command and assign an output file, causing all subsequent output to be sent to that file:

mysql>tee sales_report.txt

 

Conclusion

Whether you pick and choose or apply all 10, the tips and tricks introduced in this article will undoubtedly save you a great deal of time, effort, and potentially pain! Do you know of any other MySQL tips and tricks? Tell us about them in the comments!

About the Author

Jason Gilmore is founder of the publishing and consulting firm WJGilmore.com. He is also the author of several popular books, including "Easy PHP Websites with the Zend Framework", "Easy PayPal with PHP", and "Beginning PHP and MySQL, Fourth Edition". Follow him on Twitter at @wjgilmore.



Originally published on http://www.developer.com.

Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel