MySQL Conventional Data Types

Implementing Database in MySQL – Part 3

Foreword: In this part of the series, I talk about MySQL data types.

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 3 of my series, Implementing Database in MySQL. I assume you have read the previous parts of the series before reaching here, because this is the continuation. In this part of the series, I talk about MySQL data types.

Values (numbers and strings) are not typed into MySQL arbitrary. You have to indicate the kind of datum (singular for data) you are dealing with. A data type is used to determine the type of data that will be used by the cell values in a table column - see later.

The String Types
The string data types are for text. There are different types.

The CHAR Data Type
This type can have a maximum length of 255 characters. If you know that in your column, no cell value will be longer than 30 characters, then you should type, CHAR(30). That is you type the maximum number of characters you want, in parentheses.

The VARCHAR Type
This is similar to CHAR, but the maximum length is 65,535.

The BINARY Data Type
The binary data type is used for non-text data such as image files. You can have a column where each cell has the content of an image file. The maximum size of the BINARY type is 255 bytes. Consider the size of a byte as the size of one character.

The VARBINARY Type
The VARBINARY Type is like the BINARY, but its maximum size is 65,535.

The Text Types
A text type is like CHAR, but it is for larger text. It has 4 variations as follows:

TINYTEXT: maximum size is 256 characters.
TEXT: maximum size is 65,536 characters.
MEDIUMTEXT: maximum size is 16,777,216 characters
LONGTEXT: maximum size of 4,294,967,296 characters

The BLOB Types
A BLOB Type is like BINARY. It has 4 variations as follows:

TINYBLOB: maximum of 256 bytes.
BLOB: maximum size is 65,536 bytes.
MEDIUMBLOB: maximum size is 16,777,216 bytes.
LONGBLOB: maximum size of 4,294,967,296 bytes.

The ENUM Type
ENUM means enumeration. An example of an ENUM type is,

ColumnName ENUM('small', 'medium', 'large')

You type this during table creation. Now, in the column that has the ENUM type, instead of typing ‘small’ in any of the cells, you would type, 1, Instead of typing 'medium' you would type 2. Instead of typing 'large' you would type 3. So in the table cells of the column you have but numbers and not the string values themselves. So any value in the column comes from the enumeration. In the above case, you cannot have any number that represents a value above 3.

A numeration can have any number of values. Remember, what you type in a column cell is the position number of the string value of the enumeration. A string value can actually be a phrase. An enumeration can have a maximum of 65,535 elements. The ENUM type deserves a full tutorial article on its own. I hope to go into its details in a separate article.

The SET Type
The SET is similar to the ENUM, but each string value here cannot have a comma. Consider the example, which you can have during table creation:

SET('one', 'two') NOT NULL

A column cell value can be, ‘one’ or ‘two’ or ‘one, two’. Whatever you type into a column cell must come from the set. You can type a combination of the values from the set, but separate them with commas. Because of this, you cannot have a comma in a SET value (during table creation). The SET type deserves a full tutorial article on its own. I hope to go into its details in a separate article.

All what I have said so far belong to the string type. Let us now look at other types. The major types are not many, but the detail types are many.

The Numeric Data Types
These types are for the numbers.

The INTEGER Type
INT is synonymous to INTEGER. The INTEGER type has variations. The INT types are for integers:

TINYINT: is stored in1 byte
SMALLINT: is stored in 2 bytes
MEDIUMINT: is stored in 3 bytes
INT, that is, INTEGER: is stored in 4 bytes
BIGINT: is stored in 8 bytes

The DECEMAL and NUMERIC Types
The DECIMAL type is typed as DECIMAL(M,D), where M is the total number of digits and D is the number of decimal places in the total number, M. The NUMERIC type is typed as NUMERIC(M,D), where M is the number of digits and D is the number of decimal places. For a money column use DECIMAL type; you should type something like, DECIMAL (8,2).

The Approximate NUMERIC Data Types
When you are working with approximation, you should use the FLOAT, REAL, or DOUBLE PRECISION data types (see below):

The FLOAT type: this type is a decimal number that is stored in 4 bytes. You can have something like, FLOAT (5,3); it means the total number of digits is 5 of which the last 3 are decimals; the number of decimal places being 3.

The DOUBLE PRECISION or REAL
Either of these can be stored in 8 bytes. You can type either of them in the form REAL(M,D) or DOUBLE PRECISION(M,D), where M is the total number of digits and D is the number of decimal places. D is part of the total.

FLOAT, DOUBLE PRECISION or REAL are approximate numbers. Precise numbers are DECIMAL and REAL.

The Date and Time Types
There are five variations to this. You have the DATETIME, DATE, TIMESTAMP, TIME, and YEAR types.

The Date Type
The DATE type is typed in the format: Year-Month-Day; e.g. 2010-11-28. If you are from the U.S.A you may not like this since you are used to the Month-Day-Year format. There is a tendency to think that every major software package originates from the U.S.A. MySQL originates from a country in Europe, but today, it is as popular as the packages from the U.S.A. The DATE data type ranges from 1970 to 1999.

The TIME Type
This type is typed in the format: HH:MM:SS, e.g. 10:11:12. HH means hours; MM means minutes and SS means Seconds. Time values range from -838:59:59 to 838:59:59.

The Year Type
This is a four-digit year. It ranges from 1901 to 2155.

The DATETIME Type
This type means the date and time together. It has the format, YYYY-MM-DD HH:MM:SS, e.g. 98-12-31 11:30:45.

The TIMESTAMP Data Type
This data type is the same as the DATETIME type, but it is normally given by the DBMS, when a row is inserted (created).

That is what I have prepared for MySQL data types. It is a summary of MySQL conventional data types. Let us take a break here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

MySQL Conventional Data Types

Implementing Database in MySQL – Part 3

Introduction

Related Links

Comments