Tips for Interacting with mysql

This section discusses how to interact with the mysql client program more efficiently and with less typing. It describes how to connect to the server more easily and how to enter queries without typing each one from scratch.

Simplifying the Connection Process

It's likely that you need to specify connection parameters such as hostname, username, or password when you invoke mysql. That's a lot of typing just to run a program, and it gets tiresome very quickly. There are several ways to make it easier to connect by minimizing the amount of typing you do:

• Use an option file to store connection parameters.

• Repeat commands by taking advantage of your shell's command history capabilities.

• Define a mysql command line shortcut using a shell alias or script.

Using an Option File

As of version 3.22, MySQL allows you to store connection parameters in an option file. Then you don't have to type the parameters each time you run mysql; they are used just as if you had entered them on the command line. The parameters are also used by other MySQL clients, such as mysqlimport. That means an option file reduces typing when you use those programs, too.

To use the option file method of specifying connection parameters, create a file named ~/.my.cnf (that is, a file named .my.cnf in your home directory). An option file is a plain text file, so you can create it using any text editor. The file's contents look something like this:

[client]
host=serverhost
user=yourname
password=yourpass
							

The [client] line signals the beginning of the client option group; any lines following it are read by MySQL client programs to obtain option values, through the end of the file or until a different parameter group begins. Replace serverhost, yourname, and yourpass with the hostname, username, and password that you specify when you connect to the server. For me, .my.cnf looks like this:

[client]
host=pit-viper.snake.net
user=paul
password=secret

Only the [client] line is required. The lines that define parameter values are optional; you can specify just the ones you want. For example, if your MySQL username is the same as your UNIX login name, there is no need to include a user line.

After you create the .my.cnf file, set its access mode to a restrictive value to make sure no one else can read it:

% chmod 600 .my.cnf
							

Under Windows, the option file contents are the same, although the name is different (C:my.cnf), and you don't invoke the chmod command.

Because option files were not added to MySQL until version 3.22, you can't use them with earlier releases. In particular, you cannot use an option file under Windows with the clients that come with the shareware MySQL distribution because that's based on MySQL 3.21. Option files work fine with the registered Windows version of MySQL, or you can get newer option file-aware clients from the MySQL Web site.

More information on option files may be found in Appendix E, "MySQL Program Reference."

Using Your Shell's Command History

Shells such as csh, tcsh, and bash remember your commands in a history list and allow you to repeat commands from that list. If you use such a shell, your history list can help you avoid typing entire commands. For example, if you've recently invoked mysql, you can execute it again like this:

% !my
							

The '!' character tells your shell to search through your command history to find the most recent command that begins with 'my' and reissue it as though you'd typed it again yourself. Some shells also allow you to move up and down through your history list using the up-arrow and down-arrow keys (or perhaps Ctrl-P and Ctrl-N). You can select the command you want this way and then press Enter to execute it. tcsh and bash have this facility, and other shells may as well. Check the documentation for your shell to find out more about using your history list.

Using Shell Aliases and Scripts

If your shell provides an alias facility, you can set up command shortcuts that allow you to invoke a long command by typing a short name. For example, in csh or tcsh, you can use the alias command to set up an alias named samp_db, such as this:

alias samp_db 'mysql -h pit-viper.snake.net -u paul -p samp_db'

The syntax for bash is slightly different:

alias samp_db='mysql -h pit-viper.snake.net -u paul -p samp_db'

Defining an alias makes these two commands equivalent:

samp_db
mysql -h pit-viper.snake.net -u paul -p samp_db

Clearly, the first is easier to type than the second. To make the alias take effect each time you log in, put the alias command in one of your shell's startup files (for example, .cshrc for csh, or .bash_profile for bash).

Another form of shortcut is to create a shell script that executes mysql for you with the proper options. In UNIX, a script file that is equivalent to the samp_db alias just shown looks like this:

