Tuesday, 28 June, 2022

webmastertools4u

Webmaster Tools

single post

  • Home
  • MySQL datatypes
MySQL

MySQL datatypes

Specifying MySQL data field datatypes


Well, choosing proper datatypes greatly influence the performance of a database, that’s why it is important to understand and choose the correct datatype for each database field. And as you remember, a group of fields form a record.

There are lots of different datatypes, and we are going to cover some of the most used:

VARCHAR (M)

The VARCHAR stores data in variable length format from 1 to 255 characters. For example, firstname VARCHAR (25). Here, firstname is the name of the database field.

INT (M) [Unsigned]

The INT stores integers from -2147483648 to 2147483647. “Unsigned” is optional, and if used, the range changes from 0 to 4294967295. For example, distance INT unsigned.

CHAR (M)

The CHAR datatype stores fixed length strings ranging from 1 to 255 characters. The CHAR is much faster than VARCHAR.

FLOAT [(M,D)]

The FLOAT datatype stores decimal numbers. For example, length (5,2). Here, 5 represents up to 5 characters and 2 decimal places.

TEXT or BLOB

These datatypes are used to store strings of 255 – 65535 characters. TEXT data is compared case insensitively, and BLOB is compared case sensitively.

SET or ENUM

These datatypes allow you to specify a set of up to 64 values that can be chosen. For example, fruits SET (“apple”, “pear”, “orange”). This means that fruits field can hold:

“apple”
“apple, pear, orange”
“pear, orange”
and so on…

… , and if fruits ENUM (“apple”, “pear”, “orange”), than:

“apple”
“pear”
“orange”

… only, because with ENUM only one value may be chosen.

DATE

This datatype represents data related information that ranges from 0000-00-00 to 9999-12-31.

There are several options that can be placed after any datatype:

PRIMARY KEY

Use this option if you need that no two records could have the same values. For example, phone INT PRIMARY KEY.

AUTO_INCREMENT

When a new record (row) is inserted into a database, field value with this datatype is automatically incremented by 1. For example, ID INT AUTO_INCREMENT.

NOT NULL

Tells that the field value can never be assigned a NULL value. For example, ID INT NOT NULL AUTO_INCREMENT.

Well, lets go to the next tutorial Manipulating the database.

Recent Comments

No comments to show.

Archives

Categories