Sometimes you need to ensure that every row in your database is guaranteed to be unique. You could do this in your program by carefully checking the data you enter and making sure that there is at least one value that differs in any two rows, but this approach is error-prone and works only in certain circumstances. In the classics table, for instance, an author may appear multiple times. Likewise, the year of publication will also be frequently duplicated, and so on. It would be hard to guarantee that you have no duplicate rows.

The general solution is to use an extra column just for this purpose. In a while, we’ll look at using a publication’s ISBN (International Standard Book Number) or product code, but first I’d like to introduce the AUTO_INCREMENT data type.

As its name implies, a column given this data type will set the value of its contents to that of the column entry in the previously inserted row, plus 1. Next example shows how to add a new column called id to the table classics with auto-incrementing.

Adding the auto-incrementing column id:
ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;

This is your introduction to the ALTER command, which is very similar to CREATE. ALTER operates on an existing table and can add, change, or delete columns. Our example adds a column named id with the following characteristics:

INT UNSIGNED -> Makes the column take an integer large enough for us to store more than 4 billion records in the table.

NOT NULL -> Ensures that every column has a value. Many programmers use NULL in a field to indicate that it doesn’t have any value. But that would allow duplicates, which would violate the whole reason for this column’s existence, so we disallow NULL values.

AUTO_INCREMENT -> Causes MySQL to set a unique value for this column in every row, as described earlier. We don’t really have control over the value that this column will take in each row, but we don’t care: all we care about is that we are guaranteed a unique value.

KEY -> An auto-increment column is useful as a key, because you will tend to search for rows based on this column.

Each entry in the column id will now have a unique number, with the first starting at 1 and the others counting upward from there. And whenever a new row is inserted, its id column will automatically be given the next number in the sequence. Rather than applying the column retroactively, you could have included it by issuing the CREATE command in a slightly different format.

Adding the auto-incrementing id column at table creation:

CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4),
id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE InnoDB;

The main remaining data types supported by MySQL relate to the date and time and can be seen in next table.

Data type Time/date format
DATETIME ‘0000-00-00 00:00:00’
DATE ‘0000-00-00’
TIMESTAMP ‘0000-00-00 00:00:00’
TIME ’00:00:00′
YEAR 0000 (Only years 0000 and 1901–2155)

The DATETIME and TIMESTAMP data types display the same way. The main difference is that TIMESTAMP has a very narrow range (from the years 1970 through 2037), whereas DATETIME will hold just about any date you’re likely to specify, unless you’re interested in ancient history or science fiction. TIMESTAMP is useful, however, because you can let MySQL set the value for you. If you don’t specify the value when adding a row, the current time is automatically inserted. You can also have MySQL update a TIMESTAMP column each time you change a row.

The term BLOB stands for Binar Large OBject, and therefore, as you would think, the BLOB data type is most useful for binary data in excess of 65,536 bytes in size. The main other difference between the BLOB and BINARY data types is that BLOBs cannot have default values.

• TINYBLOB(n) Up to n (<= 255) Treated as binary data—no character set
• BLOB(n) Up to n (<= 65535) Treated as binary data—no character set
• MEDIUMBLOB(n) Up to n (<= 1.67e+7) Treated as binary data—no character set
• LONGBLOB(n) Up to n (<= 4.29e+9) Treated as binary data—no character set

MySQL supports various numeric data types, from a single byte up to doubleprecision floating-point numbers. Although the most memory that a numeric field can use up is 8 bytes, you are well advised to choose the smallest data type that will adequately handle the largest value you expect. This will help keep your databases small and quickly accessible.

Next lists the numeric data types supported by MySQL and the ranges of values they can contain. In case you are not acquainted with the terms, a signed number is one with a possible range from a minus value, through 0, to a positive one; and anunsigned number has a value ranging from 0 to a positive one. They can both hold the same number of values; just picture a signed number as being shifted halfway to the left so that half its values are negative and half are positive. Note that floating-point
values (of any precision) may only be signed.

Data type / Bytes used / Minimum value / Maximum value / Unsigned min / Unsigned max
TINYINT 1 –128 127 0 255
SMALLINT 2 –32768 32767 0 65535
MEDIUMINT 3 –8.38e+6 8.38e+6 0 1.67e+7
INT / INTEGER 4 –2.15e+9 2.15e+9 0 4.29e+9
BIGINT 8 –9.22e+18 9.22e+18 0 1.84e+19
FLOAT 4 –3.40e+38 3.4e+38 – –
DOUBLE / REAL 8 –1.80e+308 1.80e+308 – –