#! /bin/sh
exec mysql -h pit-viper.snake.net -u paul -p samp_db

If I name the script samp_db and make it executable (with chmod +x samp_db), I can type samp_db to run mysql and connect to my database.

Under Windows, a batch file can be used to do the same thing. Name the file samp_db.bat and put a line like this in it:

mysql -h pit-viper.snake.net -u paul -p samp_db

This batch file can be run either by typing samp_db at the prompt in a DOS console or by double-clicking its Windows icon.

If you access multiple databases or connect to multiple hosts, you can define several aliases or scripts, each of which invokes mysql with different options.

Issuing Queries with Less Typing

mysql is an extremely useful program for interacting with your database, but its interface is most suitable for short, single-line queries. mysql itself doesn't care whether or not a query spreads across multiple lines, of course, but long queries aren't much fun to type. Nor it is very entertaining to enter a query, even a short one, only to discover that you must retype it because it has a syntax error.

There are several techniques you can use to avoid needless typing and retyping:

• Use mysql's input line-editing facility.

• Use copy and paste.

• Run mysql in batch mode.

• Avoid typing INSERT statements by using existing data to create new records.

Using mysql's Input Line Editor

mysql has the GNU Readline library built in to allow input line editing. You can manipulate the line you're currently entering, or you can recall previous input lines and re-enter them, either as is or after further modification. This is convenient when you're entering a line and spot a typo; you can back up within the line to correct the problem before pressing Enter. If you enter a query that has a mistake in it, you can recall the query and edit it to fix the problem, then resubmit it. (This is easiest if you type the entire query on one line.)

Some of the editing sequences you will find useful are shown in Table 1.4, but there are many input editing commands available besides those shown in the table. You should be able to find an online version of the Readline manual by consulting your favorite Internet search engine. The manual is also included in the Readline distribution, available from the GNU project Web site at http://www.gnu.org/.

The following example describes a simple use for input editing. Suppose you've entered this query while using mysql:

mysql> SHOW COLUMNS FROM persident;
							

If you notice that you've misspelled "president" as "persident" before pressing Enter, press left arrow or Ctrl-B a few times to move the cursor left until it's on the 's'. Then press Delete twice to erase the 'er', type 're' to fix the error, and press Enter to issue the query. If you press Enter before you notice the misspelling, that's not a problem. After mysql displays its error message, press up arrow or Ctrl-P to recall the line, then edit it as just described.

Input line editing is not available in the Windows version of mysql, but you can obtain the free cygwin_32 client distribution from the MySQL Web site. The mysqlc program in that distribution is like mysql, but understands input line editing commands.

Using Copy and Paste to Issue Queries

If you work in a windowing environment, you can save the text of queries that you find useful in a file and use copy and paste operations to issue those queries easily. Simply follow this procedure:

The procedure sounds cumbersome when written out, but it's an easy way to enter queries quickly and with no typing when you're actually using it.

This technique also allows you to edit your queries in the document window, and it allows you to construct new queries by copying and pasting pieces of existing queries. For example, if you often select rows from a particular table, but like to view the output sorted in different ways, you can keep a list of different ORDER BY clauses in your document window, then copy and paste the one you want to use for any particular query.

You can use copy and paste in the other direction, too (from Telnet to your query file). When you enter lines in mysql, they are saved in a file named .mysql_history in your home directory. If you manually enter a query that you want to save for further reference, quit mysql, open .mysql_history in an editor, and then copy and paste the query from .mysql_history into your query file.

Running mysql in Batch Mode

It's not necessary to run mysql interactively. mysql can read input from a file in non-interactive (batch) mode. This is useful for queries that you run periodically because you certainly don't want to retype such a query every time you run it. It's easier to put it into a file once, then have mysql execute the contents of the file as needed.

Suppose you have a query that finds Historical League members who have an interest in a particular area of US history by looking in the interests column of the member table. For example, to find members with an interest in the Great Depression, the query could be written like this (note the semicolon at the end so that mysql can tell where it ends):

