Not all applications are able to use the output from a SQL query, but they are able to handle input in the form of a text file. The SELECT command in MySQL includes a clause called INTO [OUTFILE | DUMPFILE] that allows the result of a query to be placed in a file. As listed, there are two options for the INTO clause: OUTFILE and DUMPFILE. The OUTFILE option is used to dump all rows returned in a query. For example:
mysql> SELECT * FROM acc INTO OUTFILE 'test.outfile'; Query OK, 8 rows affected (0.00 sec)
This query results in a text file with all of the rows, as shown here: 1034033 jsmith smithy 20021014165845 1034034 jime \N 20021014165845 1034067 jthompson james2 20021014165845 1034089 sstanford stanford 20021014165845 1034123 blewis lewis 20021014165845 1034154 ysheets sheets 20021014165845 1034546 jjmyers \N 20021014165845 1034055 jdoe doey 20021014165908
Notice that the NULL values are converted to \N and line terminations are pro- vided. If you need the data sorted, you can add the appropriate clauses to the query. MySQL also includes the clause INTO DUMPFILE, which basically dumps a single row into a file without any sort of special processing. The DUMPFILE is typically used to output a BLOB to a file. Our sample database doesn’t include a BLOB, but the query might look like the following:
SELECT pic_blob FROM images INTO DUMPFILE 'world.jpg' WHERE pic_name = 'World';
Counting
If you consider the various SELECT queries we’ve created in this section, you will note that they all output some number of result rows. What if we want a query that counts the total number of rows in a result? The total count can be returned using the count(*) option. For example:
mysql> SELECT count(*) FROM acc; +---+
| count(*) | +---+ | 8 | +---+
1 row in set (0.00 sec)
Here we execute a SELECT to return the total number of rows in the acc table. Notice that the count value is returned as a column in the result. The column heading value can be changed, as we explained earlier in this section.
Updates
The first major SQL statement we covered was INSERT, which you use to place data into your database. This was followed by the SELECT statement, which you use to pull the data from your database. What do you do if you want to change the data within a row? You have two options. The first is to just make the change. You can do this with the UPDATE command:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2, ...] [WHERE where_definition]
[LIMIT #]
If you have a user who changes his or her password, you can use the UPDATE command to make the change in the database. Consider the following SELECT, UPDATE, SELECT combination:
mysql> SELECT * FROM acc WHERE username='jime'; +---+---+---+---+ | acc_id | username | password | ts | +---+---+---+---+ | 1034034 | jime | NULL | 20021014165845 | +---+---+---+---+ 1 row in set (0.00 sec)
mysql> UPDATE acc SET password='ime' WHERE username='jime'; Query OK, 1 rows affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM acc WHERE username='jime'; +---+---+---+---+ | acc_id | username | password | ts | +---+---+---+---+ | 1034034 | jime | ime | 20021014204947 | +---+---+---+---+ 1 row in set (0.00 sec)
In this combination of SQL commands, we display the row where the username is jime. The password is shown to be NULL. We use the UPDATE command to change the password to ime. Notice that the UPDATE command instructs a specific table to be updated; then the column that needs to be changed is indi- cated by SET. If we have to change numerous columns, we can use multiple SETs and separate them by commas. Finally, we can use a condition to limit the rows changed. The last SELECT command shows that the row was updated correctly.
The second way to update a database is to never change a row in the database but instead to inactivate one row and insert a new one. In order to do this type of update, you must include two timestamp fields in each row. The first is called an active timestamp, and the second is just the timestamp. The most active row in the database for a particular key has a timestamp of 0. The active time- stamp will be the time when the row was inserted. Once the row is inserted, the active timestamp of the current row is copied to the timestamp (ts field) of the inactive row.
To support this type of update, we’ve changed the table acc a bit. The new table definition looks like this:
mysql> describe acc;
+---+---+---+---+---+---+ | Field | Type | NULL | Key | Default | Extra | +---+---+---+---+---+---+ | acc_id | int(11) | | PRI | 0 | | | username | varchar(64) | | PRI | | | | password | varchar(64) | YES | | NULL | | | ts | timestamp(14) | YES | PRI | NULL | | | act_ts | timestamp(14) | YES | | NULL | | +---+---+---+---+---+---+ 5 rows in set (0.00 sec)
As you can see, we’ve added an act_ts column defined as a timestamp; defined the username, acc_id, and ts not to be NULL; and defined the primary key as a combi- nation of acc_id, username, and ts. To show the process of doing the update, con- sider the row with an acc_id of 1034055. When the initial row was placed in the database, the ts column was set to 0, and the act_ts was set to the actual time the row was inserted. Here’s the output of a SELECT showing the row:
mysql> SELECT * FROM acc WHERE acc_id = '1034055';
+--- +---+---+---+--- --+ | acc_id |username| password | ts | act_ts | +--- -+---+---+---+-- ---+ |1034055 | jdoe | ime | 00000000000000 |20021014212444| +---+---+---+---+---+ 1 row in set (0.00 sec)
Next, we need to insert a new row into the database. In order for the database to remain consistent, we need to relate the old row to the new row using a time- stamp. The timestamp needs to be the same, so the first step is to obtain the current time and place it in a temporary variable. We accomplish this by using a SET command and local system variable. For example:
mysql> set @time=now();
Query OK, 0 rows affected (0.03 sec)
The @time variable now holds a timestamp, and it can be used to insert the new row and change the old row. First, the old row is updated and the ts column is set to the current time:
mysql> UPDATE acc SET ts=@time WHERE acc_id = 1034055; Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM acc WHERE acc_id = '1034055';
+---+---+---+---+---+ |acc_id |username| password | ts | act_ts | +---+---+---+---+---+ |1034055 | jdoe | ime | 20021014212553 | 20021014212444| +---+---+---+---+---+ 1 row in set (0.01 sec)
Now we can insert the new row:
mysql> INSERT INTO acc VALUES(1034055, 'jdoe', 'newpass', 0, @time); Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
A final SELECT will show both of the rows and how they relate through the act_ts column of the new row and the ts of the old row:
mysql> SELECT * FROM acc WHERE acc_id = '1034055';
+---+---+---+---+---+ |acc_id |username| password | ts | act_ts | +---+---+---+---+---+ |1034055| jdoe | newpass | 00000000000000 | 20021014212553 | |1034055| jdoe | ime | 20021014212553 | 20021014212444 | +---+---+---+---+---+ 2 rows in set (0.01 sec)
We can always know the active row by including ts=0 in our queries.
Deletes
When data is no longer needed in a database, you can use the DELETE com- mand to remove a row. However, if you want to maintain the history of the rows in the database, you should instead make the row inactive.
First, let’s show the removal of a row. The query looks like this: DELETE FROM acc WHERE acc_id = '1034154';
The query will select the appropriate row based on the WHERE clause. Another use of the DELETE command is:
DELETE FROM acc;
This query doesn’t include a WHERE clause and thus will remove all rows from the specified database table. To maintain the history of the rows in the data- base, you shouldn’t use the DELETE command because the row will be perma- nently removed. In that case, the best way to “delete” the row is to make the row inactive by setting the ts of the row to a timestamp other than 0. In most cases, you want to update the current row to a current timestamp value so that the row has a record of when it was made inactive.