Praise for The Definitive Guide to MySQL, Second Edition: "In short, it is one of the most exhaustive books on the subject and provides every thingfor a new user as well as complete reference coverage for an experienced user, programmer, or administrator. The Definitive Guide to MySQL, Second Edition is highly recommended for anyone interested in learning, administering, or program ming MySQL." -Harold McFarland, The Midwest Book Review (www.midwestbookreview.com) Praise for the first edition of The Definitive Guide to MySQL: "There are a number ofb ooks written on the subject ofMySQL databases. I have had the opportunity to read some and have found many oft hem lacking and confusing. I happened to get a glimpse ofa book simply called MySQL, it seemed quite inter esting and as I was about to build a dynamically driven website for a client I felt I should get this book as the others I have seen were not up to the job. As I suspected from my chancery glimpse, it was what I needed. "MySQL by Michael Kofler is a comprehensive guide to MySQL. The book explains . how to install and configure MySQL on both Linux/UNIX and also on MS Windows NTI200019x environments. Kofler presents some of the issues one should consider when using MySQL. The books guides you through SQL programming and MySQL administration." -M. Williams, Kemputing (http://kemputing.net) "Whatever the secret formula that goes into Apress books, the result produced is clear, thorough, useful, powerful books that are easy to read, grasp, and apply. The titles are focused on explodingly popular computer topics. MySQL by Michael Kofler lives up to the high Apress standard." -International News Agency (www.inewsagency.com) The Definitive Guide to MySQl Second Edition MICHAEL KOFLER Translated by DAVID KRAMER APress Media, LLC The Definitive Guide to MySQL, Second Edition Copyright ©2004 by Michael Kofler Originally published by Apress in 2004 All rights reserved. No part of this work may be reproduced or transmitted in any fonn or by any means, elecuonic or mechanical, including photocopying. recording. or by any infonnation storage or retrieval system, without the prior written pennission of the copyright owner and the publisher. ISBN 978-1-59059-144-4 ISBN 978-1-4302-0669-9 (eBook) DOI 10.1007/978-1-4302-0669-9 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Translator, Editor, and Compositor: David Kramer Editorial Board: Steve Anglin, Dan Appleman, Craig Berry, Gary Cornell, Tony Davis, Jason Gilmore, Steven Rycroft, Julian Skinner, Martin Streicher, Jim Sumser, Karen Watterson, Gavin Wray, John Zukowski Assistant Publisher: Grace Wong Production Manager: Kart Brooks Proofreader: Elizabeth Berry Cover Designer: Kurt Krames Manufacturing Manager: Tom Debolski In the United States: phone 1-800-SPRINGER, email orders~pringer-ny.cOln, or visit http://_.springer-ny.com. Outside the United States: fax +49 6221 345229, email orderstlspringer. de, or visit http://_.springer .de. For infonnation on translations, please contact Apress directly at 2560 Ninth Street, SUite 219, BerkeJey, CA 94710. Phone 510-549-5930, fax 510-549-5939, email info{lapress ,COlI, or visit http://_ ...p ress .COlLt The infonnation in this book is distributed on an ~as is» basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the infonnation contained in this work. Contents at a Glance About the Author xi About the Translator xii Preface xiii I Introduction 1 1 What Is MySQU 3 2 The Test Environment 21 3 Introductory Example (An Opinion Poll) 67 4 User Interfaces 85 II Fundamentals 83 5 Database Design 121 6 An Introduction to SQL 177 7 SQLRecipes 199 8 InnoDB Tables and Transactions 239 9 Security 261 10 Administration and Server Configuration 305 III Programming 373 11 PUP: Fundamentals and Programming Techniques 375 12 PUP: Library Management 413 13 PUP: Discussion Forum 467 14 Perl 495 15 Java, JSP' JDBC 523 16 CandC++ 555 17 Visual Basic, C#, ODBC 577 IV Reference 639 18 SQL Reference 641 19 MySQLToois 703 20 API Reference 733 V Appendices 759 A Glossary 761 B MySQL4.1 767 C Example Files 781 D Bibliography 783 Index 785 ill Contents About the Author xi About the Translator xii Preface xiii What Does This Book Offer? . xiii What Is New in the Second Edition? xiv What Does This Book Not Offer? xv Example Programs, Source Code xv Versions .. xvi Notation ... xvii Commands xviii Abbreviations xviii I Introduction 1 1 WhatIsMySQU 3 What Is a Database? . 4 MySQL ....... . 6 Features of MySQL 7 Limitations ofMySQL . 9 MySQL Version Numbers 11 MySQL Licensing . . . 14 Alternatives to MySQL . 18 Summary ....... 19 2 The Test Environment 21 Wmdows or Unix/Linux? 22 MySQL (Server Installation) . 23 MySQL (Client Installation) 37 Apache ... . 38 PHP .... . 46 phpMyAdmin 53 Perl ...... 55 Connector/ODBC 62 Editors ...... 63 Using MySQL with an ISP 64 v Contents 3 Introductory Example (An Opinion Poll) 67 Overview ....... . 68 Database Development . . . . . . . . . . 69 The Questionnaire. . . . . . . . . . . . . 75 Questionnaire Evaluation and Displaying Results 76 Ideas for Improvements . . . . . . . . . . . . . . 80 II Fundamentals 83 4 User Interfaces 85 Overview ........... . 86 mysql ............. . 87 WinMySQLadmin (Windows) . 93 MySQL Control Center (Windows, Linux) 97 phpMyAdmin (HTML) ......... . 101 5 Database Design 121 Introduction . . . 122 Database Theory 123 MySQL Data Types 137 Indexes ..... . 146 MySQL Table Types 151 Creating Databases, Tables, and Indexes 155 Example mylibrary (Library) ..... 158 Example myforum (Discussion Group) 170 Example exceptions (Special Cases) . 174 6 An Introduction to SQL 177 Introduction . . . . . . . 178 Simple Queries (SELECT) 179 Queries with Related Tables . 184 Uniting Query Results . . . . 188 Grouped Queries, Aggregate Functions . 189 Altering Data (INSERT, UPDATE, and DELETE) 191 7 SQL Recipes 199 Character Strings ... . . . . . . . . 200 Date and Time . . . . . . . . . . . . . 204 Variables and Conditionals (IF, CASE) 207 Copying Data from One Table to Another. 210 Pivot Tables . . . . . . . . . . 212 SubSELECTs . . . . . . . . . . 216 Ensuring the Integrity of Data. 219 Tracking Down Duplicates .. 224 Bringing a Table into Normal Form . 225 Processing the First or Last n Records 227 Selecting Random Records 229 Full-Text Search . . . . . . . . . . . . 231 vi Contents 8 InnoDB Tables and 1ransactlons 239 Introduction . . . . . . . . . . . . . . . . . ...... 240 First Experiments with InnoDB Tables 244 'Ii'ansactions . . . . . . . . . . . . . . 245 Integrity'Rules (Foreign Key Constraints) . 255 9 Security 261 Introduction . 262 FirstAid ... 267 The Internal Workings of the Access System 274 Tools for Setting Access Privileges. . . . . 290 Problems with Establishing a Connection . 296 System Security . . . . . . . . . . . . . . . 301 10 Administration and Server Configuration 305 Basic Administration 306 Backups .............. . 310 Database Migration . . . . . . . . . 316 Importing and Exporting Text Files . 320 International Customization, Character Sets . 331 Logging ............ . 335 Replication ............ . 342 Maintenance ofMyISAM Tables . 353 Administration ofInnoDB Tables . 357 Server 1\ming . . . . . . . . 365 ISP Database Administration 369 III Programming 373 11 PHP: Fundamentals and Programming Techniques 375 Introduction . . . . . . . . . . . . . . . . . . . . . 376 Establishing a Connection to the Database . . . . 378 Executing SQL Commands . . . . . . . . . . 383 SELECT Queries . . . . . . . . . . . . . . . . . . 385 Changes to Data (INSERT, UPDATE, DELETE) . . 390 Character Strings, BWBs, DATEs, SETs, ENUMs, andNULL. . . . . . .. . .... 391 Error Checking ....... . 403 Storing and Displaying Images 404 12 PHP: UbraryManagement 413 Introduction . . . . . . . . . . . . . . . . . . . 414 Auxiliary Functions (mylibraryconnect.inc.php) 414 Book Search (jind.php) ..... . . . . . . . . 416 Simple Input of New Books (simpleinput.php) . 430 Convenient Input of New Book Data (input. php) . 433 Managing the Book Categories (categories.php) 455 Ideas for Improvements and Extensions . . . . . 465 vii Contents 13 PUP: Discussion Forum 467 How to Run It . . . . . . 468 Overview of the Program Code 471 Registration (forumlogin. php) . 473 Defining a New User (forumnewlogin. php) 475 Display list of All Discussion Groups (forumlist. php) 477 Message List of a Discussion Group (forumread. php) . 478 Representation of Message Text (forummessage. php) 484 Displaying a Thread (forumthread. php) . . . . . . . 486 Contributing to a Discussion (forumwri te • php) . 487 Leaving the Forum (forumlogout. php) 492 Possibilities for Extension . . . . . . . . . . . . 492 14 Perl 495 Programming Techniques . . . . . . . . . . . . . . . 496 Example: Deleting Invalid Data Records (mylibrary) . 512 CGI Example: Library Management (mylibrary) 514 15 Java, JSP' JDBC 523 Introduction . . . . . . . . 524 Programming Techniques . 528 Java Server Pages OSP) .. 548 16 C and C++ 555 The C Application Programming Interface 556 Connector/C++ (MySQL++) . . .... 567 17 Visual Basic, C#, ODBC 577 Fundamentals . . . . . . 578 Access .......... 584 Data Analysis with Excel. 590 ADO Programming and Visual Basic/VBA 592 MyVbQL (Visual Basic Programming Without ADO/ODBC) . 612 ADO.NET (Visual Basic .NET, C#) . . . . . . . . . 616 Converter: Microsoft SQL Server MySQL . . . . . . . . . 635 -t IV Reference 639 18 SQL Reference 641 Syntax ......... . 642 Operators ....... . 647 Variables and Constants . 651 MySQL Data Types ... 653 Command Overview (Thematic) . 655 Command Reference (Alphabetical) 657 Function Reference . . . . . . . . . . 692 viii Contents 19 MySQL Tools 703 Overview ................................... . 704 Common Options and Configuration Files . . . . . . . . . . . . . . . . . . 705 mysqld(Server) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 711 mysqld_safe(Server Start) .......................... . 718 mysql_ install_d b(New Installation of mysql Database) . . . . . . . . . . . 719 mysql_f ix_ privilegesCUpdating the mysql Database) . . . . . . . . . . . . 719 mysql_f ix_ extension 5 (Renaming MylSAM Files) ............. . 720 mysql(SQL Command Interpreter) ..................... . 720 mysqladmin(Administration) . . . . . . . . . . . . . . . . . . . . . . . . . . 723 mysqldump(Backup/Export) . . . . . . . . . . . . . . . . . . . . . . . . . . 724 mysqlimport(Text Import, Bulk Import) . . . . . . . . . . . . . . . . . . . . 727 mysqlshow(Displaying Information) . . . . . . . . . . . . . . . . . . . . . . 728 mysqlbug(Sending Error Notification) . . . . . . . . . . . . . . . . . . . . . 728 myisarrichk(Repairing MylSAM Files) .................... . 729 myisampack(Compressing MyISAM Files) . . . . . . . . . . . . . . . . . . . 731 20 API Reference 733 PHPAPI ......... . 734 PerlDBI ......... . 738 JDBC (Connector/J) . . . . . . 745 CAPI ............... . 752 V Appendices 759 A Glossary 761 B MySQL4.1 767 C Example Files 781 D Bibliography 783 Index 785 ix