MY SQL BOOK FOR STUDENTS, Study notes for Computer-Aided Power System Analysis
raghav3040
raghav3040

MY SQL BOOK FOR STUDENTS, Study notes for Computer-Aided Power System Analysis

1326 pages
10Number of visits
Description
SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system.
20 points
Download points needed to download
this document
Download the document
Preview3 pages / 1326
This is only a preview
3 shown on 1326 pages
Download the document
This is only a preview
3 shown on 1326 pages
Download the document
This is only a preview
3 shown on 1326 pages
Download the document
This is only a preview
3 shown on 1326 pages
Download the document

MySQL ®

Fourth Edition

informit.com/devlibrary

Developer’s Library

ESSENTIAL REFERENCES FOR PROGRAMMING PROFESSIONALS

Developer’s Library books are designed to provide practicing programmers with unique, high-quality references and tutorials on the programming languages and technologies they use in their daily work.

All books in the Developer’s Library are written by expert technology practitioners who are especially skilled at organizing and presenting information in a way that’s useful for other programmers.

Key titles include some of the best, most widely acclaimed books within their topic areas:

PHP & MySQL Web Development Luke Welling & Laura Thomson ISBN 978-0-672-32916-6

MySQL Paul DuBois ISBN-13: 978-0-672-32938-8

Linux Kernel Development Robert Love ISBN-13: 978-0-672-32946-3

Python Essential Reference David Beazley ISBN-13: 978-0-672-32862-6

Programming in Objective-C Stephen G. Kochan ISBN-13: 978-0-321-56615-7

PostgreSQL Korry Douglas ISBN-13: 978-0-672-33015-5

Developer’s Library books are available at most retail and online bookstores, as well as by subscription from Safari Books Online at safari.informit.com.

Developer’s Library

Upper Saddle River, NJ • Boston • Indianapolis • San Francisco New York • Toronto • Montreal • London • Munich • Paris • Madrid

Cape Town • Sydney • Tokyo • Singapore • Mexico City

MySQL ®

Fourth Edition

Paul DuBois

MySQL® Fourth Edition Copyright © 2009 by Pearson Education, Inc.

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 permission from the publisher. No patent liability is assumed with respect to the use of the information contained 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 informa- tion contained herein.

ISBN-13: 978-0-672-32938-8 ISBN-10: 0-672-32938-7

Library of Congress Cataloging-in-Publication Data

DuBois, Paul, 1956-

MySQL / Paul DuBois. — 4th ed.

p. cm.

Includes index.

ISBN 978-0-672-32938-8 (pbk.)

1. SQL (Computer program language) 2. MySQL (Electronic resource) 3. Database man- agement. I. Title.

QA76.73.S67D588 2009

005.13’3—dc22

2008030855

Printed in the United States of America

First Printing August 2008

Trademarks All terms mentioned in this book that are known to be trademarks or service marks have been appropriately 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 arising 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 corpsales@pearsontechgroup.com

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

International Sales international@pearson.com

Acquisitions Editor Mark Taber

Development Editor Michael Thurston

Managing Editor Kristy Hart

Project Editor Jovana San Nicolas-Shirley

Indexer Cheryl Lenser

Proofreaders Leslie Joseph Water Crest Publishing

Technical Editors Stephen Frein Tim Boronczyk

Publishing Coordinator Vanessa Evans

Cover Designer Gary Adair

Compositor Jake McFarland

Contents at a Glance Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

I: General MySQL Use

1 Getting Started with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

2 Using SQL to Manage Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

3 Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201

4 Stored Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289

5 Query Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303

II: Using MySQL Programming Interfaces

6 Introduction to MySQL Programming . . . . . . . . . . . . . . . . . . . . . . . . 341

7 Writing MySQL Programs Using C . . . . . . . . . . . . . . . . . . . . . . . . . . 359

8 Writing MySQL Programs Using Perl DBI. . . . . . . . . . . . . . . . . . . . . . 435

9 Writing MySQL Programs Using PHP . . . . . . . . . . . . . . . . . . . . . . . . 527

III: MySQL Administration

10 Introduction to MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . 579

11 The MySQL Data Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585

12 General MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609

13 Access Control and Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699

14 Database Maintenance, Backups, and Replication . . . . . . . . . . . . . . 737

IV: Appendixes

A Obtaining and Installing Software . . . . . . . . . . . . . . . . . . . . . . . . . . 777

B Data Type Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 797

C Operator and Function Reference . . . . . . . . . . . . . . . . . . . . . . . . . . 813

D System, Status, and User Variable Reference. . . . . . . . . . . . . . . . . . 889

E SQL Syntax Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 937

F MySQL Program Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1037

Note: Appendixes G, H, and I are located online and are accessible either by registering this book at informit.com/register or by visiting www.kitebird.com/mysql-book.

G C API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1121

H Perl DBI API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1177

I PHP API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1207

Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1225

Table of Contents

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Why Choose MySQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Already Running Another RDBMS?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

Tools Provided with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

What You Can Expect from This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

Road Map to This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Part I: General MySQL Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Part II: Using MySQL Programming Interfaces . . . . . . . . . . . . . . . . . . . . . . . 6

Part III: MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Part IV: Appendixes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

How to Read This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Versions of Software Covered in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

Conventions Used in This Book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Additional Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

I: General MySQL Use

1 Getting Started with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 1.1 How MySQL Can Help You . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

