MySQL
is easy to learn and use, and comes with extensive documentation—two factors
that have contributed a great deal to its popularity. However, given the speed
at which the MySQL code base evolves, even veteran MySQL users are sometimes
pleasantly surprised to discover hitherto-unexpected capabilities. This
document looks under the hood to introduce you to some of these less well-known
capabilities.

Note: The tips
discussed below apply to MySQL
4.1.x and/or MySQL 5.0.x
.

View query results in XML format

You can have MySQL return query results to you in
well-formed XML (instead of the traditional tabular format), by
invoking the MySQL command-line client with the additional –xml option. This
is useful if you plan to integrate the query output with some other
application. Here’s an example:

Listing A

shell> mysql –xml

mysql> SELECT * FROM test.stories;
<?xml version=”1.0″?>

<resultset statement=”SELECT * FROM test.stories”>
  <row>
        <id>1</id>
        <headline>This is a test</headline>
        <tstamp>2005-07-28 00:14:57</tstamp>
  </row>

  <row>
        <id>2</id>
        <headline>This is the second test</headline>
        <tstamp>2005-07-28 00:15:11</tstamp>
  </row>
</resultset>
2 rows in set (0.11 sec)

Quickly rebuild indexes

Normally, every time you make changes to the server’s full-text
search variables, you need to rebuild the full-text indexes on your tables to
ensure that your changes are reflected. This can take a fair amount of time,
especially if you have a large volume of data. A quicker alternative is to use
the REPAIR TABLE command.
Here’s how:

Listing B

mysql> REPAIR TABLE content QUICK;
+———–+——–+———-+———-+
| Table     | Op     | Msg_type | Msg_text |
+———–+——–+———-+———-+
| content   | repair | status   | OK       |
+———–+——–+———-+———-+
1 row in set (0.05 sec)

Compress certain table types

If you have read-only MyISAM
tables, MySQL lets you compress them to save disk space. Use the included myisampack utility for
this, as illustrated below:

Listing C

shell> myisampackmovies.MYI
Compressing movies.MYD: (146 records)
– Calculating statistics
– Compressing file
41.05%

Use conditional SQL

MySQL supports the use of conditional tests within an SQL
query. Both IF and CASE structures are supported, and they can
come in handy if you need your query results to change depending on the results
of a conditional test. Here is a simple example:

Listing D

mysql> SELECT IF (priv=1, ‘admin’, ‘guest’) As usertype FROM privs WHERE username = ‘joe’;
+———-+
| usertype |
+———-+
| admin    |
+———-+
1 row in set (0.00 sec)

Dump table data in CSV format

Normally, a MySQL dump file contains a complete list of the
SQL commands needed to recreate the table. This is very handy if you intend to
import the dump file back into MySQL at a later date, but isn’t such a great
idea if your target application (for example, Excel) doesn’t understand SQL. In
such situations, you can save yourself some time by telling MySQL to create
dump files in CSV format, which can be easily imported into most applications.
Here’s how to do it using mysqldump:

shell> mysqldump -T . –fields-terminated-by=”, ” mydbmytable

This will produce a text file in the current directory,
containing the records from the table mydb.mytable in
comma-delimited format.

Reduce the incidence of “bad” data by activating strict mode

The MySQL server can be run in a number of different
“modes”, each one optimized for a particular purpose. By default, no
modes are set; however, it’s easy to alter this and run MySQL in “strict
mode” by adding the following option to the server command line:

shell> mysqld –sql_mode=”STRICT_ALL_TABLES” &

In strict mode, many of the server’s automatic corrections
are disabled, with MySQL aborting query execution and returning an error instead
of internally correcting the bad input. Stricter date checking is also
implemented in this mode.

Note: This mode
is only available in MySQL 5.0.2 and better

Monitor the server

You can obtain a report of server activity and
statistics—the number of open connections, the number of active queries, the
server uptime, and so on—by issuing a SHOW
STATUS

command. Here’s an example:

Listing E

mysql> SHOW STATUS;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| Aborted_clients  | 0     |
| Aborted_connects | 0     |

| Uptime           | 851   |
+——————+——-+
156 rows in set (0.16 sec)

Automatically retrieve CREATE TABLE code

MySQL lets you automatically obtain the SQL commands to
re-create a particular table. Simply issue the SHOW CREATE TABLE command to see the table
creation code, as in this example:

Listing F

mysql> SHOW CREATE TABLE products;
—————————————————–
| Table    | Create Table
+———-+—————————————–
| products | CREATE TABLE `products` (
  `id` int(8) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default ”,
  `price` int(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+———-+—————————————–
1 row in set (0.27 sec)

Create a more useful command prompt

By default, the MySQL command-line client displays a simple mysql> prompt. However, you can use special
modifiers to alter this prompt and make it more useful, by including the name
of the currently-logged in user, the host name, and the currently-selected
database. Here’s how:

Listing G

mysql> prompt \U:/\d>
PROMPT set to ‘\U:/\d>’
root@localhost:/db1>

You can obtain a complete list of modifiers supported by the
MySQL client from the documentation
files
.