different datatypes in mysql

different datatypes in mysql

String Data Types

The CHAR data type is a fixed-length character data type that can store up to 255 characters.
The actual storage space is fixed at the specified amount. Take a look at an example to demonstrate how this works. The following table definition includes a column definition for the Category column:

CREATE TABLE Catalog
(
ProductID SMALLINT,
Description VARCHAR(40),
Category CHAR(3),
Price DECIMAL(7,2)
);

The Category column is defined with the CHAR(3) data type. As a result, the column can store zero to three characters per value, but the storage amount allotted to that value is always three bytes, one for each character.

The CHAR data type is an appropriate data type to use when you know how many characters most values in a column will consist

VARCHAR 

If you don’t know how many characters each value will be, you should use a VARCHAR data
type. The VARCHAR data type also allows you to specify a maximum length; however, storage requirements are based on the actual number of characters, rather than on the  length value.

CHAR columns are processed more efficiently than VARCHAR columns, 

yet CHAR columns can result in wasted storage.

DECIMAL

DECIMAL(2,1) means  a decimal of 2 characters wide (in total!) and 1 decimal. 

If you want 10.0, you need DECIMAL(3,1) (three wide, one decimal).

Binary data types 

Binary data types support the storage of large amounts of data, such as image and sound files.

These types are useful when you expect values to grow large or to vary widely. The four binary data types are identical except for the maximum amount of data that each one supports. Maximum size of values permitted in a column configured with one of these data types.

Data type                                                                   Maximum size

TINYBLOB/TINYTEXT                                         255 characters (355 bytes)

BLOB/TEXT                                                             65,535 characters (64 KB)

MEDIUMBLOB/MEDIUMTEXT                           16,777,215 characters (16 MB)

LONGBLOB/LONGTEXT                                       4,294,967,295 characters (4 GB)

ENUM data type

The ENUM data type allows you to specify a list of values that you can use in a column configured with that type. When you insert a row in the table, you can also insert one of the values defined for the data type in the column. The column can contain only one value, and it must be one of the listed values.

CREATE TABLE Orders
(
OrderID SMALLINT UNSIGNED,
BikeModel VARCHAR(40),
BikeColor ENUM(‘red’, ‘blue’, ‘green’, ‘yellow’),
BikeOptions SET(‘rack’, ‘light’, ‘helmet’, ‘lock’)
);

Notice that the list of values follows the data type. The values are enclosed in single quotes and separated by commas, and all values are enclosed in parentheses. For an ENUM data type, you can specify up to 65,535 values.

CREATE TABLE BookOrders
(
OrderID SMALLINT UNSIGNED,
BookID SMALLINT UNSIGNED,
Copyright YEAR,
OrderDate TIMESTAMP
);

The Copyright column allows you to add a value to the column that falls in the range of 1901 to 2155;  however, you’re restricted from adding any other types of values. The OrderDate column automatically  records the current data and time when a particular row is inserted or updated, so you don’t have to  insert any values in this column.

create table nvarchar_test
(
_id varchar(10) character set utf8,
_name varchar(200) character set ascii
)

In the above example _id will be able to take 10 unicode characters and _name will be able to take 100 unicode characters (each unicode character will evaluate to two ascii character)

CREATE TABLE t
(
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);

This table definition creates a column named c1 that has a character set of utf8 with the default collation for that character set, and a column named c2 that has a character set of latin1 and a case-sensitive collation.  Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

MySQL allows you to create six different types of tables, which are shown in the following syntax:
ENGINE = {BDB | MEMORY | ISAM | INNODB | MERGE | MYISAM}
To define a table type, you must include an ENGINE clause at the end of your table definition, after
the parentheses that enclose your table elements. For example, the following table definition specifies the  InnoDB table type:

CREATE TABLE AuthorBios
(
AuthID SMALLINT UNSIGNED NOT NULL,
YearBorn YEAR NOT NULL,
CityBorn VARCHAR(40) NOT NULL DEFAULT ‘Unknown’
)
ENGINE=INNODB;

ENUM

Using ENUMs in stored programs
CREATE PROCEDURE sp_enums(in_option ENUM('Yes','No','Maybe'))
BEGIN
DECLARE position INTEGER;
SET position=in_option;
SELECT in_option,position;
END

SET

Behavior of SET variables in stored programs
CREATE PROCEDURE sp_set(in_option SET('Yes','No','Maybe'))
BEGIN
SELECT in_option;
END