By George Reese
Price: $14.99 USD
£9.50 GBP

Cover | Table of Contents

Table of Contents

Chapter 1: MySQL Pocket Reference

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When I fly across the country, I often pass the hours programming on my PowerBook. If that programming involves MySQL, I inevitably end up lugging around the book I co-wrote, Managing and Using MySQL (O'Reilly). I don't carry around the book to show it off; the problem is that no matter how experienced you are with MySQL, you never know when you will need to look up the exact syntax of an obscure function or SQL statement.

The MySQL Pocket Reference is a quick reference that you can take with you anywhere you go. Instead of racking your brain for the exact syntax of a variant of ALTER TABLE that you generally never use, you can reach into your laptop case and grab this reference. As an experienced MySQL architect, administrator, or programmer, you can look to this reference.

This book does not, however, teach MySQL. I expect that you have learned or are in the process of learning MySQL from a book such as Managing and Using MySQL. Though I start with a reference on MySQL setup, it is designed to help you remember the full process of MySQL configuration—not to teach you the process.

I first would like to thank my editor Andy Oram, as always, for helping me along. I would also like to thank the book's strong technical reviewers, Paul Dubois, Judith Myerson, and Tim Allwine. Finally, I would like to thank my co-authors for Managing and Using MySQL, Tim King and Randy Jay Yarger, who helped set the foundation that made this pocket reference possible and necessary.

The following conventions are used in this book:

Constant width

Used to indicate anything that might appear in a program, including keywords, function names, SQL commands, and variable names. This font is also used for code examples, output displayed by commands, and system configuration files.

Constant width bold

Used to indicate user input.

Constant width italic

Used to indicate an element (e.g., a filename or variable) that you supply.

Italic

Used to indicate directory names, filenames, program names, Unix commands, and URLs. This font is also used to introduce new terms and for emphasis.

If you have been using MySQL for a while, you really don't need to learn a thing about MySQL 5 to keep going. Everything you are used to using still works just as it always has. For the most part, MySQL 5 is about adding enterprise database features seen in other database engines without burdening MySQL with concepts that make it harder to learn and use.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When I fly across the country, I often pass the hours programming on my PowerBook. If that programming involves MySQL, I inevitably end up lugging around the book I co-wrote, Managing and Using MySQL (O'Reilly). I don't carry around the book to show it off; the problem is that no matter how experienced you are with MySQL, you never know when you will need to look up the exact syntax of an obscure function or SQL statement.

The MySQL Pocket Reference is a quick reference that you can take with you anywhere you go. Instead of racking your brain for the exact syntax of a variant of ALTER TABLE that you generally never use, you can reach into your laptop case and grab this reference. As an experienced MySQL architect, administrator, or programmer, you can look to this reference.

This book does not, however, teach MySQL. I expect that you have learned or are in the process of learning MySQL from a book such as Managing and Using MySQL. Though I start with a reference on MySQL setup, it is designed to help you remember the full process of MySQL configuration—not to teach you the process.

I first would like to thank my editor Andy Oram, as always, for helping me along. I would also like to thank the book's strong technical reviewers, Paul Dubois, Judith Myerson, and Tim Allwine. Finally, I would like to thank my co-authors for Managing and Using MySQL, Tim King and Randy Jay Yarger, who helped set the foundation that made this pocket reference possible and necessary.

The following conventions are used in this book:

Constant width

Used to indicate anything that might appear in a program, including keywords, function names, SQL commands, and variable names. This font is also used for code examples, output displayed by commands, and system configuration files.

Constant width bold

Used to indicate user input.

Constant width italic

Used to indicate an element (e.g., a filename or variable) that you supply.

Italic

Used to indicate directory names, filenames, program names, Unix commands, and URLs. This font is also used to introduce new terms and for emphasis.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

MySQL 5

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you have been using MySQL for a while, you really don't need to learn a thing about MySQL 5 to keep going. Everything you are used to using still works just as it always has. For the most part, MySQL 5 is about adding enterprise database features seen in other database engines without burdening MySQL with concepts that make it harder to learn and use.

