In the last article, we’ve seen about what is SQL and why do we use it. Also we saw how to create a database, installing it and what are the uses and benefits of using MySQL. In this tutorial, let’s take a look on data types and functions used in MySQL. Without any further delay, let’s jump into the tutorial.
Where do we use the data types
Every record in the database requires a name and a data type. It helps developer to easily understand what type of data the column contains and it’s very useful for analyzing the data.
Data Types in MySQL
Mainly, there are three types of data types. They are, String, Numeric and date and time.
String Data Type
| Data Type | Description |
|---|---|
| CHAR(size) | We will use it to declare a string(contains letters, numbers and symbols) of fixed length. It can exceed up to 255. Default is 1. |
| VARCHAR(size) | Can contain a variable length string (contains letters, numbers and symbols) similar to CHAR(size). The size parameter can vary from 0 to 65535. So, mostly we will use VARCHAR(size) than CHAR(size). |
| BINARY(size) | Similar to CHAR, but it stores binary byte strings. The size is mentioned in bytes. Default is 1. |
| VARBINARY(size) | It is more or less the same of VARCHAR(size), bit it stores the byte strings. |
| TINYBLOB | Binary Large Objects. Maximum length is 255 bytes. |
| TINYTEXT | Holds a string with a maximum length of 255 characters. |
| TEXT(size) | Holds a string with a maximum of 65,535 bytes. |
| BLOB(size) | For Binary Large Objects. Can hold up to 65,535 byte. |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters |
| MEDIUMBLOB | For Binary Large Objects. Can hold up to 16,777,215 bytes of data. |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters. |
| LONGBLOB | For Binary Large Objects. Can hold up to 4,294,967,295 bytes of data. |
| SET(val1, val2, val3, …) | A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list |
Numeric Data Types
| Data Types | Description |
|---|---|
| BIT(size) | A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1. |
| TINYINT(size) | A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255) |
| BOOL | Zero is considered as false, nonzero values are considered as true. |
| SMALLINT(size) | A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255) |
| MEDIUMINT(size) | A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255) |
| INT(size) | A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) |
| BIGINT(size) | A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. |
| FLOAT(size, d) | A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax will be removed in future MySQL versions |
| DOUBLE(size, d) | A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter |
| DECIMAL(size, d) | An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. |
Conclusion
In this article, we’ve seen the data types of MySQL. In the next tutorial, we’ll see about functions used in MySQL. Happy learning.