1.2 A Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

1.2.1 The U.S. Historical League . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

1.2.2 The Grade-Keeping Project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

1.2.3 How the Sample Database Applies to You . . . . . . . . . . . . . . . . . . . . 20

1.3 Basic Database Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

1.3.1 Structural Terminology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

1.3.2 Query Language Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

1.3.3 MySQL Architectural Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

1.4 A MySQL Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

1.4.1 Obtaining the Sample Database Distribution . . . . . . . . . . . . . . . . . . 26

1.4.2 Preliminary Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

1.4.3 Establishing and Terminating Connections to the MySQL Server . . . . 28

1.4.4 Executing SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

1.4.5 Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

1.4.6 Creating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

viiContents

1.4.7 Adding New Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

1.4.8 Resetting the sampdb Database to a Known State . . . . . . . . . . . . . 57

1.4.9 Retrieving Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

1.4.10 Deleting or Updating Existing Rows . . . . . . . . . . . . . . . . . . . . . . . . 91

1.5 Tips for Interacting with mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93

1.5.1 Simplifying the Connection Process . . . . . . . . . . . . . . . . . . . . . . . . . 93

1.5.2 Issuing Statements with Less Typing . . . . . . . . . . . . . . . . . . . . . . . . 95

1.6 Where to Now? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

2 Using SQL to Manage Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 2.1 The Server SQL Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

2.2 MySQL Identifier Syntax and Naming Rules . . . . . . . . . . . . . . . . . . . . . . 103

2.3 Case Sensitivity in SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

2.4 Character Set Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

2.4.1 Specifying Character Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

2.4.2 Determining Character Set Availability and Current Settings. . . . . . . 109

2.4.3 Unicode Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

2.5 Selecting, Creating, Dropping, and Altering Databases . . . . . . . . . . . . . . 112

2.5.1 Selecting Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112

2.5.2 Creating Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

2.5.3 Dropping Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

2.5.4 Altering Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

2.6 Creating, Dropping, Indexing, and Altering Tables . . . . . . . . . . . . . . . . . . 114

2.6.1 Storage Engine Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

2.6.2 Creating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

2.6.3 Dropping Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135

2.6.4 Indexing Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

2.6.5 Altering Table Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141

2.7 Obtaining Database Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

2.7.1 Obtaining Metadata with SHOW . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

2.7.2 Obtaining Metadata with INFORMATION_SCHEMA . . . . . . . . . . . . 147

2.7.3 Obtaining Metadata from the Command Line . . . . . . . . . . . . . . . . . 149

2.8 Performing Multiple-Table Retrievals with Joins . . . . . . . . . . . . . . . . . . . . 150

2.8.1 The Inner Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152

2.8.2 Qualifying References to Columns from Joined Tables . . . . . . . . . . . 153

2.8.3 Left and Right (Outer) Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

viii Contents

2.9 Performing Multiple-Table Retrievals with Subqueries . . . . . . . . . . . . . . . 158

2.9.1 Subqueries with Relative Comparison Operators . . . . . . . . . . . . . . . 159

2.9.2 IN and NOT IN Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

2.9.3 ALL, ANY, and SOME Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . 161

2.9.4 EXISTS and NOT EXISTS Subqueries . . . . . . . . . . . . . . . . . . . . 162

2.9.5 Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163

2.9.6 Subqueries in the FROM Clause . . . . . . . . . . . . . . . . . . . . . . . . . . 163

2.9.7 Rewriting Subqueries as Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164

2.10 Performing Multiple-Table Retrievals with UNION . . . . . . . . . . . . . . . . . 165

2.11 Using Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169

2.12 Multiple-Table Deletes and Updates. . . . . . . . . . . . . . . . . . . . . . . . . . . 173

2.13 Performing Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

2.13.1 Using Transactions to Ensure Safe Statement Execution . . . . . . . . 176

2.13.2 Using Transaction Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

2.13.3 Transaction Isolation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

2.13.4 Non-Transactional Approaches to Transactional Problems . . . . . . . 182

2.14 Foreign Keys and Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . 185

2.14.1 Creating and Using Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . 187

2.14.2 Living Without Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192

2.15 Using FULLTEXT Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

2.15.1 Natural Language FULLTEXT Searches. . . . . . . . . . . . . . . . . . . . 196

2.15.2 Boolean Mode FULLTEXT Searches . . . . . . . . . . . . . . . . . . . . . . 197

2.15.3 Query Expansion FULLTEXT Searches . . . . . . . . . . . . . . . . . . . . 199

2.15.4 Configuring the FULLTEXT Search Engine . . . . . . . . . . . . . . . . . . 200

3 Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 3.1 Data Value Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203

3.1.1 Numeric Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203

3.1.2 String Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204

3.1.3 Date and Time (Temporal) Values. . . . . . . . . . . . . . . . . . . . . . . . . . 213

3.1.4 Spatial Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213

3.1.5 Boolean Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213

3.1.6 The NULL Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214

3.2 MySQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214

3.2.1 Overview of Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215

3.2.2 Specifying Column Types in Table Definitions . . . . . . . . . . . . . . . . . 217

3.2.3 Specifying Column Default Values . . . . . . . . . . . . . . . . . . . . . . . . . 218

ixContents

3.2.4 Numeric Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219

3.2.5 String Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226

3.2.6 Date and Time Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242

3.2.7 Spatial Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250

3.3 How MySQL Handles Invalid Data Values . . . . . . . . . . . . . . . . . . . . . . . . 252