Views are denormalized, table-like structures that represent a snapshot of your data that match specific query parameters. You can thus represent as data from a single table the result of a complex join. New commands supporting views include CREATE VIEW, DROP VIEW, and ALTER VIEW.

A database trigger is functionality that you create that gets executed whenever a specific event occurs on a table. For example, you can trigger behavior for a table whenever a new row is inserted. New commands supporting triggers include CREATE TRIGGER and DROP TRIGGER.

Stored procedures are the big feature most people have been waiting for. A stored procedure is much like creating a function that is written entirely in SQL and stored in the database. Stored procedures are useful for encapsulating a number of SQL statements that always get executed together under a single logical name for use by clients. MySQL includes a number of new commands to support stored procedures:

• CREATE PROCEDURE
• ALTER PROCEDURE
• DROP PROCEDURE
• CALL
• BEGIN/END

A cursor is a tool that enables you to represent an entire data set within a MySQL stored procedure. MySQL cursors are limited in that they are asensitive (a quality affecting their response to changes in the table), nonscrolling (cursors must be used sequentially, moving forward), and read-only. New commands supporting cursors include OPEN, FETCH, and CLOSE.

The most common storage engines (also known as table types) in MySQL are MyISAM and InnoDB. But a number of new ones were added in recent versions of MySQL:

ARCHIVE

Offers fast stores and selects without indexes, but no updates or deletions.

BLACKHOLE

Discards data; used to support replication.

CSV

Stores data in a comma-separated values format commonly used for plain text data exchange.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Setup

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can install MySQL by compiling the source code with the options that best suit your needs, or by downloading and installing a prebuilt binary. In general, you'll want to use the package management system (such as the BSD ports system) appropriate to your operating system. You can also find both binary and source code at the MySQL web site, .

Before installing using either approach, you need to prepare your operating system for MySQL. Specifically, you should create a mysql user and group under which MySQL will run.

MySQL AB changes the download process somewhat frequently, so the exact process of downloading MySQL may vary from the details described here. MySQL comes in standard and debug packages. When in doubt, get the standard package. It is generally what you will want for a production server.

If you are having runtime problems with your MySQL environment, you can test your application against a Debug install to get detailed debug information on your MySQL operation. You do not want to use the Debug package for any production environment.

The MySQL download page also provides a variety of additional tools, including test suites, client utilities, libraries, and header files. These tools are not essential to getting MySQL up and running, though they may be necessary for programming on a machine without a MySQL server installation or just to make life easier.

MySQL has three different kinds of configuration, both for the server process at server startup and for the client processes when a user executes them. In order of preference, these configuration options include:

1. Command-line options
2. Configuration file options
3. Environment variable options

In other words, if you have the password option specified on the command line, in your configuration file, and in an environment variable, the command-line option wins. shows a list of configuration options. Each option applies to one or more MySQL tools, depending on the context.

Table : MySQL configuration options

Option	Description
basedir=directory	Specifies the root directory of your MySQL installation.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Command-Line Tools

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can interact with MySQL entirely from the command line. In general, each MySQL command accepts as an argument any appropriate option from the configuration options listed earlier. You prefix any such option with two dashes:

mysql --user=username

In addition, each of these options has a short form:

mysql -uusername

To see which options apply to individual commands and their short forms, refer to the manpage for the command in question using the following command:

$ man -M/usr/local/mysql/man mysql

MySQL provides the following command-line tools:

msql2mysql

This utility is handy for people converting applications written for mSQL to MySQL. These days, however, few people need this help.

myisamchk

This tool verifies the integrity of your MyISAM tables and potentially fixes any problems that it detects.

mysql

The MySQL interactive SQL interpreter. It enables you to execute SQL on the command line. You can span your SQL across any number of lines. The tool executes your SQL when you terminate it with a semicolon or the escape sequence \g.

mysql_upgrade

After you install a new version of MySQL, you can run this utility to examine your tables and make sure they are consistent with your new version of MySQL. You should run this command each time you upgrade MySQL.

mysqladmin

The MySQL administrative interface. Though many of this tool's functions can be accomplished using SQL and the mysql command-line utility, it nevertheless provides a quick way to perform an administrative task straight from the Unix command line without entering an SQL interpreter. You can specifically execute the following administrative commands:

