Database Reference
In-Depth Information
%
mysql cookbook <
inputfile
| perl csv.pl >
outputfile
tr
and
sed
normally are unavailable under Windows. Perl may be more suitable as a
cross-platform solution because it runs under both Unix and Windows. (On Unix sys‐
tems, Perl is usually preinstalled. On Windows, it is freely available for you to install.)
Another way to produce CSV output is to use the Perl Text::CSV_XS module, which
was designed for that purpose.
Recipe 11.5
discusses this module and uses it to construct
a general-purpose file reformatter.
Controlling mysql's verbosity level
When you run
mysql
noninteractively, not only does the default output format change,
but it becomes more terse. For example,
mysql
doesn't print row counts or indicate how
long statements took to execute. To tell
mysql
to be more verbose, use
-v
or
--
verbose
, specifying the option multiple times for increasing verbosity. Try the following
commands to see how the output differs:
%
echo "SELECT NOW()" | mysql
%
echo "SELECT NOW()" | mysql -v
%
echo "SELECT NOW()" | mysql -vv
%
echo "SELECT NOW()" | mysql -vvv
The counterparts of
-v
and
--verbose
are
-s
and
--silent
, which also can be used
multiple times for increased effect.
1.8. Using User-Defined Variables in SQL Statements
Problem
You want to use a value in one statement that is produced by an earlier statement.
Solution
Save the value in a user-defined variable to store it for later use.
Discussion
To save a value returned by a
SELECT
statement, assign it to a user-defined variable. This
enables you to refer to it in other statements later in the same session (but not
across
sessions). User variables are a MySQL-specific extension to standard SQL. They will not
work with other database engines.
To assign a value to a user variable within a
SELECT
statement, use
@
var_name
:=
val
ue
syntax. The variable can be used in subsequent statements wherever an expression
is permitted, such as in a
WHERE
clause or in an
INSERT
statement.