3.4 Working with Sequences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254

3.4.1 General AUTO_INCREMENT Properties . . . . . . . . . . . . . . . . . . . . . 254

3.4.2 Storage Engine-Specific AUTO_INCREMENT Properties . . . . . . . . . 256

3.4.3 Issues to Consider with AUTO_INCREMENT Columns . . . . . . . . . . 259

3.4.4 Tips for Working with AUTO_INCREMENT Columns . . . . . . . . . . . . 260

3.4.5 Generating Sequences Without AUTO_INCREMENT . . . . . . . . . . . . 262

3.5 Expression Evaluation and Type Conversion . . . . . . . . . . . . . . . . . . . . . . 264

3.5.1 Writing Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265

3.5.2 Type Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272

3.6 Choosing Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280

3.6.1 What Kind of Values Will the Column Hold? . . . . . . . . . . . . . . . . . . 282

3.6.2 Do Your Values Lie Within Some Particular Range? . . . . . . . . . . . . . 285

3.6.3 Inter-Relatedness of Data Type Choice Issues . . . . . . . . . . . . . . . . 286

4 Stored Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 4.1 Compound Statements and Statement Delimiters . . . . . . . . . . . . . . . . . 290

4.2 Stored Functions and Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292

4.2.1 Privileges for Stored Functions and Procedures. . . . . . . . . . . . . . . . 294

4.2.2 Stored Procedure Parameter Types. . . . . . . . . . . . . . . . . . . . . . . . . 295

4.3 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296

4.4 Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298

4.5 Security for Stored Programs and Views . . . . . . . . . . . . . . . . . . . . . . . . 300

5 Query Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 5.1 Using Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304

5.1.1 Benefits of Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304

5.1.2 Costs of Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307

5.1.3 Choosing Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

5.2 The MySQL Query Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311

5.2.1 How the Optimizer Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312

5.2.2 Using EXPLAIN to Check Optimizer Operation . . . . . . . . . . . . . . . . 316

5.3 Choosing Data Types for Efficient Queries . . . . . . . . . . . . . . . . . . . . . . . 322

x Contents

5.4 Loading Data Efficiently . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326

5.5 Scheduling and Locking Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329

5.5.1 Changing Statement Scheduling Priorities . . . . . . . . . . . . . . . . . . . 331

5.5.2 Using Delayed Inserts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331

5.5.3 Using Concurrent Inserts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332

5.5.4 Locking Levels and Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . 333

5.6 Administrative-Level Optimizations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334

5.6.1 Using MyISAM Key Caches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336

5.6.2 Using the Query Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337

5.6.3 Hardware Optimizations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339

II: Using MySQL Programming Interfaces

6 Introduction to MySQL Programming . . . . . . . . . . . . . . . . . . . . . . . . . . 341 6.1 Why Write Your Own MySQL Programs? . . . . . . . . . . . . . . . . . . . . . . . . . 341

6.2 APIs Available for MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345

6.2.1 The C API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347

6.2.2 The Perl DBI API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347

6.2.3 The PHP API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349

6.3 Choosing an API. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350

6.3.1 Execution Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351

6.3.2 Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352

6.3.3 Development Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354

6.3.4 Portability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357

7 Writing MySQL Programs Using C . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359 7.1 Compiling and Linking Client Programs . . . . . . . . . . . . . . . . . . . . . . . . . 360

7.2 Connecting to the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363

7.3 Handling Errors and Processing Command Options . . . . . . . . . . . . . . . . 367

7.3.1 Checking for Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

7.3.2 Getting Connection Parameters at Runtime . . . . . . . . . . . . . . . . . . 371

7.3.3 Incorporating Option-Processing into a MySQL Client Program . . . . . 384

7.4 Processing SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389

7.4.1 Handling Statements That Modify Rows . . . . . . . . . . . . . . . . . . . . . 390

7.4.2 Handling Statements That Return a Result Set. . . . . . . . . . . . . . . . 391

7.4.3 A General-Purpose Statement Handler . . . . . . . . . . . . . . . . . . . . . . 394

7.4.4 Alternative Approaches to Statement Processing . . . . . . . . . . . . . . 396

7.4.5 mysql_store_result() Versus mysql_use_result(). . . . 398

xiContents

7.4.6 Using Result Set Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400

7.4.7 Encoding Special Characters and Binary Data . . . . . . . . . . . . . . . . 405

7.5 An Interactive Statement-Execution Program . . . . . . . . . . . . . . . . . . . . . 409

7.6 Writing Clients That Include SSL Support. . . . . . . . . . . . . . . . . . . . . . . . 410

7.7 Using the Embedded Server Library. . . . . . . . . . . . . . . . . . . . . . . . . . . . 416

7.7.1 Writing an Embedded Server Application . . . . . . . . . . . . . . . . . . . . 416

7.7.2 Producing the Application Executable Binary . . . . . . . . . . . . . . . . . . 419

7.8 Using Multiple-Statement Execution. . . . . . . . . . . . . . . . . . . . . . . . . . . . 420

7.9 Using Server-Side Prepared Statements . . . . . . . . . . . . . . . . . . . . . . . . 422

8 Writing MySQL Programs Using Perl DBI . . . . . . . . . . . . . . . . . . . . . . . 435 8.1 Perl Script Characteristics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436

8.2 Perl DBI Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437

8.2.1 DBI Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437

8.2.2 A Simple DBI Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437