create databasename

Creates the specified database.

drop databasename

The opposite of create, this command destroys the specified database.

extended-status

Provides an extended status message from the server.

flush-hosts

Flushes all cached hosts.

flush-logs

Flushes all logs.

flush-status

Flushes all status variables.

flush-tables

Flushes all tables.

flush-threads

Flushes the thread cache.

flush-privileges

Forces MySQL to reload all grant tables.

kill id[,id]

Kills the specified MySQL threads.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Data Types

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

For each data type, the syntax shown uses square brackets ([]) to indicate optional parts of the syntax. The following example shows how BIGINT is explained in this chapter:

BIGINT[(display_size)]

This indicates that you can use BIGINT alone or with a display size value. The italics indicate that you do not enter display_size literally, but instead enter your own value. Possible uses of BIGINT include:

BIGINT
BIGINT(20)

In addition to the BIGINT type, many other MySQL data types support the specification of a display size. Unless otherwise specified, this value must be an integer between 1 and 255.

Before MySQL 5, MySQL would silently change column values in certain circumstances. As of MySQL 5, these silent changes no longer happen.

VARCHAR → CHAR

When the specified VARCHAR column size is less than four characters, it is converted to CHAR.

CHAR → VARCHAR

When a table has at least one column of a variable length, all CHAR columns greater than three characters in length are converted to VARCHAR.

TIMESTAMP display sizes

Display sizes for TIMESTAMP fields must be an even value between 2 and 14. A display size of 0 or greater than 14 converts the field to a display size of 14. An odd-valued display size is converted to the next highest even value. MySQL 5 no longer takes a size value for timestamps.

MySQL supports all ANSI SQL2 numeric data types. MySQL numeric types break down into integer, decimal, and floating point types. Within each group, the types differ by the amount of storage required for them.

Numeric types allow you to specify a display size, which affects the way MySQL displays results. The display size bears no relation to the internal storage provided by each data type. In addition, the decimal and floating point types allow you to optionally specify the number of digits that follow the decimal point. In such cases, the digits value should be an integer from 0 to 30 that is at most two less than the display size. If you do make the digits value greater than two less than the display size, the display size will automatically change to two more than the digits value. For instance, MySQL automatically changes

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

SQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL fully supports ANSI SQL 92, entry level. A SQL reference for MySQL is thus largely a general SQL reference. Nevertheless, MySQL contains some proprietary enhancements that can help you at the mysql command line. This section thus provides a reference for the SQL query language as it is supported in MySQL.

SQL is a kind of controlled English language consisting of verb phrases. Each of these verb phrases begins with an SQL command followed by other SQL keywords, literals, identifiers, or punctuation.

Case-sensitivity in MySQL depends on a variety of factors, including the token in question and the underlying operating system. shows the case-sensitivity of different SQL tokens in MySQL.

Table : The case-sensitivity of MySQL

Token type	Case-sensitivity
Keywords	Case-insensitive.
Identifiers (databases and tables)	Dependent on the case-sensitivity for the underlying operating system. On all Unix systems except Mac OS X using HFS+, database and table names are case-sensitive. On Mac OS X using HFS+ and Windows, they are case-insensitive.
Table aliases	Case-sensitive.
Column aliases	Case-insensitive.

Literals come in the following varieties:

String

String literals may be enclosed either by single or double quotes. If you wish to be ANSI compatible, you should always use single quotes. Within a string literal, you may represent special characters through escape sequences. An escape sequence is a backslash followed by another character to indicate to MySQL that the second character has a meaning other than its normal meaning. shows the MySQL escape sequences. Quotes within a string can be escaped with doubled apostrophes:

'This is a ''quote'''

Table : MySQL escape sequences

Escape sequence	Value
\0	NUL
\'	Single quote
\"	Double quote
\b	Backspace
\n	Newline
\r	Carriage return
\t	Tab
\z	Ctrl-z (workaround for Windows use of Ctrl-z as EOF)
\\	Backslash
\%	Percent sign (only in contexts where a percent sign would be interpreted as a wildcard)
\_	Underscore (only in contexts where an underscore would be interpreted as a wildcard)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Operators

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL offers three kinds of operators: arithmetic, comparison, and logical.