To specify whether a data type is unsigned, use the UNSIGNED qualifier. The following example creates a table called tablename with a field in it called fieldname of the data type UNSIGNED INTEGER:

CREATE TABLE tablename (fieldname INT UNSIGNED);

When creating a numeric field, you can also pass an optional number as a parameter, like this:

CREATE TABLE tablename (fieldname INT(4));

But you must remember that, unlike with the BINARY and CHAR data types, this parameter does not indicate the number of bytes of storage to use. It may seem counterintuitive, but what the number actually represents is the display width of the data in the field when it is retrieved. It is commonly used with the ZEROFILL qualifier, like
this:

CREATE TABLE tablename (fieldname INT(4) ZEROFILL);

What this does is cause any numbers with a width of less than four characters to be padded with one or more zeros, sufficient to make the display width of the field four characters long. When a field is already of the specified width or greater, no padding takes place.

Character data can also be stored in one of the set of TEXT fields. The differences between these fields and VARCHAR fields are small:

• Prior to version 5.0.3, MySQL would remove leading and trailing spaces from VARCHAR fields.
• TEXT fields cannot have default values.

• MySQL indexes only the first n characters of a TEXT column (you specify n when you create the index).

What this means is that VARCHAR is the better and faster data type to use if you need to search the entire contents of a field. If you will never search more than a certain number of leading characters in a field, you should probably use a TEXT data type.

• TINYTEXT(n) Up to n (<= 255) Treated as a string with a character set
• TEXT(n) Up to n (<= 65535) Treated as a string with a character set
• MEDIUMTEXT(n) Up to n (<= 1.67e+7) Treated as a string with a character set
• LONGTEXT(n) Up to n (<= 4.29e+9) Treated as a string with a character set

The data types that have smaller maximums are also more efficient; therefore, you should use the one with the smallest maximum that you know is enough for any string you will be storing in the field.

In a similar fashion to ORDER BY, you can group results returned from queries using GROUP BY, which is good for retrieving information about a group of data. For example, if you want to know how many artist there are of each category in the classics table, you can issue the following query:

SELECT category,COUNT(author) FROM classics GROUP BY category;

ORDER BY sorts returned results by one or more columns in ascending or descending order.

SELECT author,title FROM classics ORDER BY author;
SELECT author,title FROM classics ORDER BY title DESC;

As you can see, the first query returns the artist by author in ascending alphabetical order (the default), and the second returns them by title in descending order.

If you wanted to sort all the rows by author and then by descending year of publication (to view the most recent first), you would issue the following query:

SELECT author,title,year FROM classics ORDER BY author,year DESC;

This shows that each ascending and descending qualifier applies to a single column. The DESC keyword applies only to the preceding column, year. Because you allow author to use the default sort order, it is sorted in ascending order. You could also have explicitly specified ascending order for that column, with the same results:

SELECT author,title,year FROM classics ORDER BY author ASC,year DESC;

This construct allows you to update the contents of a field. If you wish to change the contents of one or more fields, you need to first narrow in on just the field or fields to be changed, in much the same way you use the SELECT command.

Example:
UPDATE classics SET author=’Markku Aro (Markku Puputti)’
WHERE author=’Markku Aro’;
UPDATE classics SET type=’Suomi POP’
WHERE type=’POP’;

In the first query, Markku Aros’s real name of Markku Puputti was appended to his pen name in parentheses, which affected only one row. The second query, however, affected three rows, because it changed all occurrences of the word POP in the category column to the term Suomi POP. When performing an update, you can also make use of the qualifiers you have already seen, such as LIMIT, and the following ORDER BY and GROUP BY keywords.

If you wish to give your MATCH…AGAINST queries even more power, use Boolean mode. This changes the effect of the standard FULLTEXT query so that it searches for any combination of search words, instead of requiring all search words to be in the text. The presence of a single word in a column causes the search to return the row. Boolean mode also allows you to preface search words with a + or – sign to indicate
whether they must be included or excluded. If normal Boolean mode says, “Any of these words will do,” a plus sign means, “This word must be present; otherwise, don’t return the row.” A minus sign means, “This word must not be present; its presence disqualifies the row from being returned.”

The first asks for all rows containing the word suosikkia and not the word Aro to be returned. The second uses double quotes to request that all rows containing the exact phrase origin of be returned.

SELECT author,title FROM classics
WHERE MATCH(author,title)
AGAINST(‘+suosikkia -Aro’ IN BOOLEAN MODE);
SELECT author,title FROM classics
WHERE MATCH(author,title)
AGAINST(‘kaikki parhaat’ IN BOOLEAN MODE);