MySQL

TEAM LinG

MySQL ® Language

Reference

00 6337 fm 6/24/04 12:44 PM Page i

TEAM LinG

00 6337 fm 6/24/04 12:44 PM Page ii

TEAM LinG

MySQL ® Language

Reference

800 East 96th Street, Indianapolis, Indiana 46240 USA

MySQL AB

00 6337 fm 6/24/04 12:44 PM Page iii

TEAM LinG

MySQL Language Reference

Copyright © 2005 by MySQL AB

All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted

by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permis￾sion from the publisher. No patent liability is assumed with respect to the use of the information con￾tained herein. Although every precaution has been taken in the preparation of this book, the publisher

and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages

resulting from the use of the information contained herein.

International Standard Book Number: 0-672-32633-7

Library of Congress Catalog Card Number: 2003110975

Printed in the United States of America

First Printing: July 2004

06 05 04 4 3 2 1

Trademarks

All terms mentioned in this book that are known to be trademarks or service marks have been appropri￾ately capitalized. Pearson cannot attest to the accuracy of this information. Use of a term in this book

should not be regarded as affecting the validity of any trademark or service mark.

Warning and Disclaimer

Every effort has been made to make this book as complete and as accurate as possible, but no warranty

or fitness is implied. The information provided is on an “as is” basis. The author and the publisher shall

have neither liability nor responsibility to any person or entity with respect to any loss or damages aris￾ing from the information contained in this book.

Bulk Sales

Pearson offers excellent discounts on this book when ordered in quantity for bulk purchases or special

sales. For more information, please contact

U.S. Corporate and Government Sales

1-800-382-3419

[email protected]

For sales outside of the U.S., please contact

International Sales

1-317-428-3341

[email protected]

00 6337 fm 6/24/04 12:44 PM Page iv

TEAM LinG

ASSOCIATE PUBLISHER

Mark Taber

ACQUISITIONS EDITOR

Shelley Johnston

DEVELOPMENT EDITOR

Damon Jordan

MANAGING EDITOR

Charlotte Clapp

PROJECT EDITOR

Andy Beaster

COPY EDITOR

Mike Henry

INDEXER

Ken Johnson

PROOFREADER

Wendy Ott

PUBLISHING

COORDINATOR

Vanessa Evans

BOOK DESIGNER

Gary Adair

PAGE LAYOUT

Stacey Richwine-DeRome

MYSQL AB DOCUMENTATION

TEAM

Paul DuBois

Stefan Hinz

Arjen Lentz

MYSQL HQ

MySQL AB

Bangårdsgatan 8

S-753 20 Uppsala

Sweden

UNITED STATES

MySQL Inc.

2510 Fairview Avenue East

Seattle, WA 98102

USA

GERMANY, AUSTRIA AND

SWITZERLAND

MySQL GmbH

Schlosserstraße 4

D-72622 Nürtingen

Germany

FINLAND

MySQL Finland Oy

Tekniikantie 21

FIN-02150 Espoo

Finland

FRANCE

MySQL AB (France)

123, rue du Faubourg St. Antoine

75011, Paris

France

MySQL® Press is the exclusive publisher of technology books and materials that have been

authorized by MySQL AB. MySQL Press books are written and reviewed by the world's

leading authorities on MySQL technologies, and are edited, produced, and distributed by

the Que/Sams Publishing group of Pearson Education, the worldwide leader in integrated

education and computer technology publishing. For more information on MySQL Press

and MySQL Press books, please go to www.mysqlpress.com.

MySQL® AB develops, markets, and supports a family of high-performance, affordable

database servers and tools. MySQL AB is the sole owner of the MySQL server source code,

the MySQL trademark, and the mysql.com domain. For more information on MySQL AB

and MySQL AB products, please go to www.mysql.com or the following areas of the

MySQL Web site:

n Training information: www.mysql.com/training

n Support services: www.mysql.com/support

n Consulting services: www.mysql.com/consulting

