Table of Contents
1. General Information.
About This Guide.
Conventions Used in This Manual.
Overview of the MySQL Database Management System.
History of MySQL.
The Main Features of MySQL.
MySQL Stability.
How Big MySQL Tables Can Be.
Year 2000 Compliance.
Overview of MySQL AB.
The Business Model and Services of MySQL AB.
Contact Information.
MySQL Support and Licensing.
Support Offered by MySQL AB.
Copyrights and Licenses Used by MySQL.
MySQL Licenses.
MySQL AB Logos and Trademarks.
MySQL Development Roadmap.
MySQL 4.0 in a Nutshell.
MySQL 4.1 in a Nutshell.
MySQL 5.0: The Next Development Release.
MySQL and the Future (the TODO).
New Features Planned for 4.1.
New Features Planned for 5.0.
New Features Planned for 5.1.
New Features Planned for the Near Future.
New Features Planned for the Mid-Term Future.
New Features We Don't Plan to Implement.
MySQL Information Sources.
MySQL Mailing Lists.
MySQL Community Support on IRC (Internet Relay Chat).
MySQL Standards Compliance.
What Standards MySQL Follows.
Selecting SQL Modes.
Running MySQL in ANSI Mode.
MySQL Extensions to Standard SQL.
MySQL Differences from Standard SQL.
How MySQL Deals with Constraints.
Known Errors and Design Deficiencies in MySQL.
2. Language Structure.
Literal Values.
Strings.
Numbers.
Hexadecimal Values.
Boolean Values.
NULL Values.
Database, Table, Index, Column, and Alias Names.
Identifier Qualifiers.
Identifier Case Sensitivity.
User Variables.
System Variables.
Structured System Variables.
Comment Syntax.
Treatment of Reserved Words in MySQL.
3. Character Set Support.
Character Sets and Collations in General.
Character Sets and Collations in MySQL.
Determining the Default Character Set and Collation.
Server Character Set and Collation.
Database Character Set and Collation.
Table Character Set and Collation.
Column Character Set and Collation.
Examples of Character Set and Collation Assignment.
Connection Character Sets and Collations.
Character String Literal Character Set and Collation.
Using COLLATE in SQL Statements.
COLLATE Clause Precedence.
BINARY Operator.
Some Special Cases Where the Collation
Determination Is Tricky.
Collations Must Be for the Right Character Set.
An Example of the Effect of Collation.
Operations Affected by Character Set Support.
Result Strings.
CONVERT().
CAST().
SHOW Statements.
Unicode Support.
UTF8 for Metadata.
Compatibility with Other DBMSs.
New Character Set Configuration File Format.
National Character Set.
Upgrading Character Sets from MySQL 4.0.
4.0 Character Sets and Corresponding 4.1 Character Set/Collation Pairs.
Converting 4.0 Character Columns to 4.1 Format.
Character Sets and Collations That MySQL Supports.
Unicode Character Sets.
West European Character Sets.
Central European Character Sets.
South European and Middle East Character Sets.
Baltic Character Sets.
Cyrillic Character Sets.
Asian Character Sets.
4. Column Types.
Column Type Overview.
Overview of Numeric Types.
Overview of Date and Time Types.
Overview of String Types.
Numeric Types.
Date and Time Types.
The DATETIME, DATE, and TIMESTAMP Types.
The TIME Type.
The YEAR Type.
Y2K Issues and Date Types.
String Types.
The CHAR and VARCHAR Types.
The BLOB and TEXT Types.
The ENUM Type.
The SET Type.
Column Type Storage Requirements.
Storage Requirements for Numeric Types.
Storage Requirements for Date and Time Types.
Storage Requirements for String Types.
Choosing the Right Type for a Column.
Using Column Types from Other Database Engines.
5. Functions and Operators.
Operators.
Parentheses.
Comparison Operators.
Logical Operators.
Case-Sensitivity Operators.
Control Flow Functions.
String Functions.
String Comparison Functions.
Numeric Functions.
Arithmetic Operators.
Mathematical Functions.
Date and Time Functions.
Full-Text Search Functions.
Boolean Full-Text Searches.
Full-Text Searches with Query Expansion.
Full-Text Restrictions.
Fine-Tuning MySQL Full-Text Search.
Full-Text Search TODO.
Cast Functions.
Other Functions.
Bit Functions.
Encryption Functions.
Information Functions.
Miscellaneous Functions.
Functions and Modifiers for Use with GROUP BY Clauses.
GROUP BY (Aggregate) Functions.
GROUP BY Modifiers.
GROUP BY with Hidden Fields.
6. SQL Statement Syntax.
Data Manipulation Statements.
DELETE Syntax.
DO Syntax.
HANDLER Syntax.
INSERT Syntax.
LOAD DATA INFILE Syntax.
REPLACE Syntax.
SELECT Syntax.
Subquery Syntax.
TRUNCATE Syntax.
UPDATE Syntax.
Data Definition Statements.
ALTER DATABASE Syntax.
ALTER TABLE Syntax.
CREATE DATABASE Syntax.
CREATE INDEX Syntax.
CREATE TABLE Syntax.
DROP DATABASE Syntax.
DROP INDEX Syntax.
DROP TABLE Syntax.
RENAME TABLE Syntax.
MySQL Utility Statements.
DESCRIBE Syntax (Get Information About Columns).
USE Syntax.
MySQL Transactional and Locking Statements.
START TRANSACTION, COMMIT, and ROLLBACK Syntax.
Statements That Cannot Be Rolled Back.
Statements That Cause an Implicit Commit.
SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax.
LOCK TABLES and UNLOCK TABLES Syntax.
SET TRANSACTION Syntax.
Database Administration Statements.
Account Management Statements.
Table Maintenance Statements.
SET and SHOW Syntax.
Other Administrative Statements.
Replication Statements.
SQL Statements for Controlling Master Servers.
SQL Statements for Controlling Slave Servers.
7. Spatial Extensions in MySQL.
Introduction.
The OpenGIS Geometry Model.
The Geometry Class Hierarchy.
Class Geometry.
Class Point.
Class Curve.
Class LineString.
Class Surface.
Class Polygon.
Class GeometryCollection.
Class MultiPoint.
Class MultiCurve.
Class MultiLineString.
Class MultiSurface.
Class MultiPolygon.
Supported Spatial Data Formats.
Well-Known Text (WKT) Format.
Well-Known Binary (WKB) Format.
Creating a Spatially Enabled MySQL Database.
MySQL Spatial Data Types.
Creating Spatial Values.
Creating Spatial Columns.
Populating Spatial Columns.
Fetching Spatial Data.
Analyzing Spatial Information.
Geometry Format Conversion Functions.
Geometry Functions.
Functions That Create New Geometries from Existing Ones.
Functions for Testing Spatial Relations Between Geometric Objects.
Relations on Geometry Minimal Bounding Rectangles (MBRs).
Functions That Test Spatial Relationships Between Geometries.
Optimizing Spatial Analysis.
Creating Spatial Indexes.
Using a Spatial Index.
MySQL Conformance and Compatibility.
GIS Features That Are Not Yet Implemented.
8. Stored Procedures and Functions.
Stored Procedure Syntax.
Maintaining Stored Procedures.
SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS.
CALL.
BEGIN ... END Compound Statement.
DECLARE Statement.
Variables in Stored Procedures.
Conditions and Handlers.
Cursors.
Flow Control Constructs.
9. Error Handling in MySQL.
Error Returns.
Error Messages.
Appendix A. Troubleshooting Query Problems.
Query-Related Issues.
Case Sensitivity in Searches.
Problems Using DATE Columns.
Problems with NULL Values.
Problems with Column Aliases.
Rollback Failure for Non-Transactional Tables.
Deleting Rows from Related Tables.
Solving Problems with No Matching Rows.
Problems with Floating-Point Comparisons.
Optimizer-Related Issues.
Table Definition-Related Issues.
Problems with ALTER TABLE.
How to Change the Order of Columns in a Table.
TEMPORARY TABLE Problems.
Appendix B. MySQL Regular Expressions.
ABOUT THE AUTHOR
|
| Paul DuBois | Paul DuBois began his involvement with MySQL after recoiling in horror at the complexities of dealing with a database from one of the larger commercial vendors, and with its customer support mechanism. Turning to MySQL for relief proved to have unforeseen and unexpected consequences: first as the opportunity to contribute to the MySQL Reference Manual, then to writing MySQL with New Riders, and most recently to his present employment with NuSphere, a company actively involved in MySQL development, promotion, and training.
Paul's responsibilities and interests have at one time or another involved database development, Web site development and management, mailing list management, system administration, and TCP/IP and AppleTalk networking. He's considered a leader in the MySQL and open source communities. |
