MySQL

1311 Pages·2009·3.974 MB·English

by  Paul DuBois

MySQL
®
Fourth Edition
Paul DuBois
Upper Saddle River, NJ • Boston • Indianapolis • San Francisco
New York • Toronto • Montreal • London • Munich • Paris • Madrid
Cape Town • Sydney • Tokyo • Singapore • Mexico City
MySQL® Acquisitions Editor
Fourth Edition Mark Taber
Copyright © 2009 by Pearson Education,Inc. Development Editor
All rights reserved. No part of this book shall be reproduced,stored in a retrieval system,or Michael Thurston
transmitted by any means,electronic,mechanical,photocopying,recording,or otherwise, Managing Editor
without written permission from the publisher. No patent liability is assumed with respect to
Kristy Hart
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 Project Editor
omissions. Nor is any liability assumed for damages resulting from the use of the informa- Jovana
tion contained herein. San Nicolas-Shirley
ISBN-13: 978-0-672-32938-8 Indexer
ISBN-10: 0-672-32938-7 Cheryl Lenser
Proofreaders
Library of Congress Cataloging-in-Publication Data
Leslie Joseph
Water Crest
DuBois,Paul,1956-
Publishing
MySQL / Paul DuBois. — 4th ed.
Technical Editors
p. cm. Stephen Frein
Includes index. Tim Boronczyk
ISBN 978-0-672-32938-8 (pbk.) Publishing
1. SQL (Computer program language) 2. MySQL (Electronic resource) 3. Database man- Coordinator
agement. I. Title. Vanessa Evans
QA76.73.S67D588 2009 Cover Designer
005.13’3—dc22 Gary Adair
2008030855 Compositor
Jake McFarland
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
[email protected]
For sales outside of the U.S.,please contact
International Sales
[email protected]
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/registeror 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
Contents vii
1.4.7 Adding New Rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
1.4.8 Resetting the sampdbDatabase 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.2INandNOT INSubqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
2.9.3ALL,ANY,and SOMESubqueries . . . . . . . . . . . . . . . . . . . . . . . . . 161
2.9.4EXISTSandNOT EXISTSSubqueries . . . . . . . . . . . . . . . . . . . . 162
2.9.5 Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
2.9.6 Subqueries in the FROMClause . . . . . . . . . . . . . . . . . . . . . . . . . . 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 FULLTEXTSearches. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
2.15.1 Natural Language FULLTEXTSearches. . . . . . . . . . . . . . . . . . . . 196
2.15.2 Boolean Mode FULLTEXTSearches. . . . . . . . . . . . . . . . . . . . . . 197
2.15.3 Query Expansion FULLTEXTSearches . . . . . . . . . . . . . . . . . . . . 199
2.15.4 Configuring the FULLTEXTSearch 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 NULLValue. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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
Contents ix
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_INCREMENTProperties. . . . . . . . . . . . . . . . . . . . . 254
3.4.2 Storage Engine-Specific AUTO_INCREMENTProperties . . . . . . . . . 256
3.4.3 Issues to Consider with AUTO_INCREMENTColumns . . . . . . . . . . 259
3.4.4 Tips for Working with AUTO_INCREMENTColumns . . . . . . . . . . . . 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 EXPLAINto 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.5mysql_store_result()Versus mysql_use_result(). . . . 398
Contents xi
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 NULLValues 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