What is MYSQL data type with Example by ocec
What is MySQL Data Type ?
MySQL data types define the type of data that can be stored in a database column. They include INTEGER for whole numbers, VARCHAR for variable-length strings, DATE for dates, BOOLEAN for true/false values, and more. Each data type has specific characteristics and storage requirements, optimizing data management and retrieval.
String Data Types :
| Data Type | Description | Size Parameter | Default Size |
|---|---|---|---|
| CHAR(size) | A fixed-length string that can contain letters, numbers, and special characters. | Specifies the column length in characters (0 to 255) | 1 |
| VARCHAR(size) | A variable-length string that can contain letters, numbers, and special characters. | Specifies the maximum column length in characters (0 to 65535) | 1 |
| BINARY(size) | Similar to CHAR(), but stores binary byte strings. | Specifies the column length in bytes | 1 |
| VARBINARY(size) | Similar to VARCHAR(), but stores binary byte strings. | Specifies the maximum column length in bytes | - |
| TINYBLOB | For small Binary Large Objects (BLOBs) with a maximum length of 255 bytes. | - | - |
| TINYTEXT | Holds a small string with a maximum length of 255 characters. | - | - |
| TEXT(size) | Holds a string with a maximum length of 65,535 bytes. | - | - |
| BLOB(size) | For Binary Large Objects (BLOBs) with a maximum length of 65,535 bytes. | - | - |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters. | - | - |
| MEDIUMBLOB | For Binary Large Objects (BLOBs) with a maximum length of 16,777,215 bytes. | - | - |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters. | - | - |
| LONGBLOB | For Binary Large Objects (BLOBs) with a maximum length of 4,294,967,295 bytes. | - | - |
| ENUM(val1, val2, ...) | A string object that can have only one value, chosen from a list of possible values. Up to 65,535 values can be listed. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them. | - | Varies based on values |
| SET(val1, val2, ...) | A string object that can have zero or more values, chosen from a list of possible values. Up to 64 values can be listed. | - | Varies based on values |
Nemeric Data Type :
| Data Type | Description | Size Parameter | Default Size |
|---|---|---|---|
| BIT(size) | A bit-value type. The number of bits per value is specified in size. | Specifies the number of bits per value (1 to 64) | 1 |
| TINYINT(size) | A very small integer. Signed range: -128 to 127. Unsigned range: 0 to 255. | Specifies the maximum display width (up to 255) | - |
| BOOL / BOOLEAN | Zero is considered as false, nonzero values are considered as true. | - | - |
| SMALLINT(size) | A small integer. Signed range: -32768 to 32767. Unsigned range: 0 to 65535. | Specifies the maximum display width (up to 255) | - |
| MEDIUMINT(size) | A medium integer. Signed range: -8388608 to 8388607. Unsigned range: 0 to 16777215. | Specifies the maximum display width (up to 255) | - |
| INT(size) | A medium integer. Signed range: -2147483648 to 2147483647. Unsigned range: 0 to 4294967295. | Specifies the maximum display width (up to 255) | - |
| INTEGER(size) | Equivalent to INT(size). | - | - |
| BIGINT(size) | A large integer. Signed range: -9223372036854775808 to 9223372036854775807. Unsigned range: 0 to 18446744073709551615. | Specifies the maximum display width (up to 255) | - |
| FLOAT(size, d) | A floating point number. Deprecated in MySQL 8.0.17. | Specifies the total number of digits and digits after the decimal point | - |
| FLOAT(p) | A floating point number. | MySQL uses the p value to determine whether to use FLOAT or DOUBLE | - |
| DOUBLE(size, d) | A normal-size floating point number. | Specifies the total number of digits and digits after the decimal point | - |
| DOUBLE PRECISION(size, d) | A synonym for DOUBLE(size, d). | - | - |
| DECIMAL(size, d) | An exact fixed-point number. | Specifies the total number of digits and digits after the decimal point | 10, 0 |
| DEC(size, d) | Equivalent to DECIMAL(size, d). | - | - |
Date and Time Data Type :
| Data Type | Description |
|---|---|
| DATE | A date. Format: YYYY-MM-DD. Supported range: '1000-01-01' to '9999-12-31'. |
| DATETIME(fsp) | A date and time combination. Format: YYYY-MM-DD hh:mm:ss. Supported range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition enables automatic initialization and updating to the current date and time. |
| TIMESTAMP(fsp) | A timestamp. Stored as seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. Supported range: '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition. |
| TIME(fsp) | A time. Format: hh:mm:ss. Supported range: '-838:59:59' to '838:59:59'. |
| YEAR | A year in four-digit format. Values allowed: 1901 to 2155, and 0000. |
MySQL 8.0 does not support the YEAR data type in two-digit format.
Share this article
Sandipan Kr Bag
I'm a dedicated full-stack developer, entrepreneur, and the proud owner of ocec.org.in , hailing from the vibrant country of India. My passion lies in creating informative tutorials and sharing valuable tips that empower fellow artisans in their journey. With a deep-rooted love for technology, I've been an ardent enthusiast of PHP, Laravel, Angular, Vue, Node, JavaScript, jQuery, Codeigniter, and Bootstrap from their earliest days. My philosophy revolves around the values of hard work and unwavering consistency, driving me to continuously explore, create, and share my knowledge with the tech community.
*Hire Me
Related Posts
Step-by-Step Guide a Dynamic Image Slider with HTML, CSS, and JavaScript
0 Comments
Load more Comments
Post a Comment
About me
i am sandipan kumar bag
Latest Posts
Implementing Password Reset via Email in Laravel with SweetAlert2 Integration
5 months ago
Full List of Common $_SERVER Variables | PHP Course
6 months ago
What is AJAX & guide to the AJAX methods in jQuery BY OCEC
8 months ago
helllo