book

Expert PHP and MySQL®

Name: Expert PHP and MySQL®
ISBN: 9780470563120

by Andrew Curioso, Ronald Bradford, Patrick Galbraith

March 2010

Intermediate to advanced

622 pages

15h 16m

English

Wrox

Read now

Unlock full access

Copyright
ABOUT THE AUTHORS
ABOUT THE TECHNICAL EDITORS
CREDITS
ACKNOWLEDGMENTS
INTRODUCTION
0.1. WHO THIS BOOK IS FOR0.2. WHAT THIS BOOK COVERS0.3. WHAT YOU NEED TO USE THIS BOOK0.3.1.0.3.1.1. Using Linux0.3.1.2. Using Windows0.4. CONVENTIONS0.5. SOURCE CODE0.6. ERRATA0.7. P2P.WROX.COM
1. Techniques Every Expert Programmer Needs to Know
1.1. OBJECT-ORIENTED PHP1.1.1. Instantiation and Polymorphism1.1.1.1. Polymorphism in Action1.1.1.2. Handling Terminal Types and Type Hinting1.1.2. Interfaces1.1.3. Magic Methods and Constants1.1.3.1. Practical Use of Magic Constants1.1.3.2. Adding Magic Functionality to Classes1.1.4. Design Patterns1.1.4.1. Singleton and Multiton Patterns1.1.4.2. Proxy and Façade Patterns1.1.4.3. Decorator Pattern1.1.4.4. Factory Method1.1.4.5. Observer and Publisher/Subscriber Patterns1.2. USING MYSQL JOINS1.2.1. INNER JOIN1.2.1.1. The Table Alias1.2.1.2. ON and USING1.2.1.3. An Alternative INNER JOIN Syntax1.2.2. OUTER JOIN1.2.2.1. RIGHT JOIN1.2.2.2. LEFT JOIN1.2.3. Other JOIN Syntax1.2.3.1. UPDATE and DELETE JOIN Syntax1.2.3.2. Case Sensitivity1.2.4. Complex Joins1.3. MYSQL UNIONS1.4. GROUP BY IN MYSQL QUERIES1.4.1. WITH ROLLUP1.5. HAVING1.6. LOGICAL OPERATIONS AND FLOW CONTROL IN MYSQL1.6.1. Logic Operators1.6.2. Flow Control1.7. MAINTAINING RELATIONAL INTEGRITY1.7.1. Constraints1.8. NOT NULL1.8.1. UNSIGNED1.8.2. ENUM and SET1.8.3. UNIQUE KEY1.8.4. FOREIGN KEY1.8.5. Using Server SQL Modes1.8.5.1. sql_mode=TRADITIONAL1.8.5.2. sql_mode=NO_ENGINE_SUBSTITUTION1.8.6. Storage Engine Integrity1.8.7. What MySQL Does Not Tell You1.8.8. What's Missing?1.9. SUBQUERIES IN MYSQL1.9.1. Subquery1.9.2. Correlated Subquery1.9.3. Derived Table1.9.4. Complex Sub Queries1.10. USING REGULAR EXPRESSIONS1.10.1. General Patterns1.10.1.1. Matching a Range of Characters1.10.2. Expert Regular Expressions1.10.2.1. Lookaheads and Lookbehinds1.10.2.2. Capturing Data1.10.2.3. Documenting Regular Expressions1.10.3. Putting It All Together in PHP1.10.3.1. Replacing Strings1.10.4. Regular Expressions in MySQL1.10.4.1. Using LIB_MYSQLUDF_PREG1.10.4.2. Capturing Data1.10.4.3. String Replacement1.10.4.4. Filtering a Query Based on a Regular Expression1.11. SUMMARY
2. Advanced PHP Concepts
2.1. A PROBLEM THAT NEEDS SOLVING2.2. ITERATORS AND THE SPL2.2.1. A Sample View for the Application2.2.2. The Iterator Interface2.2.2.1. Rewinding an Iterator2.2.2.2. Validating and Returning the Current Record2.2.3. The Countable Interface2.2.4. The SeekableIterator Interface and Pagination2.2.5. The ArrayAccess Interface2.3. LAMBDA FUNCTIONS AND CLOSURES2.3.1. The Old Way: Lambda-Style Functions2.3.2. Understanding Closures2.3.3. Using the Query Builder for Prototyping2.4. SUMMARY
3. MySQL Drivers and Storage Engines
3.1. MYSQL DRIVERS3.2. ABOUT MYSQL STORAGE ENGINES3.2.1. Obtaining Storage Engine Information3.2.1.1. Available Engines3.2.1.2. Defining the Storage Engine3.2.1.3. Confirming a Table Storage Engine3.3. DEFAULT STORAGE ENGINES3.3.1. MyISAM3.3.1.1. Key Features3.3.1.2. Limitations3.3.1.3. Important Parameters3.3.1.4. Examples3.3.1.5. When to Use MyISAM3.3.2. InnoDB3.3.2.1. Key Features3.3.2.2. Limitations3.3.2.3. Important Parameters3.3.2.4. Understanding InnoDB Table Usage3.3.2.5. Differences from MyISAM3.3.2.6. Optimizing SQL Using InnoDB3.3.3. Memory3.3.3.1. Key Features3.3.3.2. Limitations3.3.3.3. Important Parameters3.3.3.4. Example Table Usage3.3.4. Blackhole3.3.4.1. Key Features3.3.4.2. Limitations3.3.4.3. Important Parameters3.3.4.4. Uses for Blackhole3.3.5. Archive3.3.5.1. Key Features3.3.5.2. Limitations3.3.5.3. Important Parameters3.3.5.4. Understanding Archive Tables Usages3.3.6. Merge3.3.7. CSV3.3.7.1. Key Features3.3.7.2. Limitations3.3.7.3. Important Parameters3.3.7.4. Understanding CSV Table Usage3.3.8. Federated3.3.8.1. Characteristics of the Federated Storage Engine3.3.8.2. Creating a Federated Table3.3.8.3. Federated Servers3.3.8.4. Federated under the Hood3.4. OTHER MYSQL SUPPLIED ENGINES3.4.1. Falcon3.4.1.1. Key Features3.4.1.2. Limitations3.4.1.3. Important Parameters3.4.1.4. Falcon Table Usage3.4.2. Maria3.4.2.1. Key Features3.5. PLUGGABLE ENGINES3.5.1. InnoDB Plugin3.5.1.1. Key Features (in Addition to Those Listed in InnoDB) Include:3.5.1.2. Important Parameters3.5.2. PBXT3.5.2.1. Key features3.5.2.2. Limitations3.5.2.3. Important Parameters3.5.3. XtraDB3.6. ENGINES AS STANDALONE PRODUCTS3.6.1. InfiniDB3.6.2. TokuDB3.6.3. Infobright3.7. OTHER MYSQL OFFERINGS3.7.1. Storage Engine Patch Products3.7.1.1. Percona Performance Builds3.7.1.2. Our Delta3.7.2. MySQL-Related Products3.7.2.1. Drizzle3.7.2.2. MariaDB3.7.3. Other Engines3.7.3.1. NDB3.7.3.2. eBay Memory Engine3.7.3.3. NitroEDB3.7.3.4. Solid3.7.4. Integrated Hardware Engines3.7.4.1. Kickfire3.7.4.2. Virident3.7.5. Other Solutions3.7.6. Wafflegrid3.8. SUMMARY
4. Improving Performance through Caching
4.1. EACCELERATOR AND APC4.1.1. Installing and Configuring APC4.1.2. Installing and Configuring eAccelerator4.1.3. User Cache4.1.4. Checking the Cache Status4.1.5. When to Use APC and eAccelerator4.2. MEMCACHED4.2.1. What Is memcached?4.2.2. What memcached Does for You4.2.3. How Does memcached Work?4.2.4. How to Use memcached4.2.4.1. Types of Caching4.2.5. What Is Gearman?4.2.6. Caching Strategies4.3. INSTALLING MEMCACHED4.3.1. CentOS4.3.1.1. Ubuntu4.3.1.2. OpenSolaris4.3.1.3. Installing Memcached from Source4.4. STARTING MEMCACHED4.4.1. Startup Scripts4.4.1.1. Debian-Based Startup Scripts4.4.1.2. Redhat-Based Startup Scripts4.4.1.3. OpenSolaris4.4.2. Testing Your memcached Installation4.4.3. memcached Clients4.5. LIBMEMCACHED4.5.1. Libmemcached Features4.5.2. Libmemcache Utility Programs4.5.3. Installing libmemcached4.6. LIBMEMCACHED UTILITY PROGRAMS4.6.1. memcat4.6.2. memflush4.6.3. memcp4.6.4. memstat4.6.5. memrm4.6.6. memslap4.6.7. memerror4.7. PECL/MEMCACHED4.7.1. Connecting, Instantiation4.7.2. Setting Client Behavior4.7.3. Putting and Retrieving Data4.7.4. Append and Prepend4.7.5. Delete4.7.6. Increment and Decrement4.7.7. Multi-get4.7.8. Multi-set4.7.9. Cache Locality Using byKey Methods and Multi get/set4.7.10. getDelayed4.7.11. CAS4.7.12. Statistics4.7.13. Server List4.7.14. Error Handling4.8. PRACTICAL CACHING4.8.1. memcached Proxy: moxi4.8.2. Other "memcapable" Key-Value Stores4.8.3. Tokyo Tyrant4.9. SUMMARY

