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;
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 ...