Mysql Column Type

Similarities between DATETIME & TIMESTAMP

Both store the data in the "YYYY-MM-DD HH:MM: SS" format.

Both include a date as well as a time part.

Automatic initialization can happen for both.

Both change the data while updating the record with current data time as per the constraint.

Both can have fractional seconds part up to 6 digit microsecond precision.

Difference between DATETIME & TIMESTAMP

Supported range for DATETIME is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' while for TIMESTAMP, it is '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.

In MySQL 5.6.4, TIMESTAMP requires 4 bytes to store the data while DATETIME requires 8 bytes.

In MySQL5+, TIMESTAMP value converts from the current time to UTC and vice-versa while DATETIME does not do any conversion.

TIMESTAMP differs with current time zone settings while DATETIME remains constant.

TIMESTAMP data can be indexed but datetime data cannot be indexed.

DATETIME query will not be cached but TIMESTAMP query will be cached.

File Name :

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY

varchar AND text

File Name :

Text :-

store a paragraph or more of text

don't need to index the column

If you have reached the row size limit for your table

you cannot put an index (except for a fulltext index) on a TEXT column.

both data types have a maximum length of 65,535 characters.

varchar :-

store a few words or a sentence

index the (entire) column

use the column with foreign-key constraints

if you want to have an index on the column, you have to use VARCHAR.

ENUM

ENUM data type is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation.

When you create ENUM, you create a list of items from which the value must be selected (or it can be NULL).

each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values.

File Name :

CREATE TABLE trendelook(
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(35),
size ENUM('small', 'medium', 'large','x-large')
);

INSERT INTO trendelook (id, name, size)
VALUES (1,'t-shirt', 'medium'),
(2, 'casual-shirt', 3),
(3, 'formal-shirt', 4),
(4, 'party-shirt', 'small');

File Name :

SELECT * FROM trendelook;

id    name         size
1     t-shirt          medium
2     casual-shirt     large
3     formal-shirt     x-large
4     party-shirt     small

ENUM AND SET

The SET and ENUM data types work in a similar way because MySQL uses it with a predefined list of values.

ENUM data type can hold only a single member of the predefined list of values, whereas SET data type allows us to store zero or any number of values together.

File Name :

SET datatype

A SET is a string object that can store zero or more values. set must be chosen from a predefined list of values specified during table creation.

The SET and ENUM data types work in a similar way because MySQL uses it with a predefined list of values.

SET values are separated by commas (,).

File Name :

CREATE TABLE itechxpert(
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name SET('mahtab','sana','sara','simran')
);

Previous Next

Review & Rating

0.0 / 5

0 Review

5

(0)

4

(0)

3

(0)

2

(0)

1

(0)