Библиотека RTFM Начало / Базы данных / MySQL / MySQL Reference Manual 5.0.0 (eng) www.mysql.org MySQL Reference Manual for version 5.0.0-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 Overview of MySQL AB 1.3.1 The Business Model and Services of MySQL AB 1.3.1.1 Support 1.3.1.2 Training and Certification 1.3.1.3 Consulting 1.3.1.4 Commercial Licenses 1.3.1.5 Partnering 1.3.2 Contact Information 1.4 MySQL Support and Licensing 1.4.1 Support Offered by MySQL AB 1.4.2 Copyrights and Licenses Used by MySQL 1.4.3 MySQL Licenses 1.4.3.1 Using the MySQL Software Under a Commercial License 1.4.3.2 Using the MySQL Software for Free Under GPL 1.4.4 MySQL AB Logos and Trademarks 1.4.4.1 The Original MySQL Logo 1.4.4.2 MySQL Logos that may be Used Without Written Permission 1.4.4.3 When You Need Written Permission to Use MySQL Logos 1.4.4.4 MySQL AB Partnership Logos 1.4.4.5 Using the Word MySQL in Printed Text or Presentations 1.4.4.6 Using the Word MySQL in Company and Product Names 1.5 MySQL Development Roadmap 1.5.1 MySQL 4.0 in a Nutshell 1.5.1.1 Features Available in MySQL 4.0 1.5.1.2 The Embedded MySQL Server 1.5.2 MySQL 4.1 in a Nutshell 1.5.2.1 Features Available in MySQL 4.1 1.5.2.2 Stepwise Rollout 1.5.2.3 Ready for Immediate Development Use 1.5.3 MySQL 5.0, the Next Development Release 1.6 MySQL and the Future (The TODO) 1.6.1 New Features Planned for 4.1 1.6.2 New Features Planned for 5.0 1.6.3 New Features Planned for 5.1 1.6.4 New Features Planned for the Near Future 1.6.5 New Features Planned for the Mid-Term Future 1.6.6 New Features We Don't Plan to Implement 1.7 MySQL Information Sources 1.7.1 MySQL Mailing Lists 1.7.1.1 The MySQL Mailing Lists 1.7.1.2 Asking Questions or Reporting Bugs 1.7.1.3 How to Report Bugs or Problems 1.7.1.4 Guidelines for Answering Questions on the Mailing List 1.7.2 MySQL Community Support on IRC (Internet Relay Chat) 1.8 MySQL Standards Compliance 1.8.1 What Standards MySQL Follows 1.8.2 Selecting SQL Modes 1.8.3 Running MySQL in ANSI Mode 1.8.4 MySQL Extensions to the SQL-92 Standard 1.8.5 MySQL Differences Compared to SQL-92 1.8.5.1 Subqueries 1.8.5.2 SELECT INTO TABLE 1.8.5.3 Transactions and Atomic Operations 1.8.5.4 Stored Procedures and Triggers 1.8.5.5 Foreign Keys 1.8.5.6 Views 1.8.5.7 `--' as the Start of a Comment 1.8.6 How MySQL Deals with Constraints 1.8.6.1 Constraint PRIMARY KEY / UNIQUE 1.8.6.2 Constraint NOT NULL and DEFAULT values 1.8.6.3 Constraint ENUM and SET 1.8.7 Known Errors and Design Deficiencies in MySQL 1.8.7.1 Errors in 3.23 Fixed in a Later MySQL Version 1.8.7.2 Errors in 4.0 Fixed in a Later MySQL Version 1.8.7.3 Open Bugs / 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.8 Installation Layouts 2.2 Standard MySQL Installation Using a Binary Distribution 2.2.1 Installing MySQL on Windows 2.2.1.1 Windows System Requirements 2.2.1.2 Installing a Windows Binary Distribution 2.2.1.3 Preparing the Windows MySQL Environment 2.2.1.4 Selecting a Windows Server 2.2.1.5 Starting the Server for the First Time 2.2.1.6 Starting MySQL from the Windows Command Line 2.2.1.7 Starting MySQL as a Windows Service 2.2.1.8 Running MySQL Client Programs on Windows 2.2.1.9 MySQL on Windows Compared to MySQL on Unix 2.2.2 Installing MySQL on Linux 2.2.3 Installing MySQL on Mac OS X 2.2.4 Installing MySQL on NetWare 2.2.5 Installing MySQL on Other Unix-like Systems 2.3 MySQL Installation Using a Source Distribution 2.3.1 Quick Source Installation Overview 2.3.2 Typical configure Options 2.3.3 Installing from the Development Source Tree 2.3.4 Dealing With Problems Compiling MySQL 2.3.5 MIT-pthreads Notes 2.3.6 Installing MySQL from Source on Windows 2.3.6.1 Building MySQL Using VC++ 2.3.6.2 Creating a Windows Source Package from the Latest Development Source 2.3.7 Compiling MySQL Clients on Windows 2.4 Post-installation Setup and Testing 2.4.1 Windows Post-installation Procedures 2.4.2 Unix Post-installation Procedures 2.4.2.1 Problems Running mysql_install_db 2.4.2.2 Starting and Stopping MySQL Automatically 2.4.2.3 Starting and Troubleshooting the MySQL Server 2.5 Upgrading/Downgrading MySQL 2.5.1 Upgrading from Version 4.1 to 5.0 2.5.2 Upgrading from Version 4.0 to 4.1 2.5.3 Upgrading from Version 3.23 to 4.0 2.5.4 Upgrading from Version 3.22 to 3.23 2.5.5 Upgrading from Version 3.21 to 3.22 2.5.6 Upgrading from Version 3.20 to 3.21 2.5.7 Upgrading MySQL under Windows 2.5.8 Upgrading the Grant Tables 2.5.9 Copying MySQL Databases to Another Machine 2.6 Operating System Specific Notes 2.6.1 Linux Notes 2.6.1.1 Linux Operating System Notes 2.6.1.2 Linux Binary Distribution Notes 2.6.1.3 Linux Source Distribution Notes 2.6.1.4 Linux Post-installation Notes 2.6.1.5 Linux x86 Notes 2.6.1.6 Linux SPARC Notes 2.6.1.7 Linux Alpha Notes 2.6.1.8 Linux PowerPC Notes 2.6.1.9 Linux MIPS Notes 2.6.1.10 Linux IA-64 Notes 2.6.2 Mac OS X Notes 2.6.2.1 Mac OS X 10.x (Darwin) 2.6.2.2 Mac OS X Server 1.2 (Rhapsody) 2.6.3 Solaris Notes 2.6.3.1 Solaris 2.7/2.8 Notes 2.6.3.2 Solaris x86 Notes 2.6.4 BSD Notes 2.6.4.1 FreeBSD Notes 2.6.4.2 NetBSD Notes 2.6.4.3 OpenBSD 2.5 Notes 2.6.4.4 OpenBSD 2.8 Notes 2.6.4.5 BSD/OS Version 2.x Notes 2.6.4.6 BSD/OS Version 3.x Notes 2.6.4.7 BSD/OS Version 4.x Notes 2.6.5 Other Unix Notes 2.6.5.1 HP-UX Version 10.20 Notes 2.6.5.2 HP-UX Version 11.x Notes 2.6.5.3 IBM-AIX notes 2.6.5.4 SunOS 4 Notes 2.6.5.5 Alpha-DEC-UNIX Notes (Tru64) 2.6.5.6 Alpha-DEC-OSF/1 Notes 2.6.5.7 SGI Irix Notes 2.6.5.8 SCO Notes 2.6.5.9 SCO UnixWare Version 7.1.x Notes 2.6.6 OS/2 Notes 2.6.7 BeOS Notes 2.7 Perl Installation Notes 2.7.1 Installing Perl on Unix 2.7.2 Installing ActiveState Perl on Windows 2.7.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 mysqld 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 MySQL 5.2.1 mysqld Command-line Options 5.2.2 The Server SQL Mode 5.3 General Security Issues 5.3.1 General Security Guidelines 5.3.2 Making MySQL Secure Against Attackers 5.3.3 Startup Options for mysqld Concerning Security 5.3.4 Security Issues with LOAD DATA LOCAL 5.4 The MySQL Access Privilege System 5.4.1 What the Privilege System Does 5.4.2 How the Privilege System Works 5.4.3 Privileges Provided by MySQL 5.4.4 Connecting to the MySQL Server 5.4.5 Access Control, Stage 1: Connection Verification 5.4.6 Access Control, Stage 2: Request Verification 5.4.7 When Privilege Changes Take Effect 5.4.8 Causes of Access denied Errors 5.4.9 Password Hashing in MySQL 4.1 5.4.9.1 Implications of Password Hashing Changes for Application Programs 5.4.9.2 Password Hashing in MySQL 4.1.0 5.5 MySQL User Account Management 5.5.1 MySQL Usernames and Passwords 5.5.2 Securing the Initial MySQL Accounts 5.5.3 Adding New User Accounts to MySQL 5.5.4 Removing User Accounts from MySQL 5.5.5 Limiting Account Resources 5.5.6 Assigning Account Passwords 5.5.7 Keeping Your Password Secure 5.5.8 Using Secure Connections 5.5.8.1 Basic SSL Concepts 5.5.8.2 Requirements 5.5.8.3 Setting Up SSL Certificates for MySQL 5.5.8.4 SSL GRANT Options 5.5.8.5 SSL Command-line Options 5.5.8.6 Connecting to MySQL Remotely from Windows with SSH 5.6 Disaster Prevention and Recovery 5.6.1 Database Backups 5.6.2 Using myisamchk for Table Maintenance and Crash Recovery 5.6.2.1 myisamchk Invocation Syntax 5.6.2.2 General Options for myisamchk 5.6.2.3 Check Options for myisamchk 5.6.2.4 Repair Options for myisamchk 5.6.2.5 Other Options for myisamchk 5.6.2.6 myisamchk Memory Usage 5.6.2.7 Using myisamchk for Crash Recovery 5.6.2.8 How to Check MyISAM Tables for Errors 5.6.2.9 How to Repair Tables 5.6.2.10 Table Optimization 5.6.3 Setting Up a Table Maintenance Regimen 5.6.4 Getting Information About a Table 5.7 MySQL Localization and International Usage 5.7.1 The Character Set Used for Data and Sorting 5.7.1.1 Using the German Character Set 5.7.2 Setting the Error Message Language 5.7.3 Adding a New Character Set 5.7.4 The Character Definition Arrays 5.7.5 String Collating Support 5.7.6 Multi-byte Character Support 5.7.7 Problems With Character Sets 5.8 The MySQL Log Files 5.8.1 The Error Log 5.8.2 The General Query Log 5.8.3 The Update Log 5.8.4 The Binary Log 5.8.5 The Slow Query Log 5.8.6 Log File Maintenance 5.9 Running Multiple MySQL Servers on the Same Machine 5.9.1 Running Multiple Servers on Windows 5.9.1.1 Starting Multiple Windows Servers at the Command Line 5.9.1.2 Starting Multiple Windows Servers as Services 5.9.2 Running Multiple Servers on Unix 5.9.3 Using Client Programs in a Multiple-Server Environment 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 How MySQL Optimizes OR Clauses 7.2.6 How MySQL Optimizes IS NULL 7.2.7 How MySQL Optimizes DISTINCT 7.2.8 How MySQL Optimizes LEFT JOIN and RIGHT JOIN 7.2.9 How MySQL Optimizes ORDER BY 7.2.10 How MySQL Optimizes LIMIT 7.2.11 Speed of INSERT Queries 7.2.12 Speed of UPDATE Queries 7.2.13 Speed of DELETE Queries 7.2.14 Other Optimization Tips 7.3 Locking Issues 7.3.1 How MySQL Locks Tables 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 Large Numbers of Tables in the Same Database 7.5 Optimizing the MySQL Server 7.5.1 System/Compile Time and Startup Parameter Tuning 7.5.2 Tuning Server Parameters 7.5.3 How Compiling and Linking Affects the Speed of MySQL 7.5.4 How MySQL Uses Memory 7.5.5 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 MySQL Language Reference 10 Language Structure 10.1 Literal Values 10.1.1 Strings 10.1.2 Numbers 10.1.3 Hexadecimal Values 10.1.4 Boolean Values 10.1.5 NULL Values 10.2 Database, Table, Index, Column, and Alias Names 10.2.1 Identifier Qualifiers 10.2.2 Identifier Case Sensitivity 10.3 User Variables 10.4 System Variables 10.4.1 Dynamic System Variables 10.4.2 Structured System Variables 10.5 Comment Syntax 10.6 Treatment of Reserved Words in MySQL 11 Column Types 11.1 Numeric Types 11.2 Date and Time Types 11.2.1 Y2K Issues and Date Types 11.2.2 The DATETIME, DATE, and TIMESTAMP Types 11.2.3 The TIME Type 11.2.4 The YEAR Type 11.3 String Types 11.3.1 The CHAR and VARCHAR Types 11.3.2 The BLOB and TEXT Types 11.3.3 The ENUM Type 11.3.4 The SET Type 11.4 Choosing the Right Type for a Column 11.5 Using Column Types from Other Database Engines 11.6 Column Type Storage Requirements 12 Functions and Operators 12.1 Non-Type-Specific Operators and Functions 12.1.1 Parentheses 12.1.2 Comparison Operators 12.1.3 Logical Operators 12.1.4 Control Flow Functions 12.2 String Functions 12.2.1 String Comparison Functions 12.2.2 Case-Sensitivity 12.3 Numeric Functions 12.3.1 Arithmetic Operations 12.3.2 Mathematical Functions 12.4 Date and Time Functions 12.5 Cast Functions 12.6 Other Functions 12.6.1 Bit Functions 12.6.2 Encryption Functions 12.6.3 Information Functions 12.6.4 Miscellaneous Functions 12.7 Functions and Modifiers for Use with GROUP BY Clauses 12.7.1 GROUP BY Functions 12.7.2 GROUP BY Modifiers 12.7.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 Correlated Subqueries 13.1.8.6 EXISTS and NOT EXISTS 13.1.8.7 Row 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 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 CREATE DATABASE Syntax 13.2.4 CREATE INDEX Syntax 13.2.5 CREATE TABLE Syntax 13.2.5.1 Silent Column Specification Changes 13.2.6 DROP DATABASE Syntax 13.2.7 DROP INDEX Syntax 13.2.8 DROP TABLE Syntax 13.2.9 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 and SHOW Syntax 13.5.3.1 SET Syntax 13.5.3.2 SHOW COLUMNS Syntax 13.5.3.3 SHOW CREATE DATABASE Syntax 13.5.3.4 SHOW CREATE TABLE Syntax 13.5.3.5 SHOW DATABASES Syntax 13.5.3.6 SHOW GRANTS Syntax 13.5.3.7 SHOW INDEX Syntax 13.5.3.8 SHOW LOGS Syntax 13.5.3.9 SHOW PRIVILEGES Syntax 13.5.3.10 SHOW PROCESSLIST Syntax 13.5.3.11 SHOW STATUS Syntax 13.5.3.12 SHOW TABLE STATUS Syntax 13.5.3.13 SHOW TABLE TYPES Syntax 13.5.3.14 SHOW TABLES Syntax 13.5.3.15 SHOW VARIABLES Syntax 13.5.3.16 SHOW WARNINGS | ERRORS Syntax 13.5.4 Other Administrative Statements 13.5.4.1 CACHE INDEX Syntax 13.5.4.2 FLUSH Syntax 13.5.4.3 KILL Syntax 13.5.4.4 LOAD INDEX INTO CACHE Syntax 13.5.4.5 RESET Syntax 13.6 Replication Statements 13.6.1 SQL Statements for Controlling Master Servers 13.6.1.1 PURGE MASTER LOGS 13.6.1.2 RESET MASTER 13.6.1.3 SET SQL_LOG_BIN 13.6.1.4 SHOW BINLOG EVENTS 13.6.1.5 SHOW MASTER STATUS 13.6.1.6 SHOW MASTER LOGS 13.6.1.7 SHOW SLAVE HOSTS 13.6.2 SQL Statements for Controlling Slave Servers 13.6.2.1 CHANGE MASTER TO 13.6.2.2 LOAD DATA FROM MASTER 13.6.2.3 LOAD TABLE tbl_name FROM MASTER 13.6.2.4 MASTER_POS_WAIT() 13.6.2.5 RESET SLAVE 13.6.2.6 SET GLOBAL SQL_SLAVE_SKIP_COUNTER 13.6.2.7 SHOW SLAVE STATUS 13.6.2.8 START SLAVE 13.6.2.9 STOP SLAVE 13.7 MySQL Full-text Search 13.7.1 Full-text Restrictions 13.7.2 Fine-tuning MySQL Full-text Search 13.7.3 Full-text Search TODO 13.8 MySQL Query Cache 13.8.1 How the Query Cache Operates 13.8.2 Query Cache Configuration 13.8.3 Query Cache Options in SELECT 13.8.4 Query Cache Status and Maintenance 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 ISAM Storage Engine 15 The InnoDB Storage Engine 15.1 InnoDB Tables Overview 15.2 InnoDB Contact Information 15.3 InnoDB in MySQL Version 3.23 15.4 InnoDB Startup Options 15.5 Creating InnoDB Tablespace 15.5.1 If Something Goes Wrong in Database Creation 15.6 Creating InnoDB Tables 15.6.1 How to Use Transactions in InnoDB with Different APIs 15.6.2 Converting MyISAM Tables to InnoDB 15.6.3 How an AUTO_INCREMENT Column Works in InnoDB 15.6.4 FOREIGN KEY Constraints 15.6.5 InnoDB and MySQL Replication 15.6.6 Multiple tablespaces - putting each table into its own `.ibd' file 15.7 Adding and Removing InnoDB Data and Log Files 15.8 Backing up and Recovering an InnoDB Database 15.8.1 Forcing recovery 15.8.2 Checkpoints 15.9 Moving an InnoDB Database to Another Machine 15.10 InnoDB Transaction Model and Locking 15.10.1 InnoDB and AUTOCOMMIT 15.10.2 InnoDB and TRANSACTION ISOLATION LEVEL 15.10.3 Consistent Non-Locking Read 15.10.4 Locking Reads SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE 15.10.5 Next-key Locking: Avoiding the Phantom Problem 15.10.6 Locks Set by Different SQL Statements in InnoDB 15.10.7 When Does MySQL Implicitly Commit or Rollback a Transaction? 15.10.8 Deadlock Detection and Rollback 15.10.9 An Example of How the Consistent Read Works in InnoDB 15.10.10 How to Cope With Deadlocks 15.11 Performance Tuning Tips 15.11.1 SHOW INNODB STATUS and the InnoDB Monitors 15.12 Implementation of Multi-Versioning 15.13 Table and Index Structures 15.13.1 Physical Structure of an Index 15.13.2 Insert Buffering 15.13.3 Adaptive Hash Indexes 15.13.4 Physical Record Structure 15.14 File Space Management and Disk I/O 15.14.1 Disk I/O 15.14.2 Using Raw Devices for Tablespace 15.14.3 File Space Management 15.14.4 Defragmenting a Table 15.15 Error Handling 15.15.1 InnoDB Error Codes 15.15.2 Operating System error codes 15.16 Restrictions on InnoDB Tables 15.17 InnoDB Troubleshooting 15.17.1 Troubleshooting InnoDB Data Dictionary Operations 16 Introduction to MaxDB 16.1 History of MaxDB 16.2 Licensing and Support 16.3 Basic Concepts of MaxDB 16.4 Feature Differences between MaxDB and MySQL 16.5 Interoperability Features between MaxDB and MySQL 16.6 MaxDB-related Links 16.7 Reserved Words in MaxDB 16.8 Functions 16.9 Column Types 17 National Character Sets and Unicode 17.1 Character Sets and Collations in General 17.2 Character Sets and Collations in MySQL 17.3 Determining the Default Character Set and Collation 17.3.1 Server Character Set and Collation 17.3.2 Database Character Set and Collation 17.3.3 Table Character Set and Collation 17.3.4 Column Character Set and Collation 17.3.5 Examples of Character Set and Collation Assignment 17.3.6 Connection Character Sets and Collations 17.3.7 Character String Literal Character Set and Collation 17.3.8 COLLATE Clause in Various Parts of an SQL Query 17.3.9 COLLATE Clause Precedence 17.3.10 BINARY Operator 17.3.11 Some Special Cases Where the Collation Determination is Tricky 17.3.12 Collations Must Be for the Right Character Set 17.3.13 An example of the Effect of Collation 17.4 Operations Affected by Character Set Support 17.4.1 Result Strings 17.4.2 CONVERT() 17.4.3 CAST() 17.4.4 SHOW CHARACTER SET 17.4.5 SHOW COLLATION 17.4.6 SHOW CREATE DATABASE 17.4.7 SHOW FULL COLUMNS 17.5 Unicode Support 17.6 UTF8 for Metadata 17.7 Compatibility with Other DBMSs 17.8 New Character Set Configuration File format 17.9 National Character Set 17.10 Upgrading from MySQL 4.0 17.10.1 4.0 Character Sets and Corresponding 4.1 Character Set/Collation Pairs 17.11 The Character Sets and Collations that MySQL Supports 17.11.1 The Unicode Character Sets 17.11.2 Platform Specific Character Sets 17.11.3 Character Sets for South Europe and Middle East 17.11.4 The Asian Character Sets 17.11.5 The Baltic Character Sets 17.11.6 The Cyrillic Character Sets 17.11.7 The Central European Character Sets 17.11.8 The West European Character Sets 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.4 Class Point 18.2.7 Class Curve 18.2.9 Class LineString 18.2.12 Class Surface 18.2.14 Class Polygon 18.2.17 Class GeometryCollection 18.2.18 Class MultiPoint 18.2.21 Class MultiCurve 18.2.23 Class MultiLineString 18.2.25 Class MultiSurface 18.2.27 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 Datatypes 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 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 MySQL APIs 20.1 MySQL Program Development Utilities 20.1.1 msql2mysql, Convert mSQL Programs for Use with MySQL 20.1.2 mysql_config, Get compile options for compiling clients 20.2 MySQL C API 20.2.1 C API Datatypes 20.2.2 C API Function Overview 20.2.3 C API Function Descriptions 20.2.3.1 mysql_affected_rows() 20.2.3.2 mysql_change_user() 20.2.3.3 mysql_character_set_name() 20.2.3.4 mysql_close() 20.2.3.5 mysql_connect() 20.2.3.6 mysql_create_db() 20.2.3.7 mysql_data_seek() 20.2.3.8 mysql_debug() 20.2.3.9 mysql_drop_db() 20.2.3.10 mysql_dump_debug_info() 20.2.3.11 mysql_eof() 20.2.3.12 mysql_errno() 20.2.3.13 mysql_error() 20.2.3.14 mysql_escape_string() 20.2.3.15 mysql_fetch_field() 20.2.3.16 mysql_fetch_fields() 20.2.3.17 mysql_fetch_field_direct() 20.2.3.18 mysql_fetch_lengths() 20.2.3.19 mysql_fetch_row() 20.2.3.20 mysql_field_count() 20.2.3.21 mysql_field_seek() 20.2.3.22 mysql_field_tell() 20.2.3.23 mysql_free_result() 20.2.3.24 mysql_get_client_info() 20.2.3.25 mysql_get_client_version() 20.2.3.26 mysql_get_host_info() 20.2.3.27 mysql_get_proto_info() 20.2.3.28 mysql_get_server_info() 20.2.3.29 mysql_get_server_version() 20.2.3.30 mysql_info() 20.2.3.31 mysql_init() 20.2.3.32 mysql_insert_id() 20.2.3.33 mysql_kill() 20.2.3.34 mysql_list_dbs() 20.2.3.35 mysql_list_fields() 20.2.3.36 mysql_list_processes() 20.2.3.37 mysql_list_tables() 20.2.3.38 mysql_num_fields() 20.2.3.39 mysql_num_rows() 20.2.3.40 mysql_options() 20.2.3.41 mysql_ping() 20.2.3.42 mysql_query() 20.2.3.43 mysql_real_connect() 20.2.3.44 mysql_real_escape_string() 20.2.3.45 mysql_real_query() 20.2.3.46 mysql_reload() 20.2.3.47 mysql_row_seek() 20.2.3.48 mysql_row_tell() 20.2.3.49 mysql_select_db() 20.2.3.50 mysql_set_server_option() 20.2.3.51 mysql_shutdown() 20.2.3.52 mysql_sqlstate() 20.2.3.53 mysql_ssl_set() 20.2.3.54 mysql_stat() 20.2.3.55 mysql_store_result() 20.2.3.56 mysql_thread_id() 20.2.3.57 mysql_use_result() 20.2.3.58 mysql_warning_count() 20.2.3.59 mysql_commit() 20.2.3.60 mysql_rollback() 20.2.3.61 mysql_autocommit() 20.2.3.62 mysql_more_results() 20.2.3.63 mysql_next_result() 20.2.4 C API Prepared Statements 20.2.5 C API Prepared Statement Datatypes 20.2.6 C API Prepared Statement Function Overview 20.2.7 C API Prepared Statement Function Descriptions 20.2.7.1 mysql_stmt_init() 20.2.7.2 mysql_stmt_bind_param() 20.2.7.3 mysql_stmt_bind_result() 20.2.7.4 mysql_stmt_execute() 20.2.7.5 mysql_stmt_fetch() 20.2.7.6 mysql_stmt_fetch_column() 20.2.7.7 mysql_stmt_result_metadata() 20.2.7.8 mysql_stmt_param_count() 20.2.7.9 mysql_stmt_param_metadata() 20.2.7.10 mysql_stmt_prepare() 20.2.7.11 mysql_stmt_send_long_data() 20.2.7.12 mysql_stmt_affected_rows() 20.2.7.13 mysql_stmt_close() 20.2.7.14 mysql_stmt_data_seek() 20.2.7.15 mysql_stmt_errno() 20.2.7.16 mysql_stmt_error() 20.2.7.17 mysql_stmt_free_result() 20.2.7.18 mysql_stmt_num_rows() 20.2.7.19 mysql_stmt_reset() 20.2.7.20 mysql_stmt_row_seek() 20.2.7.21 mysql_stmt_row_tell() 20.2.7.22 mysql_stmt_sqlstate() 20.2.7.23 mysql_stmt_store_result() 20.2.8 C API Handling of Multiple Query Execution 20.2.9 C API Handling of Date and Time Values 20.2.10 C API Threaded Function Descriptions 20.2.10.1 my_init() 20.2.10.2 mysql_thread_init() 20.2.10.3 mysql_thread_end() 20.2.10.4 mysql_thread_safe() 20.2.11 C API Embedded Server Function Descriptions 20.2.11.1 mysql_server_init() 20.2.11.2 mysql_server_end() 20.2.12 Common questions and problems when using the C API 20.2.12.1 Why mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success 20.2.12.2 What Results You Can Get from a Query 20.2.12.3 How to Get the Unique ID for the Last Inserted Row 20.2.12.4 Problems Linking with the C API 20.2.13 Building Client Programs 20.2.14 How to Make a Threaded Client 20.2.15 libmysqld, the Embedded MySQL Server Library 20.2.15.1 Overview of the Embedded MySQL Server Library 20.2.15.2 Compiling Programs with libmysqld 20.2.15.3 Restrictions when using the Embedded MySQL Server 20.2.15.4 Using Option Files with the Embedded Server 20.2.15.5 Things left to do in Embedded Server (TODO) 20.2.15.6 A Simple Embedded Server Example 20.2.15.7 Licensing the Embedded Server 20.3 MySQL ODBC Support 20.3.1 How to Install MyODBC 20.3.2 How to Fill in the Various Fields in the ODBC Administrator Program 20.3.3 Connect parameters for MyODBC 20.3.4 How to Report Problems with MyODBC 20.3.5 Programs Known to Work with MyODBC 20.3.6 How to Get the Value of an AUTO_INCREMENT Column in ODBC 20.3.7 Reporting Problems with MyODBC 20.4 MySQL Java Connectivity (JDBC) 20.5 MySQL PHP API 20.5.1 Common Problems with MySQL and PHP 20.6 MySQL Perl API 20.7 MySQL C++ API 20.7.1 Borland C++ 20.8 MySQL Python API 20.9 MySQL Tcl API 20.10 MySQL Eiffel Wrapper 21 Error Handling in MySQL 21.1 Error Returns 21.2 Error Messages 22 Extending MySQL 22.1 MySQL Internals 22.1.1 MySQL Threads 22.1.2 MySQL Test Suite 22.1.2.1 Running the MySQL Test Suite 22.1.2.2 Extending the MySQL Test Suite 22.1.2.3 Reporting Bugs in the MySQL Test Suite 22.2 Adding New Functions to MySQL 22.2.1 CREATE FUNCTION/DROP FUNCTION Syntax 22.2.2 Adding a New User-defined Function 22.2.2.1 UDF Calling Sequences for simple functions 22.2.2.2 UDF Calling Sequences for aggregate functions 22.2.2.3 Argument Processing 22.2.2.4 Return Values and Error Handling 22.2.2.5 Compiling and Installing User-defined Functions 22.2.3 Adding a New Native Function 22.3 Adding New Procedures to MySQL 22.3.1 Procedure Analyse 22.3.2 Writing a Procedure A Problems and Common Errors A.1 How to Determine What Is Causing Problems A.2 Common Errors When Using MySQL A.2.1 Access denied Error A.2.2 MySQL server has gone away Error A.2.3 Can't connect to [local] MySQL server Error A.2.4 Client does not support authentication protocol error A.2.5 Password Fails When Entered Interactively A.2.6 Host '...' is blocked Error A.2.7 Too many connections Error A.2.8 Some non-transactional changed tables couldn't be rolled back Error A.2.9 Out of memory Error