Which Of These Data Types Is Able To Store The Greatest Range Of Values?

MySQL Data Types

Final update on April 04 2022 13:11:08 (UTC/GMT +8 hours)

What is data type

i. A data type specifies a particular type of data, such as integer, floating-betoken, Boolean etc.

two. A data type too specifies the possible values for that type, the operations that can be performed on that type and the way the values of that blazon are stored.

MySQL data types

MySQL supports a number of SQL standard information types in various categories. MySQL has Numeric Types, the DATETIME, DATE, and TIMESTAMP Types and String Types. Data types are discussed on this folio are based on MySQL community server 5.6

MySQL Numeric Types

MySQL supports all standard SQL numeric information types which include INTEGER, SMALLINT, DECIMAL, and NUMERIC. It besides supports the approximate numeric information types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keywords December and FIXED are synonyms for DECIMAL. DOUBLE is a synonym for DOUBLE PRECISION (a nonstandard extension). REAL is a synonym for DOUBLE PRECISION (a nonstandard variation) unless the REAL_AS_FLOAT SQL fashion is enabled. The BIT data blazon stores chip-field values and is supported for MyISAM, MEMORY, InnoDB, and NDB tables.

Integer types

SQL standard integer types INTEGER (or INT) and SMALLINT are supported by MySQL. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. Following table shows the required storage and range (maximum and minimum value for signed and unsigned integer) for each integer type.

Blazon	Length in Bytes	Minimum Value (Signed)	Maximum Value (Signed)	Maximum Value (Unsigned)
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607 to	16777215
INT	4	-2147483648	2147483647	4294967295
BIGINT	8	-9223372036854775808	92233720368 54775807	184467440737 09551615

Floating-Point Types

The Float and DOUBLE types represent approximate numeric information values. MySQL uses four bytes for single-precision values and viii bytes for double-precision values.

Types	Description
FLOAT	A precision from 0 to 23 results in a four-byte unmarried-precision Float column
DOUBLE	A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column.

MySQL allows a nonstandard syntax: Float(Thousand,D) or REAL(M,D) or DOUBLE PRECISION(1000,D). Here values can be stored up to Thousand digits in full where D represents the decimal point. For instance, a cavalcade defined as FLOAT(8,v) will look like -999.99999. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the gauge result is 999.0001.

Post-obit table shows the required storage and range (maximum and minimum value for signed and unsigned integer) for each floating-point type.

Type	Length in Bytes	Minimum Value (Signed)	Maximum Value (Signed)	Minimum Value (Unsigned)	Maximum Value (Unsigned)
Bladder	4	-3.402823466E+38	-one.175494351E-38	i.175494351E-38	3.402823466E+38
DOUBLE	8	-1.7976931348623 157E+ 308	-2.22507385850720 14E- 308	0, and 2.22507385850720 14E- 308	ane.797693134862315 7E+ 308

Fixed-Bespeak Types

Fixed-Point data types are used to preserve verbal precision, for example with currency data. In MySQL DECIMAL and NUMERIC types store verbal numeric data values. MySQL five.6 stores DECIMAL values in binary format.

