In Mysql we have different data types to save different kinds of values in Mysql database. Previously we have discussed different functionalities of Mysql DBMS. In the last post we have discussed that how you can import database in mysql command line interface and how you can create a database in mysql command line interface.
In this post we are going to give you different data types of Mysql. In Mysql database we have many data types see the followings:
Numeric data types – These are used for numbers
String data types – These are used for words and text
Large storage data types – These are for documents and files
Date and time data types – These are for date and time
Bit values or Boolean data types – These are used for logical values and flags
Enumeration – These are for mnemonic values
Now let us discuss these data types in detail.
Numeric Data types in Mysql
There are three kinds of numeric data types in Mysql.
- Integer – These are the whole numbers (numbers that do not have a decimal point).
- Floating Point – These are the real numbers (used for mathematics and science).
- Fixed Point – These are the decimal numbers with fixed precision.
The difference between a floating point and a fixed point is very important to know. The fixed point numbers are used in financial calculations where we need accurate answer to a specific number of decimal points. On the other hand the floating point numbers are used for scientific calculations where we need accurate result not to a specific decimal point. For example adding
0.2 + 0.1 = 0.3 (in decimal point numbers)
0.2 + 0.1 = 0.300000004470348 (in floating point numbers)
So you can see that the decimal point numbers sacrifices the precision for accuracy. On the other hand the floating point numbers give precisions but they are not accurate and they cannot be used in monitory applications.
String Data Types (mysql data types varchar)
There are some string types used in Mysql data types.
- Fixed String data types – These are used for fixed size strings; CHAR is used for this data type.
- Variable String data types – These are of different values and different sizes; VARCHAR is used for this data type.
- Non-textual data types – These are used for non textual binary data; BINARY is used for fixed length type and VARBINARY is the variable length type.
Large Storage Data Types (Non-textual)
These are used for storing images etc. in the database.
- BLOB – It is used for Binary Large Object and for non-text data
- TINYBLOB – it can store up to 256 bytes
- BLOB – It can store up to 64K bytes
- MEDIUMBLOB – It is used to store data up to 16M bytes
- LONGBLOB – It is used to store data up to 4G bytes
Large Storage Data Types (mysql data types text data)
- These are used to store long text data in Mysql database.
- TEXT types – These are for textual data
- TINYTEXT – These can be used for a value up to 256 bytes
- TEXT – These are used to store a value up to 64K bytes
- MEDIUMTEXT – These are used for storing up to 16M bytes
- LOGNTEXT – These are used for storing value up to 4G bytes
Date Time Data Types
- DATE – This data type is used for storing date in Mysql database and it ranges from 1000 – 9999
- TIME – This data type is used for storing time in Mysql database and it ranges from -839:59:59 – +839:59:59
- DATETIME – This data type is used for storing the combination of date/time in Mysql database.
- TIMESTAMP – It is used for event logging.
Mysql Data Types Bit
It is used in Mysql to stored bit or binary number values. To store a binary number (1001)2 we have to declare the field as a bit field by writing like (b,1001) in SQL statement. When you want to display it you can use the bit function BIN(bit_field) etc.
Mysql Data Types Boolean
In Mysql we do not have a Boolean data type. But we use BOOL for storing a Boolean value.
- BOOL – It is an alias for TINYINT in Mysql
- True – 1 (one) is used for a true value
- False – It is represented by 0 (Zero) in Mysql.
- BIT – It is more robust way to use this data type for Mysql Boolean Data Types
Enum Datatype in Mysql Database
Its main advantage is that it avoids the code required to join and show the code description. It uses only the declared set of values as inputs. It also eases setting the values if they arrive in string form.
ENUM takes 1 byte (if under 255 values) or 2 bytes (up to maximum of 65,535)
CREATE TABLE enum_example (students ENUM (‘Kashif’, ‘Shoaib’, ‘David’));
Mysql Set Datatype | Set Datatype in Mysql Example
It can be used instead of enum when we require combination of the declared values as SET in Mysql. We can use single values as well as combination of values as an input to a field.
CREATE TABLE set_example (students SET (‘Kashif’, ‘Shoaib’, ‘David’));