When your SQL contains complex expressions, the sub-expressions are evaluated based on MySQL's rules of precedence. Of course, you may always override MySQL's rules of precedence by enclosing an expression in parentheses.

• BINARY, COLLATE
• !
• - (unary minus) ~ (unary bit inversion)
• ^
• * / % DIV MOD
• + -
• << >>
• &
• |
• < <= > >= = <=> <> IN IS LIKE REGEXP
• BETWEEN CASE WHEN THEN ELSE
• NOT
• && AND
• || OR XOR
• :=

Arithmetic operators perform basic arithmetic on two values.

+

Adds two numerical values.

-

Subtracts two numerical values.

*

Multiplies two numerical values.

/

Divides two numerical values.

DIV

Integer division.

%

Gives the modulo of two numerical values.

|

Performs a bitwise OR on two integer values.

^

Performs a bitwise exclusive OR on two integer values.

&

Performs a bitwise AND on two integer values.

<<

Performs a bitwise left shift on an integer value.

>>

Performs a bitwise right shift on an integer value.

Comparison operators compare values and return 1 if the comparison is true and 0 otherwise. Except for the <=> operator, NULL values cause a comparison operator to evaluate to NULL.

<> or !=

Match rows if the two values are not equal.

<=

Match rows if the left value is less than or equal to the right value.

<

Match rows if the left value is less than the right value.

>=

Match rows if the left value is greater than or equal to the right value.

>

Match rows if the left value is greater than the right value.

value BETWEEN value1 AND value2

Match rows if value is between value1 and value2, or equal to one of them.

value NOT BETWEEN value1 AND value2

Match rows if value is not between value1 and value2.

value IN (value1,value2,...)

Match rows if value is among the values listed.

value NOT IN (value1, value2,...)

Match rows if value is not among the values listed.

value1 LIKE value2

