Data Types

Possible Fields data types in databases’ tables

By NGIRUWONSANGA Albert
Column Types

• MySQL comes with a range of column types.

Several are similar but have subtle yet
important differences.
1. Text column types
• MySQL has seven 4. text
column types suitable for 5. mediumtext
storing text strings:
6. longtext
1. char
2. varchar 7. enum

3. tinytext
1.1. CHAR Usage: char(length)
• The char column type has a maximum length of
255 characters.
• This is a fixed length type, meaning that when a
value is inserted that has fewer characters than
the maximum length of the column, the field will
be right-padded with spaces.
1.1. CHAR Usage: char(length)
• So if a column has been defined as char(10) and
you want to store the value “happy”,
• MySQL will actually store “happy” and then five
spaces.
• The spaces are removed from the result when the
value is retrieved from the table.
1.2. VARCHAR Usage: varchar(length)
• This is nearly identical to char and is used in many

of the same places.

• It also has a maximum length of 255. The

difference is that varchar is a variable-length

column type.
1.2. VARCHAR Usage: varchar(length)
• The values will not be padded with spaces.

Instead MySQL will add one character to each

varchar field, which stores the length of the field.

• MySQL removes spaces from the end of strings in

varchar fields.
1.3. TINYTEXT Usage: tinytext

• This is first of the four binary (or blob) text

character types.

• All of these types (tinytext, text, mediumtext,

and largetext) are variable column types,
similar to varchar.
1.3. TINYTEXT Usage: tinytext
• They differ only in the size of string they can
contain.

• The tinytext type has a maximum length of 255, so

in fact it serves the same purpose as
varchar(255). An index can be created for an
entire tinytext column
1.4. TEXT Usage: text

• The text type has a maximum length of

65,535 characters.

• Indexes can be created on the first 255

characters of a text column.
1.5. MEDIUMTEXT Usage: mediumtext

• The mediumtext type has a maximum length

of 16,777,215 characters.

• Indexes can be created on the first 255

characters of a mediumtext column.
1.6. LONGTEXT Usage: longtext

• The longtext type has a maximum length of

4,294,967,295 characters.

• Indexes can be created on the first 255

characters of a longtext column. However,
this column currently is not very useful, as
MySQL allows string of only 16 million bytes.
1.7. ENUM
Usage: enum (‘value1’, ‘value2’, ‘value3’ ...) [default
‘value’]

• With enum, you can limit the potential values of a

column to those you specify.

• It allows for 65,535 values, though it’s difficult to see a

situation where you’d want to use this column with
more than a few potential values.
1.7. ENUM
• This type would be of use when, for example, you
want to allow only values of “yes” or “no”. The create
statement that makes use of enum will look like this:
create table my_table (
id int auto_increment primary key,
answer enum (‘yes’, ‘no’) default ‘no’
);
SET Usage: set
• set (‘value1’, ‘value2’, ‘value3’ ...) [default ‘value’]

• This column type defines a superset of values.

• This allows for zero or more values from the list you
specify to be included in a field.
2. Numeric column types
• MySQL has seven column types suitable for storing
numeric values.

• Note that the following are synonyms: int and

integer; double, double precision, and real; and
decimal and numeric.
2. Numeric column types
1) int/integer
2) tinyint
3) mediumint
4) bigint
5) float
6) double/double precision/real
7) decimal/numeric
2. Numeric column types
• For all numeric types the maximum display size is
255. For most numeric types you will have the
option to zerofill a column—to left-pad it with zeros.

• For example, if you have an int column that has a

display size of 10 and you insert a value of 25 into
this column,
2. Numeric column types
• MySQL will store and display 0000000025.

• The numeric column types may also be defined as

signed or unsigned. Signed is the default definition.
2.1. INT/INTEGER
• Usage: int(display size) [unsigned] [zerofill]

• If you use the unsigned flag, this column type can

store integers from 0 to 4,294,967,295. If signed, the
range is from –2,147,483,648 to 2,147,483,647.

• Int will often be used with auto_increment to

define the primary key of a table.
2.1. INT/INTEGER
create table my_table (
table_id int unsigned auto_increment primary key,
next_column text
);
2.2. TINYINT
• Usage: tinyint(display size) [unsigned] [zerofill]

• If unsigned, tinyint stores integers between 0 and

255.

• If signed, the range is from -128 to 127.

2.3. MEDIUMINT
• Usage: mediumint(display size) [unsigned] [zerofill]

