What is data type
1. A data type specifies a particular type of data, such as integer, floating point, Boolean, and so on.
2. A data type
also specifies the possible values for that type, the operations that can be performed on that type, and how values of that type are stored
. MySQL data types
MySQL supports several standard SQL data types in various categories. MySQL has numeric types, the types and strings DATETIME, DATE and TIMESTAMP. The data types discussed on this page are based on MySQL community server 5.6
MySQL
numeric
types MySQL supports all standard SQL numeric data types including INTEGER, SMALLINT, DECIMAL, and NUMERIC. It also supports approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT stands for INTEGER, and the keywords DEC and FIXED are synonymous with DECIMAL. DOUBLE stands for DOUBLE PRECISION (a non-standard extension). REAL stands for DOUBLE PRECISION (a non-standard variation) unless SQL mode is REAL_AS_FLOAT enabled. The BIT data type stores bitfield values and is supported by MyISAM, MEMORY, InnoDB, and NDB tables.
Integer
types
The standard SQL integer (or INT) and SMALLINT integer types are supported in MySQL. As an extension of the standard, MySQL also supports the TINYINT, MEDIUMINT and BIGINT integer types. The following table shows the required storage and interval (maximum and minimum value for signed and unsigned integers) for each integer type.
Floating-point
types
The FLOAT and DOUBLE types represent numeric values of approximate data. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
MySQL allows a non-standard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here the values can be stored up to M digits in total, where D represents the decimal point. For example, a column defined as FLOAT(8,5) will look similar to -999.99999. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.
The following table shows the required storage and range (maximum and minimum value for signed and unsigned integers) for each type of floating point.
Fixed-point data types
Fixed-point
data
types are used to preserve exact precision, for example, with currency data. In MySQL, the DECIMAL and NUMERIC types store exact numeric values. MySQL 5.6 stores DECIMAL values in binary format.
In standard SQL the syntax DECIMAL(5,2) (where 5 is the precision and 2 is the scale.) be able to store any value with five digits and two decimal places. Therefore, the range of values will be from -999.99 to 999.99. The syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the DECIMAL syntax is equivalent to DECIMAL(M,0). MySQL supports both variant forms of DECIMAL syntax. The default value of M is 10. If the scale is 0, the DECIMAL values contain no decimal point or fractional part. The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column may be limited by the precision or scale of a given column.
Bit Value Types
The BIT data type is used to store bit field values. A type of BIT(N) allows the storage of N-bit values. N can range from 1 to 64. To specify bit values, you can use the b’value’ notation. value is a binary value written using zeros and ones. For example, b’111′ and b’10000000′ represent 7 and 128, respectively
Numeric type attributes
MySQL
supports an extension to optionally specify the display width of integer data types in parentheses after the base keyword for the MySQL type
Date and time types
Date and time
types represent DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each type has a range of valid values, as well as a “zero” value.
DATETIME, DATE, and TIMESTAMP types MySQL
time
type gets and displays TIME values in the format ‘HH:MM:SS’
or ‘HHH:MM:SS’ range. TIME values from ‘-838:59:59’ to ‘838:59:59’. The timesheet can be quite large because not only can the TIME type be used to represent the time of day, i.e. less than 24 hours, but also the elapsed time or an interval time between two events.
TIME values in MySQL can be recognized in many different formats, some of which can include a portion of final fractional seconds with microsecond accuracy of up to 6 digits. The range for TIME values is
‘-838:59:59.000000’ to ‘838:59:59.000000’.
MySQL explains TIME abbreviated values with colons as the time of day. Suppose ’09:10′ means ’09:10:00′, not ’00:09:10′. MySQL understands abbreviated values without a colon, as the rightmost two digits represent seconds. For example, we think of ‘0910’ and 0910 as ’09:10:00′, that is, 10 minutes after 9 o’clock, but the reality is that MySQL understands them as ’00:09:10′, that is, 9 minutes and 10 seconds. Therefore, be careful when using abbreviated time in MySQL.
By default, time values outside of TIME are converted to the valid range of time values. For example, ‘-930:00:00’ and ‘930:00:00’ become ‘-838:59:59’ and ‘838:59:59’. Invalid TIME values are converted to ’00:00:00′, because ’00:00:00′ is itself a valid TIME value in MySQL.
Type
of year
The type YEAR is a type of 1 byte that is used to represent the values of the year. It can be declared as YEAR(2) or YEAR(4) to specify a display width of two or four characters. If no width is provided, the default value is four characters
YEAR
(4) and YEAR(2) have a different display format but have the same range of values. For the 4-digit format, MySQL displays YEAR values in YYYY format, ranging from 1901 to 2155, or 0000. For the 2-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69 (2069).
You can specify YEAR values in a variety of formats:
String
types
The string types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET
. CHAR and
VARCHAR types The CHAR and VARCHAR
types are
similar but differ in the way they are stored and retrieved. They also differ in the maximum length and whether the trailing spaces are preserved.
BINARY and VARBINARY
types The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings instead of non-binary strings
.
BLOB and text
types
A BLOB is a large binary object that can contain a variable amount of data. There are four types of BLOB, TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. These differ only in the maximum length of the values they can contain. The four types of TEXT are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four types of blobs and have the same maximum lengths and storage requirements.
Enumeration Types
A string object whose value is chosen from a list of values provided at table creation. For example –
CREATE TABLE length ( length ENUM(‘small’, ‘medium’, ‘large’) );
SET Types
A string object with zero or more comma-separated values (maximum 64). The values are chosen from a list of values provided at the time of table creation.
Difference between MySQL Datetime and Timestamp data types
The DATETIME type is used when you need values that contain date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is 1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59’.
The TIMESTAMP data type is also used when values containing date and time information are needed. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’
UTC
The main difference between DATETIME and TIMESTAMP is that TIMESTAMP values are converted from the current time zone to UTC while being stored, and converted back from UTC to the current time zone when retrieved. The value of the datetime data type does not change.
See
the following example:
The following command displays current time zone information:
<img src="https://www.w3resource.com/w3r_images/mysql-data-types.png" alt="show variable timezone" /
>
Let’s create a table with two fields udatetime (data type -> datetime) utimestamp (data type -> timestamp) and insert a record with now() (returns the current date and time) as a value in both fields.
MySQL> create tempdate table (udatetime datetime, utimestamp timestamp); Query OK, 0 rows affected (0.32 sec) MySQL> insert in tempdate values ((now()), (now())); Query successful, 1 row affected (0.05 sec)
Now view the logs;
At
this point, the datetime and timestamp data types have the
same values.
In the above output, both fields have the same values. Let’s change the time zone and see the result.
MySQL> SET TIME_ZONE =’Europe/Paris’; Query successful, 0 rows affected (0.00 sec)
Now run the following command
:
Sample output:
MySQL> select * from tempdate; +-+-+ | udatetime | utimestamp | +-+-+ | 2013-06-29 16:55:18 | 2013-06-29 13:25:18 | +-+-+ 1 row altogether (0.00 sec)
The above output shows that udatetime (data type is DATETIME) does not change, while utimestamp (data type is TIMESTAMP) is changed as the new time zone is set to ‘Europe/Paris’.
Summary
: MySQL data types
Previous:MySQL language structureNext: MySQL extensions for spatial data
