COM372: Notes, Chapter 13

Advanced MySQL Programming
pages 307-319

LOAD DATA INFILE: This statement lets you load table data fril a file, as in LOAD DATA INFILE "booklist.txt" INTO TABLE books_table;. Data fields in the text file must be tab delimited and enclosed in single quotation marks, with rows separated by a newline (\n) and special characters need to be escaped (e.g., \'). (Requires FILE privelege).

Storage Engines: MySQL uses six means to store data, identified when a table is created, as CREATE TABLE table_name TYPE=type;

• MyISAM (default)—Indexed Sequential Access Method, includes more tools for checking and repairing tables. These tables can be compressed and they support full text searching; they are not transaction safe and do not support foreign keys.
• ISAM—deprecated, replaced with MyISAM
• MEMORY—(previously HEAP). Stored in memory and indexes are hashed; fast but susceptible to loss in crashes. Good for temporary or derived data. Limit these with MAX_ROWS specification in CREATE TABLE to avoid memory hogging. Don't allow BLOB, TEXT, or AUTO INCREMENT columns.
• MERGE—Permiet use of a collection of MyISAM tables as a single queriable table (work-around for maximum file size limitations on some operating systems.
• BDB—(Berkeley DataBase) Transaction safe, permitting COMMIT and ROLLBACK; slower than MyISAM.
• InnoDB—Transaction safe and support foreign keys. Recommended over BDB.

Use MyISAM when you are using many SELECTs or INSERTs because it is fastest (generally the best overall choice). Use InnoDB for transactions (e.g., to store financial data or when writes and reads are being interleaved (bulletin boards or online forums). You can alter the table type using the ALTER TABLE statement (p. 309).

Transactions: A transaction is a set of changes to the database, executed as a query, that is either completely executed, or not at all. If you are moving, for example, money from one back account to another, you want to both deduct from the first account and add to the second; you don't want the possibility that the deduction will be made without the addition because funds would then be lost and the books wouldn't balance. The solution is to use a transaction.

Transactions should follow ACID compliance:

• Atomicity—the transaction should either be completely executed or not at all.
• Consistency—a completed transaction should leave the database in a consistent state.
• Isolation—tranactions should not be visible until they are complete (you shouldn't be able to see part of a transfer before the other part is also done, for example).
• Durability—once complete, the transaction should be permanent.

Completed transactions are committed; to roll back means to reset the database to the state it had before the transaction was attempted.

The default setting in MySQL is to use autocommit; all statements executed against the database are committed immediately (written to the database). To turn this off (for use in a transaction-safe operation), use set autocommit=0; To start a tranaction, use start transaction; To complete a transaction, use commit; To revert to a previous state, use rollback; All of this is briefly illustrated on p. 310-11. Remember that you should be using the innoDB table type for transactions.

Foreign Keys: InnoDB also supports foreign keys. A foreign key is a reference to a record in a different table. To maintain referential integrity, that record must exist. With innoDB tables, the reference can be checked and you will get an error message if the entry isn't in the foreign table.

Stored Procedures: Stored procedures include SQL strings and database controls; they are equivalent to encapsulated functions for programming, the OOP approach to database programming. Creation and use is illustrated on pages 312-318. Listing 13.4 (p. 315-316) illustrates use of conditional and looping structures, and the use of declare, exit, and continue handlers. The use of cursors (p. 316-17) was still new to MySQL 5 and not well documented in this text. (more)