13 Advanced MySQL Programming
IN THIS CHAPTER, YOU LEARN ABOUT SOME more advanced MySQL topics, including table types, transactions, and stored procedures.
Key topics covered in this chapter include
The LOAD DATA INFILE statement
The LOAD DATA INFILE Statement
One useful feature of MySQL that we have not yet discussed is the LOAD DATA INFILE statement. You can use it to load table data in from a file. It executes very quickly.
This flexible command has many options, but typical usage is something like the following:
LOAD DATA INFILE "newbooks.txt" INTO TABLE books;
This line reads row data from the file newbooks.txt into the table books. By default, data fields in the file must be separated by tabs and enclosed in single quotation marks, and each row must be separated by a newline (
). Special characters must be escaped out with a slash (). All these characteristics are configurable with the various options of the LOAD statement; see the MySQL manual for more details.
To use the LOAD DATA INFILE statement, a user must have the FILE privilege discussed in Chapter 9, “Creating Your Web Database.”
Storage Engines
MySQL supports a number of different storage engines, sometimes also called table types. This means that you have a choice about the underlying implementation of the tables. Each table in your database can use a different storage engine, and you can easily convert between them.
You can choose a table type when you create a table by using
CREATE TABLE table TYPE=type ….
The commonly available table types are
MyISAM—This type is the default and what we have used so far in the book. It is based on the traditional ISAM type, which stands for Indexed Sequential Access Method, a standard method for storing records and files. MyISAM adds a number of advantages over the ISAM type. Compared to the other storage engines, MyISAM has the most tools for checking and repairing tables. MyISAM tables can be compressed, and they support full text searching. They are not transaction safe and do not support foreign keys.
MEMORY (previously known as HEAP)—Tables of this type are stored in memory, and their indexes are hashed. This makes MEMORY tables extremely fast, but, in the event of a crash, your data will be lost. These characteristics make MEMORY tables ideal for storing temporary or derived data. You should specify MAX_ROWS in the CREATE TABLE statement; otherwise, these tables can hog all your memory. Also, they cannot have BLOB, TEXT, or AUTO INCREMENT columns.
MERGE—These tables allow you to treat a collection of MyISAM tables as a single table for the purpose of querying. This way, you can work around maximum file size limitations on some operating systems.
ARCHIVE—These tables store large amounts of data but with a small footprint. Tables of this type support only INSERT and SELECT queries, not DELETE, UPDATE, or REPLACE. Additionally, indexes are not used.
CSV—These tables are stored on the server in a single file containing comma-separated values. The benefit of these types of tables only appears when you need to view or otherwise work with the data in an external spreadsheet application such as Microsoft Excel.
InnoDB—These tables are transaction safe; that is, they provide COMMIT and ROLLBACK capabilities. InnoDB tables also support foreign keys. While slower than MyISAM tables, the ability to use transactions in your applications is a worthy trade-off.
In most web applications, you will generally use either MyISAM or InnoDB tables or a mix of the two.
You should use MyISAM when you are using a large number of SELECTs or INSERTs on a table (not both mixed together) because it is the fastest at doing this. For many web applications such as catalogs, MyISAM is the best choice. You should also use MyISAM if you need full text-searching capabilities. You should use InnoDB when transactions are important, such as for tables storing financial data or for situations in which INSERTs and SELECTs are being interleaved, such as online message boards or forums.
You can use MEMORY tables for temporary tables or to implement views, and MERGE tables if you need to deal with very large MyISAM tables.
You can change the type of a table after creation with an ALTER TABLE statement, as follows:
alter table orders type=innodb;
alter table order_items type=innodb;
We used MyISAM tables through most of this part of the book. We will now spend some time focusing on the use of transactions and ways they are implemented in InnoDB tables.
Transactions
Transactions are mechanisms for ensuring database consistency, especially in the event of error or server crash. In the following sections, you learn what transactions are and how to implement them with InnoDB.
Understanding Transaction Definitions
First, let’s define the term transaction. A transaction is a query or set of queries guaranteed either to be completely executed on the database or not executed at all. The database is therefore left in a consistent state whether or not the transaction completed.
To see why this capability might be important, consider a banking database. Imagine the situation in which you want to transfer money from one account to another. This action involves removing the money from one account and placing it in another, which would involve at least two queries. It is vitally important that either these two queries are both executed or neither is executed. If you take the money out of one account and the power goes out before you put it into another account, what happens? Does the money just disappear?
You may have heard the expression ACID compliance. ACID is a way of describing four requirements that transactions should satisfy:
Atomicity—A transaction should be atomic; that is, it should either be completely executed or not executed.
Consistency—A transaction should leave the database in a consistent state.
Isolation—Uncompleted transactions should not be visible to other users of the database; that is, until transactions are complete, they should remain isolated.
Durability—Once written to the database, a transaction should be permanent or durable.
A transaction that has been permanently written to the database is said to be committed. A transaction that is not written to the database—so that the database is reset to the state it was in before the transaction began—is said to be rolled back.
Using Transactions with InnoDB
By default, MySQL runs in autocommit mode. This means that each statement you execute is immediately written to the database (committed). If you are using a transaction-safe table type, more than likely you don’t want this behavior.
To turn autocommit off in the current session, type
set autocommit=0;
If autocommit is on, you need to begin a transaction with the statement
start transaction;
If it is off, you do not need this command because a transaction will be started automatically for you when you enter an SQL statement.
After you have finished entering the statements that make up a transaction, you can commit it to the database by simply typing
commit;
If you have changed your mind, you can revert to the previous state of the database by typing
rollback;
Until you have committed a transaction, it will not be visible to other users or in other sessions.
Let’s look at an example. Execute the ALTER TABLE statements in the previous section of the chapter on your books database, as follows, if you have not already done so:
alter table orders type=innodb;
alter table order_items type=innodb;
These statements convert two of the tables to InnoDB tables. (You can convert them back later if you want by running the same statement but with type=MyISAM.)
Now open two connections to the books database. In one connection, add a new order record to the database:
insert into orders values (5, 2, 69.98, '2008-06-18'),
insert into order_items values (5, '0-672-31697-8', 1);
Now check that you can see the new order:
select * from orders where orderid=5;
You should see the order displayed:
+---------+------------+--------+------------+
| orderid | customerid | amount | date |
+---------+------------+--------+------------+
| 5 | 2 | 69.98 | 2008-06-18 |
+---------+------------+--------+------------+
Leaving this connection open, go to your other connection and run the same select query. You should not be able to see the order:
Empty set (0.00 sec)
(If you can see it, most likely you forgot to turn off autocommitting. Check this and that you converted the table in question to the InnoDB format.) The reason is that the transaction has not yet been committed. (This is a good illustration of transaction isolation in action.)
Now go back to the first connection and commit the transaction:
commit;
You should now be able to retrieve the row in your other connection.
Foreign Keys
InnoDB also supports foreign keys. You may recall that we discussed the concept of foreign keys in Chapter 8, “Designing Your Web Database.” When you use MyISAM tables, you have no way to enforce foreign keys.
Consider, for example, inserting a row into the order_items table. You need to include a valid orderid. Using MyISAM, you need to ensure the validity of the orderid you insert somewhere in your application logic. Using foreign keys in InnoDB, you can let the database do the checking for you.
How do you set this up? To create the table initially using a foreign key, you could change the table DDL statement as follows:
create table order_items (
orderid int unsigned not null references orders(orderid),
isbn char(13) not null,
quantity tinyint unsigned,
primary key (orderid, isbn)
) type=InnoDB;
We added the words references orders(orderid) after orderid. This means this column is a foreign key that must contain a value from the orderid column in the orders table.
Finally, we added the table type type=InnoDB at the end of the declaration. This is required for the foreign keys to work.
You can also make these changes to the existing table using ALTER TABLE statements, as follows:
alter table order_items type=InnoDB;
alter table order_items
add foreign key (orderid) references orders(orderid);
To see that this change has worked, you can try to insert a row with an orderid for which there is no matching row in the orders table:
insert into order_items values (77, '0-672-31697-8', 7);
You should receive an error similar to
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
Stored Procedures
A stored procedure is a programmatic function that is created and stored within MySQL. It can consist of SQL statements and a number of special control structures. It can be useful when you want to perform the same function from different applications or platforms, or as a way of encapsulating functionality. Stored procedures in a database can be seen as analogous to an object-oriented approach in programming. They allow you to control the way data is accessed.
Let’s begin by looking at a simple example.
Basic Example
Listing 13.1 shows the declaration of a stored procedure.
Listing 13.1 basic_stored_procedure.sql—Declaring a Stored Procedure
# Basic stored procedure example
delimiter //
create procedure total_orders (out total float)
BEGIN
select sum(amount) into total from orders;
END
//
delimiter ;
Let’s go through this code line by line.
The first statement
delimiter //
changes the end-of-statement delimiter from the current value—typically a semicolon unless you have changed it previously—to a double forward slash. You do this so that you can use the semicolon delimiter within the stored procedure as you are entering the code for it without MySQL trying to execute the code as you go.
The next line
create procedure total_orders (out total float)
creates the actual procedure. The name of this procedure is total_orders. It has a single parameter called total, which is the value you are going to calculate. The word OUT indicates that this parameter is being passed out or returned.
Parameters can also be declared IN, meaning that a value is being passed into the procedure, or INOUT, meaning that a value is being passed in but can be changed by the procedure.
The word float indicates the type of the parameter. In this case, you return a total of all the orders in the orders table. The type of the orders column is float, so the type returned is also float. The acceptable data types map to the available column types.
If you want more than one parameter, you can provide a comma-separated list of parameters as you would in PHP.
The body of the procedure is enclosed within the BEGIN and END statements. They are analogous to the curly braces within PHP ({}) because they delimit a statement block.
In the body, you simply run a SELECT statement. The only difference from normal is that you include the clause into total to load the result of the query into the total parameter.
After you have declared the procedure, you return the delimiter back to being a semicolon with the line
delimiter ;
After the procedure has been declared, you can call it using the call keyword, as follows:
call total_orders(@t);
This statement calls the total orders and passes in a variable to store the result. To see the result, you need to then look at the variable:
select @t;
The result should be similar to
+-----------------+
| @t |
+-----------------+
| 289.92001152039 |
+-----------------+
In a way similar to creating a procedure, you can create a function. A function accepts input parameters (only) and returns a single value.
The basic syntax for this task is almost the same. A sample function is shown in Listing 13.2.
Listing 13.2 basic_function.sql—Declaring a Stored Function
# Basic syntax to create a function
delimiter //
create function add_tax (price float) returns float
return price*1.1;
//
delimiter ;
As you can see, this example uses the keyword function instead of procedure. There are a couple of other differences.
Parameters do not need to be specified as IN or OUT because they are all IN, or input parameters. After the parameter list, you can see the clause returns float. It specifies the type of the return value. Again, this value can be any of the valid MySQL types.
You return a value using the return statement, much as you would in PHP.
Notice that this example does not use the BEGIN and END statements. You could use them, but they are not required. Just as in PHP, if a statement block contains only one statement, you do not need to mark the beginning and end of it.
Calling a function is somewhat different from calling a procedure. You can call a stored function in the same way you would call a built-in function. For example,
select add_tax(100);
This statement should return the following output:
+-------------+
| add_tax(100) |
+-------------+
| 110 |
+-------------+
After you have defined procedures and functions, you can view the code used to define them by using, for example,
show create procedure total_orders;
or
show create function addtax;
You can delete them with
drop procedure total_orders;
drop function add_tax;
Stored procedures come with the ability to use control structures, variables, DECLARE handlers (like exceptions), and an important concept called cursors. We briefly look at each of these in the following sections.
Local Variables
You can declare local variables within a begin…end block by using a declare statement. For example, you could alter the add_tax function to use a local variable to store the tax rate, as shown in Listing 13.3.
Listing 13.3 basic_function_with_variables.sql—Declaring a Stored Function with Variables
# Basic syntax to create a function
delimiter //
create function add_tax (price float) returns float
begin
declare tax float default 0.10;
return price*(1+tax);
end
//delimiter ;
As you can see, you declare the variable using declare, followed by the name of the variable, followed by the type. The default clause is optional and specifies an initial value for the variable. You then use the variable as you would expect.
Cursors and Control Structures
Let’s consider a more complex example. For this example, you’ll write a stored procedure that works out which order was for the largest amount and returns the orderid. (Obviously, you could calculate this amount easily enough with a single query, but this simple example illustrates how to use cursors and control structures.) The code for this stored procedure is shown in Listing 13.4.
Listing 13.4 control_structures_cursors.sql—Using Cursors and Loops to Process a Resultset
# Procedure to find the orderid with the largest amount
# could be done with max, but just to illustrate stored procedure principles
delimiter //
create procedure largest_order(out largest_id int)
begin
declare this_id int;
declare this_amount float;
declare l_amount float default 0.0;
declare l_id int;
declare done int default 0;
declare continue handler for sqlstate '02000' set done = 1;
declare c1 cursor for select orderid, amount from orders;
open c1;
repeat
fetch c1 into this_id, this_amount;
if not done then
if this_amount > l_amount then
set l_amount=this_amount;
set l_id=this_id;
end if;
end if;
until done end repeat;
close c1;
set largest_id=l_id;
end
//
delimiter ;
This code uses control structures (both conditional and looping), cursors, and declare handlers. Let’s consider it line by line.
At the start of the procedure, you declare a number of local variables for use within the procedure. The variables this_id and this_amount store the values of orderid and amount in the current row. The variables l_amount and l_id are for storing the largest order amount and the corresponding ID. Because you will work out the largest amount by comparing each value to the current largest value, you initialize this variable to zero.
The next variable declared is done, initialized to zero (false). This variable is your loop flag. When you run out of rows to look at, you set this variable to 1 (true):
The line
declare continue handler for sqlstate '02000' set done = 1;
is called a declare handler. It is similar to an exception in stored procedures. Also available are continue handlers and exit handlers. Continue handlers, like the one shown, take the action specified and then continue execution of the procedure. Exit handlers exit from the nearest begin…end block.
The next part of the declare handler specifies when the handler will be called. In this case, it will be called when sqlstate '02000' is reached. You may wonder what that means because it seems very cryptic! This means it will be called when no rows are found. You process a resultset row by row, and when you run out of rows to process, this handler will be called. You could also specify FOR NOT FOUND equivalently. Other options are SQLWARNING and SQLEXCEPTION.
The next thing is a cursor. A cursor is not dissimilar to an array; it retrieves a resultset for a query (such as returned by mysqli_query()) and allows you to process it a single line at a time (as you would with, for example, mysqli_fetch_row()). Consider this cursor:
declare c1 cursor for select orderid, amount from orders;
This cursor is called c1. This is just a definition of what it will hold. The query will not be executed yet.
The next line
open c1;
actually runs the query. To obtain each row of data, you must run a fetch statement. You do this in a repeat loop. In this case, the loop looks like this:
repeat
…
until done end repeat;
Note that the condition (until done) is not checked until the end. Stored procedures also support while loops, of the form
while condition do
…
end while;
There are also loop loops, of the form
loop
…
end loop
These loops have no built-in conditions but can be exited by means of a leave; statement.
Note that there are no for loops.
Continuing with the example, the next line of code fetches a row of data:
fetch c1 into this_id, this_amount;
This line retrieves a row from the cursor query. The two attributes retrieved by the query are stored in the two specified local variables.
You check whether a row was retrieved and then compare the current loop amount with the largest stored amount, by means of two IF statements:
if not done then
if this_amount > l_amount then
set l_amount=this_amount;
set l_id=this_id;
end if;
end if;
Note that variable values are set by means of the set statement.
In addition to if…then, stored procedures also support an if…then…else construct with the following form:
if condition then
…
[elseif condition then]
…
[else]
…
end if
There is also a case statement, which has the following form:
case value
when value then statement
[when value then statement …]
[else statement]
end case
Back to the example, after the loop has terminated, you have a little cleaning up to do:
close c1;
set largest_id=l_id;
The close statement closes the cursor.
Finally, you set the OUT parameter to the value you have calculated. You cannot use the parameter as a temporary variable, only to store the final value. (This usage is similar to some other programming languages, such as Ada.)
If you create this procedure as described here, you can call it as you did the other procedure:
call largest_order(@l);
select @l;
You should get output similar to the following:
+------+
| @l |
+------+
| 3 |
+------+
You can check for yourself that the calculation is correct.
Further Reading
In this chapter, we took a cook’s tour of the stored procedure functionality. You can find out more about stored procedures from the MySQL manual.
For more information on LOAD DATA INFILE, the different storage engines, and stored procedures, also consult the MySQL manual.
If you want to find out more about transactions and database consistency, we recommend a good basic relational database text such as An Introduction to Database Systems by C. J. Date.
Next
We have now covered the fundamentals of PHP and MySQL. In Chapter 14, “Running an E-commerce Site,” we look at the e-commerce and security aspects of setting up database-backed websites.