Features that MySQL Does Not Support
This section describes features that are found in some other databases that MySQL does not support. It covers what's missing, and it shows how to work around these omissions where possible. In general, features are missing from MySQL because they have negative performance implications. Several of the items in this list are on the developers' To Do list, for implementation as time permits, and assuming a way can be found to do so without sacrificing their primary goal of good performance.
Subselects. A subselect is a SELECT nested inside another SELECT, such as in the following query:
SELECT * FROM score WHERE event_id IN (SELECT event_id FROM event WHERE type = "T")
Subselects are scheduled to appear in MySQL 3.24, at which point they will become a non-omission. Until then, many queries that are written using subselects can be written as joins instead. See the section "Rewriting Subselects as Joins."
Transactions and commit/rollback. A transaction is a set of SQL statements that are executed as a unit without interruption by other clients. Commit/rollback capability allows you to state that the statements must be executed as a unit or not at all. That is, if any statement in the transaction fails, any statements executed up to that point are undone.
MySQL automatically performs locking for single SQL statements to keep clients from interfering with each other. (For example, two clients cannot write to the same table simultaneously.) In addition, you can use LOCK TABLES and UNLOCK TABLES to group statements as a unit, which allows you to perform operations for which single-statement concurrency control isn't sufficient. The transaction-related issues for MySQL are that it will not group the statements automatically for you, and you cannot perform rollback on the statements if one of them fails.
To see how transactions are useful, suppose you're in the garment sales business and you update inventory levels whenever one of your salesmen makes a sale. The following example demonstrates the kind of problem that can occur when multiple salesmen are updating the database simultaneously (assuming the initial shirt inventory level is 47):
At the end of this sequence of events, you've sold five shirts (that's good), but the inventory level says 45 rather than 42 (that's bad). The problem is that if you look up the inventory level in one statement and update the value in another statement, you have a multiple-statement transaction. The action taken in the second statement is dependent on the value retrieved in the first. But if separate transactions occur during overlapping time frames, the statements from each transaction intertwine and interfere with each other. In a transactional database, each salesman's statements can be executed as a transaction and Salesman 2's statements wouldn't execute until those for Salesmen 1 had completed. In MySQL, you can achieve the same effect in two ways:
Workaround 1: Execute a group of statements as a unit. You can group together statements and execute them as an atomic unit by surrounding them with LOCK TABLES and UNLOCK TABLES: Lock all the tables that you need to use, issue your queries, and release the locks. This prevents anyone else from using the tables while you have them locked. Using table locking, the inventory situation looks like this:
Now the statements from the two transactions don't get mixed up and the inventory level is set properly. We use a WRITE lock here because we need to modify the inventory table. If you are only reading tables, you can use a READ lock instead. This lets other clients read the tables while you're using them, but prevents clients from writing to them.
In the example just shown, Salesman 2 probably wouldn't notice any difference in speed because the transactions are short and would execute quickly. Howsever, as a general rule, you want to avoid locking tables for a long time.
If you're using multiple tables, you must lock all of them before you execute the grouped queries. If you only read from a particular table, however, you need only a read lock on it, not a write lock. Suppose you have a set of queries in which you want to make some changes to an inventory table, and you also need to read some data from a customer table. In this case, you need a write lock on the inventory table and a read lock on the customer table:
LOCK TABLES inventory WRITE, customer READ … UNLOCK TABLES
This requires you to lock and unlock your tables yourself. A database system with transaction support would do so automatically. However, the aspect of grouping statements for execution as a unit is the same as in transactional databases.
Workaround 2: Use relative updates, not absolute updates. The second way around the problem of statements from multiple transactions getting mixed up is to eliminate the dependency between statements. Though that isn't always possible, it is for our inventory example. For the inventory updating method used with Workaround 1, the transaction involves looking up the current inventory level, computing the new value based on the number of shirts sold, and then updating the level to the new value. It's possible to do this in one step simply by updating the shirt count relative to its current value:
As you can see, this involves no need for multiple-statement transactions at all and thus no need to lock tables to simulate transactional capability. If the types of transactions you've been using are similar to this, you may be able to get by without transactions at all.
The preceding example shows how to avoid the "need" for transactions for a specific situation. This is not to say that there aren't other situations where you really do need transactions. The typical example of this involves a financial transfer where money from one account is placed into another account. Suppose Bill writes a check to Bob for $100.00 and Bob cashes the check. Bill's account should be decremented by $100.00 and Bob's account incremented by the same amount:
UPDATE account SET balance = balance - 100 WHERE name = "Bill" UPDATE account SET balance = balance + 100 WHERE name = "Bob"
If a crash occurs between the two statements, the transaction is incomplete. A database system with true transactions and commit/rollback capability would be able to handle this situation. (At least that's the theory. You may still have to figure out which transactions weren't entered and re-issue them, but at least you don't have half-transactions to worry about.) In MySQL, you can figure out the state of transactions at crash time by examining the update log, although this may require some manual examination of the log.
Foreign keys and referential integrity. A foreign key allows you to declare that a key in one table is related to a key in another, and referential integrity allows you to place constraints on what may be done to the table containing the foreign key. For example, the score table in our samp_db sample database contains a student_id column, which we use to relate score records to students in the student table. score.student_id would be declared a foreign key in databases supporting that concept, and we would impose a constraint on it such that no score record could be entered for a student that does not exist in the student table. In addition, we could allow cascading deletion such that if a student were deleted from the student table, any score records for the student would automatically be deleted from the score table.
Foreign keys help maintain the consistency of your data, and they provide a certain measure of convenience. The reasons MySQL doesn't support them are due primarily to certain negative effects of foreign keys on database performance and maintenance. (The MySQL Reference Manual details a whole list of such reasons.) Note that this view of foreign keys is a bit different than you will find in some other database literature, where you often find them described in terms such as "essential." The MySQL developers don't subscribe to that view. If you do, it might be best to consider other databases to provide foreign key support. For example, if your data have particularly complex relationships, you may not want to be responsible for implementing these dependencies in your applications (even if, as is often the case, this amounts to little more than adding a few extra DELETE statements).
MySQL doesn't support foreign keys, except to the extent that it parses FOREIGN KEY clauses in CREATE TABLE statements. (This helps make it easier to port SQL code from other databases to MySQL.) MySQL doesn't enforce foreign keys as a constraint, nor does it provide cascading delete capability.
The constraints that foreign keys are used to enforce are often not difficult to implement through application logic. Sometimes, it's simply a matter of how you approach the data entry process. For example, for inserting new records into our score table, it's unlikely that you'd insert scores for non-existent students. Clearly, the way you'd enter a set of scores would be to start with a list of students from the student table, and then for each one, take the score and use the student's ID number to generate a new score table record. With this procedure, there isn't any possibility of entering a record for a student that doesn't exist. You wouldn't just invent a score record to put in the score table.
To achieve the effect of cascading on DELETE, you must implement this with your own application logic, too. Suppose you want to delete student number 13. This also implies you want to delete any score records for that student. In a database that supports cascading deletes, you would delete the student table record and any scores table records with this statement:
DELETE FROM student WHERE student_id = 13
The records in the score for student 13 are automatically deleted. In MySQL, you perform the secondary deletion yourself with an explicit DELETE statement:
Stored procedures and triggers. A stored procedure is SQL code that is compiled and stored in the server. It can be referred to later without having to be sent from the client and parsed again. You can also make changes to a procedure to affect any client applications that use it. Trigger capability allows a stored procedure to be activated when some event occurs, such as a record being deleted from a table. For example, you might do this if you wanted to regenerate some complex summary of which the record was a part to keep the summary up to date. A stored procedure language is on the MySQL to-do list.
Views. A view is a logical entity that acts like a table but is not one. It provides a way to look at columns from different tables as though they're all part of the same table. Views are sometimes called virtual tables. Views are on the MySQL to-do list.
Record-level privileges and locking. MySQL supports various levels of privileges, from global privileges down to database, table, and column privileges. It does not support record-level privileges. However, you can use the GET_LOCK() and RELEASE_LOCK() functions in your applications to implement cooperative record locks. The procedure for this is described in the entry for GET_LOCK() in Appendix C, "Operator and Function Reference."
'--'as a comment. This comment style is not supported because it's an ambiguous construct, although as of MySQL 3.23.3, a comment beginning with two dashes and a space is accepted. See the section "Writing Comments" for more information.