5. memcached and MySQL
5.1. THE MEMCACHED FUNCTIONS FOR MYSQL5.2. HOW THE MEMCACHED FUNCTIONS FOR MYSQL WORK5.3. INSTALLING THE MEMCACHED FUNCTIONS FOR MYSQL5.3.1. Prerequisites5.3.2. Configure the Source5.3.3. Build the Source5.3.4. Install the UDF5.3.4.1. Using SQL Script Install5.3.4.2. Using the Perl Install Utility5.3.5. Checking Installation5.4. USING THE MEMCACHED FUNCTIONS FOR MYSQL5.4.1. Establishing a Connection to the memcached Server5.4.1.1. memc_servers_set5.4.1.2. memc_server_count5.4.2. Data Setting Functions5.4.2.1. memc_set5.4.2.2. memc_set_by_key5.4.2.3. memc_add5.4.2.4. memc_replace5.4.2.5. memc_cas5.4.2.6. memc_prepend5.4.2.7. memc_append5.4.2.8. memc_delete5.4.3. Data Fetching Functions5.4.3.1. memc_get5.4.4. Increment and Decrement5.4.4.1. memc_increment5.4.4.2. memc_decrement5.4.5. Behavioral Functions5.4.5.1. memc_list_behaviors5.4.5.2. memc_behavior_get5.4.5.3. memc_behavior_set5.4.5.4. memc_list_hash_types5.4.5.5. memc_list_distribution_types5.4.6. Statistical Functions5.4.6.1. memc_stats5.4.6.2. memc_stat_get_value5.4.6.3. memc_stat_get_keys5.4.7. Version Functions5.4.7.1. memc_libmemcached_version5.4.7.2. memc_udf_version5.4.8. Fun with Triggers (and UDFs)5.4.8.1. The Source Distribution Directory5.4.8.2. Trigger Execution5.4.9. Read-Through Caching with Simple Select Statements5.4.10. Updates5.5. SUMMARY
6. Advanced MySQL
6.1. VIEWS6.1.1. Access Permissions6.1.2. Additional Information about Views6.2. STORED PROCEDURES AND FUNCTIONS6.2.1. General Attributes6.2.1.1. Parameters6.2.1.2. Characteristics6.2.2. Stored Routine Logic6.2.2.1. Variables6.2.2.2. Cursors6.2.2.3. Handlers and Conditions6.2.2.4. Flow Control6.2.3. Using Stored Routines Privileges and Meta Data6.2.4. Extending Stored Routines6.2.5. Stored Routine Disadvantages6.2.6. User Defined Functions6.3. TRIGGERS6.3.1. No Triggers6.3.2. Trigger Syntax6.3.3. Insert Triggers6.3.4. Update Triggers6.3.5. Delete Triggers6.3.6. Replace Triggers6.3.7. Trigger Permissions6.4. TRANSACTIONS6.4.1. Atomicity6.4.1.1. Non-transactional Tables6.4.1.2. Transactional Tables6.4.2. Consistency6.4.3. Isolation6.4.3.1. Repeatable Read6.4.3.2. Read Committed6.4.3.3. Read Uncommitted6.4.3.4. Serializable6.4.3.5. Isolation Levels and Replication6.4.4. Durability6.4.5. Implied Commit6.5. REPLICATION6.5.1. Replication Purposes6.5.2. Replication Setup6.5.2.1. Master Configuration6.5.2.2. Slave Configuration6.5.2.3. Slave Operation6.5.3. Testing MySQL Replication6.5.4. How Does MySQL Replication Work?6.5.4.1. Master Analysis6.5.4.2. Binary Log File Analysis6.5.4.3. Slave Analysis6.5.5. Testing MySQL Replication6.5.6. Important Configuration Options6.5.7. Important Replication Commands6.5.8. Breaking Replication6.5.9. Using Replication Selectively6.5.10. The Issues with MySQL Replication6.5.11. The Benefits of MySQL Replication6.6. EVENTS6.6.1. Creating Events6.6.2. Enabling the Events Scheduler6.6.3. Altering Events6.6.4. Event Privileges6.6.5. Event Meta Data6.7. SUMMARY
7. Extending MySQL with User-Defined Functions
7.1. INTRODUCTION TO UDFS7.2. DEVELOPING A UDF7.2.1. UDF Development Requirements7.2.2. UDF Required Functions7.3. A PRACTICAL UDF EXAMPLE7.3.1. UDF High-Level Design7.3.2. Designing an Algorithm to Use for Your UDF7.3.3. Implementing the Program7.3.3.1. Coding the UDF7.3.3.2. Project Directory Organization — Creating an Open Source Project7.3.3.3. Source Code Implementation7.3.3.4. Checking That the UDF Is Installed7.3.4. Building the UDF7.3.5. Installing the UDF7.3.6. Running Your New UDF7.4. USING A UDF WITH PHP7.4.1. Connecting and Disconnecting to MySQL7.4.2. Returning the Result Set Array7.5. OTHER UDF SQL STATEMENTS7.6. DEBUGGING A UDF7.6.1. Attaching gdb to an Already Running Process7.6.2. Setting a BreakPoint and Stepping through Code7.6.3. Dealing with Literal Values7.6.4. Debugging Summary7.7. SUMMARY
8. Writing PHP Extensions
8.1. SETTING UP THE BUILD ENVIRONMENT8.2. CREATING AN EXTENSION WITH EXT_SKEL8.2.1. Creating and Compiling Skeleton Code8.2.2. Using a Function Definitions File8.2.3. Generating Help Files8.3. CREATING AN EXTENSION WITH CODEGEN_PECL8.3.1. Creating the Basic XML File8.3.2. Defining Functions8.3.2.1. Defining a Public Function8.3.2.2. Defining Internal Functions8.3.3. Defining Constants, INI Directives, and Globals8.3.3.1. Defining Constants8.3.3.2. Defining INI Directives and Globals8.3.4. Defining Objects, Methods, and Properties8.3.4.1. Defining Methods8.3.4.2. Defining Properties8.3.4.3. An Example Class8.3.4.4. Interfaces8.4. VARIABLES IN PHP EXTENSIONS8.4.1. Setting and Testing zvals8.4.2. Reading and Comparing zvals8.4.3. Dealing with Strings as zvals8.4.3.1. Manipulating and Comparing Strings8.4.3.2. Converting to Strings8.4.4. Advanced Memory Management8.5. USING FUNCTIONS IN EXTENSIONS8.5.1. Basic Definitions8.5.2. Using Arguments8.5.3. Defining Argument Information8.5.4. Returning Values8.5.5. Built-In Functions8.5.6. Creating and Consuming PHP API Functions8.5.6.1. Consuming PHP APIs8.5.6.2. Providing an API8.5.7. More Notes and Creating Helper Functions8.6. INPUT/OUTPUT8.6.1. Standard Out8.6.2. Files and Streams8.6.2.1. Basic Streams8.6.2.2. Using Context8.6.3. Networking8.6.3.1. Building a Client8.6.3.2. Building a Server8.6.4. Errors and Warnings8.7. ARRAYS AND HASH TABLES8.7.1. Building and Accessing Arrays8.7.2. Accessing and Modifying Hash Tables8.8. OBJECTS AND INTERFACES8.8.1. Creating a Class8.8.1.1. Class Definition, Inheritance, and Namespaces8.8.1.2. Using Methods8.8.1.3. Using Properties8.8.1.4. Using Class Constants8.8.1.5. Creating an Interface8.8.2. Interacting with Objects8.8.2.1. Identifying Objects8.8.2.2. Calling Methods8.9. CONSTANTS, INI DIRECTIVES, AND GLOBALS8.9.1. Constants8.9.2. Globals and INI Directives8.9.2.1. Defining Globals and INI Directives8.9.2.2. Accessing Globals8.9.2.3. True Globals and Thread Safety8.10. DESCRIBING AN EXTENSION8.10.1. Printing a Description8.10.2. Building a Table8.10.3. Specifying a Logo8.11. SUMMARY
9. Full-Text Searching
9.1. MYSQL FULLTEXT INDEXES9.1.1. Using MySQL FULLTEXT Indexes9.1.1.1. Natural Language Mode9.1.1.2. Boolean Mode9.1.2. MySQL FULLTEXT Index Issues9.2. A BETTER SOLUTION: THE SPHINX FULL-TEXT SEARCH ENGINE9.2.1. Sphinx Configuration and Installation9.2.2. Sphinx.conf Settings9.2.2.1. Sphinx Data Sources9.2.2.2. Sphinx Indexes9.2.2.3. Indexer Options9.2.2.4. searchd Options9.2.2.5. Data Sources9.2.2.6. Defining the sakila_main Data Source9.2.2.7. Defining the Indexes9.2.2.8. Defining the film_main and Its Inherited Indexes9.2.2.9. Specifying the Indexer Options9.2.2.10. Specifying the searchd Options9.2.3. Starting Sphinx9.2.3.1. Running the Indexer9.2.3.2. Starting the Search Daemon9.2.4. Searching Sphinx9.2.4.1. Search Modes9.2.4.2. Sort Modes9.2.4.3. Boolean Query Syntax9.2.4.4. Extended Query Syntax9.2.4.5. The Search Utility9.2.4.6. SphinxQL9.2.4.7. One Last Step to Using Sphinx9.2.5. Delta Indexes9.2.6. Merging Indexes9.3. DEVELOPING APPLICATIONS THAT USE SPHINX9.3.1. Sphinx and PHP9.4. SUMMARY
10. Multi-tasking in PHP and MySQL
10.1. GEARMAN10.2. WHAT IS GEARMAN?10.2.1. Installing and Running Gearman10.2.1.1. gearmand Job Server Install10.2.1.2. PECL/Gearman10.2.1.3. Gearman MySQL UDF Install10.2.1.4. Running the Gearman Job Server10.2.2. Using the Gearman MySQL UDFs10.2.2.1. gman_severs_set()10.2.2.2. gman_do()10.2.2.3. gman_sum()10.2.2.4. Usage Examples10.2.3. PHP and Gearman10.3. NARADA: A SEARCH ENGINE APPLICATION10.3.1. Obtaining Narada10.3.2. Narada Components10.3.3. Database Tables for the Search Engine Application10.3.4. Sphinx Setup10.3.4.1. The Narada Configuration File10.3.4.2. The Narada Class10.3.5. Gearman Workers10.3.5.1. Insert Worker10.3.5.2. Fetch Worker10.3.5.3. Search Worker10.3.5.4. Index Worker10.3.6. Index Page10.3.7. One Other Tidbit of Code10.3.8. The Big Picture10.3.9. Running Narada10.3.10. To-Do List for Narada10.3.11. Other Job Server Systems10.4. SUMMARY
11. Rewrite Rules
11.1. USING REWRITE RULES11.1.1. Understanding the Purpose and Structure of Rewrite Rules11.1.2. Understanding and Controlling Rewrite Rule Flow11.1.3. Conditional Rules11.1.4. Logging and Optimization11.2. REWRITE MAPS11.2.1. Built-in Maps11.2.2. Random and Text Lookups11.2.3. Using PHP and MySQL11.3. SUMMARY
12. User Authentication
12.1. DESIGNING THE DATABASE12.2. HTTP-BASED AUTHENTICATION12.2.1. Basic Authentication12.2.2. Digest Authentication12.3. PURE PHP AUTHENTICATION12.3.1. Using PHP Sessions12.3.2. Building Secure Cookies12.4. ACCESS CONTROL LISTS12.5. SUMMARY
13. Understanding the INFORMATION_SCHEMA
13.1. USING THE INFORMATION_SCHEMA13.2. TABLE OBJECTS TABLES13.3. OTHER DATABASE OBJECTS TABLES13.4. MYSQL STATUS TABLES13.5. PROCESSLIST (5.1)13.6. SESSION_STATUS/GLOBAL_STATUS (5.1)13.7. SESSION_VARIABLES/GLOBAL_VARIABLES (5.1)13.8. MYSQL META DATA TABLES13.9. CHARACTER_SETS13.10. COLLATIONS13.11. COLLATION_CHARACTER_SET_APPLICABILITY13.12. MYSQL ACL PERMISSIONS TABLES13.13. USER_PRIVILEGES13.14. SCHEMA_PRIVILEGES13.15. TABLE_PRIVILEGES13.16. COLUMN_PRIVILEGES13.17. INFORMATION_SCHEMA EXTENSIONS13.18. SHOW CROSS REFERENCE13.19. SUMMARY
14. Security
14.1. HARDENING YOUR MYSQL SERVER14.1.1. Installation Defaults14.1.2. Operating System Security14.1.3. MySQL Security Permissions14.1.3.1. About the MySQL Security Model14.1.3.2. GRANT14.1.3.3. REVOKE14.1.3.4. DROP USER14.1.3.5. The Security Backdoor14.1.3.6. Change Default 'root' User14.1.3.7. Using Privileges Appropriately14.1.3.8. Defining User Host Permissions14.1.3.9. Ideal Application Security14.1.4. Additional Database Security14.1.5. Auditing14.2. ENCODING DATA14.2.1. Bi-directional Encoding14.2.1.1. The XOR Cipher14.2.1.2. Using the mcrypt Extension14.2.2. Single-Directional Encoding14.3. PHP SECURITY RECIPES14.3.1. Protecting against SQL Injection14.3.2. Protecting against Replay Attacks14.3.2.1. What Do Replay Attacks Look Like?14.3.2.2. The Code14.3.3. Protecting against XSS14.3.3.1. What Does XXS Look Like?14.3.3.2. The Code14.3.4. Protecting against CSRF14.3.4.1. What Does a CSRF Look Like?14.3.4.2. The Code14.3.5. Automation Attacks14.4. SUMMARY
15. Command-Line and Web Services
15.1. CREATING COMMAND-LINE SCRIPTS15.1.1. Reading Command-Line Input15.1.2. Prompting for Input15.1.3. Completed Classes15.1.4. Setting Up Cron Jobs15.1.5. Bonus: Output in Color15.2. CREATING WEB SERVICES15.2.1. RESTful Web Services15.2.1.1. Defining Resources15.2.1.2. Defining the Controller15.2.1.3. Testing Using cURL15.2.1.4. Making REST Requests from PHP15.2.2. SOAP Web Services15.2.3. Getting Started with the SOAP Library15.2.3.1. Defining the WSDL15.2.3.2. WSDL Types15.2.3.3. Defining the Message15.2.3.4. Specifying Ports15.2.3.5. Defining Bindings15.2.3.6. Defining the Endpoint15.2.3.7. Setting up a SOAP Server15.2.3.8. Setting Up a Soap Client15.3. SUMMARY
16. Optimization and Debugging
16.1. DEBUGGING PHP16.1.1. Creating Stack Traces16.2. OPTIMIZING QUERIES16.2.1. Reducing SQL16.2.2. Identifying SQL Statements16.2.3. Optimizing SELECT16.2.3.1. The EXPLAIN syntax16.2.3.2. Example Queries16.2.3.3. Identifying Indexes16.2.3.4. About Cardinality16.2.3.5. Better Index Types16.2.3.6. Optimizing Indexes16.2.3.7. When Indexes Are Not Used16.2.3.8. Common INDEX Mistakes16.2.3.9. Other Commands16.2.4. Optimizing UPDATE16.2.5. Optimizing DELETE16.2.6. Optimizing INSERT16.2.7. Optimizing REPLACE16.2.8. INSERT ... ON DUPLICATE KEY UPDATE16.3. DEBUGGING MYSQL16.3.1. Error Log16.3.2. Slow Query Log16.3.3. Processlist16.3.4. Other Commands16.3.5. Using a Debugger with MySQL16.4. SUMMARY