SELECT last_name, first_name, email, interests FROM member
WHERE interests LIKE "%depression%"
ORDER BY last_name, first_name;

Put the query in a file interests.sql, then run it by feeding it to mysql like this:

% mysql samp_db < interests.sql
							

By default, mysql produces output in tab-delimited format when run in batch mode. If you want the same kind of tabular ("boxed") output you get when you run mysql interactively, use the -t option:

% mysql -t samp_db < interests.sql
							

If you want to save the output, redirect it to a file:

% mysql -t samp_db < interests.sql >
								output_file
							

To use the query to find members with an interest in Thomas Jefferson, you could edit the query file to change depression to Jefferson and then run mysql again. That works okay as long as you don't use the query very often. If you do, a better method is needed. One way to make the query more flexible is to put it in a shell script that takes an argument from the script command line and uses it to change the text of the query. That parameterizes the query so that you can specify the interest keyword when you run the script. To see how this works, write a little shell script, interests.sh:

#! /bin/sh
if [ $# -ne 1 ]; then echo "Please specify one keyword"; exit; fi
mysql -t samp_db <<QUERY_INPUT
SELECT last_name, first_name, email, interests FROM member
WHERE interests LIKE "%$1%"
ORDER BY last_name, first_name;
QUERY_INPUT

The second line makes sure there is one keyword on the command line; it prints a short message and exits otherwise. Everything between <<QUERY_INPUT and the final QUERY_INPUT line becomes the input to mysql. Within the text of the query, the shell replaces the reference to $1 with the keyword from the command line. (In shell scripts, $1, $2, … refer to the command arguments.) This causes the query to reflect whatever keyword you specify on the command line when you run the script.

Before you can run the script, you must make it executable:

% chmod +x interests.sh
							

Now you don't need to edit the script each time you run it. Just tell it what you're looking for on the command line:

% interests.sh depression
% interests.sh Jefferson
							

Creating New Records Using Existing Data

It's possible to add new records into a table a row at a time with INSERT, but after creating a few records by typing INSERT statements manually, most people are convinced there must be a better way. One alternative is to use a file that contains data values only and then load records from the file using the LOAD DATA statement or the mysqlimport utility.

Often, you can create the data file using data that already exist in some other format. Your information might be contained in a spreadsheet, or perhaps it's in some other database and you'd like to transfer it to MySQL. To keep this discussion simple, I'll assume you have data in a spreadsheet on your desktop microcomputer.

To transfer spreadsheet data from your desktop microcomputer to a file in your UNIX account, you can use copy and paste in conjunction with Telnet. Here's how:

You now have a file data.txt containing the block of data you selected in your spreadsheet, and it's ready to be loaded into your database with LOAD DATA or mysqlimport.

Copy and paste is a quick and easy way to transfer data into a UNIX file, but it's most appropriate for smaller data sets. Larger amounts of data may exceed the limits of your system's copy buffer. In such cases, it might be better to save the spreadsheet in plain text format (tab delimited). Then you can transfer the file from your microcomputer to your UNIX account using FTP. Transfer the file in text mode (rather than in binary or image mode) so that line endings get converted to UNIX line endings. (UNIX uses linefeeds, Mac OS uses carriage returns, and Windows uses carriage return/linefeed pairs.) You can tell LOAD DATA or mysqlimport what kind of line endings to expect, but under UNIX it's easier to work with the file if it contains linefeeds.

After transferring the file, it's a good idea to check whether or not it has blank lines at the end. If so, you should delete them, or they'll turn into blank or malformed records when you load the file into the database.

Files saved as plain text from a spreadsheet may have quotes surrounding values that contain spaces. To strip the quotes when you load the file into your database, use the FIELDS ENCLOSED BY clause for LOAD DATA or the --fields-enclosed-by option for mysqlimport. See the entry for LOAD DATA in Appendix D for more details.