MySQL 5 certification. A new challenge

MySQL 5 is still beta (today, September 23, 2005) and we, the users, don't know when it will become stable. However, some clues are starting to mount, and by putting things together we can try to foresee when this event will take place.

One of such clues is the recent release of a new book, MySQL® 5 Certification Study Guide, the official guide to the exam to become a MySQL certified professional.
The book is available at major bookstores. I am still waiting for my copy from Amazon, but in the meantime I was able to read the interesting parts in Safari. The news are that there are no longer a Core and Professional Certifications. Now there are two certification paths, Developer and DBA, each of which require two exams to get the title.

Therefore, in the new course, rather than holding a "core certification" or a "professional certification", you can get :

• MySQL Developer I
• MySQL Developer II
• MySQL DBA I
• MySQL DBA II

Among the new things that were not in the previous exam and are required in the new one are:

• The Information Schema
• prepared statements
• stored procedures and functions
• triggers
• views
  
• Mysql query browser
• scaling MySQL
  

However, the book seems to have been rewritten almost completely.
The book is 672 pages long. So, if you are thinking about getting a certificate as early as possible, start planning your reading path.

Multiple conditions updates

PW says he would like to find a way of executing several UPDATE statements in one table using just one query.
In practice, he has several products from different companies, and he wants to update them in a compact way, without repeating the same query several times.

For example, PW wants to apply a different discount on books from IT, finance, and current affairs sections, with different amounts for each category.

One way is this one:

UPDATE books
SET discount =
   CASE
       WHEN section = 'IT'              THEN 12
       WHEN section = 'current affairs' THEN 10
       WHEN section = 'finance'         THEN 8
       ELSE discount
   END
WHERE id_publisher IN (5,6,8,11);

This query acts only on books from the requested publishers (see the WHERE clause) and among them updates the account column from the given sections.
The remaining sections the updated value is the same, thanks to CASE's function ELSE clause.

Since this operation runs in one query only, composing it could be a daunting task, harder than making separate queries for each condition, but sometimes there is a real efficiency gain, and it's good to know that there are alternatives available!

Listing records with a running count

This request came to a programming forum: "how do I list a record set with a running count for each record?"

There are several answers to this question. The first that would come to mind should be to implement the count within the application at the receiving end of the data stream. This solution, though, requires different implementation details foe each language, and could not be what we are looking for.

Instead, there is a quick way of making MySQL count records while listing them, so that we have a result that is always the same, no matter which language we use to code our application.

Perhaps not everybody is aware that MySQL allows so called "user variables" in your queries. They are private variables for each connection, and they stay available for the whole duration of your session.

For example, a frequent usage of this feature was a workaround for lack of subqueries in MySQL versions lower than 4.1:

mysql> SELECT @maximum := max(salary) from wages;

mysql> SELECT name, surname, salary from wages where salary = @maximum;

In the same vein, we can use a user variable to create a running counter. Since each variable holds a NULL value unless explicitly initialized, you need to send two queries to get what you want.

First, let's see the query without a counter.

mysql> SELECT name,salary, gender FROM persons;
+--------+--------+--------+
| name   | salary | gender |
+--------+--------+--------+
| John   |   5000 | m      |
| Mario  |   6000 | m      |
| Frank  |   5000 | m      |
| Otto   |   6000 | m      |
| Susan  |   5500 | f      |
| Martin |   5500 | m      |
| Mary   |   5500 | f      |
| Bill   |   5000 | m      |
| June   |   6000 | f      |
+--------+--------+--------+
9 rows in set (0.01 sec)

Thyen, let's execute those two queries we have mentioned. First of all, we need to initialize the counter.

mysql> set @N = 0;
Query OK, 0 rows affected (0.27 sec)

Thus, we can send the second query, including our counter, with an appropriate formula to increment it.

mysql> SELECT @N := @N +1 AS number, name,salary,gender FROM persons;
+--------+--------+-----------+--------+
| number | name   |    salary | gender |
+--------+--------+-----------+--------+
|      1 | John   |      5000 | m      |
|      2 | Mario  |      6000 | m      |
|      3 | Frank  |      5000 | m      |
|      4 | Otto   |      6000 | m      |
|      5 | Susan  |      5500 | f      |
|      6 | Martin |      5500 | m      |
|      7 | Mary   |      5500 | f      |
|      8 | Bill   |      5000 | m      |
|      9 | June   |      6000 | f      |
+--------+--------+-----------+--------+
9 rows in set (0.01 sec)

Please note that each variable in a query can be calculated only once per record.

Finally, if you adopt such a solution, don't forget to initialize the counter before running the query a second time, otherwise your records will hold a constantly increasing counter!