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));