Content preview from Expert PHP and MySQL®

Chapter 13. Understanding the INFORMATION_SCHEMA

WHAT'S IN THIS CHAPTER?

What tables exist in the INFORMATION_SCHEMA
How to retrieve information on your schemas and tables
How to list core MySQL metadata used for database objects
How to retrieve instrumentation on a running MySQL instance
What extensions to INFORMATION_SCHEMA exist

The ANSI Standard SQL (e.g, SQL:2003 and SQL:2008) defines the support for a level of database metadata using the concept of INFORMATION_SCHEMA. MySQL starting with version 5.0 has implemented the INFORMATION_SCHEMA, often referred to as I_S, to provide a level of SQL access to information previously found in the popular but not SQL standard SHOW commands. The metadata is a form of data dictionary that provides the user access to various information including database objects such as TABLES, COLUMNS, and VIEWS; schema metadata such as CHARACTER_SETS; and internal MySQL operations including GLOBAL_STATUS, GLOBAL_VARIABLES, and PROCESSLIST.

This chapter steps through the INFORMATION_SCHEMA tables from versions 5.0, 5.1, and the 5.4 alpha release. It also gives an example of the INFORMATION_SCHEMA extensions possible using the InnoDB Plugin 1.0.4 and greater, which is available separately and now included in MySQL starting with version 5.1.38.

USING THE INFORMATION_SCHEMA

The SHOW command can be used to retrieve information from the INFORMATION_SCHEMA tables. As you can see, this 5.1 schema contains a number of tables, which are discussed in detail in this chapter: ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470563120Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Expert PHP and MySQL®

by Andrew Curioso, Ronald Bradford, Patrick Galbraith

Chapter 13. Understanding the INFORMATION_SCHEMA

USING THE INFORMATION_SCHEMA

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.