8.2.3 Handling Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443

8.2.4 Handling Statements That Modify Rows . . . . . . . . . . . . . . . . . . . . . 446

8.2.5 Handling Statements That Return a Result Set. . . . . . . . . . . . . . . . 447

8.2.6 Quoting Special Characters in Statement Strings . . . . . . . . . . . . . . 457

8.2.7 Placeholders and Prepared Statements . . . . . . . . . . . . . . . . . . . . . 460

8.2.8 Binding Query Results to Script Variables. . . . . . . . . . . . . . . . . . . . 463

8.2.9 Specifying Connection Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 464

8.2.10 Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468

8.2.11 Using Result Set Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471

8.2.12 Performing Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475

8.3 Putting DBI to Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477

8.3.1 Generating the Historical League Directory . . . . . . . . . . . . . . . . . . . 478

8.3.2 Sending Membership Renewal Notices. . . . . . . . . . . . . . . . . . . . . . 484

8.3.3 Historical League Member Entry Editing . . . . . . . . . . . . . . . . . . . . . 490

8.3.4 Finding Historical League Members with Common Interests . . . . . . 496

8.3.5 Putting the Historical League Directory Online . . . . . . . . . . . . . . . . 497

8.4 Using DBI in Web Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500

8.4.1 Setting Up Apache for CGI Scripts . . . . . . . . . . . . . . . . . . . . . . . . . 502

8.4.2 A Brief CGI.pm Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503

8.4.3 Connecting to the MySQL Server from Web Scripts . . . . . . . . . . . . . 510

8.4.4 A Web-Based Database Browser . . . . . . . . . . . . . . . . . . . . . . . . . . 513

8.4.5 A Grade-Keeping Project Score Browser . . . . . . . . . . . . . . . . . . . . . 517

xii Contents

8.4.6 Historical League Common-Interest Searching . . . . . . . . . . . . . . . . 521

9 Writing MySQL Programs Using PHP . . . . . . . . . . . . . . . . . . . . . . . . . . 527 9.1 PHP Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529

9.1.1 A Simple PHP Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531

9.1.2 Using PHP Library Files for Code Encapsulation . . . . . . . . . . . . . . . 534

9.1.3 A Simple Data-Retrieval Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539

9.1.4 Processing Statement Results. . . . . . . . . . . . . . . . . . . . . . . . . . . . 543

9.1.5 Testing for NULL Values in Query Results . . . . . . . . . . . . . . . . . . . 547

9.1.6 Using Prepared Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

9.1.7 Using Placeholders to Handle Data Quoting Issues. . . . . . . . . . . . . 548

9.1.8 Handling Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550

9.2 Putting PHP to Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552

9.2.1 An Online Score-Entry Application . . . . . . . . . . . . . . . . . . . . . . . . . 552

9.2.2 Creating an Interactive Online Quiz . . . . . . . . . . . . . . . . . . . . . . . . 565

9.2.3 Historical League Online Member Entry Editing . . . . . . . . . . . . . . . . 570

III: MySQL Administration

10 Introduction to MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . 579 10.1 MySQL Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 580

10.2 General MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581

10.3 Access Control and Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582

10.4 Database Maintenance, Backups, and Replication . . . . . . . . . . . . . . . . 582

11 The MySQL Data Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585 11.1 Location of the Data Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 586

11.2 Structure of the Data Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587

11.2.1 How the MySQL Server Provides Access to Data. . . . . . . . . . . . . . 588

11.2.2 Representation of Databases in the Filesystem . . . . . . . . . . . . . . 590

11.2.3 Representation of Tables in the Filesystem . . . . . . . . . . . . . . . . . 590

11.2.4 Representation of Views and Triggers in the Filesystem . . . . . . . . 592

11.2.5 How SQL Statements Map onto Table File Operations . . . . . . . . . . 592

11.2.6 Operating System Constraints on Database Object Names . . . . . . 593

11.2.7 Factors That Affect Maximum Table Size. . . . . . . . . . . . . . . . . . . . 596

11.2.8 Implications of Data Directory Structure for System Performance. . 597

11.2.9 MySQL Status and Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599

11.3 Relocating Data Directory Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . 602

xiiiContents

11.3.1 Relocation Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602

11.3.2 Relocation Precautions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603

11.3.3 Assessing the Effect of Relocation. . . . . . . . . . . . . . . . . . . . . . . . 603

11.3.4 Relocating the Entire Data Directory. . . . . . . . . . . . . . . . . . . . . . . 604

11.3.5 Relocating Individual Databases . . . . . . . . . . . . . . . . . . . . . . . . . 604

11.3.6 Relocating Individual Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606

11.3.7 Relocating the InnoDB Shared Tablespace . . . . . . . . . . . . . . . . . . 606

11.3.8 Relocating Status and Log Files. . . . . . . . . . . . . . . . . . . . . . . . . . 607

12 General MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609 12.1 Securing a New MySQL Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . 610

12.1.1 Establishing Passwords for the Initial MySQL Accounts . . . . . . . . . 610

12.1.2 Setting Up Passwords for a Second Server. . . . . . . . . . . . . . . . . . 615

12.2 Arranging for MySQL Server Startup and Shutdown . . . . . . . . . . . . . . . 616

12.2.1 Running the MySQL Server On Unix . . . . . . . . . . . . . . . . . . . . . . . 616

12.2.2 Running the MySQL Server On Windows. . . . . . . . . . . . . . . . . . . . 621

