MySQL Reference Manual for version 5.0.3-alpha

• 1 General Information
  • 1.1 About This Manual
    • 1.1.1 Conventions Used in This Manual
  • 1.2 Overview of the MySQL Database Management System
    • 1.2.1 History of MySQL
    • 1.2.2 The Main Features of MySQL
    • 1.2.3 MySQL Stability
    • 1.2.4 How Big MySQL Tables Can Be
    • 1.2.5 Year 2000 Compliance
  • 1.3 MySQL Development Roadmap
    • 1.3.1 MySQL 4.0 in a Nutshell
      • 1.3.1.1 Features Available in MySQL 4.0
      • 1.3.1.2 The Embedded MySQL Server
    • 1.3.2 MySQL 4.1 in a Nutshell
      • 1.3.2.1 Features Available in MySQL 4.1
    • 1.3.3 MySQL 5.0: The Next Development Release
  • 1.4 MySQL Information Sources
    • 1.4.1 MySQL Mailing Lists
      • 1.4.1.1 The MySQL Mailing Lists
      • 1.4.1.2 Asking Questions or Reporting Bugs
      • 1.4.1.3 How to Report Bugs or Problems
      • 1.4.1.4 Guidelines for Answering Questions on the Mailing List
    • 1.4.2 MySQL Community Support on IRC (Internet Relay Chat)
    • 1.4.3 MySQL Community Support at the MySQL Forums
  • 1.5 MySQL Standards Compliance
    • 1.5.1 What Standards MySQL Follows
    • 1.5.2 Selecting SQL Modes
    • 1.5.3 Running MySQL in ANSI Mode
    • 1.5.4 MySQL Extensions to Standard SQL
    • 1.5.5 MySQL Differences from Standard SQL
      • 1.5.5.1 Subqueries
      • 1.5.5.2 SELECT INTO TABLE
      • 1.5.5.3 Transactions and Atomic Operations
      • 1.5.5.4 Stored Procedures and Triggers
      • 1.5.5.5 Foreign Keys
      • 1.5.5.6 Views
      • 1.5.5.7 `--' as the Start of a Comment
    • 1.5.6 How MySQL Deals with Constraints
      • 1.5.6.1 PRIMARY KEY and UNIQUE Index Constraints
      • 1.5.6.2 Constraints on Invalid Data
      • 1.5.6.3 ENUM and SET Constraints
    • 1.5.7 Known Errors and Design Deficiencies in MySQL
      • 1.5.7.1 Errors in 3.23 Fixed in a Later MySQL Version
      • 1.5.7.2 Errors in 4.0 Fixed in a Later MySQL Version
      • 1.5.7.3 Open Bugs and Design Deficiencies in MySQL
• 2 Installing MySQL
  • 2.1 General Installation Issues
    • 2.1.1 Operating Systems Supported by MySQL
    • 2.1.2 Choosing Which MySQL Distribution to Install
      • 2.1.2.1 Choosing Which Version of MySQL to Install
      • 2.1.2.2 Choosing a Distribution Format
      • 2.1.2.3 How and When Updates Are Released
      • 2.1.2.4 Release Philosophy--No Known Bugs in Releases
      • 2.1.2.5 MySQL Binaries Compiled by MySQL AB
    • 2.1.3 How to Get MySQL
    • 2.1.4 Verifying Package Integrity Using MD5 Checksums or GnuPG
      • 2.1.4.1 Verifying the MD5 Checksum
      • 2.1.4.2 Signature Checking Using GnuPG
      • 2.1.4.3 Signature Checking Using RPM
    • 2.1.5 Installation Layouts
  • 2.2 Standard MySQL Installation Using a Binary Distribution
  • 2.3 Installing MySQL on Windows
    • 2.3.1 Windows System Requirements
    • 2.3.2 Choosing An Installation Package
    • 2.3.3 Installing MySQL with the Automated Installer
    • 2.3.4 Using the MySQL Installation Wizard
      • 2.3.4.1 Introduction
      • 2.3.4.2 Downloading and Starting the MySQL Installation Wizard
      • 2.3.4.3 Choosing an Install Type
      • 2.3.4.4 The Custom Install Dialog
      • 2.3.4.5 The Confirmation Dialog
      • 2.3.4.6 Changes Made by MySQL Installation Wizard
      • 2.3.4.7 Upgrading MySQL
    • 2.3.5 Using the Configuration Wizard
      • 2.3.5.1 Introduction
      • 2.3.5.2 Starting the MySQL Configuration Wizard
      • 2.3.5.3 Choosing a Maintenance Option
      • 2.3.5.4 Choosing a Configuration Type
      • 2.3.5.5 The Server Type Dialog
      • 2.3.5.6 The Database Usage Dialog
      • 2.3.5.7 The InnoDB Tablespace Dialog
      • 2.3.5.8 The Concurrent Connections Dialog
      • 2.3.5.9 The Networking Options Dialog
      • 2.3.5.10 The Character Set Dialog
      • 2.3.5.11 The Service Options Dialog
      • 2.3.5.12 The Security Options Dialog
      • 2.3.5.13 The Confirmation Dialog
      • 2.3.5.14 The Location of the my.ini File
      • 2.3.5.15 Editing The my.ini File
    • 2.3.6 Installing MySQL from a noinstall Zip Archive
    • 2.3.7 Extracting the Install Archive
    • 2.3.8 Creating an Option File
    • 2.3.9 Selecting a MySQL Server type
    • 2.3.10 Starting the Server for the First Time
    • 2.3.11 Starting MySQL from the Windows Command Line
    • 2.3.12 Starting MySQL as a Windows Service
    • 2.3.13 Testing The MySQL Installation
    • 2.3.14 Troubleshooting a MySQL Installation Under Windows
    • 2.3.15 Upgrading MySQL on Windows
    • 2.3.16 MySQL on Windows Compared to MySQL on Unix
  • 2.4 Installing MySQL on Linux
  • 2.5 Installing MySQL on Mac OS X
  • 2.6 Installing MySQL on NetWare
  • 2.7 Installing MySQL on Other Unix-Like Systems
  • 2.8 MySQL Installation Using a Source Distribution
    • 2.8.1 Source Installation Overview
    • 2.8.2 Typical configure Options
    • 2.8.3 Installing from the Development Source Tree
    • 2.8.4 Dealing with Problems Compiling MySQL
    • 2.8.5 MIT-pthreads Notes
    • 2.8.6 Installing MySQL from Source on Windows
      • 2.8.6.1 Building MySQL Using VC++
      • 2.8.6.2 Creating a Windows Source Package from the Latest Development Source
    • 2.8.7 Compiling MySQL Clients on Windows
  • 2.9 Post-Installation Setup and Testing
    • 2.9.1 Windows Post-Installation Procedures
    • 2.9.2 Unix Post-Installation Procedures
      • 2.9.2.1 Problems Running mysql_install_db
      • 2.9.2.2 Starting and Stopping MySQL Automatically
      • 2.9.2.3 Starting and Troubleshooting the MySQL Server
    • 2.9.3 Securing the Initial MySQL Accounts
  • 2.10 Upgrading MySQL
    • 2.10.1 Upgrading from Version 4.1 to 5.0
    • 2.10.2 Upgrading from Version 4.0 to 4.1
    • 2.10.3 Upgrading from Version 3.23 to 4.0
    • 2.10.4 Upgrading from Version 3.22 to 3.23
    • 2.10.5 Upgrading from Version 3.21 to 3.22
    • 2.10.6 Upgrading from Version 3.20 to 3.21
    • 2.10.7 Upgrading the Grant Tables
    • 2.10.8 Copying MySQL Databases to Another Machine
  • 2.11 Downgrading MySQL
    • 2.11.1 Downgrading to 4.0
  • 2.12 Operating System-Specific Notes
    • 2.12.1 Linux Notes
      • 2.12.1.1 Linux Operating System Notes
      • 2.12.1.2 Linux Binary Distribution Notes
      • 2.12.1.3 Linux Source Distribution Notes
      • 2.12.1.4 Linux Post-Installation Notes
      • 2.12.1.5 Linux x86 Notes
      • 2.12.1.6 Linux SPARC Notes
      • 2.12.1.7 Linux Alpha Notes
      • 2.12.1.8 Linux PowerPC Notes
      • 2.12.1.9 Linux MIPS Notes
      • 2.12.1.10 Linux IA-64 Notes
    • 2.12.2 Mac OS X Notes
      • 2.12.2.1 Mac OS X 10.x (Darwin)
      • 2.12.2.2 Mac OS X Server 1.2 (Rhapsody)
    • 2.12.3 Solaris Notes
      • 2.12.3.1 Solaris 2.7/2.8 Notes
      • 2.12.3.2 Solaris x86 Notes
    • 2.12.4 BSD Notes
      • 2.12.4.1 FreeBSD Notes
      • 2.12.4.2 NetBSD Notes
      • 2.12.4.3 OpenBSD 2.5 Notes
      • 2.12.4.4 OpenBSD 2.8 Notes
      • 2.12.4.5 BSD/OS Version 2.x Notes
      • 2.12.4.6 BSD/OS Version 3.x Notes
      • 2.12.4.7 BSD/OS Version 4.x Notes
    • 2.12.5 Other Unix Notes
      • 2.12.5.1 HP-UX Version 10.20 Notes
      • 2.12.5.2 HP-UX Version 11.x Notes
      • 2.12.5.3 IBM-AIX notes
      • 2.12.5.4 SunOS 4 Notes
      • 2.12.5.5 Alpha-DEC-UNIX Notes (Tru64)
      • 2.12.5.6 Alpha-DEC-OSF/1 Notes
      • 2.12.5.7 SGI Irix Notes
      • 2.12.5.8 SCO Notes
      • 2.12.5.9 SCO UnixWare Version 7.1.x Notes
    • 2.12.6 OS/2 Notes
    • 2.12.7 BeOS Notes
  • 2.13 Perl Installation Notes
    • 2.13.1 Installing Perl on Unix
    • 2.13.2 Installing ActiveState Perl on Windows
    • 2.13.3 Problems Using the Perl DBI/DBD Interface
• 3 MySQL Tutorial
  • 3.1 Connecting to and Disconnecting from the Server
  • 3.2 Entering Queries
  • 3.3 Creating and Using a Database
    • 3.3.1 Creating and Selecting a Database
    • 3.3.2 Creating a Table
    • 3.3.3 Loading Data into a Table
    • 3.3.4 Retrieving Information from a Table
      • 3.3.4.1 Selecting All Data
      • 3.3.4.2 Selecting Particular Rows
      • 3.3.4.3 Selecting Particular Columns
      • 3.3.4.4 Sorting Rows
      • 3.3.4.5 Date Calculations
      • 3.3.4.6 Working with NULL Values
      • 3.3.4.7 Pattern Matching
      • 3.3.4.8 Counting Rows
      • 3.3.4.9 Using More Than one Table
  • 3.4 Getting Information About Databases and Tables
  • 3.5 Using mysql in Batch Mode
  • 3.6 Examples of Common Queries
    • 3.6.1 The Maximum Value for a Column
    • 3.6.2 The Row Holding the Maximum of a Certain Column
    • 3.6.3 Maximum of Column per Group
    • 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field
    • 3.6.5 Using User Variables
    • 3.6.6 Using Foreign Keys
    • 3.6.7 Searching on Two Keys
    • 3.6.8 Calculating Visits Per Day
    • 3.6.9 Using AUTO_INCREMENT
  • 3.7 Queries from the Twin Project
    • 3.7.1 Find All Non-distributed Twins
    • 3.7.2 Show a Table of Twin Pair Status
  • 3.8 Using MySQL with Apache
• 4 Using MySQL Programs
  • 4.1 Overview of MySQL Programs
  • 4.2 Invoking MySQL Programs
  • 4.3 Specifying Program Options
    • 4.3.1 Using Options on the Command Line
    • 4.3.2 Using Option Files
    • 4.3.3 Using Environment Variables to Specify Options
    • 4.3.4 Using Options to Set Program Variables
• 5 Database Administration
  • 5.1 The MySQL Server and Server Startup Scripts
    • 5.1.1 Overview of the Server-Side Scripts and Utilities
    • 5.1.2 The mysqld-max Extended MySQL Server
    • 5.1.3 The mysqld_safe Server Startup Script
    • 5.1.4 The mysql.server Server Startup Script
    • 5.1.5 The mysqld_multi Program for Managing Multiple MySQL Servers
  • 5.2 Configuring the MySQL Server
    • 5.2.1 mysqld Command-Line Options
    • 5.2.2 The Server SQL Mode
    • 5.2.3 Server System Variables
      • 5.2.3.1 Dynamic System Variables
    • 5.2.4 Server Status Variables
  • 5.3 The MySQL Server Shutdown Process
  • 5.4 General Security Issues
    • 5.4.1 General Security Guidelines
    • 5.4.2 Making MySQL Secure Against Attackers
    • 5.4.3 Startup Options for mysqld Concerning Security
    • 5.4.4 Security Issues with LOAD DATA LOCAL
  • 5.5 The MySQL Access Privilege System
    • 5.5.1 What the Privilege System Does
    • 5.5.2 How the Privilege System Works
    • 5.5.3 Privileges Provided by MySQL
    • 5.5.4 Connecting to the MySQL Server
    • 5.5.5 Access Control, Stage 1: Connection Verification
    • 5.5.6 Access Control, Stage 2: Request Verification
    • 5.5.7 When Privilege Changes Take Effect
    • 5.5.8 Causes of Access denied Errors
    • 5.5.9 Password Hashing in MySQL 4.1
      • 5.5.9.1 Implications of Password Hashing Changes for Application Programs
      • 5.5.9.2 Password Hashing in MySQL 4.1.0
  • 5.6 MySQL User Account Management
    • 5.6.1 MySQL Usernames and Passwords
    • 5.6.2 Adding New User Accounts to MySQL
    • 5.6.3 Removing User Accounts from MySQL
    • 5.6.4 Limiting Account Resources
    • 5.6.5 Assigning Account Passwords
    • 5.6.6 Keeping Your Password Secure
    • 5.6.7 Using Secure Connections
      • 5.6.7.1 Basic SSL Concepts
      • 5.6.7.2 Requirements
      • 5.6.7.3 Setting Up SSL Certificates for MySQL
      • 5.6.7.4 SSL GRANT Options
      • 5.6.7.5 SSL Command-Line Options
      • 5.6.7.6 Connecting to MySQL Remotely from Windows with SSH
  • 5.7 Disaster Prevention and Recovery
    • 5.7.1 Database Backups
    • 5.7.2 Table Maintenance and Crash Recovery
      • 5.7.2.1 myisamchk Invocation Syntax
      • 5.7.2.2 General Options for myisamchk
      • 5.7.2.3 Check Options for myisamchk
      • 5.7.2.4 Repair Options for myisamchk
      • 5.7.2.5 Other Options for myisamchk
      • 5.7.2.6 myisamchk Memory Usage
      • 5.7.2.7 Using myisamchk for Crash Recovery
      • 5.7.2.8 How to Check MyISAM Tables for Errors
      • 5.7.2.9 How to Repair Tables
      • 5.7.2.10 Table Optimization
    • 5.7.3 Setting Up a Table Maintenance Schedule
    • 5.7.4 Getting Information About a Table
  • 5.8 MySQL Localization and International Usage
    • 5.8.1 The Character Set Used for Data and Sorting
      • 5.8.1.1 Using the German Character Set
    • 5.8.2 Setting the Error Message Language
    • 5.8.3 Adding a New Character Set
    • 5.8.4 The Character Definition Arrays
    • 5.8.5 String Collating Support
    • 5.8.6 Multi-Byte Character Support
    • 5.8.7 Problems With Character Sets
    • 5.8.8 MySQL Server Time Zone Support
  • 5.9 The MySQL Log Files
    • 5.9.1 The Error Log
    • 5.9.2 The General Query Log
    • 5.9.3 The Update Log
    • 5.9.4 The Binary Log
    • 5.9.5 The Slow Query Log
    • 5.9.6 Log File Maintenance
  • 5.10 Running Multiple MySQL Servers on the Same Machine
    • 5.10.1 Running Multiple Servers on Windows
      • 5.10.1.1 Starting Multiple Windows Servers at the Command Line
      • 5.10.1.2 Starting Multiple Windows Servers as Services
    • 5.10.2 Running Multiple Servers on Unix
    • 5.10.3 Using Client Programs in a Multiple-Server Environment
  • 5.11 The MySQL Query Cache
    • 5.11.1 How the Query Cache Operates
    • 5.11.2 Query Cache SELECT Options
    • 5.11.3 Query Cache Configuration
    • 5.11.4 Query Cache Status and Maintenance
• 6 Replication in MySQL
  • 6.1 Introduction to Replication
  • 6.2 Replication Implementation Overview
  • 6.3 Replication Implementation Details
    • 6.3.1 Replication Master Thread States
    • 6.3.2 Replication Slave I/O Thread States
    • 6.3.3 Replication Slave SQL Thread States
    • 6.3.4 Replication Relay and Status Files
  • 6.4 How to Set Up Replication
  • 6.5 Replication Compatibility Between MySQL Versions
  • 6.6 Upgrading a Replication Setup
    • 6.6.1 Upgrading Replication to 4.0 or 4.1
    • 6.6.2 Upgrading Replication to 5.0
  • 6.7 Replication Features and Known Problems
  • 6.8 Replication Startup Options
  • 6.9 Replication FAQ
  • 6.10 Troubleshooting Replication
  • 6.11 Reporting Replication Bugs
• 7 MySQL Optimization
  • 7.1 Optimization Overview
    • 7.1.1 MySQL Design Limitations and Tradeoffs
    • 7.1.2 Designing Applications for Portability
    • 7.1.3 What We Have Used MySQL For
    • 7.1.4 The MySQL Benchmark Suite
    • 7.1.5 Using Your Own Benchmarks
  • 7.2 Optimizing SELECT Statements and Other Queries
    • 7.2.1 EXPLAIN Syntax (Get Information About a SELECT)
    • 7.2.2 Estimating Query Performance
    • 7.2.3 Speed of SELECT Queries
    • 7.2.4 How MySQL Optimizes WHERE Clauses
    • 7.2.5 Range Optimization
      • 7.2.5.1 Range Access Method for Single-Part Indexes
      • 7.2.5.2 Range Access Method for Multiple-Part Indexes
    • 7.2.6 Index Merge Optimization
      • 7.2.6.1 Index Merge Intersection Access Algorithm
      • 7.2.6.2 Index Merge Union Access Algorithm
      • 7.2.6.3 Index Merge Sort-Union Access Algorithm
    • 7.2.7 How MySQL Optimizes IS NULL
    • 7.2.8 How MySQL Optimizes DISTINCT
    • 7.2.9 How MySQL Optimizes LEFT JOIN and RIGHT JOIN
    • 7.2.10 How MySQL Optimizes ORDER BY
    • 7.2.11 How MySQL Optimizes GROUP BY
      • 7.2.11.1 Loose index scan
      • 7.2.11.2 Tight index scan
    • 7.2.12 How MySQL Optimizes LIMIT
    • 7.2.13 How to Avoid Table Scans
    • 7.2.14 Speed of INSERT Statements
    • 7.2.15 Speed of UPDATE Statements
    • 7.2.16 Speed of DELETE Statements
    • 7.2.17 Other Optimization Tips
  • 7.3 Locking Issues
    • 7.3.1 Locking Methods
    • 7.3.2 Table Locking Issues
  • 7.4 Optimizing Database Structure
    • 7.4.1 Design Choices
    • 7.4.2 Make Your Data as Small as Possible
    • 7.4.3 Column Indexes
    • 7.4.4 Multiple-Column Indexes
    • 7.4.5 How MySQL Uses Indexes
    • 7.4.6 The MyISAM Key Cache
      • 7.4.6.1 Shared Key Cache Access
      • 7.4.6.2 Multiple Key Caches
      • 7.4.6.3 Midpoint Insertion Strategy
      • 7.4.6.4 Index Preloading
      • 7.4.6.5 Key Cache Block Size
      • 7.4.6.6 Restructuring a Key Cache
    • 7.4.7 How MySQL Counts Open Tables
    • 7.4.8 How MySQL Opens and Closes Tables
    • 7.4.9 Drawbacks to Creating Many Tables in the Same Database
  • 7.5 Optimizing the MySQL Server
    • 7.5.1 System Factors and Startup Parameter Tuning
    • 7.5.2 Tuning Server Parameters
    • 7.5.3 Controlling Query Optimizer Performance
    • 7.5.4 How Compiling and Linking Affects the Speed of MySQL
    • 7.5.5 How MySQL Uses Memory
    • 7.5.6 How MySQL Uses DNS
  • 7.6 Disk Issues
    • 7.6.1 Using Symbolic Links
      • 7.6.1.1 Using Symbolic Links for Databases on Unix
      • 7.6.1.2 Using Symbolic Links for Tables on Unix
      • 7.6.1.3 Using Symbolic Links for Databases on Windows
• 8 MySQL Client and Utility Programs
  • 8.1 Overview of the Client-Side Scripts and Utilities
  • 8.2 myisampack, the MySQL Compressed Read-only Table Generator
  • 8.3 mysql, the Command-Line Tool
    • 8.3.1 mysql Commands
    • 8.3.2 Executing SQL Statements from a Text File
    • 8.3.3 mysql Tips
      • 8.3.3.1 Displaying Query Results Vertically
      • 8.3.3.2 Using the --safe-updates Option
      • 8.3.3.3 Disabling mysql Auto-Reconnect
  • 8.4 mysqladmin, Administering a MySQL Server
  • 8.5 The mysqlbinlog Binary Log Utility
  • 8.6 mysqlcc, the MySQL Control Center
  • 8.7 The mysqlcheck Table Maintenance and Repair Program
  • 8.8 The mysqldump Database Backup Program
  • 8.9 The mysqlhotcopy Database Backup Program
  • 8.10 The mysqlimport Data Import Program
  • 8.11 mysqlshow, Showing Databases, Tables, and Columns
  • 8.12 perror, Explaining Error Codes
  • 8.13 The replace String-Replacement Utility
• 9 Language Structure
  • 9.1 Literal Values
    • 9.1.1 Strings
    • 9.1.2 Numbers
    • 9.1.3 Hexadecimal Values
    • 9.1.4 Boolean Values
    • 9.1.5 NULL Values
  • 9.2 Database, Table, Index, Column, and Alias Names
    • 9.2.1 Identifier Qualifiers
    • 9.2.2 Identifier Case Sensitivity
  • 9.3 User Variables
  • 9.4 System Variables
    • 9.4.1 Structured System Variables
  • 9.5 Comment Syntax
  • 9.6 Treatment of Reserved Words in MySQL
• 10 Character Set Support
  • 10.1 Character Sets and Collations in General
  • 10.2 Character Sets and Collations in MySQL
  • 10.3 Determining the Default Character Set and Collation
    • 10.3.1 Server Character Set and Collation
    • 10.3.2 Database Character Set and Collation
    • 10.3.3 Table Character Set and Collation
    • 10.3.4 Column Character Set and Collation
    • 10.3.5 Examples of Character Set and Collation Assignment
    • 10.3.6 Connection Character Sets and Collations
    • 10.3.7 Character String Literal Character Set and Collation
    • 10.3.8 Using COLLATE in SQL Statements
    • 10.3.9 COLLATE Clause Precedence
    • 10.3.10 BINARY Operator
    • 10.3.11 Some Special Cases Where the Collation Determination Is Tricky
    • 10.3.12 Collations Must Be for the Right Character Set
    • 10.3.13 An Example of the Effect of Collation
  • 10.4 Operations Affected by Character Set Support
    • 10.4.1 Result Strings
    • 10.4.2 CONVERT()
    • 10.4.3 CAST()
    • 10.4.4 SHOW Statements
  • 10.5 Unicode Support
  • 10.6 UTF8 for Metadata
  • 10.7 Compatibility with Other DBMSs
  • 10.8 New Character Set Configuration File Format
  • 10.9 National Character Set
  • 10.10 Upgrading Character Sets from MySQL 4.0
    • 10.10.1 4.0 Character Sets and Corresponding 4.1 Character Set/Collation Pairs
    • 10.10.2 Converting 4.0 Character Columns to 4.1 Format
  • 10.11 Character Sets and Collations That MySQL Supports
    • 10.11.1 Unicode Character Sets
    • 10.11.2 West European Character Sets
    • 10.11.3 Central European Character Sets
    • 10.11.4 South European and Middle East Character Sets
    • 10.11.5 Baltic Character Sets
    • 10.11.6 Cyrillic Character Sets
    • 10.11.7 Asian Character Sets
• 11 Column Types
  • 11.1 Column Type Overview
    • 11.1.1 Overview of Numeric Types
    • 11.1.2 Overview of Date and Time Types
    • 11.1.3 Overview of String Types
  • 11.2 Numeric Types
  • 11.3 Date and Time Types
    • 11.3.1 The DATETIME, DATE, and TIMESTAMP Types
      • 11.3.1.1 TIMESTAMP Properties Prior to MySQL 4.1
      • 11.3.1.2 TIMESTAMP Properties as of MySQL 4.1
    • 11.3.2 The TIME Type
    • 11.3.3 The YEAR Type
    • 11.3.4 Y2K Issues and Date Types
  • 11.4 String Types
    • 11.4.1 The CHAR and VARCHAR Types
    • 11.4.2 The BINARY and VARBINARY Types
    • 11.4.3 The BLOB and TEXT Types
    • 11.4.4 The ENUM Type
    • 11.4.5 The SET Type
  • 11.5 Column Type Storage Requirements
  • 11.6 Choosing the Right Type for a Column
  • 11.7 Using Column Types from Other Database Engines
• 12 Functions and Operators
  • 12.1 Operators
    • 12.1.1 Operator Precedence
    • 12.1.2 Parentheses
    • 12.1.3 Comparison Functions and Operators
    • 12.1.4 Logical Operators
  • 12.2 Control Flow Functions
  • 12.3 String Functions
    • 12.3.1 String Comparison Functions
  • 12.4 Numeric Functions
    • 12.4.1 Arithmetic Operators
    • 12.4.2 Mathematical Functions
  • 12.5 Date and Time Functions
  • 12.6 Full-Text Search Functions
    • 12.6.1 Boolean Full-Text Searches
    • 12.6.2 Full-Text Searches with Query Expansion
    • 12.6.3 Full-Text Restrictions
    • 12.6.4 Fine-Tuning MySQL Full-Text Search
    • 12.6.5 Full-Text Search TODO
  • 12.7 Cast Functions and Operators
  • 12.8 Other Functions
    • 12.8.1 Bit Functions
    • 12.8.2 Encryption Functions
    • 12.8.3 Information Functions
    • 12.8.4 Miscellaneous Functions
  • 12.9 Functions and Modifiers for Use with GROUP BY Clauses
    • 12.9.1 GROUP BY (Aggregate) Functions
    • 12.9.2 GROUP BY Modifiers
    • 12.9.3 GROUP BY with Hidden Fields
• 13 SQL Statement Syntax
  • 13.1 Data Manipulation Statements
    • 13.1.1 DELETE Syntax
    • 13.1.2 DO Syntax
    • 13.1.3 HANDLER Syntax
    • 13.1.4 INSERT Syntax
      • 13.1.4.1 INSERT ... SELECT Syntax
      • 13.1.4.2 INSERT DELAYED Syntax
    • 13.1.5 LOAD DATA INFILE Syntax
    • 13.1.6 REPLACE Syntax
    • 13.1.7 SELECT Syntax
      • 13.1.7.1 JOIN Syntax
      • 13.1.7.2 UNION Syntax
    • 13.1.8 Subquery Syntax
      • 13.1.8.1 The Subquery as Scalar Operand
      • 13.1.8.2 Comparisons Using Subqueries
      • 13.1.8.3 Subqueries with ANY, IN, and SOME
      • 13.1.8.4 Subqueries with ALL
      • 13.1.8.5 Row Subqueries
      • 13.1.8.6 EXISTS and NOT EXISTS
      • 13.1.8.7 Correlated Subqueries
      • 13.1.8.8 Subqueries in the FROM clause
      • 13.1.8.9 Subquery Errors
      • 13.1.8.10 Optimizing Subqueries
      • 13.1.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions
    • 13.1.9 TRUNCATE Syntax
    • 13.1.10 UPDATE Syntax
  • 13.2 Data Definition Statements
    • 13.2.1 ALTER DATABASE Syntax
    • 13.2.2 ALTER TABLE Syntax
    • 13.2.3 ALTER VIEW Syntax
    • 13.2.4 CREATE DATABASE Syntax
    • 13.2.5 CREATE INDEX Syntax
    • 13.2.6 CREATE TABLE Syntax
      • 13.2.6.1 Silent Column Specification Changes
    • 13.2.7 CREATE VIEW Syntax
    • 13.2.8 DROP DATABASE Syntax
    • 13.2.9 DROP INDEX Syntax
    • 13.2.10 DROP TABLE Syntax
    • 13.2.11 DROP VIEW Syntax
    • 13.2.12 RENAME TABLE Syntax
  • 13.3 MySQL Utility Statements
    • 13.3.1 DESCRIBE Syntax (Get Information About Columns)
    • 13.3.2 USE Syntax
  • 13.4 MySQL Transactional and Locking Statements
    • 13.4.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax
    • 13.4.2 Statements That Cannot Be Rolled Back
    • 13.4.3 Statements That Cause an Implicit Commit
    • 13.4.4 SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax
    • 13.4.5 LOCK TABLES and UNLOCK TABLES Syntax
    • 13.4.6 SET TRANSACTION Syntax
  • 13.5 Database Administration Statements
    • 13.5.1 Account Management Statements
      • 13.5.1.1 DROP USER Syntax
      • 13.5.1.2 GRANT and REVOKE Syntax
      • 13.5.1.3 SET PASSWORD Syntax
    • 13.5.2 Table Maintenance Statements
      • 13.5.2.1 ANALYZE TABLE Syntax
      • 13.5.2.2 BACKUP TABLE Syntax
      • 13.5.2.3 CHECK TABLE Syntax
      • 13.5.2.4 CHECKSUM TABLE Syntax
      • 13.5.2.5 OPTIMIZE TABLE Syntax
      • 13.5.2.6 REPAIR TABLE Syntax
      • 13.5.2.7 RESTORE TABLE Syntax
    • 13.5.3 SET Syntax
    • 13.5.4 SHOW Syntax
      • 13.5.4.1 SHOW CHARACTER SET Syntax
      • 13.5.4.2 SHOW COLLATION Syntax
      • 13.5.4.3 SHOW COLUMNS Syntax
      • 13.5.4.4 SHOW CREATE DATABASE Syntax
      • 13.5.4.5 SHOW CREATE TABLE Syntax
      • 13.5.4.6 SHOW CREATE VIEW Syntax
      • 13.5.4.7 SHOW DATABASES Syntax
      • 13.5.4.8 SHOW ENGINES Syntax
      • 13.5.4.9 SHOW ERRORS Syntax
      • 13.5.4.10 SHOW GRANTS Syntax
      • 13.5.4.11 SHOW INDEX Syntax
      • 13.5.4.12 SHOW INNODB STATUS Syntax
      • 13.5.4.13 SHOW LOGS Syntax
      • 13.5.4.14 SHOW PRIVILEGES Syntax
      • 13.5.4.15 SHOW PROCESSLIST Syntax
      • 13.5.4.16 SHOW STATUS Syntax
      • 13.5.4.17 SHOW TABLE STATUS Syntax
      • 13.5.4.18 SHOW TABLES Syntax
      • 13.5.4.19 SHOW VARIABLES Syntax
      • 13.5.4.20 SHOW WARNINGS Syntax
    • 13.5.5 Other Administrative Statements
      • 13.5.5.1 CACHE INDEX Syntax
      • 13.5.5.2 FLUSH Syntax
      • 13.5.5.3 KILL Syntax
      • 13.5.5.4 LOAD INDEX INTO CACHE Syntax
      • 13.5.5.5 RESET Syntax
  • 13.6 Replication Statements
    • 13.6.1 SQL Statements for Controlling Master Servers
      • 13.6.1.1 PURGE MASTER LOGS Syntax
      • 13.6.1.2 RESET MASTER Syntax
      • 13.6.1.3 SET SQL_LOG_BIN Syntax
      • 13.6.1.4 SHOW BINLOG EVENTS Syntax
      • 13.6.1.5 SHOW MASTER LOGS Syntax
      • 13.6.1.6 SHOW MASTER STATUS Syntax
      • 13.6.1.7 SHOW SLAVE HOSTS Syntax
    • 13.6.2 SQL Statements for Controlling Slave Servers
      • 13.6.2.1 CHANGE MASTER TO Syntax
      • 13.6.2.2 LOAD DATA FROM MASTER Syntax
      • 13.6.2.3 LOAD TABLE tbl_name FROM MASTER Syntax
      • 13.6.2.4 MASTER_POS_WAIT() Syntax
      • 13.6.2.5 RESET SLAVE Syntax
      • 13.6.2.6 SET GLOBAL SQL_SLAVE_SKIP_COUNTER Syntax
      • 13.6.2.7 SHOW SLAVE STATUS Syntax
      • 13.6.2.8 START SLAVE Syntax
      • 13.6.2.9 STOP SLAVE Syntax
  • 13.7 SQL Syntax for Prepared Statements
• 14 MySQL Storage Engines and Table Types
  • 14.1 The MyISAM Storage Engine
    • 14.1.1 MyISAM Startup Options
    • 14.1.2 Space Needed for Keys
    • 14.1.3 MyISAM Table Storage Formats
      • 14.1.3.1 Static (Fixed-Length) Table Characteristics
      • 14.1.3.2 Dynamic Table Characteristics
      • 14.1.3.3 Compressed Table Characteristics
    • 14.1.4 MyISAM Table Problems
      • 14.1.4.1 Corrupted MyISAM Tables
      • 14.1.4.2 Problems from Tables Not Being Closed Properly
  • 14.2 The MERGE Storage Engine
    • 14.2.1 MERGE Table Problems
  • 14.3 The MEMORY (HEAP) Storage Engine
  • 14.4 The BDB (BerkeleyDB) Storage Engine
    • 14.4.1 Operating Systems Supported by BDB
    • 14.4.2 Installing BDB
    • 14.4.3 BDB Startup Options
    • 14.4.4 Characteristics of BDB Tables
    • 14.4.5 Things We Need to Fix for BDB
    • 14.4.6 Restrictions on BDB Tables
    • 14.4.7 Errors That May Occur When Using BDB Tables
  • 14.5 The EXAMPLE Storage Engine
  • 14.6 The FEDERATED Storage Engine
    • 14.6.1 Installing the FEDERATED Storage Engine
    • 14.6.2 Description of the FEDERATED Storage Engine
    • 14.6.3 How to use FEDERATED Tables
    • 14.6.4 Limitations of the FEDERATED Storage Engine
  • 14.7 The ARCHIVE Storage Engine
  • 14.8 The CSV Storage Engine
  • 14.9 The ISAM Storage Engine
• 15 The InnoDB Storage Engine
  • 15.1 InnoDB Overview
  • 15.2 InnoDB Contact Information
  • 15.3 InnoDB in MySQL 3.23
  • 15.4 InnoDB Configuration
  • 15.5 InnoDB Startup Options
  • 15.6 Creating the InnoDB Tablespace
    • 15.6.1 Dealing with InnoDB Initialization Problems
  • 15.7 Creating InnoDB Tables
    • 15.7.1 How to Use Transactions in InnoDB with Different APIs
    • 15.7.2 Converting MyISAM Tables to InnoDB
    • 15.7.3 How an AUTO_INCREMENT Column Works in InnoDB
    • 15.7.4 FOREIGN KEY Constraints
    • 15.7.5 InnoDB and MySQL Replication
    • 15.7.6 Using Per-Table Tablespaces
  • 15.8 Adding and Removing InnoDB Data and Log Files
  • 15.9 Backing Up and Recovering an InnoDB Database
    • 15.9.1 Forcing Recovery
    • 15.9.2 Checkpoints
  • 15.10 Moving an InnoDB Database to Another Machine
  • 15.11 InnoDB Transaction Model and Locking
    • 15.11.1 InnoDB and AUTOCOMMIT
    • 15.11.2 InnoDB and TRANSACTION ISOLATION LEVEL
    • 15.11.3 Consistent Non-Locking Read
    • 15.11.4 Locking Reads SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE
    • 15.11.5 Next-Key Locking: Avoiding the Phantom Problem
    • 15.11.6 An Example of How the Consistent Read Works in InnoDB
    • 15.11.7 Locks Set by Different SQL Statements in InnoDB
    • 15.11.8 When Does MySQL Implicitly Commit or Roll Back a Transaction?
    • 15.11.9 Deadlock Detection and Rollback
    • 15.11.10 How to Cope with Deadlocks
  • 15.12 InnoDB Performance Tuning Tips
    • 15.12.1 SHOW INNODB STATUS and the InnoDB Monitors
  • 15.13 Implementation of Multi-Versioning
  • 15.14 Table and Index Structures
    • 15.14.1 Physical Structure of an Index
    • 15.14.2 Insert Buffering
    • 15.14.3 Adaptive Hash Indexes
    • 15.14.4 Physical Record Structure
  • 15.15 File Space Management and Disk I/O
    • 15.15.1 Disk I/O
    • 15.15.2 Using Raw Devices for the Tablespace
    • 15.15.3 File Space Management
    • 15.15.4 Defragmenting a Table
  • 15.16 Error Handling
    • 15.16.1 InnoDB Error Codes
    • 15.16.2 Operating System Error Codes
  • 15.17 Restrictions on InnoDB Tables
  • 15.18 InnoDB Troubleshooting
    • 15.18.1 Troubleshooting InnoDB Data Dictionary Operations
• 16 MySQL Cluster
  • 16.1 MySQL Cluster Overview
  • 16.2 Basic MySQL Cluster Concepts
  • 16.3 MySQL Cluster Configuration
    • 16.3.1 Building from Source Code
    • 16.3.2 Installing the Software
    • 16.3.3 Quick Test Setup of MySQL Cluster
    • 16.3.4 Configuration File
      • 16.3.4.1 Example Configuration for a MySQL Cluster
      • 16.3.4.2 The MySQL Cluster connectstring
      • 16.3.4.3 Defining the Computers Making up a MySQL Cluster
      • 16.3.4.4 Defining the MySQL Cluster Management Server
      • 16.3.4.5 Defining MySQL Cluster Storage Nodes
      • 16.3.4.6 Defining the MySQL Servers for a MySQL Cluster
      • 16.3.4.7 MySQL Cluster TCP/IP Connections
      • 16.3.4.8 MySQL Cluster Shared-Memory Connections
      • 16.3.4.9 MySQL Cluster SCI Transport Connections
  • 16.4 Process Management in MySQL Cluster
    • 16.4.1 MySQL Server Process Usage for MySQL Cluster
    • 16.4.2 ndbd, the Storage Engine Node Process
    • 16.4.3 ndb_mgmd, the Management Server Process
    • 16.4.4 ndb_mgm, the Management Client Process
    • 16.4.5 Command Options for MySQL Cluster Processes
      • 16.4.5.1 MySQL Cluster-Related Command Options for mysqld
      • 16.4.5.2 Command Options for ndbd
      • 16.4.5.3 Command Options for ndb_mgmd
      • 16.4.5.4 Command Options for ndb_mgm
  • 16.5 Management of MySQL Cluster
    • 16.5.1 Commands in the Management Client
    • 16.5.2 Event Reports Generated in MySQL Cluster
      • 16.5.2.1 Logging Management Commands
      • 16.5.2.2 Log Events
    • 16.5.3 Single User Mode
    • 16.5.4 On-line Backup of MySQL Cluster
      • 16.5.4.1 Cluster Backup Concepts
      • 16.5.4.2 Using The Management Server to Create a Backup
      • 16.5.4.3 How to Restore a Cluster Backup
      • 16.5.4.4 Configuration for Cluster Backup
      • 16.5.4.5 Backup Troubleshooting
  • 16.6 Using High-Speed Interconnects with MySQL Cluster
    • 16.6.1 Configuring MySQL Cluster to use SCI Sockets
    • 16.6.2 Low-level benchmarks to understand impact of cluster interconnects
  • 16.7 MySQL Cluster Limitations in 4.1
• 17 Introduction to MaxDB
  • 17.1 History of MaxDB
  • 17.2 Licensing and Support
  • 17.3 MaxDB-Related Links
  • 17.4 Basic Concepts of MaxDB
  • 17.5 Feature Differences Between MaxDB and MySQL
  • 17.6 Interoperability Features Between MaxDB and MySQL
  • 17.7 Reserved Words in MaxDB
• 18 Spatial Extensions in MySQL
  • 18.1 Introduction
  • 18.2 The OpenGIS Geometry Model
    • 18.2.1 The Geometry Class Hierarchy
    • 18.2.2 Class Geometry
    • 18.2.3 Class Point
    • 18.2.4 Class Curve
    • 18.2.5 Class LineString
    • 18.2.6 Class Surface
    • 18.2.7 Class Polygon
    • 18.2.8 Class GeometryCollection
    • 18.2.9 Class MultiPoint
    • 18.2.10 Class MultiCurve
    • 18.2.11 Class MultiLineString
    • 18.2.12 Class MultiSurface
    • 18.2.13 Class MultiPolygon
  • 18.3 Supported Spatial Data Formats
    • 18.3.1 Well-Known Text (WKT) Format
    • 18.3.2 Well-Known Binary (WKB) Format
  • 18.4 Creating a Spatially Enabled MySQL Database
    • 18.4.1 MySQL Spatial Data Types
    • 18.4.2 Creating Spatial Values
      • 18.4.2.1 Creating Geometry Values Using WKT Functions
      • 18.4.2.2 Creating Geometry Values Using WKB Functions
      • 18.4.2.3 Creating Geometry Values Using MySQL-Specific Functions
    • 18.4.3 Creating Spatial Columns
    • 18.4.4 Populating Spatial Columns
    • 18.4.5 Fetching Spatial Data
      • 18.4.5.1 Fetching Spatial Data in Internal Format
      • 18.4.5.2 Fetching Spatial Data in WKT Format
      • 18.4.5.3 Fetching Spatial Data in WKB Format
  • 18.5 Analyzing Spatial Information
    • 18.5.1 Geometry Format Conversion Functions
    • 18.5.2 Geometry Functions
      • 18.5.2.1 General Geometry Functions
      • 18.5.2.2 Point Functions
      • 18.5.2.3 LineString Functions
      • 18.5.2.4 MultiLineString Functions
      • 18.5.2.5 Polygon Functions
      • 18.5.2.6 MultiPolygon Functions
      • 18.5.2.7 GeometryCollection Functions
    • 18.5.3 Functions That Create New Geometries from Existing Ones
      • 18.5.3.1 Geometry Functions That Produce New Geometries
      • 18.5.3.2 Spatial Operators
    • 18.5.4 Functions for Testing Spatial Relations Between Geometric Objects
    • 18.5.5 Relations on Geometry Minimal Bounding Rectangles (MBRs)
    • 18.5.6 Functions That Test Spatial Relationships Between Geometries
  • 18.6 Optimizing Spatial Analysis
    • 18.6.1 Creating Spatial Indexes
    • 18.6.2 Using a Spatial Index
  • 18.7 MySQL Conformance and Compatibility
    • 18.7.1 GIS Features That Are Not Yet Implemented
• 19 Stored Procedures and Functions
  • 19.1 Stored Procedure Syntax
    • 19.1.1 Maintaining Stored Procedures
      • 19.1.1.1 CREATE PROCEDURE and CREATE FUNCTION
      • 19.1.1.2 ALTER PROCEDURE and ALTER FUNCTION
      • 19.1.1.3 DROP PROCEDURE and DROP FUNCTION
      • 19.1.1.4 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION
    • 19.1.2 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS
    • 19.1.3 CALL Statement
    • 19.1.4 BEGIN ... END Compound Statement
    • 19.1.5 DECLARE Statement
    • 19.1.6 Variables in Stored Procedures
      • 19.1.6.1 DECLARE Local Variables
      • 19.1.6.2 Variable SET Statement
      • 19.1.6.3 SELECT ... INTO Statement
    • 19.1.7 Conditions and Handlers
      • 19.1.7.1 DECLARE Conditions
      • 19.1.7.2 DECLARE Handlers
    • 19.1.8 Cursors
      • 19.1.8.1 Declaring Cursors
      • 19.1.8.2 Cursor OPEN Statement
      • 19.1.8.3 Cursor FETCH Statement
      • 19.1.8.4 Cursor CLOSE Statement
    • 19.1.9 Flow Control Constructs
      • 19.1.9.1 IF Statement
      • 19.1.9.2 CASE Statement
      • 19.1.9.3 LOOP Statement
      • 19.1.9.4 LEAVE Statement
      • 19.1.9.5 ITERATE Statement
      • 19.1.9.6 REPEAT Statement
      • 19.1.9.7 WHILE Statement
• 20 Triggers
  • 20.1 CREATE TRIGGER Syntax
  • 20.2 DROP TRIGGER Syntax
  • 20.3 Using Triggers
• 21 MySQL APIs
  • 21.1 MySQL Program Development Utilities
    • 21.1.1 msql2mysql, Convert mSQL Programs for Use with MySQL
    • 21.1.2 mysql_config, Get compile options for compiling clients
  • 21.2 MySQL C API
    • 21.2.1 C API Data types
    • 21.2.2 C API Function Overview
    • 21.2.3 C API Function Descriptions
      • 21.2.3.1 mysql_affected_rows()
      • 21.2.3.2 mysql_change_user()
      • 21.2.3.3 mysql_character_set_name()
      • 21.2.3.4 mysql_close()
      • 21.2.3.5 mysql_connect()
      • 21.2.3.6 mysql_create_db()
      • 21.2.3.7 mysql_data_seek()
      • 21.2.3.8 mysql_debug()
      • 21.2.3.9 mysql_drop_db()
      • 21.2.3.10 mysql_dump_debug_info()
      • 21.2.3.11 mysql_eof()
      • 21.2.3.12 mysql_errno()
      • 21.2.3.13 mysql_error()
      • 21.2.3.14 mysql_escape_string()
      • 21.2.3.15 mysql_fetch_field()
      • 21.2.3.16 mysql_fetch_fields()
      • 21.2.3.17 mysql_fetch_field_direct()
      • 21.2.3.18 mysql_fetch_lengths()
      • 21.2.3.19 mysql_fetch_row()
      • 21.2.3.20 mysql_field_count()
      • 21.2.3.21 mysql_field_seek()
      • 21.2.3.22 mysql_field_tell()
      • 21.2.3.23 mysql_free_result()
      • 21.2.3.24 mysql_get_client_info()
      • 21.2.3.25 mysql_get_client_version()
      • 21.2.3.26 mysql_get_host_info()
      • 21.2.3.27 mysql_get_proto_info()
      • 21.2.3.28 mysql_get_server_info()
      • 21.2.3.29 mysql_get_server_version()
      • 21.2.3.30 mysql_hex_string()
      • 21.2.3.31 mysql_info()
      • 21.2.3.32 mysql_init()
      • 21.2.3.33 mysql_insert_id()
      • 21.2.3.34 mysql_kill()
      • 21.2.3.35 mysql_list_dbs()
      • 21.2.3.36 mysql_list_fields()
      • 21.2.3.37 mysql_list_processes()
      • 21.2.3.38 mysql_list_tables()
      • 21.2.3.39 mysql_num_fields()
      • 21.2.3.40 mysql_num_rows()
      • 21.2.3.41 mysql_options()
      • 21.2.3.42 mysql_ping()
      • 21.2.3.43 mysql_query()
      • 21.2.3.44 mysql_real_connect()
      • 21.2.3.45 mysql_real_escape_string()
      • 21.2.3.46 mysql_real_query()
      • 21.2.3.47 mysql_reload()
      • 21.2.3.48 mysql_row_seek()
      • 21.2.3.49 mysql_row_tell()
      • 21.2.3.50 mysql_select_db()
      • 21.2.3.51 mysql_set_server_option()
      • 21.2.3.52 mysql_shutdown()
      • 21.2.3.53 mysql_sqlstate()
      • 21.2.3.54 mysql_ssl_set()
      • 21.2.3.55 mysql_stat()
      • 21.2.3.56 mysql_store_result()
      • 21.2.3.57 mysql_thread_id()
      • 21.2.3.58 mysql_use_result()
      • 21.2.3.59 mysql_warning_count()