Default values

It is a common situation that you will not have all the data for all fields a table, so what do you do with the fields that have no data? Using default values, you can specify what value a field should have if you do not provide it - not only does this stop you from having NULL values scattered everywhere, but it also means that you can purposefully rely on default values in situations where it makes sense, e.g. if you are creating a table to track the number of times someone has bought something, you will always want to start the number off at 0.

You can provide default values for all data types, including strings, and MySQL will automatically use the default if you do not provide a value when inserting data. Consider the following code to track how many times a given person has visited a site:

CREATE TABLE people (Name CHAR(50), NumVisits INT);
INSERT INTO people VALUES ('Bob', 30);
INSERT INTO people VALUES ('Simone', 22);
INSERT INTO people (Name) VALUES ('Ildiko');
INSERT INTO people (Name, NumVisits) VALUES ('Richard', 25);
SELECT * FROM people;

After running those queries, we will get the following output:

As you can see, Ildiko's NumVisits is NULL - she's visited the site "unknown" times. If you now wanted to run a query to see who had never visited the site, you would probably use a query something like this:

SELECT * FROM people WHERE NumVisits = 0;

Sadly, that would return no rows - NULL never evaluates to any number, which means you cannot use conventional queries on it. This is one of the many places where default values can help out - we should really assign NumVisits a default value of 0. This is done using the DEFAULT keyword for CREATE TABLE - here's an example where NumVisits has a default value of 0, and Name has a default value of "Bubba" to illustrate how to give strings a default value:

CREATE TABLE people (Name CHAR(50) DEFAULT 'Bubba', NumVisits INT DEFAULT 0);

This time running our original SELECT statement outputs the following rows:

As you can see, the default value of 0 got used for Ildiko, where we did not provide a NumVisits value. Similarly, if no Name field was provided, Bubba would be used - here's a query to insert an empty row:

INSERT INTO people VALUES ();

Now we will have Bubba in there, with a NumVisits of 0.

Apart from eliminating NULLs, default values are a great way of cutting down the amount of work MySQL needs to do - rather than passing in lots of default values in your SQL queries, you can just leave them for MySQL to fill in, which works out a great deal faster.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.