WDS 3

MySQL Data Types

Data type is the characteristic of columns and variables that defines what types of data values they can store. The characteristic indicating whether a data item represents a number, date, character string, and etc.

Data type descriptions use the following conventions:

M indicates the maximum display width for integer types and the total amount of digits that can be stored
M is also the maximum length for string types
M’s maximum allowable value depends on the data type you’ll use
D applies to floating-point and fixed-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M–2
Square brackets ('[' and ']') indicate optional parts of type definitions.

The guidelines for this success criterion are listed bellow:

Numeric Types

Some numeric data types are INTEGER, SMALLINT, DECIMAL, and NUMERIC just to name a few. Each have different characteristics that helps the database know how much to hold in its columns and rows.

numeric data type's storage and range

If you want to specify the width of integer specifically, you can put the length in parenthesis like this:

INT(5)

If you use ZEROFILL, it will put in zeros where you had potential spaces, for example:

INT(5)ZEROFILL

You then enter 4
00004 is what’s retrieved

Date and Time Types

Some date and time data types are DATETIME, DATE, TIMESTAMP, TIME, YEAR and their formats are shown bellow.

date and time data type formats

Just a note: Starting from MySQL 5.0.2, MySQL gives warnings or errors if you try to insert an illegal date. By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. Some ways to even beat the system if you do want to use illegal dates are to use these following attributes, ALLOW_INVALID_DATES and NO_ZERO_IN_DATE.

String Types

For string types we are pretty particular on binary and character strings. For my purposes I’m only going to address just a few with their similarities and differences.

CHAR and VARCHAR

CHAR holds up to 30 characters and length is from 0 to 255. When it’s retrieved, trailing spaces are removed.
VARCHAR holds 0 to 65,535 in MySQL 5.0.3 and trailing spaces are not removed in this version

VARBINARY and BINARY

VARBINARY and BINARY are just about the same as CHAR and VARCHAR except that the lengths are in bytes and not characters.

BLOB and TEXT

A BLOB is a binary large object that can hold a variable amount of data. BOLBS are binary strings where TEXT is character strings. There is no trailing space removal for either. You probably want to store media files in these types of data types.

Data Type Storage Requirements

By clicking on the above link, you will find the actual specification of the storage requirements for some data types in MySQL’s website.

There is many more data types that are not mentioned in this research summary but I hope it gives you a taste of what is being accomplished with MySQL and its relationship to databases. For more information on data types and MySQL check out Hscripts.com's tutorial on MySQL.