12.2.3 Specifying Server Startup Options . . . . . . . . . . . . . . . . . . . . . . . . 624

12.2.4 Stopping the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 626

12.2.5 Regaining Control of the Server When You Cannot Connect to It . . 626

12.3 Controlling How the Server Listens for Connections . . . . . . . . . . . . . . . 629

12.4 Managing MySQL User Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630

12.4.1 High-Level MySQL Account Management . . . . . . . . . . . . . . . . . . . 631

12.4.2 Granting Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634

12.4.3 Displaying Account Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . 643

12.4.4 Revoking Privileges and Removing Users . . . . . . . . . . . . . . . . . . . 643

12.4.5 Changing Passwords or Resetting Lost Passwords . . . . . . . . . . . . 644

12.5 Maintaining Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645

12.5.1 The Error Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648

12.5.2 The General Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649

12.5.3 The Slow-Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649

12.5.4 The Binary Log and the Binary Log Index File . . . . . . . . . . . . . . . . 650

12.5.5 The Relay Log and the Relay Log Index File . . . . . . . . . . . . . . . . . 652

12.5.6 Using Log Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652

12.5.7 Log Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653

12.6 Tuning the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660

12.6.1 Checking and Setting System Variable Values. . . . . . . . . . . . . . . . 661

12.6.2 General-Purpose System Variables. . . . . . . . . . . . . . . . . . . . . . . . 665

12.6.3 Checking Status Variable Values . . . . . . . . . . . . . . . . . . . . . . . . . 667

xiv Contents

12.7 Storage Engine Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669

12.7.1 Selecting Which Storage Engines a Server Supports . . . . . . . . . . . 669

12.7.2 Configuring the MyISAM Storage Engine . . . . . . . . . . . . . . . . . . . . 671

12.7.3 Configuring the InnoDB Storage Engine . . . . . . . . . . . . . . . . . . . . 674

12.7.4 Configuring the Falcon Storage Engine . . . . . . . . . . . . . . . . . . . . . 680

12.8 Enabling or Disabling LOCAL Capability for LOAD DATA . . . . . . . . . . . 681

12.9 Internationalization and Localization Issues . . . . . . . . . . . . . . . . . . . . . 681

12.9.1 Configuring Time Zone Support . . . . . . . . . . . . . . . . . . . . . . . . . . 682

12.9.2 Selecting the Language for Error Messages . . . . . . . . . . . . . . . . . 684

12.9.3 Configuring Character Set Support . . . . . . . . . . . . . . . . . . . . . . . . 684

12.10 Running Multiple Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 685

12.10.1 General Multiple Server Issues . . . . . . . . . . . . . . . . . . . . . . . . . 686

12.10.2 Configuring and Compiling Different Servers . . . . . . . . . . . . . . . . 688

12.10.3 Strategies for Specifying Startup Options . . . . . . . . . . . . . . . . . . 690

12.10.4 Using mysqld_multi for Server Management. . . . . . . . . . . . . 691

12.10.5 Running Multiple Servers on Windows . . . . . . . . . . . . . . . . . . . . 693

12.11 Updating MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695

13 Access Control and Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699 13.1 Internal Security: Preventing Unauthorized Filesystem Access . . . . . . . . 700

13.1.1 How to Steal Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 701

13.1.2 Securing Your MySQL Installation. . . . . . . . . . . . . . . . . . . . . . . . . 702

13.2 External Security: Preventing Unauthorized Network Access . . . . . . . . . 709

13.2.1 Structure and Contents of the MySQL Grant Tables. . . . . . . . . . . . 709

13.2.2 How the Server Controls Client Access . . . . . . . . . . . . . . . . . . . . 719

13.2.3 A Privilege Puzzle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 724

13.2.4 Grant Table Risks to Avoid. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 728

13.3 Setting Up Secure Connections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 731

14 Database Maintenance, Backups, and Replication . . . . . . . . . . . . . . . . 737 14.1 Principles of Preventive Maintenance. . . . . . . . . . . . . . . . . . . . . . . . . . 737

14.2 Performing Database Maintenance with the Server Running . . . . . . . . . 739

14.2.1 Locking Individual Tables for Read-Only or Read/Write Access . . . . 740

14.2.2 Locking All Databases for Read-Only Access. . . . . . . . . . . . . . . . . 743

14.3 General Preventative Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . 743

14.3.1 Using the Server’s Auto-Recovery Capabilities. . . . . . . . . . . . . . . . 744

14.3.2 Scheduling Preventive Maintenance . . . . . . . . . . . . . . . . . . . . . . . 745

14.4 Making Database Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 746

xvContents

14.4.1 Making Text Backups with mysqldump . . . . . . . . . . . . . . . . . . . . 748

14.4.2 Making Binary Database Backups . . . . . . . . . . . . . . . . . . . . . . . . 751

14.4.3 Backing Up InnoDB or Falcon Tables . . . . . . . . . . . . . . . . . . . . . . 754

14.5 Copying Databases to Another Server . . . . . . . . . . . . . . . . . . . . . . . . . 755

14.5.1 Copying Databases Using a Backup File. . . . . . . . . . . . . . . . . . . . 755

14.5.2 Copying Databases from One Server to Another . . . . . . . . . . . . . . 756

14.6 Checking and Repairing Database Tables. . . . . . . . . . . . . . . . . . . . . . . 757

14.6.1 Using the Server to Check and Repair Tables . . . . . . . . . . . . . . . . 758

14.6.2 Using mysqlcheck to Check and Repair Tables . . . . . . . . . . . . . 759

14.6.3 Using myisamchk to Check and Repair Tables . . . . . . . . . . . . . . 760

14.7 Using Backups for Data Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . 763

14.7.1 Recovering Entire Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . 764

14.7.2 Recovering Individual Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 764

14.7.3 Re-Executing Statements in Binary Log Files . . . . . . . . . . . . . . . . 765

14.7.4 Coping with InnoDB Auto-Recovery Problems . . . . . . . . . . . . . . . . 767

14.8 Setting Up Replication Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 768

14.8.1 How Replication Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769

14.8.2 Establishing a Master-Slave Replication Relationship . . . . . . . . . . 770

14.8.3 Binary Logging Formats. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773

14.8.4 Using a Replication Slave for Making Backups . . . . . . . . . . . . . . . 774

IV: Appendixes

A Obtaining and Installing Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . 777 A.1 Obtaining the sampdb Sample Database Distribution . . . . . . . . . . . . . . 777

A.2 Obtaining MySQL and Related Software . . . . . . . . . . . . . . . . . . . . . . . . . 778

A.3 Choosing a Version of MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 780

A.4 Installing MySQL on Unix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 780

A.4.1 Creating a Login Account for the MySQL User . . . . . . . . . . . . . . . . . 782

A.4.2 Obtaining and Installing a MySQL Distribution on Unix. . . . . . . . . . . 782

A.4.3 Post-Installation Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 786

A.4.4 Installing Perl DBI Support on Unix . . . . . . . . . . . . . . . . . . . . . . . . 789

A.4.5 Installing Apache and PHP on Unix. . . . . . . . . . . . . . . . . . . . . . . . . 790

A.5 Installing MySQL on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 792

A.5.1 Installing Perl DBI Support on Windows . . . . . . . . . . . . . . . . . . . . . 796

A.5.2 Installing Apache and PHP on Windows . . . . . . . . . . . . . . . . . . . . . 796

xvi Contents

B Data Type Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 797 B.1 Numeric Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 799

B.1.1 Integer Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 799

B.1.2 Fixed-Point Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 801

B.1.3 Floating-Point Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 801

B.1.4 BIT Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 803

B.2 String Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 803

B.2.1 Binary String Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805

B.2.2 Non-Binary String Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 807

B.2.3 ENUM and SET Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 809

B.3 Date and Time Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 809

B.4 Spatial Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 811

C Operator and Function Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 813 C.1 Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 814

C.1.1 Operator Precedence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 814

C.1.2 Grouping Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815

C.1.3 Arithmetic Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 816

C.1.4 Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 817

C.1.5 Bit Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823

C.1.6 Logical Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 824

C.1.7 Cast Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 825

C.1.8 Pattern-Matching Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 826

C.2 Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 830

C.2.1 Comparison Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 831

C.2.2 Cast Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 833

C.2.3 Numeric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 834

C.2.4 String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 840

C.2.5 Date and Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 852

C.2.6 Summary Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 868

C.2.7 Security and Compression Functions . . . . . . . . . . . . . . . . . . . . . . . 871

C.2.8 Advisory Locking Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 875

C.2.9 Spatial Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 877

C.2.10 XML Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 883

C.2.11 Miscellaneous Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 883

xviiContents

D System, Status, and User Variable Reference . . . . . . . . . . . . . . . . . . . . 889 D.1 System Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 889

D.2 Session-Only System Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 921

D.3 Status Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 924

D.3.1 InnoDB Status Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 930

D.3.2 Query Cache Status Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 933

D.3.3 SSL Status Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 934

D.4 User-Defined Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 935

E SQL Syntax Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 937 E.1 SQL Statement Syntax (Non-Compound Statements) . . . . . . . . . . . . . . . 938

E.2 Compound Statement Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1028

E.2.1 Control Structure Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . 1029

E.2.2 Declaration Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1031

E.2.3 Cursor Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1033

E.3 Comment Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1033

F MySQL Program Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1037 F.1 Displaying a Program’s Help Message . . . . . . . . . . . . . . . . . . . . . . . . . 1038

F.2 Specifying Program Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1039

F.2.1 Standard MySQL Program Options . . . . . . . . . . . . . . . . . . . . . . . . 1041

F.2.2 Option Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1045

F.2.3 Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1049

F.3 myisamchk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1051

F.3.1 Standard Options Supported by myisamchk . . . . . . . . . . . . . . . . 1052

F.3.2 Options Specific to myisamchk . . . . . . . . . . . . . . . . . . . . . . . . . 1053

F.3.3 Variables for myisamchk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1056

F.4 myisampack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1058

F.4.1 Standard Options Supported by myisampack . . . . . . . . . . . . . . . 1058

F.4.2 Options Specific to myisampack . . . . . . . . . . . . . . . . . . . . . . . . 1058

F.5 mysql. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1059

F.5.1 Standard Options Supported by mysql . . . . . . . . . . . . . . . . . . . . 1060

F.5.2 Options Specific to mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1061

F.5.3 Variables for mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1065

F.5.4 mysql Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1066

F.5.5 mysql Prompt Definition Sequences . . . . . . . . . . . . . . . . . . . . . . 1068

xviii Contents

F.6 mysql.server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1070

F.6.1 Options Supported by mysql.server . . . . . . . . . . . . . . . . . . . . 1070

