1.32 Using mysql as a Calculator
You need a quick way to evaluate an expression.
Use mysql as a calculator. MySQL doesn't require every SELECT statement to refer to a table, so you can select the results of arbitrary expressions.
SELECT statements typically refer to some table or tables from which you're retrieving rows. However, in MySQL, SELECT need not reference any table at all, which means that you can use the mysql program as a calculator for evaluating an expression:
mysql> SELECT (17 + 23) / SQRT(64); +----------------------+ (17 + 23) / SQRT(64) +----------------------+ 5.00000000 +----------------------+
This is also useful for checking how a comparison works. For example, to determine whether or not string comparisons are case sensitive, try the following query:
mysql> SELECT 'ABC' = 'abc'; +---------------+ 'ABC' = 'abc' +---------------+ 1 +---------------+
The result of this comparison is 1 (meaning "true"; in general, nonzero values are true). This
mysql> SELECT 'ABC' = 'abcd'; +----------------+ 'ABC' = 'abcd' +----------------+ 0 +----------------+
If the value of an expression cannot be determined, the result is NULL :
mysql> SELECT 1/0; +------+ 1/0 +------+ NULL +------+
SQL variables may be used to store the results of intermediate calculations. The following statements use
mysql> SET @daily_room_charge = 100.00; mysql> SET @num_of_nights = 3; mysql> SET @tax_percent = 8; mysql> SET @total_room_charge = @daily_room_charge * @num_of_nights; mysql> SET @tax = (@total_room_charge * @tax_percent) / 100; mysql> SET @total = @total_room_charge + @tax; mysql> SELECT @total; +--------+ @total +--------+ 324 +--------+
1.33 Using mysql in Shell Scripts
You want to invoke mysql from within a shell script rather than using it interactively.
There's no rule against that. Just be sure to supply the appropriate arguments to the command.
If you need to process query results within a program, you'll typically use a MySQL programming interface designed
For Unix shell scripting, I recommend that you stick to
1.33.4 Writing Shell Scripts Under Unix
Here is a shell script that
#! /bin/sh # mysql_uptime.sh - report server uptime in seconds mysql -B -N -e "SHOW STATUS LIKE 'Uptime'"
The first line of the script that begins with
is special. It indicates the pathname of the program that should be invoked to execute the rest of the script,
in this case. To use the script, create a file named
that contains the
% ./mysql_uptime.sh Uptime 1260142
The command shown here begins with
, indicating that the script is located in your current directory. If you move the script to a directory named in your
setting, you can invoke it from
% ./mysql_uptime.sh Uptime 1260348 % mv mysql_uptime.sh /usr/local/bin % mysql_uptime.sh mysql_uptime.sh: Command not found. % rehash % mysql_uptime.sh Uptime 1260397
If you prefer a report that lists the time in days, hours, minutes, and seconds rather than just seconds, you can use the output from the mysql STATUS statement, which provides the following information:
mysql> STATUS; Connection id: 12347 Current database: cookbook Current user: cbuser@localhost Current pager: stdout Using outfile: '' Server version: 3.23.47-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 14 days 14 hours 2 min 46 sec
For uptime reporting, the only relevant part of that information is the line that begins with Uptime . It's a simple matter to write a script that sends a STATUS command to the server and filters the output with grep to extract the desired line:
#! /bin/sh # mysql_uptime2.sh - report server uptime mysql -e STATUS grep "^Uptime"
The result looks like this:
% ./mysql_uptime2.sh Uptime: 14 days 14 hours 2 min 46 sec
The preceding two scripts specify the statement to be executed by means of the -e command-line option, but you can use other mysql input sources described earlier in the chapter, such as files and pipes. For example, the following mysql_uptime3.sh script is like mysql_uptime2.sh but provides input to mysql using a pipe:
#! /bin/sh # mysql_uptime3.sh - report server uptime echo STATUS mysql grep "^Uptime"
Some shells support the concept of a "here-document," which serves
command << MARKER input line 1 input line 2 input line 3 ... MARKER
signals the beginning of the input and indicates the marker symbol to look for at the end of the input. The symbol that you use for
is relatively arbitrary, but should be some
Here-documents are a useful alternative to the
option when you need to specify lengthy query input. In such cases, when
SELECT COUNT(*) As 'New log entries:' FROM log_tbl WHERE date_added = DATE_SUB(CURDATE( ),INTERVAL 1 DAY);
That query could be specified in a script using -e , but the command line would be difficult to read because the query is so long. A here-document is a more suitable choice in this case because you can write the query in more readable form:
#! /bin/sh # new_log_entries.sh - count yesterday's log entries mysql cookbook <<MYSQL_INPUT SELECT COUNT(*) As 'New log entries:' FROM log_tbl WHERE date_added = DATE_SUB(CURDATE( ),INTERVAL 1 DAY); MYSQL_INPUT
When you use
or here-documents, you can refer to shell
#! /bin/sh # count_rows.sh - count rows in cookbook database table # require one argument on the command line if [ $# -ne 1 ]; then echo "Usage: count_rows.sh tbl_name"; exit 1; fi # use argument () in the query string mysql cookbook <<MYSQL_INPUT SELECT COUNT(*) AS 'Rows in table:' FROM ; MYSQL_INPUT
The script uses the $# shell variable, which holds the command-line argument count, and $1 , which holds the first argument after the script name. count_rows.sh makes sure that exactly one argument was provided, then uses it as a table name in a row-counting query. To run the script, invoke it with a table name argument:
% ./count_rows.sh limbs Rows in table: 12
Variable substitution can be helpful for constructing queries, but you should use this capability with caution. A malicious user could invoke the script as
% ./count_rows.sh "limbs;DROP TABLE limbs"
In that case, the resulting query input to mysql becomes:
SELECT COUNT(*) AS 'Rows in table:' FROM limbs;DROP TABLE limbs;
This input counts the table rows, then destroys the table! For this reason, it may be prudent to limit use of variable substitution to your own private scripts. Alternatively, rewrite the script using an API that allows special
1.33.5 Writing Shell Scripts Under Windows
Under Windows, you can run mysql from within a batch file (a file with a .bat extension). Here is a Windows batch file, mysql_uptime.bat , that is similar to the mysql_uptime.sh Unix shell script shown earlier:
@ECHO OFF REM mysql_uptime.bat - report server uptime in seconds mysql -B -N -e "SHOW STATUS LIKE 'Uptime'"
Batch files may be invoked without the .bat extension:
C:\> mysql_uptime Uptime 9609
DOS scripting has some serious limitations, however. For example, here-documents are not supported, and command argument quoting capabilities are more limited. One way around these problems is to install a more reasonable working environment; see the sidebar "Finding the DOS Prompt Restrictive?"