Abbey Workshop

MySQL: MySQL Field Types

MySQL Supports the following Field Types in version 3.x of MySQL.

String Types

Name Max Size Space Taken
char(x) 255 bytes x bytes
varchar(x) 255 bytes x+1 bytes
tinytext 255 bytes x+1 bytes
tinyblob 255 bytes x+2 bytes
text 64k x+2 bytes
blob 64k x+2 bytes
mediumtext 1.6mb x+3 bytes
mediumblob 1.6mb x+3 bytes
longtext 4.2gb x+4 bytes
longblob 4.2gb x+4 bytes


Here is an example of a database commands using these field types. ach SQL command ends in a semi-colon. The following commands are issued below:

  1. Create a temp database
  2. Make the temp database the current database
  3. Create a table using some of the variables listed in the table above.

create database temp;

use database temp;

create table testTable
(testName varchar(30),testDate text,
testGiver char(30));

Integer Types

Type Range Space Taken
tinyint -128 to 127 1 byte
smallint -32768 to 32767 2 bytes
medium int -8388608 to 8388607 3 bytes
int -2147483648 to 2147483647 4 bytes
bigint -9223372036854775808 to 9223372036854775807 8 bytes
float(m,d) varies 4 bytes
double(m,d) varies 8 bytes
decimal varies M + 2 bytes

Date Types

Type Format Zero Value
datetime yyyy-nn-dd hh:mm:ss 0000-00-00 00:00:00
date yyyy-mm-dd 0000-00-00
time hh:mm:ss 00:00:00
year yyyy 0000
timestamp varies 0000000000

The various timestamp formats are:

Type Format
timestamp(14) yyyymmddhhmmss
timestamp(12) yymmddhhmmss
timestamp(10) yymmddhhmm
timestamp(8) yyyymmdd
timestamp(6) yymmdd
timestamp(4) yymm
timestamp(2) yy

Other types

In addition to the types mentioned there are two other types worth mentioning. The enum type defines a set of values, of which, only one may be chosen.

fieldname enum('a','b','c')

In addition to enum, the set field type also lets you define a set of values. However, with set, more than one value in the list may be selected.

Column Modifiers

Name Applicable Types
auto_increment All int types
binary char, varchar

all except blob and text

not null all
null all
primary key all
unique all
unsigned Numeric types
zerofill Numeric types

Auto_increment makes the field an automatic counter.

Binary makes the strings stored case sensitive. By default the values stored in the char, varchar types are not case sensitive when searched.

Default allows you specify a default value for a field

Below is a create statement that uses a number of modifiers:

create table testTable
(testID int not null primary key auto_increment,
testName varchar(30),
testDate datetime,
testGiver varchar(30));