Types of data

When designing your tables, you need to choose what attributes you want the table to describe and, just as importantly, what type of data each attribute is. Because data comes in all shapes and sizes, DBMSs allow you to choose very specific storage types for each attribute - should it be a string, a number, or a boolean? More specifically, should it be a small string, a large string, a huge string, a floating-point number, or a cardinal number (only accepts positive integers)?

Most databases support the same set of data types, but they all have their own extensions here and there. This table describes the most popular column types available in MySQL:

Column

Data

TINYINT

-128 to 127, or 0 to 255

SMALLINT

-32768 to 32767, or 0 to 65535

MEDIUMINT

-8388608 to 8388607, or 0 to 16777215

INT

-2147483648 to 2147483647, or 0 to 4294967295

BIGINT

-9223372036854775808 to 9223372036854775807, or 0 to 18446744073709551615

FLOAT

Floating-point number, single-precision

DOUBLE

Floating-point number, double-precision

CHAR

Fixed-length strings up to 255 characters in size

VARCHAR

Variable-length strings up to 255 characters in size

TEXT

Huge, variable-length strings, case-insensitive

BLOB

Huge, variable-length strings, case-sensitive

DATETIME

Date and time in the format YYYY-MM-DD HH:MM:SS

As you can see, the top five data types are all integers - this is the recommended way of saying how large an integer you need. Note that that each integer comes in signed format (allows negative numbers) and unsigned (does not allow negative numbers, but therefore has a larger range of positive numbers)..

Floats and doubles are both floating-point numbers that handle massive numbers, with doubles holding the largest. These are specified using the size of number you display when reading from the field, and the number of decimals also. Therefore FLOAT(5,2) would output a maximum number of 99999.99.

CHAR and VARCHAR fields hold character data from one character in length to a maximum of 255 characters. A character can be any data - any letter, number, symbol, etc - and that makes CHAR and VARCHAR fields very flexible. CHARs and VARCHARs have their length specified after them inside brackets, e.g. CHAR(10) is a fixed-length, 10-character string field, whereas VARCHAR(200) is a variable-length, 10-character string field

The difference between the two is that CHAR uses a fixed-length for each field, whereas VARCHAR (VARiable CHARacter) is specified using a maximum size - if one row only uses 10 characters of its 255, it only takes up 10 bytes of space. Fixed-length records always take up their full amount of space, which has advantages and disadvantages. Fixed-length fields take up more room, but are easier to search through, whereas variable-length fields take up less room, and are slower to search through.

As most hardware today is CPU bound as opposed to hard drive bound, the chance are you will want to use CHAR fields rather than VARCHAR - you should try some small experiments out to see what your situation requires. Note that if all the fields in a table are fixed-length, the table itself is classified as fixed-length, whereas if one or more fields are variable-length, the table is classified as variable-length. This is often referred to as the row format of a table.

Author's Note: I am not fond of repeating myself, and I know you are not reading this just because you like to see the same thing twice, but I want to make quite sure you read this one statement: If all the fields in a table are fixed-length, the table itself is classified as fixed-length, whereas if one or more fields are variable-length, the table is classified as variable-length and hence probably slower. That last part is particularly important.

Once you define a field as being of type X, you are usually able to convert it to another type if so needed, and MySQL will attempt to make the conversion as painless as possible. For example, if you change from a 10-character VARCHAR field to a 20-character VARCHAR field, MySQL will keep the existing data there and just give each row more room when needed. If you try to convert a 10-character VARCHAR field to an unsigned integer, however, MySQL will simply delete the existing data, as no meaningful conversion is possible.

Just to make sure you are fully up to speed on data types, here's a short table detailing possible data types for different sorts of every day variables:

Example

Data Type

Reasoning

Names

CHAR(20)

You should use separate fields for first name, last name, etc.

Ages

TINYINT UNSIGNED

Has a range of 0 to 255; more than enough

ID number

INT UNSIGNED

Gives you lots of room to grow

Images

CHAR(255)

See below

Life stories

TEXT

Enough room to hold even the longest life story

Dates

INT

See below

Those should make sense, with the exception perhaps of Date. Why not use the built-in DATETIME field type? If you read the chapter on working with date and time, you will know that PHP's default way to work with dates is using the number of seconds that have elapsed since January 1st 1970. More often than not it is easiest to store this Unix timestamp directly in the database, thereby keeping all your date and time in the same format across the board.

Author note: If you noticed, I said in the table that the best way to store images is as 255-character text strings. How do you fit images into just 255 characters? Well, the answer is that you do not - putting images into your database is not a smart move because they were not designed to handle that kind of data. Your web server, on the other hand, was designed to work well with images, and will do a far better job - faster and more efficient - than your database server.

The best way to keep images in a database, indeed the only sensible way, is to just store filenames in there that point to where the images are stored on your web server, and for that purpose CHAR(255) is more than sufficient.

One final note before we move on: always try to choose the most precise data type. Choosing CHAR(100) rather than CHAR(255) will cut your storage requirements to about 40% of what they would otherwise have needed to be. Similarly using a BIGINT for ages is also a big waste - put some thought into the data types you choose.

 

Next chapter: Date and time >>

Previous chapter: Database hierarchy

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar