Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

MySQL Commands – Part 4

Monday, July 7th, 2014 by Servage

BM8T0Numeric data types

MySQL supports various numeric data types, from a single byte up to double-precision floating-point numbers. Although the most memory that a numeric field can use up is eight 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.

To specify whether a data type is signed or 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 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.

DATE and TIME

The main remaining data types supported by MySQL relate to the date and time and can be seen. The DATETIME and TIMESTAMP data types display the same way. The main difference is that TIMESTAMP has a very narrow range (the years 1970 through 2037), whereas DATE TIME 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 AUTO_INCREMENT data type

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 is likely to be 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) to ensure that the rows in the classics table are unique, but first I’d like to introduce the AUTO_INCREMENT data type, which I will discuss in the next article.

Sources for further reading

MySQL Commands - Part 4, 5.0 out of 5 based on 1 rating
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.