Expert PHP and MySQL®

Chapter 6. Advanced MySQL

WHAT'S IN THIS CHAPTER?

• Understanding the usage and flexibility of views

• Encapsulating business logic within stored procedures and functions

• Extending statement DDL operations with triggers

• Enhancing data consistency with transactions

• Understanding and using MySQL Replication to extend your database usage

• Scheduling one-time or recurring database events

After mastering MySQL tables and columns and the various SQL commands for data management in your application, this chapter looks at key advanced features of MySQL that can enhance your application programming features and functionality.

Throughout this chapter, you create a number of different objects; some examples may specifically reference the database schema. You can ensure all examples work with the following new schema:

DROP SCHEMA IF EXISTS chapter6;
CREATE SCHEMA chapter6;
USE chapter6;

VIEWS

A view in MySQL is a server definition that enables a simpler representation of underlying tables and columns. This can lead to an easier understanding of a complex data model, and ultimately easier coding practices. Views were first available in MySQL 5.0. A view may be of benefit when you integrate data from an existing legacy system. For example, perhaps you extract data from a mainframe and use the same table structure in your MySQL database, but its structure is cryptic at best:

CREATE TABLE tbl1453 ( i_id INT UNSIGNED NOT NULL, s_nm VARCHAR(100) NOT NULL, s_txt1 VARCHAR(50) NULL, s_txt2 VARCHAR(50) NULL, d_c DATE ...