• If unsigned, mediumint stores integers between -

8,388,608 and 8,388,607.

• If signed, the range is from 0 to 1,677,215.

2.4. BIGINT
• Usage: bigint(display size) [unsigned] [zerofill]

• If unsigned, bigint stores integers between

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

• If signed, the range is from 0 to

18,446,744,073,709,551,615.
2.5. FLOAT
• Usage: FLOAT(precision) [zerofill]

• In this usage, float stores a floating-point number

and cannot be unsigned.

• The precision attribute can be <=24 for a single-

precision floating-point number and between 25
and 53 for a double-precision floating-point
number.
2.5. FLOAT
• Starting in MySQL 3.23, this is a true floating-point
value. In earlier MySQL versions, FLOAT(precision)
always has two decimals.
2.5. FLOAT
• Usage: FLOAT[(M,D)] [ZEROFILL]

• This is a small (single-precision) floating-point

number and cannot be unsigned.

• Allowable values are -3.402823466E+38 to

-1.175494351E-38, zero, and 1.175494351E-38 to

3.402823466E+38.
2.5. FLOAT
• M is the display width and D is the number of
decimals.

• FLOAT without an argument or with an argument

of <= 24 stands for a single-precision floating-point
number.
2.6. DOUBLE/DOUBLE PRECISION/REAL
• Usage: DOUBLE[(M,D)] [zerofill]

• This is a double-precision floating-point number

and cannot be unsigned.

• Allowable values are -1.7976931348623157E+308 to

-2.2250738585072014E-308, zero, and
2.6. DOUBLE/DOUBLE PRECISION/REAL
• 2.2250738585072014E-308 to 1.7976931348623157E+308.

• M is the display width and D is the number of

decimals.

• Usage: DECIMAL[(M[,D])] [ZEROFILL]

• Numbers in a decimal column are stored as

characters.
2.6. DOUBLE/DOUBLE PRECISION/REAL
• Each number is stored as a string, with one
character for each digit of the value.

• If D is 0, values will have no decimal point.

• The maximum range of DECIMAL values is the

same as for DOUBLE, but the actual range for a
given DECIMAL. If M is left out, it’s set to 10.
3. Date and time types
• MySQL has five column types suitable for storing
dates and times.
• date
• datetime
• timestamp
• time
• year
3. Date and time types
• MySQL date and time types are flexible, accepting
either strings or numbers as part of insert
statements.

• Additionally, MySQL is pretty good at interpreting

dates that you give it. For instance, if we create this
table:
3. Date and time types
create table date_test(
id int unsigned auto_increment,
the_date date
);
• The following insert statements are all interpreted
correctly by MySQL:
3. Date and time types
• insert into date_test (a_date) values (‘00-06-01’);

• insert into date_test (a_date) values (‘2000-06-01’);

• insert into date_test (a_date) values (‘20000601’);

• insert into test6 (a_date) values (000601);

3.1. DATE
• Usage: date

• The date column type stores values in the format

YYYY-MM-DD.

• It will allow values between 1000-01-01 and 9999-

12-31.
3.2. DATETIME
• Usage: datetime [null | not null] [default]

• The datetime type stores values in the format:

YYYY-MM-DD HH:MM:SS.

• It will allow values between 1000-01-01 00:00:00

and 9999-12-31 23:59:59.
3.3. TIMESTAMP
• Usage: timestamp(size)

• This is a handy column type that will automatically

record the time of the most recent change to a
row, whether it is an insert or an update.

• Size can be defined as any number between 2

and 14.
3.3. TIMESTAMP

• Table 2-3 shows the

values stored with each
column size. The default
value is 14.
3.4. TIME
• Usage: time

• Stores time in HH:MM:SS format and has a value

range from –838:59:59 to 838:59:59. The reason for
the large values is that the time column type can
be used to store the result of mathematical
equations involving times.
3.5. YEAR
• Usage: year[(2|4)]

• It’s hard to imagine that you’d want to store your

years in two-digit format, but you can.

• In two-digit format, allowable dates are between

1970 and 2069. The digits 70-99 are prepended with

19 and 01–69 are prepended with 20.
3.5. YEAR
• Four-digit year format allows values from 1901 to 2155.

Creating Indexes

• Starting in version 3.23.6 MySQL can create an

index on any column.

• There can be a maximum of 16 columns for any

table.
3.5. YEAR
• The basic syntax is: index index_name
(indexed_column)