Critical Skill 3.4 Remove a Table or Records

Similar to changing data, removing data can be serious business. For those of you using Linux, you are already familiar with the concept that deleted information is unrecoverable, but for Windows users, it must be emphasized that there is no equivalent to your OS s Recycle Bin. Unless you have a backup or some other form of a copy of your data, once that data is deleted, it is gone.

To remove data, you can use the DROP TABLE , DELETE , and TRUNCATE commands. The DROP TABLE command deletes entire tables, and we will look at that command first. The DELETE and TRUNCATE commands are somewhat similar, in that they both delete entire rows (or records) from a specified table; however, DELETE offers conditional syntax that allows you to specify which rows will be removed, and TRUNCATE always deletes all the rows in the table. Since DELETE is the more versatile command, we will cover it before TRUNCATE , and then contrast the simpler TRUNCATE command against it.

Dropping a Table

The DROP TABLE command allows you to completely remove one or more tables, including the data contained in them. The syntax is scarily simple, so be careful when using this command. It can be used to remove regular or temporary tables. In fact, there is a TEMPORARY option, beginning in MySQL version 4.1, which can safeguard against accidentally removing a permanent table because of a typo or momentary confusion over a correct name . The syntax is as follows :

 DROP [TEMPORARY] TABLE [IF EXISTS] <table_name>[, <table_name2>,  . . ]; 

Adding the IF EXISTS option allows MySQL to return a warning instead of an error when the table specified in the command does not exist.

Deleting Table Contents

The DELETE command leaves the table definition intact, so you can work with that table after you have removed data from it.

The DELETE command has three optional clauses: WHERE , ORDER BY , and LIMIT clauses. It also has three optional keywords that do very specific things: LOW_PRIORITY , QUICK , and IGNORE . The basic syntax is as follows:

 DELETE [LOW_PRIORITY] [QUICK] [IGNORE]  FROM <  table_name  > [WHERE <  where_definition  > [ORDER BY . . .] [LIMIT <  row_count  >; 

As usual, all of the syntax within square brackets is optional, so at its most simple, the command could read:

 DELETE FROM <  table_name  >; 

However, if there is no WHERE clause, you will delete all of the records in the specified table.

The LOW_PRIORITY keyword is used when the speed of your database is more important than how long it takes the DELETE command to finish. It causes MySQL to delay the execution of the DELETE command until no other clients are reading from that table.

On MyISAM tables, the QUICK keyword may result in a faster DELETE command execution. It instructs the storage engine to delete all the effected records first and then complete the accompanying changes to the index later, instead of changing each index entry immediately after its correlating record deletion occurs. Depending on the tables involved, and the indexes defined on them, this may or may not result in an increase in speed.

Appearing first in MySQL version 4.1.1, the IGNORE keyword allows MySQL to ignore any errors that occur in the process of deleting data and cause them to be returned as warnings. The difference between the two is that errors will stop the command from executing, while warnings allow the command to continue. Therefore, IGNORE allows you to delete data, even when MySQL would otherwise not allow the DELETE command to execute.

If you know that errors will occur in the process of deleting data, but you are absolutely positive that you want to delete it regardless, then you can use the IGNORE keyword to override the automatic protection that MySQL provides and enforce your commands. The error messages will still appear couched as warnings, but the data will be deleted.

The WHERE , ORDER BY , and LIMIT clauses work the same way they do in the UPDATE command. In MySQL version 4.0, you can delete rows from multiple tables with a particular condition, but when deleting from multiple tables, the ORDER BY and LIMIT options do not apply.

Note  

Before MySQL version 4.0, the DELETE command does not normally return the number of rows affected to the command line. If this information seems necessary or useful, and you are running a pre-4.0 version, adding the clause WHERE 1=1 to the syntax will cause MySQL to return the number of rows deleted.

Truncating Tables

Like the DELETE command, the TRUNCATE command removes data while leaving the table definition intact. The TRUNCATE command has a simple syntax with no options:

 TRUNCATE TABLE <  table_name  >; 

TRUNCATE functions by dropping the entire table and then re-creating it, which makes it much faster than the DELETE command, which works row by row. MySQL stores its data in a three-tiered system of files: .frm , .MYI , and .MYD . The .frm file holds the table definition; the .MYI holds the index information; and the .MYD holds the actual data. So, table duck_cust , which is found in the duckwear directory of the data directory in the mysql server, is contained in the duck_cust.frm , duck_cust.MYI , and duck_cust.MYD files. As long as the table definition file (< table_name > .frm ) is valid, the table can be re-created, even if its data or index files have become corrupted.

Caution  

If you have an active transaction or active table lock at the time the TRUNCATE command attempts to execute, you will get an error.

There are two behaviors of TRUNCATE that may or may not be negative:

• TRUNCATE does not return the number of rows affected. If you decide you need this information, then use the most basic form of the DELETE command, which gives the same overall result but does it row by row, allowing for a total count to be displayed in the RETURN statement once the command is finished. On the other hand, if you are absolutely sure that all of the data in a table needs to be deleted, it does not matter that the rows in the table are not counted and the count is not returned to the command line.

• If a column in a MyISAM or BDB table is set to AUTO_INCREMENT , once TRUNCATE re-creates the table, that table s handler may or may not resume the incrementing count where it left off. When you insert data into the table again, you will be able to tell which way the table responded to its re-creation. If it does not resume where it left off, it will restart the count from the beginning. If it does not reset, you can update the field using an ALTER TABLE command ( ALTER TABLE < table_name > AUTO_INCREMENT=0 ) or drop and re-create the table.

If you just want to empty a table as quickly as possible, TRUNCATE is the command to use. If you want more control, use the DELETE command.