Compares value1 to value2 and matches the rows if they match. The righthand value can contain the wildcard '%', which matches any number of characters (including 0), and '_', which matches exactly one character. Its most common use is comparing a field value with a literal containing a wildcard (e.g.,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Functions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL provides built-in functions that perform special operations.

Aggregate functions operate on a set of data. These are usually used to perform some action on a complete set of returned rows. For example, SELECT AVG(height) FROM kids would return the average of all the values of the height field in the kids table. AVG( ), COUNT( ), and SUM( ) allow DISTINCT.

AVG(expression)

Returns the average value of the values in expression (e.g., SELECT AVG(score) FROM tests).

BIT_AND(expression)

Returns the bitwise AND aggregate of all the values in expression (e.g., SELECT BIT_AND(flags) FROM options). A bit will be set in the result if and only if the bit is set in every input field.

BIT_OR(expression)

Returns the bitwise OR aggregate of all the values in expression (e.g., SELECT BIT_OR(flags) FROM options). A bit is set in the result if it is set in at least one of the input fields.

BIT_XOR(expression)

Returns the bitwise XOR aggregate of all the values in expression with 64-bit precision.

COUNT(expression)

Returns the number of times expression was not null. COUNT(*) will return the number of rows with some data in the entire table (e.g., SELECT COUNT(*) FROM folders).

GROUP_CONCAT([DISTNCT] expression [ORDER BY {column | expression}] [SEPARATOR sep])

Provides a string that combines in order all the results marked by expression.

MAX(expression)

Returns the largest value in expression (e.g., SELECT MAX (elevation) FROM mountains).

MIN(expression)

Returns the smallest value in expression (e.g., SELECT MIN(level) FROM toxic_waste).

STDDEV_POP(expression)

Returns the standard deviation of the values in expression (e.g., SELECT STDDEV_POP(points) FROM data). Also supported are the old MySQL STD( ) and the Oracle STDDEV( ) function, which both use the same syntax but are not portable. STDDEV_POP( ) is new standard SQL, provided as of MySQL 5.0.3.

STDDEV_SAMP([expression])

Returns the sample standard deviation of expression.

SUM(expression)

Returns the sum of the values in expression (e.g., SELECT SUM(calories) FROM daily_diet).

VAR_POP(expression)

Returns the population standard variance of expression

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Storage Engines

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Table 5 lists some of the table types supported in most MySQL installations. For truly atomic database transactions, you should use InnoDB tables. New transactional storage engines are being introduced at the time of this writing, however.

Table : MySQL table types

Type	Transactional	Description
ARCHIVE	No	Used for archiving databases without indexes in a very small footprint.
BLACKHOLE	No	Stores no data at all. All queries return no rows.
CSV	No	Stores data in comma-separated files.
FALCON	Yes	New experimental, transactional storage engine to potentially replace InnoDB in a future release.
INNODB	Yes	Transaction-safe tables with row locking.
MEMORY (formerly HEAP)	No	Memory-based table; not persistent.
MERGE	No	A collection of MyISAM tables merged as a single table.
MYISAM	No	A newer, portable table type to replace ISAM.
NDB	Yes	Clustered storage engine for MySQL Cluster.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Stored Procedures and Functions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Stored routines are encapsulated SQL components that are stored in the database for reuse in your database applications. MySQL supports two kinds of stored routines: procedures and functions. They behave very similarly except for three key differences:

• Functions accept only IN parameters; procedures can accept IN, OUT, and INOUT parameters.
• Functions return a value; procedures return values via OUT or INOUT parameters.
• Functions may be called in a query just like MySQL functions or user-defined functions; procedures are called independently via the CALL command.

The CREATE PROCEDURE/CREATE FUNCTION command creates a stored procedure. You must have CREATE ROUTINE privileges in order to create any stored procedure in MySQL. You must define a name and a body for the procedure:

CREATE PROCEDURE sitecount( )  SELECT COUNT(*) FROM web_site;

You may subsequently call the procedure using the CALL command:

CALL sitecount(  );

MySQL supports three kinds of stored procedure parameters:

The parameter is passed into the procedure as input. The procedure can then operate on the parameter values. By default, a parameter is an IN parameter. Stored functions can accept only IN parameters.

OUT

An output value is stored in the parameter for use by the caller of the stored procedure.

INOUT

The caller passes into the procedure a value for the INOUT parameter and any changes made by the procedure then become available to the caller after the procedure is executed.

Parameters are specified in the procedure definition as a comma-separated list of parameters:

CREATE PROCEDURE sitecount(OUT total INT) SELECT COUNT(*) INTO total FROM web_site;
CREATE PROCEDURE ssl_port(IN addr VARCHAR(255), OUT total INT)
SELECT ssl_port INTO total FROM web_site WHERE address = addr;

For each parameter, you may specify what kind of parameter it is, the name of the parameter, and the SQL type that it should store.

Pass parameters as a comma-separated list of values in the call:

CALL sitecount(@total);
SELECT @total;

CALL ssl_port('www.valtira.com', @port);
SELECT @port;

In additional to the simple logic described in the previous section, MySQL stored procedures allow complex application logic to be stored in the database. To perform complex logic, wrap the SQL in a

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Triggers

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Like a stored procedure, a trigger is processing logic stored in the database. Where a stored procedure executes in response to a specific application request, a trigger executes whenever a particular database event occurs. For any given event, you can define a trigger to execute BEFORE or AFTER the event. The events on which you can build a trigger are:

INSERT

The trigger will execute whenever a row is inserted into the database.

UPDATE

The trigger will execute whenever a row is updated in the database.

DELETE

The trigger will execute whenever a row is deleted from the database.

Trigger definition works much like stored procedure definition in that your logic can be made up of compound SQL nestled inside a BEGIN/END block. The main difference is that your logic is also bounded by a FOR EACH ROW section:

DELIMITER //
CREATE TRIGGER zap_addresses AFTER DELETE ON web_site
FOR EACH ROW BEGIN
DELETE FROM web_address WHERE web_site_id = OLD.web_site_id;
END;

The special identifiers NEW and OLD reference the new and old row values, respectively.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Return to MySQL Pocket Reference