Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
About the Translator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
PART 1 ¦ ¦ ¦ Introduction
¦CHAPTER 1 What Is MySQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
¦CHAPTER 2 The Test Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
¦CHAPTER 3 Introductory Example (An Opinion Poll with PHP) . . . . . . . . . . . . . . . . . . . . 47
PART 2 ¦ ¦ ¦ Administrative Tools and
User Interfaces
¦CHAPTER 4 mysql, mysqladmin, and mysqldump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
¦CHAPTER 5 MySQL Administrator and MySQL Query Browser. . . . . . . . . . . . . . . . . . . . . 71
¦CHAPTER 6 phpMyAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
¦CHAPTER 7 Microsoft Office, OpenOffice/StarOffice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
PART 3 ¦ ¦ ¦ Fundamentals
¦CHAPTER 8 Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
¦CHAPTER 9 An Introduction to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
¦CHAPTER 10 SQL Recipes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
¦CHAPTER 11 Access Administration and Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
¦CHAPTER 12 GIS Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
¦CHAPTER 13 Stored Procedures and Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
¦CHAPTER 14 Administration and Server Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
iii
PART 4 ¦ ¦ ¦ Programming
¦CHAPTER 15 PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
¦CHAPTER 16 Perl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481
¦CHAPTER 17 Java (JDBC and Connector/J) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
¦CHAPTER 18 C . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527
¦CHAPTER 19 Visual Basic 6/VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
¦CHAPTER 20 Visual Basic .NET and C# . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
PART 5 ¦ ¦ ¦ Reference
¦CHAPTER 21 SQL Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
¦CHAPTER 22 MySQL Tools and Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667
¦CHAPTER 23 API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
PART 6 ¦ ¦ ¦ Appendixes
¦APPENDIX A Glossary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 723
¦APPENDIX B The Files for this Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 729
¦APPENDIX C Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 731
¦INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 733
iv ¦CONTENTS AT A GLANCE
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
About the Translator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
PART 1 ¦ ¦ ¦ Introduction
¦CHAPTER 1 What Is MySQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
What Is a Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Relations, Database Systems, Servers, and Clients . . . . . . . . . . . . . . . . . . . 3
Relational Versus Object-Oriented Database Systems . . . . . . . . . . . . . . . . . 4
Tables, Records, Fields, Queries, SQL, Index, Keys . . . . . . . . . . . . . . . . . . . . 4
MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Features of MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Limitations of MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
MySQL Version Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Alpha, Beta, Gamma, Production (Generally Available) . . . . . . . . . . . . . . . . . 9
MySQL Functions Ordered by Version Number . . . . . . . . . . . . . . . . . . . . . . . 9
MySQL Licensing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Rights and Duties with Respect to the GPL . . . . . . . . . . . . . . . . . . . . . . . . . 11
Use of MySQL with an Open Source License . . . . . . . . . . . . . . . . . . . . . . . . 12
Use of MySQL with a Commercial License . . . . . . . . . . . . . . . . . . . . . . . . . 12
Commercial Licenses for Client Libraries
(Connector/ODBC, Connector/J, etc.) . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Client Licenses for the PHP Project, F(L)OSS Exception . . . . . . . . . . . . . . . 14
MySQL Version Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Support Contracts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Alternatives to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
¦CHAPTER 2 The Test Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Windows or Unix/Linux? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
MySQL in Practice (Public Internet Server) . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Development Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Installation Under Windows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
v
Installing Apache 2.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Installing MySQL 5.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Installing PHP 5.0. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Installing Perl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Installation Under SUSE Linux 9.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Installing Apache 2, PHP 5, and Perl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Installing MySQL 5.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Installation Under Red Hat Enterprise Linux 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Installing Apache 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Installing MySQL 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Compiling PHP 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Installing Perl 5.8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Compiling the MySQL Developer Version (Linux) . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Installing Bitkeeper . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Downloading the MySQL Developer Version . . . . . . . . . . . . . . . . . . . . . . . . . 37
Compiling MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Setting Up the mysql Database for Managing Access Privileges . . . . . . . . 37
Configuration Files and Init-V Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Starting the MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Configuring Apache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Configuration Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Elementary Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Access Privileges for Individual Directories (.htaccess) . . . . . . . . . . . . . . . 40
Configuring PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Configuring MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Launching the MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
¦CHAPTER 3 Introductory Example (An Opinion Poll with PHP) . . . . . . . . . . . 47
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Assumptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Database Development . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Executing the mysql Command-Line Interpreter . . . . . . . . . . . . . . . . . . . . . 49
Setting Up the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Creating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Why Make It Complicated, When It Could Be So Much Easier? . . . . . . . . . 52
The Questionnaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Questionnaire Evaluation and Displaying Results . . . . . . . . . . . . . . . . . . . . . . . . . 53
The mysql Interface Versus the mysqli Interface . . . . . . . . . . . . . . . . . . . . . 54
Establishing a Connection to the Database . . . . . . . . . . . . . . . . . . . . . . . . . 54
Evaluating the Data and Storing It in the Database . . . . . . . . . . . . . . . . . . . 54
Displaying the Survey Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Program Code (results.php) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
The Resulting HTML Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Ideas for Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
vi ¦CONTENTS
PART 2 ¦ ¦ ¦ Administrative Tools and
User Interfaces
¦CHAPTER 4 mysql, mysqladmin, and mysqldump. . . . . . . . . . . . . . . . . . . . . . . . . 61
mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Launching mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
mysql Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Using mysql Interactively . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Tips for Using mysql Under Unix/Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Tips for Using mysql Under Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Processing SQL Files with mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
mysqladmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
mysqldump. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
¦CHAPTER 5 MySQL Administrator and MySQL Query Browser . . . . . . . . . . . 71
Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Establishing a Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
MySQL Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Server Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Service Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Startup Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
User Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Server Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Health . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Server Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Restore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Replication Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Catalogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
MySQL Query Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Input and Execution of SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Changing Data in SELECT Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
History and Bookmarks for SQL Commands. . . . . . . . . . . . . . . . . . . . . . . . . 84
Executing Several Commands at Once (Scripts) . . . . . . . . . . . . . . . . . . . . . 84
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
MySQL Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
¦CONTENTS vii
¦CHAPTER 6 phpMyAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Installation and Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Installing phpMyAdmin Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Configuring phpMyAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
config Authentication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
http and Cookie Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
User Management, Securing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Securing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Creating New Users. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Creating and Editing Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Editing Existing Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Setting Foreign Key Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Documenting the Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Viewing, Inserting, and Editing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Executing SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Import and Export . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Database Backup (SQL File) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Exporting Tables (CSV Text File) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Importing a Database or Tables (SQL File) . . . . . . . . . . . . . . . . . . . . . . . . . 107
Inserting Table Data (Text File) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Server Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Auxiliary Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Creating a Database for phpMyAdmin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
SQL Bookmarks and History. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Saving Information on Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Creating a PDF Relational Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Transformations (Alternative Representation of Column Contents) . . . . . 115
¦CHAPTER 7 Microsoft Office, OpenOffice/StarOffice . . . . . . . . . . . . . . . . . . . . . 117
Installing Connector/ODBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Setting Up ODBC Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Importing and Exporting Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Converter: Access ? MySQL (exportsql.txt) . . . . . . . . . . . . . . . . . . . . . . . . 123
Microsoft Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Importing Data with MS Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Connector/J Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Connector/J . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
viii ¦CONTENTS
OpenOffice/StarOffice Base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Creating a Connection to a MySQL Database . . . . . . . . . . . . . . . . . . . . . . . 128
Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Forms, Reports, and Additional Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 132
OpenOffice/StarOffice Data Source View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Establishing a Data Source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Importing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
PART 3 ¦ ¦ ¦ Fundamentals
¦CHAPTER 8 Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Further Reading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Table Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
MyISAM Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
InnoDB Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
HEAP Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Temporary Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Other Table Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Table Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
MySQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Integers (xxxINT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Fixed-Point Numbers (DECIMAL) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Date and Time (DATE, TIME, DATETIME, TIMESTAMP) . . . . . . . . . . . . . . . . 145
Character Strings (CHAR, VARCHAR, xxxTEXT) . . . . . . . . . . . . . . . . . . . . . . 147
Binary Data (xxxBLOB and BIT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Options and Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Tips and Tricks on Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Rules for Good Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Tips for Naming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Tips on the Design Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Normalization Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
A First Attempt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
The First Normal Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Second Normal Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Third Normal Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Normalization Theory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Managing Hierarchies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Hierarchy Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Building the Hierarchy Tree. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Searching for Lower-Ranked Categories in the categories Table . . . . . . . 164
Searching for Higher-Ranked Categories in the categories Table . . . . . . 165
¦CONTENTS ix
Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
1:1 Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
1:n Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
n:m Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Primary and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Foreign Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Referential Integrity (Foreign Key Constraints) . . . . . . . . . . . . . . . . . . . . . . 170
Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Ordinary Indexes, Unique Indexes, Primary Indexes . . . . . . . . . . . . . . . . . 174
Full-Text Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Query and Index Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
The Definition of a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Example Database mylibrary (Library Management) . . . . . . . . . . . . . . . . . . . . . . 180
Properties of the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Properties of the Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Example Database myforum (Discussion Group) . . . . . . . . . . . . . . . . . . . . . . . . . 183
The Discussion Group Database myforum . . . . . . . . . . . . . . . . . . . . . . . . . 183
Hierarchies Among Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Example Database Exceptions (Special Cases). . . . . . . . . . . . . . . . . . . . . . . . . . . 185
The Table testall . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
The Table text_text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
The Table test_blob . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
The Table test_date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
The Table test_enum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
The Table test_null. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
The Table test_sort1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
The Table test_sort2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
The Tables importtable1, importtable2, exporttable. . . . . . . . . . . . . . . . . . 187
¦CHAPTER 9 An Introduction to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
DML, DDL, and DCL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Simple Queries (SELECT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Determining the Number of Data Records (Lines) . . . . . . . . . . . . . . . . . . . 191
Determining the Number of Distinct Data Records (DISTINCT). . . . . . . . . 191
Column Restriction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Limiting the Number of Resulting Records (LIMIT). . . . . . . . . . . . . . . . . . . 192
Determining the Number of Records Suppressed by LIMIT
(SQL_CALC_FOUND_ROWS, FOUND_ROWS) . . . . . . . . . . . . . . . . . . . . 192
x ¦CONTENTS
Sorting Records (ORDER BY) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Choosing the Sort Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Trying Out Different Sort Orders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Selecting Data Records (WHERE, HAVING) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Linking Data from Several Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
JOINs Across Two Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
JOINs Across Three or More Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Uniting Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Grouped Queries, Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
GROUP_CONCAT: Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
GROUP BY for Several Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
GROUP BY WITH ROLLUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Altering Data (INSERT, UPDATE, and DELETE) . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Executing a Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Inserting Data Records (INSERT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Altering Data Records (UPDATE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Deleting Data Records (DELETE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Creating New Tables, Databases, and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Creating a Database (CREATE DATABASE) . . . . . . . . . . . . . . . . . . . . . . . . . 210
Creating Tables (CREATE TABLE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Creating Indexes (CREATE INDEX) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Changing the Table Design (ALTER TABLE) . . . . . . . . . . . . . . . . . . . . . . . . . 212
Deleting Databases and Tables (DROP) . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Automatic Table Design Changes (Silent Column Changes) . . . . . . . . . . . 213
The SHOW Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
INFORMATION_SCHEMA Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
¦CHAPTER 10 SQL Recipes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Character Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Basic Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Changing the Character Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Setting the Client Character Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Pattern Matching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Date and Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Syntax for Date and Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Calculating with Date and Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Unix Timestamps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Time Zones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
ENUMs and SETs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
ENUM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
SET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
¦CONTENTS xi
Variables and Conditionals (IF, CASE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
IF Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
CASE Branching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Copying Data from One Table to Another. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Creating a New Table by Copying. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Copying into Existing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Pivot Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Pivot Tables for Book Titles, Languages, and Categories. . . . . . . . . . . . . . 235
Pivot Table Query Results by Month. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
SubSELECTs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Syntax Variants. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Ensuring the Integrity of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Searching for Titles Without Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Searching for Invalid Publisher Links:
Invalid Records in 1 : n Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Searching for Invalid Links Between Authors and Titles
(n : m Relation) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Tracking Down Duplicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Bringing a Table into Normal Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Processing the First or Last n Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Searching Data (SELECT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Changing Records (UPDATE and DELETE). . . . . . . . . . . . . . . . . . . . . . . . . . 245
Delete All Threads Except the Last 500. . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Selecting Random Records. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Selection Without a random Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Selection with a random Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Selection on the Basis of an id Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Full-Text Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Fundamentals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Book Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Forum Search. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
GET_LOCK and RELEASE_LOCK. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
Why Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
Controlling Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
Trying Out Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Transactions and Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Isolation Degree for Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Error Protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
xii ¦CONTENTS
¦CHAPTER 11 Access Administration and Security. . . . . . . . . . . . . . . . . . . . . . . . . 263
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Communication Between the Client and the MySQL Server . . . . . . . . . . . 263
Access Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
First Aid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
Protecting the MySQL Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Creating a New Database and User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Granting the Right to Create One’s Own Database. . . . . . . . . . . . . . . . . . . 271
Oops! I Forgot the root Password! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
The Internal Workings of the Access System . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Two-Tiered Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
The mysql Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
The user Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
The user.Host Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
The db and host Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
The tables_priv and columns_priv Tables. . . . . . . . . . . . . . . . . . . . . . . . . . 286
The procs_priv Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Tools for Setting Access Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Changing Access Privileges with GRANT and REVOKE . . . . . . . . . . . . . . . 288
Viewing Access Privileges with SHOW GRANT . . . . . . . . . . . . . . . . . . . . . . 289
Changing a Password with mysqladmin . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Secure Password Authentication Since MySQL 4.1 . . . . . . . . . . . . . . . . . . . . . . . 290
Updating the Client Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
old-passwords Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Parallel Operation with Old and New Passwords . . . . . . . . . . . . . . . . . . . . 291
Problems with Establishing a Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Possible Causes of the Connection Difficulty . . . . . . . . . . . . . . . . . . . . . . . 292
Further Tips for Error-Checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
System Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Security at the System Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Security Measures in the Storage of Critical Data . . . . . . . . . . . . . . . . . . . 296
The MySQL Server as Security Risk. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Running the MySQL Server Without root or Administrator Privileges . . . . 296
Network Security, Firewall . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
¦CHAPTER 12 GIS Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
GIS Data Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Specifying Coordinates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Well-Known Text,Well-Known Binary (OpenGIS) . . . . . . . . . . . . . . . . . . . . 300
¦CONTENTS xiii
MySQL’s GIS Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Simple Geometric Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Function for Spatial Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Indexing Geometric Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
SQL Examples (the Database glacier) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Creating the Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Inserting the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Querying the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
SQL Examples (the Database opengeodb) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Data Source and Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Radial Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
¦CHAPTER 13 Stored Procedures and Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Why Stored Procedures? Why Triggers? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Hello, SP World! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
The Command Interpreter mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
The MySQL Query Browser. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
SP Implementation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
SP Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Creating, Editing, and Deleting SPs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Backup and Restoration of SPs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
SP Syntax and Language Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
General Syntax Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
Calling SPs (CALL) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
Parameters and Return Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328
Encapsulation of Commands (BEGIN-END) . . . . . . . . . . . . . . . . . . . . . . . . . 329
Branching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Loops. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Error Handling (Handlers) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
SP Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Adding a Category . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Adding a Title . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
Determining Parent Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Producing a List of Hierarchically Ordered Categories. . . . . . . . . . . . . . . . 339
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Creating a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Deleting a Trigger. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Implementation Details and Administrative Assistance . . . . . . . . . . . . . . . 342
Functional Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
An Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
xiv ¦CONTENTS
¦CHAPTER 14 Administration and Server Configuration . . . . . . . . . . . . . . . . . . . 345
First Steps. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Using Administrative Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Setting the root Password. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
MySQL Server Configuration File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Restarting the MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Basic Configuration of the MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Directories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Communication Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Default Table Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Default Character Set and Sort Order for New Tables . . . . . . . . . . . . . . . . 349
Time Zones. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
The Language for Error Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
SQL Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Backing Up Databases (mysqldump) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Restoring a Database (mysql). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
Fast Backups (mysqlhotcopy). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Database Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Importing and Exporting Text Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Special Characters in the Imported or Exported File . . . . . . . . . . . . . . . . . 360
Working with Character Strings, Numbers, Dates, and BLOBs . . . . . . . . . 360
Importing with LOAD DATA INFILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
Importing with mysqlimport . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
Exporting with SELECT … INTO OUTFILE . . . . . . . . . . . . . . . . . . . . . . . . . . 364
Exporting with mysqldump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Exporting with mysql in Batch Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Why Logging?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Drawbacks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Logging Changes (update log) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Errors, Logins, and Slow Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
Administration of Logging Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
Setting Up the Replication Master System . . . . . . . . . . . . . . . . . . . . . . . . . 376
Setting Up the Replication Slave System . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Setting Up a Replication System with LOAD DATA . . . . . . . . . . . . . . . . . . . 379
Inside Replication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Client Programming. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
¦CONTENTS xv
Administration of MyISAM Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
myisamchk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Using myisamchk. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Speed Optimization, Memory Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Shrinking and Optimizing MyISAM Tables. . . . . . . . . . . . . . . . . . . . . . . . . . 385
Repairing MyISAM Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Compressing MyISAM Tables (myisampack) . . . . . . . . . . . . . . . . . . . . . . . 385
Administration of InnoDB Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Tablespace Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Logging Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
Server Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
Optimal Memory Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Query Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
ISP Database Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Working with ssh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
Working via phpMyAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
Implementing Custom PHP Scripts for Administration. . . . . . . . . . . . . . . . 398
Custom Perl Scripts for Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
PART 4 ¦ ¦ ¦ Programming
¦CHAPTER 15 PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
mysql Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
Establishing a Connection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Executing SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Evaluating SELECT Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Error Handling and Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
mysqli Classes, Methods, and Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
Which Will It Be: mysql or mysqli? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
Availability Tests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Overview of Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Making the Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Executing SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Evaluating SELECT Results (mysqli_result). . . . . . . . . . . . . . . . . . . . . . . . . 413
Executing Several SQL Commands at Once . . . . . . . . . . . . . . . . . . . . . . . . 415
SQL Commands with Parameters (Prepared Statements). . . . . . . . . . . . . 415
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
Outsourcing Database Functions into a Class. . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
More Security with a Separate Password File . . . . . . . . . . . . . . . . . . . . . . 418
Security and Convenience with the MyDb Class . . . . . . . . . . . . . . . . . . . . 419
xvi ¦CONTENTS
Displaying a SELECT Result As a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Character Strings, Dates, Times, BLOBs, and NULL . . . . . . . . . . . . . . . . . . . . . . . 424
Character Strings and BLOBs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424
Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425
NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
Inserting Linked Data Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
Processing Input from Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
Code Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
Creating the Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
Validation of Form Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
Storing the Form Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
Deleting a Title . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438
Room for Improvement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Pagewise Representation of Search Results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Code Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Author Search. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442
Title Search. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
Displaying Search Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
Links to Additional Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Managing Hierarchical Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Code Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Displaying the Category Tree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448
Inserting Subcategories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Deleting Categories and Subcategories . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
Searching for Parent Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
Searching for Subcategories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
Speed Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Ground Rules for Efficient Code Execution . . . . . . . . . . . . . . . . . . . . . . . . . 455
Statistical and Benchmarking Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456
Example: Generating the Category List Efficiently . . . . . . . . . . . . . . . . . . . 457
Unicode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
Setting the HTML and HTTP Coding. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
Binary Data (BLOBs) and Images . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464
Fundamentals and Programming Techniques. . . . . . . . . . . . . . . . . . . . . . . 465
Program Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
SP Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
Using the Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475
¦CONTENTS xvii
¦CHAPTER 16 Perl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481
Programming Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481
The Modules DBI and DBD::mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481
Establishing a Connection to the Database. . . . . . . . . . . . . . . . . . . . . . . . . 482
Executing SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Evaluating SELECT Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
Character Strings, BLOBs, DATEs, SETs, ENUMs, and NULL . . . . . . . . . . . 489
DBD::mysql-specific Methods and Attributes . . . . . . . . . . . . . . . . . . . . . . . 492
Unicode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494
Error-Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494
Example: Deleting Invalid Data Records (mylibrary). . . . . . . . . . . . . . . . . . . . . . . 496
CGI Example: Library Management (mylibrary). . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Book Search (mylibrary-find.pl) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Simple Input of New Books (mylibrary-simpleinput.pl) . . . . . . . . . . . . . . . 500
CGI Unicode Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503
Book Search (mylibrary-find-utf8.pl) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504
Input of New Books (mylibrary-simpleinput-utf8.pl) . . . . . . . . . . . . . . . . . 505
¦CHAPTER 17 Java (JDBC and Connector/J) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
The Java Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
Connector/J Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
Programming Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511
A First Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512
Establishing the Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513
Problems in Establishing a Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515
Executing SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516
Evaluating SELECT Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518
Prepared Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522
Batch Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523
Working with Binary Data (BLOBs). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523
¦CHAPTER 18 C . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527
The C API (libmysqlclient) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527
Hello,World! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527
Assumptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527
Introductory Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528
Compiling and Linking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
Makefile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530
xviii ¦CONTENTS
Establishing the Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532
Evaluation of the Configuration File my.cnf. . . . . . . . . . . . . . . . . . . . . . . . . 532
Evaluation of Options in the Command Line . . . . . . . . . . . . . . . . . . . . . . . . 532
Executing SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
Simple Commands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
Executing Several Commands at a Time. . . . . . . . . . . . . . . . . . . . . . . . . . . 535
Prepared Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538
Character Set Settings (Unicode) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542
Working with Binary Data and Special Characters. . . . . . . . . . . . . . . . . . . . . . . . 542
Storing Binary Data in a Database and Reading It . . . . . . . . . . . . . . . . . . . 543
Error Handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544
¦CHAPTER 19 Visual Basic 6/VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
Fundamentals and Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
Connector/ODBC Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546
ADO Programming and Visual Basic/VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548
Establishing the Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
Establishing the Connection with a DSN . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
Establishing a Connection Without a DSN . . . . . . . . . . . . . . . . . . . . . . . . . . 550
ADO Programming Techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553
Example: authors Column for the titles Table . . . . . . . . . . . . . . . . . . . . . . . 559
Example: Adding a New Book Title. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562
Example: Storing an Image File in BLOB Format and
Then Reading It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565
Converter: Microsoft SQL Server to MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567
Properties of mssql2mysql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567
Assumptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567
Restrictions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568
How to Use It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568
Setting Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568
VBMySQLDirect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570
Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570
Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571
Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571
¦CHAPTER 20 Visual Basic .NET and C# . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
Communication Between ADO.NET and MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 573
Establishing a Connection with Connector/Net. . . . . . . . . . . . . . . . . . . . . . 574
Establishing the Connection with the ODBC Data Provider . . . . . . . . . . . . 578
¦CONTENTS xix
Programming Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 580
Executing SQL Commands (MySqlCommand) . . . . . . . . . . . . . . . . . . . . . . 580
Commands with Parameters (MySql Parameters) . . . . . . . . . . . . . . . . . . . 581
Evaluating Individual SELECT Results (ExecuteScalar) . . . . . . . . . . . . . . . 582
Reading SELECT Results (MySqlDataReader) . . . . . . . . . . . . . . . . . . . . . . . 583
DataSet, DataTable, MySqlDataAdapter . . . . . . . . . . . . . . . . . . . . . . . . . . . 585
Auxiliary Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587
Error-Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587
Windows.Forms and ASP.NET Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
Example: Storing New Titles in mylibrary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
Example: Storing an Image File in a BLOB and Then Reading It . . . . . . . . . . . . . 592
PART 5 ¦ ¦ ¦ Reference
¦CHAPTER 21 SQL Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Object Names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Case Sensitivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600
Character Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600
Character Set and Sort Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601
Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
Automatic Transformation of Numbers and Character Strings . . . . . . . . . 602
Date and Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
Binary Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
Binary Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
Semicolons at the End of SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . 603
Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 604
Arithmetic Operators, Bit Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Pattern Matching with LIKE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Pattern Matching with REGEXP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Binary Character String Comparison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
Logical Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
Variables and Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607
Variable Assignment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607
Evaluating and Displaying Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607
Global System Variables versus System Variables at the
Connection Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608
SET PASSWORD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608
Structured Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608
xx ¦CONTENTS
MySQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609
Command Overview (Thematic) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611
Command Reference (Alphabetical). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614
Function Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
Arithmetic Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
Comparison Functions, Tests, Branching. . . . . . . . . . . . . . . . . . . . . . . . . . . 653
Type Conversion (Cast) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
String Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654
Date and Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 656
GROUP BY Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661
Additional Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662
GIS Data Types and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663
Language Elements for Stored Procedures and Triggers. . . . . . . . . . . . . . . . . . . 665
¦CHAPTER 22 MySQL Tools and Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667
Common Options and Configuration Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667
Common Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668
Setting Options in Configuration Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669
Memory Specifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 671
Options in Environment Variables (aka System Variables). . . . . . . . . . . . . 671
Rules for Specifying Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 671
mysqld (Server) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 672
Basic Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 672
Logging, Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674
InnoDB Configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 677
Miscellaneous . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 679
mysqld_safe (Server Start) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 679
mysql_install_db (New Installation of the mysql Database) . . . . . . . . . . . . . . . . 680
mysql_fix_privileges (Updating the mysql Database) . . . . . . . . . . . . . . . . . . . . . 680
mysql_fix_extensions (Renaming MyISAM Files) . . . . . . . . . . . . . . . . . . . . . . . . . 681
mysql (SQL Command Interpreter). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 681
mysqladmin (Administration) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
mysqldump (Backup/Export). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 684
mysqlimport (Text Import, Bulk Import) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687
mysqlshow (Displaying Information) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 688
Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 688
myisamchk (Repairing MyISAM Files) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 689
myisampack (Compressing MyISAM Files) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 690
¦CONTENTS xxi
¦CHAPTER 23 API Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
PHP API (mysql Interface) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
PHP-API (mysqli Interface) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697
The mysqli Class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697
mysqli_result Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 698
mysqli_stmt Class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699
Perl DBI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699
Common Variable Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 700
Establishing the Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 700
Executing SQL Commands, Evaluating SELECT Queries . . . . . . . . . . . . . . 701
Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 703
Auxiliary Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 704
MySQL-Specific Extension of the DBD::mysql Driver. . . . . . . . . . . . . . . . . 704
JDBC (Connector/J) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 706
Establishing a Connection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 706
Executing SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 707
Processing SELECT Results (ResultSet Class) . . . . . . . . . . . . . . . . . . . . . . 708
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709
ADO.NET (Connector/Net) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 710
Establishing a Connection, Connection Properties . . . . . . . . . . . . . . . . . . . 710
Executing and Evaluating SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . 710
Altering Data in DataSet/DataTable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712
C API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 713
Data Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 713
Connection and Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 715
Executing and Evaluating SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . 717
Prepared Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 719
PART 6 ¦ ¦ ¦ Appendixes
¦APPENDIX A Glossary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 723
¦APPENDIX B The Files for this Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 729
Trying Out the Example Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 729
Updates, Errata, Links, Discussion Forum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 729
¦APPENDIX C Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 731
Цена: 200руб.