F.7 mysql_config . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1071

F.7.1 Options Specific to mysql_config . . . . . . . . . . . . . . . . . . . . . . 1071

F.8 mysql_install_db . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1071

F.8.1 Standard Options Supported by mysql_install_db . . . . . . . . . 1072

F.8.2 Options Specific to mysql_install_db . . . . . . . . . . . . . . . . . . 1072

F.9 mysqladmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1072

F.9.1 Standard Options Supported by mysqladmin . . . . . . . . . . . . . . . 1073

F.9.2 Options Specific to mysqladmin . . . . . . . . . . . . . . . . . . . . . . . . 1073

F.9.3 Variables for mysqladmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1074

F.9.4 mysqladmin Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1074

F.10 mysqlbinlog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1076

F.10.1 Standard Options Supported by mysqlbinlog . . . . . . . . . . . . . 1076

F.10.2 Options Specific to mysqlbinlog . . . . . . . . . . . . . . . . . . . . . . 1077

F.10.3 Variables for mysqlbinlog . . . . . . . . . . . . . . . . . . . . . . . . . . . 1079

F.11 mysqlcheck . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1079

F.11.1 Standard Options Supported by mysqlcheck . . . . . . . . . . . . . . 1079

F.11.2 Options Specific to mysqlcheck . . . . . . . . . . . . . . . . . . . . . . . 1080

F.12 mysqld. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1083

F.12.1 Standard Options Supported by mysqld . . . . . . . . . . . . . . . . . . 1083

F.12.2 Options Specific to mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . 1084

F.12.3 Variables for mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1100

F.13 mysqld_multi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1101

F.13.1 Standard Options Supported by mysqld_multi . . . . . . . . . . . . 1101

F.13.2 Options Specific to mysqld_multi . . . . . . . . . . . . . . . . . . . . . 1101

F.14 mysqld_safe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1102

F.14.1 Standard Options Supported by mysqld_safe . . . . . . . . . . . . . 1102

F.14.2 Options Specific to mysqld_safe . . . . . . . . . . . . . . . . . . . . . . 1102

F.15 mysqldump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1104

F.15.1 Standard Options Supported by mysqldump . . . . . . . . . . . . . . . 1105

F.15.2 Options Specific to mysqldump . . . . . . . . . . . . . . . . . . . . . . . . 1105

F.15.3 Data Format Options for mysqldump . . . . . . . . . . . . . . . . . . . . 1112

F.15.4 Variables for mysqldump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1112

F.16 mysqlhotcopy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1113

F.16.1 Standard Options Supported by mysqlhotcopy . . . . . . . . . . . . 1114

F.16.2 Options Specific to mysqlhotcopy . . . . . . . . . . . . . . . . . . . . . 1114

xixContents

F.17 mysqlimport . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1116

F.17.1 Standard Options Supported by mysqlimport . . . . . . . . . . . . . 1117

F.17.2 Options Specific to mysqlimport . . . . . . . . . . . . . . . . . . . . . . 1117

F.17.3 Data Format Options for mysqlimport . . . . . . . . . . . . . . . . . . 1118

F.18 mysqlshow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1119

F.18.1 Standard Options Supported by mysqlshow . . . . . . . . . . . . . . . 1119

F.18.2 Options Specific to mysqlshow . . . . . . . . . . . . . . . . . . . . . . . . 1119

F.19 perror. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1120

F.19.1 Standard Options Supported by perror . . . . . . . . . . . . . . . . . . 1120

Note: Appendixes G, H, and I are located online and are accessible either by registering this book at informit.com/register or by visiting www.kitebird.com/mysql-book.

G C API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1121 G.1 Compiling and Linking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1122

G.2 C API Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1123

G.2.1 Scalar Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1123

G.2.2 Non-Scalar Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1124

G.2.3 Accessor Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1135

G.3 C API Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1136

G.3.1 Client Library Initialization and Termination Routines. . . . . . . . . . . 1136

G.3.2 Connection Management Routines . . . . . . . . . . . . . . . . . . . . . . . 1137

G.3.3 Error-Reporting Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1149

G.3.4 Statement Construction and Execution Routines . . . . . . . . . . . . . 1150

G.3.5 Result Set Processing Routines. . . . . . . . . . . . . . . . . . . . . . . . . . 1152

G.3.6 Information Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1161

G.3.7 Transaction Control Routines. . . . . . . . . . . . . . . . . . . . . . . . . . . . 1164

G.3.8 Multiple Result Set Routines. . . . . . . . . . . . . . . . . . . . . . . . . . . . 1164

G.3.9 Prepared Statement Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . 1165

G.3.10 Administrative Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1173

G.3.11 Threaded Client Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1175

G.3.12 Debugging Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1175

H Perl DBI API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1177 H.1 Writing Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1178

H.2 DBI Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1178

H.2.1 DBI Class Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1180

H.2.2 Database-Handle Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1185

xx Contents

H.2.3 Statement-Handle Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1191

H.2.4 General Handle Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1195

H.2.5 MySQL-Specific Administrative Methods . . . . . . . . . . . . . . . . . . . . 1196

H.3 DBI Utility Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1197

H.4 DBI Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1198

H.4.1 Database-Handle Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1198

H.4.2 General Handle Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1199

H.4.3 MySQL-Specific Database-Handle Attributes . . . . . . . . . . . . . . . . . 1200

H.4.4 Statement-Handle Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1201