00 6337 fm 6/24/04 12:44 PM Page v

TEAM LinG

Contents At a Glance

1 General Information 1

2 Language Structure 63

3 Character Set Support 81

4 Column Types 111

5 Functions and Operators 137

6 SQL Statement Syntax 219

7 Spatial Extensions in MySQL 349

8 Stored Procedures and Functions 383

9 Error Handling in MySQL 397

A Troubleshooting Query Problems 415

B MySQL Regular Expressions 427

Index 433

00 6337 fm 6/24/04 12:44 PM Page vi

TEAM LinG

Table of Contents

1 General Information 1

1.1 About This Guide .................................................. 1

1.1.1 Conventions Used in This Manual .......................... 2

1.2 Overview of the MySQL Database Management System ............ 4

1.2.1 History of MySQL.......................................... 5

1.2.2 The Main Features of MySQL .............................. 6

1.2.3 MySQL Stability ............................................ 8

1.2.4 How Big MySQL Tables Can Be .......................... 10

1.2.5 Year 2000 Compliance .................................... 11

1.3 Overview of MySQL AB .......................................... 12

1.3.1 The Business Model and Services of MySQL AB .......... 13

1.3.2 Contact Information ...................................... 16

1.4 MySQL Support and Licensing .................................... 17

1.4.1 Support Offered by MySQL AB .......................... 17

1.4.2 Copyrights and Licenses Used by MySQL ................ 18

1.4.3 MySQL Licenses .......................................... 18

1.4.4 MySQL AB Logos and Trademarks........................ 21

1.5 MySQL Development Roadmap .................................. 22

1.5.1 MySQL 4.0 in a Nutshell .................................. 23

1.5.2 MySQL 4.1 in a Nutshell .................................. 25

1.5.3 MySQL 5.0: The Next Development Release.............. 27

1.6 MySQL and the Future (the TODO) .............................. 27

1.6.1 New Features Planned for 4.1.............................. 27

1.6.2 New Features Planned for 5.0.............................. 27

1.6.3 New Features Planned for 5.1.............................. 28

1.6.4 New Features Planned for the Near Future ................ 29

1.6.5 New Features Planned for the Mid-Term Future .......... 32

1.6.6 New Features We Don’t Plan to Implement .............. 33

1.7 MySQL Information Sources ...................................... 33

1.7.1 MySQL Mailing Lists...................................... 33

1.7.2 MySQL Community Support on IRC

(Internet Relay Chat) ............................................ 42

00 6337 fm 6/24/04 12:44 PM Page vii

TEAM LinG

1.8 MySQL Standards Compliance .................................... 42

1.8.1 What Standards MySQL Follows .......................... 43

1.8.2 Selecting SQL Modes...................................... 43

1.8.3 Running MySQL in ANSI Mode .......................... 43

1.8.4 MySQL Extensions to Standard SQL...................... 44

1.8.5 MySQL Differences from Standard SQL .................. 47

1.8.6 How MySQL Deals with Constraints...................... 54

1.8.7 Known Errors and Design Deficiencies in MySQL ........ 56

2 Language Structure 63

2.1 Literal Values ...................................................... 63

2.1.1 Strings .................................................... 63

2.1.2 Numbers .................................................. 66

2.1.3 Hexadecimal Values........................................ 66

2.1.4 Boolean Values ............................................ 67

2.1.5 NULL Values ................................................ 67

2.2 Database, Table, Index, Column, and Alias Names ................ 67

2.2.1 Identifier Qualifiers ........................................ 69

2.2.2 Identifier Case Sensitivity .................................. 69

2.3 User Variables...................................................... 71

2.4 System Variables .................................................. 72

2.4.1 Structured System Variables................................ 74

2.5 Comment Syntax .................................................. 76

2.6 Treatment of Reserved Words in MySQL.......................... 77

3 Character Set Support 81

3.1 Character Sets and Collations in General .......................... 81

3.2 Character Sets and Collations in MySQL .......................... 82

