MySQL data types: CHAR, VARCHAR, INT, TEXT #Part-1

Swashata1
This entry is part 1 of 5 in the series MySQL DataTypes
wp-content/uploads/2012/06/mysql-datatype-series-logo.png
MySQL DataTypes

MySQL datatypes Previously, we have seen how to create and use database and tables from MySQL terminal. Now while creating a table, we have discussed briefly on datatypes. Datatypes are necessary to define the types of data inside every column. They can be simple Characters, text, integer or others. MySQL comes with plenty of datatypes to work with. Defining them properly will not only stop you from entering wrong data, but also will save space on your disc.

For example, consider a table where you have a field for phone_number. Obviously you can store the phone number in text format [like “1234567890”], but restricting it only to integer type will not only avoid accidental inputs, but also save your server disc space.

So, let us see what types of datatypes we have in MySQL and their default usage… In this part, we shall see three major types of data. We shall talk about date-time in the next post, and other datatypes in the third part of this series.

#1: Character or CHAR:

One of the most common datatype in MySQL. Here are the syntaxes and common usage…

Description:

CHAR or CHARACTER stands for fixed length characters [TEXT]. Suppose you have a column where you are to store strings of “N” character length only. Then you can specify the datatype as CHAR(N), where N is the number of characters [1,2,3,4,5…… and so].

Syntax and Usage:

It is to be set while creating a table. To set a column having CHAR datatype, use the following command…

CREATE TABLE table_name
(
column_name CHAR(N),
column_name CHAR(n)
);

It can also be used in ALTER TABLE command. We shall discuss this later on that particular chapter.

For example, you are going to create a table, named personal_info having columns, do_you_have_pets, are_you_student where each of the columns can have values either Y [for YES] or N [for NO]. So it will be a wise idea to set the datatypes of each columns to CHAR(1). So the command here will be:

CREATE TABLE personal_info
(
do_you_have_pets CHAR(1),
are_you_student CHAR(1)
);

Obviously Y and N being 1 length character we have used CHAR(1)

#2: Variable Character or VARCHAR:

Description:

Holds text data having variable character range. The range is defined inside the datatype. It is best used for holding data like email, name, place, country etc, where the data is short text, and it can be of variable length. We set the datatype as VARCHAR(N) where N is the range of the string length. For example, if we have used
VARCHAR(200) then the range of the string will be from 0 to 200.

Depending on your version of MySQL, the length can be 255 characters before the version 5.0.3 and up to 65,535 characters in MySQL 5.0.3 or later.

Syntax and Usage:

The default usage is while creating a table…

CREATE TABLE table_name
(
column_name VARCHAR(N),
column_name VARCHAR(n)
);

For example, say you want to create a table client_info having columns first_name, last_name, email. Clearly, all the three columns should contain text data. So the ideal way of creating them will be:

CREATE TABLE client_info
(
first_name VARCHAR(100),
last_name VARCHAR(60),
email VARCHAR(60)
);

Clearly, the range of first_name is 0-100, last_name is 0-60 and same for email.

Integers or TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT :

Description:

Greatly used to store numbers. Can store positive or negative numbers… Different datatypes can store different range of number. It is given in the following table:

DataType Range
TINYINT -128 to 127
SMALLINT -32768 to 32767
MEDIUMINT -8388608 to 8388607
INT -2147483648 to 2147483647
BIGINT -9223372036854775808 to 9223372036854775807

Syntax and Usage:

To be used with CREATE TABLE or ALTER TABLE to set the datatype of a specific column.

CREATE TABLE contact_detail
(
name VARCHAR(100),
phone_number BIGINT,
area_code SMALLINT,
pin_code INT
);

Special Suffix:

Add the suffix “UNSIGNED” after any of the datatype to set the range from 0 to the according value. For example,

ALTER TABLE contact_detail MODIFY
phone_number BIGINT UNSIGNED;

Will make the phone_number column range from 0 to 9223372036854775807. It can also be used with CREATE TABLE.

Texts or TEXT, MEDIUMTEXT and LONGTEXT:

Description:

Good on storing large texts. The three datatypes differ by the range of supported character. It is given below…

DataType Range of string length
TEXT 0 to 65535
MEDIUMTEXT 0 to 16777215
LONGTEXT 0 to 4294967295

Syntax and Usage:

Ideally used with columns storing long text messages like emails, feedbacks, posts etc. To be used with CREATE TABLE or ALTER TABLE in the following manner…

CREATE TABLE msg_store
(
name VARCHAR(100),
email_message LONGTEXT,
feedback_message MEDIUMTEXT,
comment TEXT
);

That was all. In the next part of this post, we shall see some other datatypes. Do give your feedback on this article. If you face any trouble, then feel free to a
sk us!

header image credit: http://everythingmysql.ning.com/

Series Navigation

MySQL date and time DataTypes Overview: DATE, TIME, DATETIME, TIMESTAMP, YEAR & Zero Values #Part2.1 »