HostMonster Web Hosting Help

MySQL Variable Types

In a MySQL database, there are three (3) main data types: text, numbers and dates/times.  When you design your database, it is important that you select the appropriate type, since this determines why type of data you can store in that column.  Using the most appropriate type can also increase the database's overall performance.

Text Types

CHAR( ) A fixed section from 0 to 255 characters long.
VARCHAR( ) A variable section from 0 to 255 characters long.
TINYTEXT A string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65535 characters.
BLOB A string with a maximum length of 65535 characters.
MEDIUMTEXT A string with a maximum length of 16777215 characters.
MEDIUMBLOB A string with a maximum length of 16777215 characters.
LONGTEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.

The ( ) brackets allow you to specify the maximum  number of characters that can be used in the column.

BLOB stands for Binary Large OBject, and can be used to store non-text information that is encoded into text.

Number Types

TINYINT ( ) -128 to 127 normal 0 to 255 UNSIGNED
SMALLINT( ) -32768 to 32767 normal 0 to 65535 UNSIGNED
MEDIUMINT( ) -8388608 to 8388607 normal 0 to 16777215 UNSIGNED
INT( ) -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED
BIGINT( ) -9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED
FLOAT A small number with a floating decimal point.
DOUBLE( , ) A large number with a floating decimal point.
DECIMAL( , ) A DOUBLE stored as a string, allowing for a fixed decimal point.

By default, the integer types will allow a range between a negative number and a positive number, as indicated in the table above.  You can use the UNSIGNED commend, which will instead only allow positive numbers, which start at 0 and count up.

Date/Time Types

DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYYMMDDHHMMSS
TIME HH:MM:SS
YEAR YYYY

Date/Time fields will only accept a valid date or time.

Knowledgebase Article 10,614 views bookmark tags: mysql (updated 822 days ago)


Was this resource helpful?

Did this resolve your issue?


Please add any other comments or suggestions about this content:





Recommended Help Content

What are MIME Types? How do I create custom MIME Types? (updated 1986 days ago)
Knowledgebase Article 53,836 views tags: create custom mime types

Guidance on adding tables to your mySQL database in phpMyAdmin (updated 822 days ago)
Knowledgebase Article 13,558 views tags: mysql phpmyadmin

I am prompted to set a new MySQL password. What does this mean and how do I change it? (updated 962 days ago)
Knowledgebase Article 63,978 views tags: connect database mysql password phpmyadmin username

Related Help Content

Do you allow the SUPER privilege on MySQL 5.x.x so I can use triggers ? (updated 1098 days ago)
Knowledgebase Article 50,817 views tags: mysql trigger

Users having problems connecting to mysql database using SSH and/or putty/other client. (updated 1718 days ago)
Knowledgebase Article 59,937 views tags: connection ip mysql putty ssh whitelist

How do I make my MySQL database compatible with the version you provide? (updated 2019 days ago)
Knowledgebase Article 80,975 views tags: database mysql

How do I grant permissions to cPanel for my MySQL database administrator without giving them the domain password? (updated 2017 days ago)
Knowledgebase Article 81,363 views tags: access mysql password permission

How can I connect to our MySQL database from Dreamweaver? (updated 1028 days ago)
Knowledgebase Article 96,444 views tags: connecting database dream mysql weaver

Optimizing MySQL: Queries and Indexes Article 3 of 4 Courtesy of: Ian Gilfillan Ordering by surname is a common requirement, so it would make sense to create an index on surname. But in this example o (updated 1327 days ago)
Knowledgebase Article 74,871 views tags: cpu exceeded index indexes mysql optimize optimizing queries query

This article will show a few MySQL errors along with explanations as to why they might be occurring. (updated 811 days ago)
Knowledgebase Article 13,331 views tags: errors mysql

This article will explain how to connect your scripts to your MySQL databases. (updated 815 days ago)
Knowledgebase Article 25,183 views tags: database mysql scripts