3.3 Determining the Default Character Set and Collation.............. 84

3.3.1 Server Character Set and Collation ........................ 84

3.3.2 Database Character Set and Collation...................... 85

3.3.3 Table Character Set and Collation ........................ 86

3.3.4 Column Character Set and Collation ...................... 86

3.3.5 Examples of Character Set and Collation Assignment...... 87

3.3.6 Connection Character Sets and Collations ................ 88

3.3.7 Character String Literal Character Set and Collation ...... 90

00 6337 fm 6/24/04 12:44 PM Page viii

TEAM LinG

3.3.8 Using COLLATE in SQL Statements ........................ 91

3.3.9 COLLATE Clause Precedence ................................ 92

3.3.10 BINARY Operator .......................................... 92

3.3.11 Some Special Cases Where the Collation

Determination Is Tricky ........................................ 92

3.3.12 Collations Must Be for the Right Character Set .......... 93

3.3.13 An Example of the Effect of Collation .................... 94

3.4 Operations Affected by Character Set Support .................... 95

3.4.1 Result Strings .............................................. 95

3.4.2 CONVERT() .................................................. 96

3.4.3 CAST()...................................................... 96

3.4.4 SHOW Statements ............................................ 97

3.5 Unicode Support .................................................. 98

3.6 UTF8 for Metadata ................................................ 99

3.7 Compatibility with Other DBMSs ................................ 101

3.8 New Character Set Configuration File Format.................... 101

3.9 National Character Set............................................ 101

3.10 Upgrading Character Sets from MySQL 4.0 .................... 101

3.10.1 4.0 Character Sets and Corresponding 4.1

Character Set/Collation Pairs .................................. 102

3.10.2 Converting 4.0 Character Columns to 4.1 Format ...... 103

3.11 Character Sets and Collations That MySQL Supports .......... 104

3.11.1 Unicode Character Sets ................................ 105

3.11.2 West European Character Sets .......................... 106

3.11.3 Central European Character Sets........................ 107

3.11.4 South European and Middle East Character Sets........ 108

3.11.5 Baltic Character Sets .................................... 108

3.11.6 Cyrillic Character Sets .................................. 109

3.11.7 Asian Character Sets .................................... 109

4 Column Types 111

4.1 Column Type Overview .......................................... 111

4.1.1 Overview of Numeric Types .............................. 111

4.1.2 Overview of Date and Time Types........................ 114

4.1.3 Overview of String Types ................................ 115

00 6337 fm 6/24/04 12:44 PM Page ix

TEAM LinG

4.2 Numeric Types.................................................... 117

4.3 Date and Time Types ............................................ 120

4.3.1 The DATETIME, DATE, and TIMESTAMP Types ................ 122

4.3.2 The TIME Type ............................................ 126

4.3.3 The YEAR Type ............................................ 127

4.3.4 Y2K Issues and Date Types .............................. 128

4.4 String Types ...................................................... 128

4.4.1 The CHAR and VARCHAR Types .............................. 128

4.4.2 The BLOB and TEXT Types ................................ 130

4.4.3 The ENUM Type ............................................ 131

4.4.4 The SET Type ............................................ 133

4.5 Column Type Storage Requirements.............................. 134

Storage Requirements for Numeric Types...................... 134

Storage Requirements for Date and Time Types .............. 135

Storage Requirements for String Types ........................ 135

4.6 Choosing the Right Type for a Column .......................... 136

4.7 Using Column Types from Other Database Engines .............. 136

5 Functions and Operators 137

5.1 Operators ........................................................ 138

5.1.1 Parentheses .............................................. 138

5.1.2 Comparison Operators.................................... 138

5.1.3 Logical Operators ........................................ 143

5.1.4 Case-Sensitivity Operators................................ 145

5.2 Control Flow Functions .......................................... 145

5.3 String Functions .................................................. 148

5.3.1 String Comparison Functions ............................ 159

5.4 Numeric Functions .............................................. 161