In standard SQL the syntax DECIMAL(5,2) (where 5 is the precision and ii is the scale. ) be able to store any value with five digits and two decimals. Therefore the value range will be from -999.99 to 999.99. The syntax DECIMAL(M) is equivalent to DECIMAL(Yard,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(Thou,0). MySQL supports both of these variant forms of DECIMAL syntax. The default value ofM is ten. If the scale is 0, DECIMAL values contain no decimal signal or fractional part.
The maximum number of digits for DECIMAL is 65, only the actual range for a given DECIMAL cavalcade can be constrained past the precision or scale for a given cavalcade.

Bit Value Types

The BIT data type is used to store bit-field values. A type of BIT(Due north) enables storage of Due north-bit values. Due north can range from 1 to 64.
To specify bit values, b'value' notation tin can exist used. 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 for optionally specifying the display width of integer data types in parentheses following the base keyword for the type

Types	Description
TYPE(N)	Where N is an integer and display width of the type is upto N digits.
ZEROFILL	The default padding of spaces is replaced with zeros. And then, for a cavalcade INT(3) ZEROFILL, 7 is displayed as 007.

MySQL Date and Time Types

The date and time types correspond Appointment, Time, DATETIME, TIMESTAMP, and Twelvemonth. Each type has a range of valid values, as well as a "naught" value.

DATETIME, DATE, and TIMESTAMP Types

Types	Description	Brandish Format	Range
DATETIME	Use when y'all demand values containing both date and time information.	YYYY-MM-DD HH:MM:SS	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
Engagement	Use when you demand merely appointment information.	YYYY-MM-DD	'yard-01-01' to '9999-12-31'.
TIMESTAMP	Values are converted from the current time zone to UTC while storing and converted back from UTC to the current fourth dimension zone when retrieved.	YYYY-MM-DD HH:MM:SS	'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

Time Type

MySQL fetches and displays Fourth dimension values in 'HH:MM:SS' format or 'HHH:MM:SS' format The range of. TIME values from '-838:59:59' to '838:59:59'. The hours part may exist rather large because non only the TIME type tin be used to stand for the fourth dimension of solar day, i.eastward. less than 24 hours, but as well the passed fourth dimension or a time of interval between 2 events.

The TIME values in MySQL can be recognized in many unlike formats, some of which can include a trailing fractional seconds function in up to 6 digits microseconds precision. The range for Time values is '-838:59:59.000000' to '838:59:59.000000'.

MySQL explains abbreviated TIME values with colons every bit the time of the day. Suppose '09:x' means '09:10:00', not '00:09:ten'. MySQL understands the abbreviated values without colons as that, the ii rightmost digits represent seconds. For example, we think of '0910' and 0910 equally meaning '09:10:00', i.due east. 10 minutes after ix o'clock but the reality is MySQL sympathize them as '00:09:ten', i.east. ix minutes and 10 seconds. Then, be careful virtually using abbreviated time in MySQL.

By default, the values of time that lie outside the TIME are converted to the valid range of time values. For case, '-930:00:00' and '930:00:00' are converted to '-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.

Year Blazon

The YEAR blazon is a 1-byte type used to represent year values. It can be declared every bit YEAR(ii) or YEAR(4) to specify a brandish width of 2 or 4 characters. If no width is given the default is iv characters

Yr(iv) and Year(2) have different display format merely accept the same range of values.
For 4-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000.
For 2-digit format, MySQL displays only the last ii (least significant) digits; for example, seventy (1970 or 2070) or 69 (2069).

You tin specify YEAR values in a variety of formats:

String length	Range
4-digit cord	'1901' to '2155'.
4-digit number	1901 to 2155.
i- or 2-digit cord	'0' to '99'. Values in the ranges '0' to '69' and '70' to '99' are converted to Yr values in the ranges 2000 to 2069 and 1970 to 1999.
i- or 2-digit number	ane to 99. Values in the ranges one to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999.

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 like but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

Types	Description	Display Format	Range in characters
CHAR	Contains non-binary strings. Length is fixed as you declare while creating a table. When stored, they are right-padded with spaces to the specified length.	Trailing spaces are removed.	The length can be any value from 0 to 255.
VARCHAR	Contains non-binary strings. Columns are variable-length strings.	As stored.	A value from 0 to 255 earlier MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

BINARY and VARBINARY Types

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings.

Types	Description	Range in bytes
BINARY	Contains binary strings.	0 to 255
VARBINARY	Contains binary strings.	A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in v.0.three and later versions.

Hulk and TEXT Types

A BLOB is a binary large object that can hold a variable amount of information. At that place are four types of Hulk, TINYBLOB, Hulk, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they tin concord.
The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four Hulk types and have the same maximum lengths and storage requirements.

Types	Description	Categories	Range
Hulk	Large binary object that containing a variable amount of information. Values are treated as binary strings.You lot don't demand to specify length while creating a column.	TINYBLOB	Maximum length of 255 characters.
		MEDIUMBLOB	Maximum length of 16777215 characters.
		LONGBLOB	Maximum length of 4294967295 characters
TEXT	Values are treated as grapheme strings having a character set.	TINYBLOB	Maximum length of 255 characters.
		MEDIUMBLOB	Maximum length of 16777215 characters.
		LONGBLOB	Maximum length of 4294967295 characters

ENUM Types

A cord object whose value is chosen from a list of values given at the time of table creation. For example -

          CREATE TABLE length (     length ENUM('pocket-sized', 'medium', 'large') );

Fix Types

A cord object having zero or more comma separated values (maximum 64). Values are called from a list of values given at the time of table creation.

Difference betwixt MySQL Datetime and Timestamp data Types

The DATETIME type is used when you demand values containing both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is thousand-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP information type is likewise used when yous need values containing both appointment and time information. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

The major departure betwixt DATETIME and TIMESTAMP is that TIMESTAMP values are converted from the current time zone to UTC while storing, and converted back from UTC to the current fourth dimension zone when retrieved. The datetime data type value is unchanged.

See the following example :

The following control displays current time zone information :

show variable timezone

Let create a table with two fields udatetime (information blazon -> datetime) utimestamp (data type -> timestamp) and insert 1 tape with now() (returns the current engagement and fourth dimension) equally a value in both fields.

          MySQL> create tabular array tempdate (udatetime datetime, utimestamp timestamp); Query OK, 0 rows affected (0.32 sec)

          MySQL> insert into tempdate values ((now()), (now()));  Query OK, i row afflicted (0.05 sec)

At present see the records;

At this point, the datetime and timestamp information types have same values.

select all from tempdata

In the above output, both the fields have same values. Let change the timezone and see the result.

          MySQL> SET TIME_ZONE ='Europe/Paris'; Query OK, 0 rows affected (0.00 sec)

Now execute the post-obit command :

Sample Output:

MySQL> select * from tempdate; +---------------------+---------------------+ | udatetime           | utimestamp          | +---------------------+---------------------+ | 2013-06-29 16:55:18 | 2013-06-29 thirteen:25:eighteen | +---------------------+---------------------+ one row in set (0.00 sec)

The above output shows that udatetime (data type is DATETIME) is unchanged whereas utimestamp (data blazon is TIMESTAMP) is inverse as the new timezone is set up to 'Europe/Paris'.

Summary : MySQL Data Types

MySQL Data Types slides presentation