H.4.5 MySQL-Specific Statement-Handle Attributes . . . . . . . . . . . . . . . . 1203

H.4.6 Dynamic Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1205

H.5 DBI Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1205

I PHP API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1207 I.1 Writing PHP Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1207

I.2 PDO Classes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1208

I.3 PDO Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1208

I.3.1 PDO Class Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1209

I.3.2 PDOStatement Object Methods. . . . . . . . . . . . . . . . . . . . . . . . . 1215

I.3.3 PDOException Object Methods. . . . . . . . . . . . . . . . . . . . . . . . . 1222

I.3.4 PDO Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1223

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1225

About the Author Paul DuBois is a writer, database administrator, and leader in the open source and MySQL communities. He has contributed to the online documentation for MySQL and is the author of MySQL and Perl for the Web (New Riders), MySQL Cookbook, Using csh and tcsh, and Software Portability with imake (O’Reilly). He is currently a technical writer with the MySQL documentation team at Sun Microsystems.

Acknowledgments Acknowledgments are presented here by edition.

Fourth Edition My technical reviewers, Stephen Frein and Tim Boronczyk, identified many points that needed correction or clarification. Ulf Wendel and Johannes Schlüter made comments and corrections on the PHP material. My thanks to each of them.

The staff at Pearson responsible for this edition were Mark Taber,Acquisitions Editor; Michael Thurston, Development Editor; Jovana San Nicolas-Shirley, Project Editor; Jake McFarland, Compositor; Cheryl Lenser, Indexer; and Gary Adair, Cover Designer.

To my wife Karen, my continued thanks and gratitude for her encouragement and support throughout this effort.

Third Edition The third edition enjoyed careful technical review by Zak Greant and Chris Newman. Their efforts improved the manuscript at many points. Monty and the developers at MySQL AB also provided insight in response to my questions.

The people at Pearson responsible for this edition were Shelley Johnston,Acquisitions Editor; Damon Jordan, Development Editor; and Andy Beaster, Project Editor.

I am happy to recognize that my wife Karen again deserves special credit for her sup- port during yet more revision and rewriting.

Second Edition For the second edition, the technical reviewers once again played a crucial role in find- ing errors and making corrections and clarifications. Hang Lau and Shane Kirk served as reviewers. I’d also like to thank Monty Widenius,Alexander Barkov, Jani Tolonen, and the other MySQL developers for patiently enduring my many questions and supplying answers that made their way into these pages.

The New Riders staff that brought this edition to life were Stephanie Wall,Associate Publisher; Chris Zahn, Development Editor; Lori Lyons, Senior Project Editor; Pat Kinyon, Copy Editor; Cheryl Lenser, Indexer; and Stacey Richwine-DeRome, Compositor.

And, as always, my wife Karen provided the behind-the-scenes support that readers do not see, but without which this book would be much poorer.

First Edition This book benefited greatly from the comments, corrections, and criticisms provided by the technical reviewers: David Axmark,Vijay Chaugule, Chad Cunningham, Bill Gerrard, Jijo George John, Fred Read, Egon Schmid, and Jani Tolonen. Special thanks goes to Michael “Monty”Widenius, the principal MySQL developer, who not only reviewed the manuscript, but also fielded hundreds of questions that I sent his way during the course of writing the book. Naturally, any errors that remain are my own. I’d also like to thank Tomas Karlsson, Colin McKinnon, Sasha Pachev, Eric Savage, Derick H. Siddoway, and Bob Worthy, who reviewed the initial proposal and helped shape the book into its pres- ent form.

The staff at New Riders are responsible first for conceiving this book and then for turning my scribblings into the finished work you hold in your hands. Laurie Petrycki acted as Executive Editor. Katie Purdum,Acquisitions Editor, helped me get under way and took the heat when I missed deadlines. Leah Williams did double duty not only as Development Editor but as Copy Editor; she put in many, many late hours, especially in the final stages of the project. Cheryl Lenser and Tim Wright produced the index. John Rahm served as Project Editor. Debra Neel proofread the manuscript. Gina Rexrode and Wil Cruz, Compositors, laid out the book in the form you see now. My thanks to each of them.

Most of all, I want to express my appreciation to my wife, Karen, for putting up with another book, and for her understanding and patience as I disappeared, sometimes for days on end, into “the writing zone.” Her support made the task easier on many occasions, and I am pleased to acknowledge her contribution; she helped me write every page.

We Want to Hear from You! As the reader of this book, you are our most important critic and commentator.We value your opinion and want to know what we’re doing right, what we could do better, what areas you’d like to see us publish in, and any other words of wisdom you’re willing to pass our way.

You can email or write me directly to let me know what you did or didn’t like about this book—as well as what we can do to make our books stronger.

Please note that I cannot help you with technical problems related to the topic of this book, and that due to the high volume of mail I receive, I might not be able to reply to every message.

When you write, please be sure to include this book’s title and author as well as your name and phone or email address. I will carefully review your comments and share them with the author and editors who worked on the book.

Email: feedback@developers-library.info Mail: Mark Taber

Associate Publisher Pearson Education 800 East 96th Street Indianapolis, IN 46240 USA

Reader Services Visit our website and register this book at informit.com/register for convenient access to any updates, downloads, or errata that might be available for this book.

This page intentionally left blank

no comments were posted
This is only a preview
3 shown on 1326 pages
Download the document