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

Examples

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:

Create a temp database
Make the temp database the current database
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
default	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));

Abbey Workshop
Home	Java	LAMP	OSX	Ruby	Unix	XML	XSLT	Misc	All	About


	Web abbeyworkshop.com