5.4.1 Arithmetic Operators .................................... 161

5.4.2 Mathematical Functions .................................. 163

5.5 Date and Time Functions ........................................ 169

5.6 Full-Text Search Functions........................................ 187

5.6.1 Boolean Full-Text Searches .............................. 190

5.6.2 Full-Text Searches with Query Expansion ................ 192

5.6.3 Full-Text Restrictions .................................... 193

5.6.4 Fine-Tuning MySQL Full-Text Search .................. 194

5.6.5 Full-Text Search TODO.................................. 196

x Contents

00 6337 fm 6/24/04 12:44 PM Page x

TEAM LinG

5.7 Cast Functions .................................................... 196

5.8 Other Functions .................................................. 198

5.8.1 Bit Functions ............................................ 198

5.8.2 Encryption Functions .................................... 200

5.8.3 Information Functions.................................... 203

5.8.4 Miscellaneous Functions.................................. 208

5.9 Functions and Modifiers for Use with GROUP BY Clauses .......... 211

5.9.1 GROUP BY (Aggregate) Functions .......................... 211

5.9.2 GROUP BY Modifiers ...................................... 214

Other Considerations When Using ROLLUP .................... 216

5.9.3 GROUP BY with Hidden Fields.............................. 217

6 SQL Statement Syntax 219

6.1 Data Manipulation Statements .................................... 219

6.1.1 DELETE Syntax ............................................ 219

6.1.2 DO Syntax ................................................ 222

6.1.3 HANDLER Syntax ............................................ 222

6.1.4 INSERT Syntax ............................................ 224

6.1.5 LOAD DATA INFILE Syntax ................................ 230

6.1.6 REPLACE Syntax ............................................ 238

6.1.7 SELECT Syntax ............................................ 239

6.1.8 Subquery Syntax .......................................... 248

6.1.9 TRUNCATE Syntax .......................................... 260

6.1.10 UPDATE Syntax............................................ 260

6.2 Data Definition Statements ...................................... 262

6.2.1 ALTER DATABASE Syntax .................................... 262

6.2.2 ALTER TABLE Syntax ...................................... 263

6.2.3 CREATE DATABASE Syntax .................................. 269

6.2.4 CREATE INDEX Syntax ...................................... 270

6.2.5 CREATE TABLE Syntax ...................................... 271

6.2.6 DROP DATABASE Syntax .................................... 286

6.2.7 DROP INDEX Syntax ........................................ 286

6.2.8 DROP TABLE Syntax ........................................ 287

6.2.9 RENAME TABLE Syntax ...................................... 287

6.3 MySQL Utility Statements........................................ 288

6.3.1 DESCRIBE Syntax (Get Information About Columns) ...... 288

6.3.2 USE Syntax ................................................ 288

Contents xi

00 6337 fm 6/24/04 12:44 PM Page xi

TEAM LinG

6.4 MySQL Transactional and Locking Statements .................. 289

6.4.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax.......... 289

6.4.2 Statements That Cannot Be Rolled Back ................ 290

6.4.3 Statements That Cause an Implicit Commit .............. 290

6.4.4 SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax ............ 290

6.4.5 LOCK TABLES and UNLOCK TABLES Syntax .................. 291

6.4.6 SET TRANSACTION Syntax .................................. 294

6.5 Database Administration Statements .............................. 294

6.5.1 Account Management Statements ........................ 294

6.5.2 Table Maintenance Statements............................ 303

6.5.3 SET and SHOW Syntax ...................................... 309

6.5.4 Other Administrative Statements ........................ 331

6.6 Replication Statements............................................ 336

6.6.1 SQL Statements for Controlling Master Servers.......... 336

6.6.2 SQL Statements for Controlling Slave Servers............ 338

7 Spatial Extensions in MySQL 349

7.1 Introduction ...................................................... 349

7.2 The OpenGIS Geometry Model .................................. 350

7.2.1 The Geometry Class Hierarchy .......................... 350

7.2.2 Class Geometry ............................................ 351

