Home > Store

MySQL Language Reference

Register your product to gain access to bonus material or receive a coupon.

MySQL Language Reference

  • By
  • Published Jul 22, 2004 by MySQL Press.

Premium Website

  • Sorry, this book is no longer in print.
Not for Sale


  • Copyright 2005
  • Edition: 1st
  • Premium Website
  • ISBN-10: 0-672-32633-7
  • ISBN-13: 978-0-672-32633-2

With more than 4 million active installations, MySQL is the world's most popular open-source database.

Known for its speed, high reliability, and ease of use, MySQL is rapidly becoming a low-cost alternative to high-priced, high-maintenance database systems from Oracle, IMB and Microsoft-and it already has more users than any of these more established proprietary databases.

The MySQL Language Reference is the only official guide to the MySQL language and programming APIs.

Written by the creators of MySQL, and edited by the highly respected MySQL authors, the MySQL Language Reference is designed for database administrators and programmers who need a reliable, detailed reference to every part of the MySQL language and all the available APIs.

Sample Content

Online Sample Chapters

Character Set Support

MySQL Character Set Support

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.



      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.



      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.



      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 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.


      Statements That Cannot Be Rolled Back.

      Statements That Cause an Implicit Commit.




    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.


    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.



      BEGIN ... END Compound Statement.

      DECLARE Statement.

      Variables in Stored Procedures.

      Conditions and Handlers.


      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.

Index .


Submit Errata

More Information

Unlimited one-month access with your purchase
Free Safari Membership