MySQL Deserves a Double Take

In early 1995, when Windows 95 was still vaporware and Red Hat was the upstart, user-friendly alternative to Slackware, I worked for the “Pathfinder” site at media giant Time Warner. Like all media companies, Time Warner realized that the Web was going to take off, but wasn't sure just how that would happen.

So, it hired a bunch of programmers and designers, and gave us the opportunity to experiment with different types of designs and applications. It was a wonderful job, with some creative, smart and interesting people. And, during my time there, I created all sorts of applications—quizzes, mail auto-responders, games, search engines and even a personalized version of Money magazine's “Best Cities” rankings.

As the applications I built became increasingly sophisticated, it became obvious that the text files I often used for data storage and retrieval were neither efficient nor flexible enough for a site as large and popular as ours. Finally, someone introduced me to our newly hired database guru, who taught me about the wonders of relational databases and SQL. I was hooked, and I enjoyed working with the database server that we had installed.

When I wanted to reap the benefits of SQL on my Linux box at home, my options were, unfortunately, limited. I found a number of abandoned open-source database projects, but nothing that was as powerful as Time Warner's Sybase server or even in the same league.

So, you can imagine my delight when I discovered MySQL. No, it didn't do all the things that Sybase did, and it wasn't released under an open-source license. But, it was free of charge, it was easy to install and it had enough features to keep people like me relatively happy. Internet service providers felt similarly, and began to install it on their systems—first as a competitive advantage over their rivals, and then because everyone else was including it in the base configuration.

Fast-forward more than a decade, and MySQL is by far the best-known open-source relational database. Monty Widenius and David Axmark, whom I met back when they were the only full-time MySQL programmers, are now at the top of a large corporate pyramid. MySQL AB now distributes its products under the GNU General Public License (GPL), with a closed-source license available to those who require it. It runs, as always, on a very large number of different operating environments. And, it is still developed at a feverish pace by people around the world, who submit patches and suggestions.

This is the first of three articles on open-source databases. This month, I discuss MySQL, including its use, features and problems. The next article will include a similar analysis of PostgreSQL, and the third article in the series will compare the two databases.

One of MySQL's claims to fame is the ease with which people can get started using it. And, indeed, when you compare MySQL with many commercial databases, it is strikingly simple. You install it (typically with an RPM or Deb, but compiling it from source is also straightforward), and start up the database server with safe_mysqld. (You also could use the plain mysqld command, but then you wouldn't benefit from some of the behind-the-scenes housekeeping that safe_mysqld offers.)

Once you have started the server, you can create one or more databases. (I admit it is somewhat confusing that MySQL is often referred to as a database when, in fact, it is a database server, offering you the chance to create one or more databases. Each database contains one or more two-dimensional tables.) To create a database, use the mysqladmin program:

mysqladmin create testdb

It is quite possible, depending on your configuration, that the above command worked without a hitch—particularly if you are logged in as the root user under Linux. However, your system administrator might have (wisely) decided to set a password for the MySQL root user, in which case, you need to type:

mysqladmin -p create testdb

The -p option tells mysqladmin that you want to enter a password for this account. You also can specify the root user, or any other user, with the -u option, as in:

mysqladmin -u mysqlroot -p create testdb

Once you have created a database, you then can connect to it with the mysql client program:

mysql -u mysqlroot -p testdb

Notice that I'm once again specifying a user name and that I want to enter a password. I return to the subject of permissions below; for now, we assume that this combination works.

In the client, you can issue any SQL command you want, and it will be executed immediately. For example, we can create a new table:

CREATE TABLE Classes (
class_name    TEXT    NOT NULL,
room_number INTEGER  NOT NULL,
starting_date  DATE NOT NULL,
ending_date   DATE  NOT NULL,
instructor       TEXT    NOT NULL
);

One of the problems with the above table is that it lacks a unique primary key. This makes it difficult to refer to the Classes table from another table. We could use the name assigned to the class by the university's registration system, but there is no guarantee that this will be unique. Moreover, what will we do next year, when a class of the same name is offered? For this reason (among others), it's the norm to create an “artificial” primary key, one whose purpose is to identify a row within the database uniquely.

In MySQL, we can do this most easily with the AUTO_INCREMENT keyword. For example:

CREATE TABLE Classes (
    class_id         INTEGER AUTO_INCREMENT,
class_name    TEXT    NOT NULL,
room_number INTEGER  NOT NULL,
starting_date  DATE NOT NULL,
ending_date   DATE  NOT NULL,
instructor       TEXT    NOT NULL,

    PRIMARY KEY(class_id)
);

If we want, we can INSERT a row into Classes with an explicit integer value for class_id. The fact that class_id is defined as a primary key means that it is both indexed and guaranteed to be unique. But, if we fail to enter an explicit value for class_id, MySQL inserts a new value into the column, giving us a primary key value for the new class without having to calculate it ourselves.

The above table definition shows a few of the many data types MySQL offers. MySQL offers many traditional data types, such as NUMERIC and VARCHAR, but it also includes a number of signed and unsigned numeric types (for example, TINYINT, SMALLINT, MEDIUMINT, INT and SIGINT), a number of CLOB/BLOB types (such as, CHAR, BINARY, BLOB and TEXT), and several having to do with dates and times (DATE, DATETIME and TIMESTAMP). There are also ENUM and SET types, allowing you to work with nonstandard sets of enumerated data.

MySQL also offers a wide variety of operators, from simple string-concatenation, to date extraction, to one of my favorites, the CASE statement, which lets you place if-then logic inside of a query.

In addition, MySQL offers a system for full-text search. This means you can store text inside of TEXT columns in your tables, and then identify the column (and retrieve the text) without having to index it yourself.

If the included suite of functions doesn't suit your needs, you can always write one of your own. Recent versions of MySQL also offer the ability to create a stored procedure or function, which provides both increased speed and centralized control over commonly used functions. Stored procedures also can be invoked automatically when particular events occur, known as a trigger in database parlance. You also can write new functions in C or C++, loading them into MySQL at runtime.