7.2.3 Class Point................................................ 353

7.2.4 Class Curve................................................ 353

7.2.5 Class LineString .......................................... 353

7.2.6 Class Surface ............................................ 354

7.2.7 Class Polygon ............................................ 354

7.2.8 Class GeometryCollection ................................ 355

7.2.9 Class MultiPoint .......................................... 355

7.2.10 Class MultiCurve ........................................ 355

7.2.11 Class MultiLineString .................................. 356

7.2.12 Class MultiSurface ...................................... 356

7.2.13 Class MultiPolygon ...................................... 356

7.3 Supported Spatial Data Formats .................................. 357

7.3.1 Well-Known Text (WKT) Format ........................ 357

7.3.2 Well-Known Binary (WKB) Format ...................... 358

xii Contents

00 6337 fm 6/24/04 12:44 PM Page xii

TEAM LinG

Contents xiii

7.4 Creating a Spatially Enabled MySQL Database .................. 359

7.4.1 MySQL Spatial Data Types .............................. 359

7.4.2 Creating Spatial Values .................................. 359

7.4.3 Creating Spatial Columns ................................ 362

7.4.4 Populating Spatial Columns .............................. 363

7.4.5 Fetching Spatial Data .................................... 364

7.5 Analyzing Spatial Information .................................... 365

7.5.1 Geometry Format Conversion Functions ................ 365

7.5.2 Geometry Functions ...................................... 366

7.5.3 Functions That Create New Geometries from

Existing Ones .................................................. 373

7.5.4 Functions for Testing Spatial Relations Between

Geometric Objects ............................................ 374

7.5.5 Relations on Geometry Minimal Bounding

Rectangles (MBRs) ............................................ 374

7.5.6 Functions That Test Spatial Relationships

Between Geometries .......................................... 375

7.6 Optimizing Spatial Analysis ...................................... 377

7.6.1 Creating Spatial Indexes .................................. 377

7.6.2 Using a Spatial Index .................................... 379

7.7 MySQL Conformance and Compatibility ........................ 381

7.7.1 GIS Features That Are Not Yet Implemented ............ 381

8 Stored Procedures and Functions 383

8.1 Stored Procedure Syntax .......................................... 384

8.1.1 Maintaining Stored Procedures .......................... 384

8.1.2 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS ...... 387

8.1.3 CALL ...................................................... 388

8.1.4 BEGIN ... END Compound Statement .................... 388

8.1.5 DECLARE Statement ........................................ 388

8.1.6 Variables in Stored Procedures............................ 388

8.1.7 Conditions and Handlers ................................ 389

8.1.8 Cursors .................................................. 391

8.1.9 Flow Control Constructs ................................ 392

9 Error Handling in MySQL 397

9.1 Error Returns .................................................... 397

9.2 Error Messages.................................................... 405

00 6337 fm 6/24/04 12:44 PM Page xiii

TEAM LinG

A Troubleshooting Query Problems 415

A.1 Query-Related Issues ............................................ 415

A.1.1 Case Sensitivity in Searches .............................. 415

A.1.2 Problems Using DATE Columns .......................... 416

A.1.3 Problems with NULL Values .............................. 417

A.1.4 Problems with Column Aliases .......................... 418

A.1.5 Rollback Failure for Non-Transactional Tables .......... 419

A.1.6 Deleting Rows from Related Tables ...................... 419

A.1.7 Solving Problems with No Matching Rows .............. 420

A.1.8 Problems with Floating-Point Comparisons.............. 420

A.2 Optimizer-Related Issues ........................................ 423

A.3 Table Definition-Related Issues .................................. 424

A.3.1 Problems with ALTER TABLE .............................. 424

A.3.2 How to Change the Order of Columns in a Table ........ 424

A.3.3 TEMPORARY TABLE Problems .............................. 425

B MySQL Regular Expressions 427

Index 433

00 6337 fm 6/24/04 12:44 PM Page xiv

TEAM LinG