MySQL
MySQL Data Types
- Introduction to MySQL Data Types
- Rules for using MySQL Data Types
- Different MySQL Data Types
Introduction to MySQL Data Types
Before creating Tables, you should know about data types. Data types have an elegant role while creating tables. Data types apply to fields. Every column stores a different kind of value in any table. Data types are used to define what kind of value the store will store. Every column is defined when creating the data type table.
Imagine that you are creating a table that will store information about employees. In this table you want to add a column that will store the names of the employees. For this column you will define character data type while creating table. This is similar to creating a variable in a programming language.
In this tutorial, I’m just telling you about data types. You can learn how to use them in the tables. MySQL data types can be divided into 4 major categories.
- Numeric
- Character String
- Binary String
- Temporal
Rules For Defining Data Types
Before discussing these data types with detail, let’s try to know some of the rules that you should keep in mind while decrying data types.
- You should use the same data type that will represent your data properly.
- You should define a data type that can define the data at least in memory space. By doing so you save resources.
- You should use data type that can store the maximum value of your data.
Now let’s try to know more about the data types used in MySQL.
Numeric Data Types
Numeric data types are used to store numeric values. Numeric types have been divided into 4 categories. These are being given below.
Integer
Integer types are used to store whole numbers. Whole numbers are numbers that do not have fractional part (decimal and numbers after that). Integer types have been divided into 5 categories according to size.
| Types | Explanation |
| Tinyint | This is an integer data type to store a very small value. In it, you can store any number from 128 to 128. |
| Smallint | This data type is used to store more value than tinyint. In it you can store a value of -32768 to 32768. |
| Medium Int | This is the data type to store a medium size value. Its range ranges from -8388608 to 8388607. |
| Int | This is the most popular integer type, in which you can store value from -2147483648 to 2147483648. |
| Big Int | This is the largest integer type and you can store any value up to 9223372036854775807. |
Floating Point
Floating point numbers are numbers that include the fractional part (decimal and the number after that). Floating point numbers have been divided into 2 categories according to size.
| Type | Explanation |
| Float | Float single precision (7 digits after decimals) is the floating point type. Its size is 4 bytes. |
| Double | This is double precision floating point type. Its size is 8 bytes. |
Fixed Point Types
Fixed point data types are used to store decimal numbers. Decimal numbers contain a number that is integer, in which the fractional part can also be included. The special thing of this data type is that in it you can store only fractional part. You can also control values as if you want to store 2 values after decimals, you can also do this by using this data type.
Bit Type
Bit data type presents the bit field value. In the Bit data type, you add width, it shows how many bits of the value to store.
Example – bit_column BIT (4)
Character String Data Types
Character string data types represent alphanumeric values (number and character). Character string is a very valuable data type, many programming languages use it. There are 3 types of character string data types in MySQL, let’s try to know them.
| Type | Explanation |
| Char | Char is a fixed length data type. When you define a column of char type, you have to give the maximum length (the number of bytes you want to store). You can not store a value greater than that length. |
| VarChar | VarChar is a variable length data type. You can also store values of more size than the length you are given in it. |
| Text | Text type stores unstructured text. These can store the value of the text variable length. |
Binary String Data Type
Binary values are sequences of bytes. It is stored as a pair of 8 bits. You can also store images, sounds, movies, and executable files in the Binary string data types. Because these are all in binary format. There are 2 types of binary string data types in MySQL.
| Type | Explanation |
| Binary | This data type is the same as char but it stores binary byte strings. |
| VarBinary | This is similar to the data type varchar but it stores the binary byte string. |
Temporal Data Type
In MySQL, date and time types are called temporal types. In such data types, you can store temporal information such as time, year, date etc. Temporal data types are of 5 types.
| Type | Explanation |
| Time type | Time store is done in this data type. Its size is 3 bytes. |
| Year type | This data type is used to store any year. |
| Date type | Date type is used to store single date. |
| DateTime type | This type is used to store both date and time. |
| Timestamp type | It also stores date and time like date Time type, just the format is different. |
If you are in Chandigarh and are looking for MySQL and PHP training in Chandigarh then join CBITSS Technologies. CBitss is the Best Mysql and PHP Training Institute in Chandigarh, join now.
