Question

In this part of the MySQL tutorial, we will cover MySQL data types.

A data type is a set of representable values. Each value belongs to one data type. Items that can be referenced by a name, such as SQL parameters, columns, fields, attributes, and variables, also have declared types.

MySQL supports three groups of data types:

• Numeric
• Date & time
• String

Choosing the right data types for columns is a part of the initial design of the database. Data types ensure the correctness of the data provided. They ensure that the data is used in a meaningful way. This is important when we do comparisons, ordering of data. For example dates are compared differently than numbers. Other developers using our tables will know what data to expect from the database schema. Data types enable MySQL to do validation on the data inserted. Finally, with correct data types for table columns, we allow MySQL to optimise the queries and use less disk space.

Numbers

Numeric types can be either integers or floating point numbers.

1. Integers
   1. TINYINT
   2. SMALLINT
   3. MEDIUMINT
   4. INTEGER
   5. BIGINT
2. Floating points
   1. FLOAT
   2. DOUBLE
   3. DECIMAL

Integers

Integers are a subset of the real numbers. They are written without a fraction or a decimal component. Integers fall within a set Z = {..., -2, -1, 0, 1, 2, ...} Integers are infinite. Computers can practically work only with a subset of integer values, because computers have finite capacity. Integers are used to count discrete entities. We can have 3, 4, 6 cars, but we cannot have 3.33 cars. We can have 3.33 kilograms.

The following is a table of integer types in MySQL: TINYINT , MEDIUMINT and BIGINT are MySQL extensions to the SQL standard.

Data type	Bytes	Minimum value	Maximum value
TINYINT	1	-128	127
SMALLINT	2	-32768	32767
MEDIUMINT	3	-8388608	8388607
INTEGER	4	-2147483648	2147483647
BIGINT	8	-9223372036854775808	9223372036854775807

The integer types differ in their storage. We can choose values that fit our requirements.

mysql> CREATE TABLE Ages(Id SMALLINT, Age TINYINT) ENGINE=Memory;

We have created a temporary Ages table. This will be only a temporary testing table, so there will be only a few rows. SMALLINT will certainly suffice. We do not know anyone older than 130 years, so TINYINT will be OK for the Age column.

mysql> INSERT INTO Ages VALUES(1, 43);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Ages VALUES(2, 128);
Query OK, 1 row affected, 1 warning (0.00 sec)

We insert two rows into the table. There is a warning for the second SQL statement.

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                    |
+---------+------+----------------------------------------------+
| Warning | 1264 | Out of range value for column 'Age' at row 1 |
+---------+------+----------------------------------------------+

We use the SHOW WARNINGS SQL statement to show the last warning message. We have tried to insert a value which is larger than the column data type allows. There is no integer overflow, as we know from the C language. In such a case, the largest allowable integer is written and a warning is issued.

When we are dealing with ages, we do not need negative integer values. MySQL supports unsigned integers. This way we can further optimise our table definitions.

mysql> ALTER TABLE Ages MODIFY Age TINYINT UNSIGNED;

We use the SQL statement to change the Age column to have a TINYINT UNSIGNED data type. Now we can insert values from 0 to 255.

mysql> INSERT INTO Ages VALUES(3, 240);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM Ages;
+------+------+
| Id   | Age  |
+------+------+
|  1 |   43 |
|  2 |  127 |
|  3 |  240 |
+------+------+

We have inserted a hypothetical 240. Now the column accepts it.

Floating point values

Floating point numbers represent real numbers in computing. Real numbers measure continuous quantities. Like weight, height or speed. MySQL has FLOAT , DOUBLE and DECIMAL floating point values.

A FLOAT is a single precision floating point number. MySQL uses four bytes to store a FLOAT value. A DOUBLE is a double precision floating point number. MySQL uses eight bytes to store a DOUBLE value. DECIMAL data type is best used for financial calculations.

Floats, doubles and decimals may have specified their precision and scale. In DECIMAL[M, D] the M is the maximum number of digits, the precision. The D is the number of digits to the right of the decimal point. It is the scale. If you have a column with DECIMAL(3, 1) , you can insert numbers with maximum of three digits. Two before and one after the decimal point.

mysql> SELECT 1/3;
+--------+
| 1/3  |
+--------+
| 0.3333 |
+--------+
1 row in set (0.02 sec)

mysql> SELECT 0.3333 = 1/3;
+--------------+
| 0.3333 = 1/3 |
+--------------+
|      0 |
+--------------+
1 row in set (0.00 sec)

You might expect that the comparison in the second SQL statement returns true, but it does not. The reason is the way, how floating point values are stored.

Caution must be exercised when working with floating point values. Floats and doubles are faster to deal with, but they are not accurate to the last digit. There is a small rounding error, which is OK in many cases. In many real word situations, we just need to have an approximate value. For example, you have a shop in which you have 7.5321 kg of apples, 4.372 kg of oranges. It is perfectly valid to store these two values as 7.5 kg and 4.4 kg. No big deal. On the other hand, when we do exact mathematical calculations; let's say we add some financial data or any scientific calculations, we need more precision. For such cases, we use the DECIMAL data type.

mysql> CREATE TABLE Numbers (Id TINYINT, Floats FLOAT, Decimals DECIMAL(3, 2));

We create a table, in which we are going to store a few floats and decimals.

mysql> INSERT INTO Numbers VALUES (1, 1.1, 1.1), (2, 1.1, 1.1), (3, 1.1, 1.1);

We insert three rows into the newly created table.

mysql> SELECT * FROM Numbers;
+------+--------+----------+
| Id   | Floats | Decimals |
+------+--------+----------+
|  1 |  1.1 |   1.10 |
|  2 |  1.1 |   1.10 |
|  3 |  1.1 |   1.10 |
+------+--------+----------+

This is how the table looks.

mysql> SELECT SUM(Floats), SUM(Decimals) FROM Numbers;
+------------------+---------------+
| SUM(Floats)    | SUM(Decimals) |
+------------------+---------------+
| 3.30000007152557 |      3.30 |
+------------------+---------------+

The two results differ. The decimal calculation is more precise. Due to some internal rounding, the sum of floats is not accurate.

Date & time values

MySQL has data types for storing dates and times. It has DATE , TIME , DATETIME , YEAR and TIMESTAMP .

The DATE is used to store dates. MySQL retrieves and displays date values in YYYY-MM-DD format. The supported range is from 1000-01-01 to 9999-12-31 .

mysql> CREATE TABLE Dates(Id TINYINT, Dates DATE);
mysql> INSERT INTO Dates VALUES(1, '2011-01-24');
mysql> INSERT INTO Dates VALUES(2, '2011/01/25');
mysql> INSERT INTO Dates VALUES(3, '20110126');
mysql> INSERT INTO Dates VALUES(4, '110127');
mysql> INSERT INTO Dates VALUES(5, '2011+01+28');

Dates are displayed in MySQL in one format, but we can use various date formats in our SQL statements. The YYYY-MM-DD is the standard format. But we can use any punctuation character between the date parts.

mysql> SELECT * FROM Dates;
+------+------------+
| Id   | Dates    |
+------+------------+
|  1 | 2011-01-24 |
|  2 | 2011-01-25 |
|  3 | 2011-01-26 |
|  4 | 2011-01-27 |
|  5 | 2011-01-28 |
+------+------------+

We have used multiple formats to insert dates into the table. MySQL uses one format to display the dates.

mysql> INSERT INTO Dates VALUES (6, '10000-01-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                  |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'Dates' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT Id, Dates FROM Dates WHERE Id=6;
+------+------------+
| Id   | Dates    |
+------+------------+
|  6 | 0000-00-00 |
+------+------------+
1 row in set (0.00 sec)

In case we go beyond the range of supported date values a warning is issued. The data is truncated to zero values.

The TIME data type is used to display time in MySQL. It shows values in HH:MM:SS format. The range is from -838:59:59 to 838:59:59 . The hours part of the time format may be greater than 24. It is because TIME data type can be used to denote time intervals. This is also why we can have negative time values.

mysql> SELECT TIMEDIFF('23:34:32', '22:00:00');
+----------------------------------+
| TIMEDIFF('23:34:32', '22:00:00') |
+----------------------------------+
| 01:34:32             |
+----------------------------------+

We use the TIMEDIFF() function to subtract two time values.

mysql> SELECT TIME('2011-01-29 11:27:42');
+-----------------------------+
| TIME('2011-01-29 11:27:42') |
+-----------------------------+
| 11:27:42          |
+-----------------------------+

We can use the TIME() function to extract the time part of the date and time value.

mysql> SELECT TIMEDIFF('211344', 201123);
+----------------------------+
| TIMEDIFF('211344', 201123) |
+----------------------------+
| 01:02:21           |
+----------------------------+

We can write time values in different formats too. The first parameter is a time value in a string format without delimiters. The second is a time value specified as a number.

The DATETIME values contain both date and time. MySQL retrieves and displays values in YYYY-MM-DD HH:MM:SS format. The supported range is from 1000-01-01 00:00:00 to 9999-12-31 23:59:59 .

mysql> SELECT DAYNAME('2011@01@29 11@50@13');
+--------------------------------+
| DAYNAME('2011@01@29 11@50@13') |
+--------------------------------+
| Saturday             |
+--------------------------------+

MySQL displays date and time in only one format. But in our SQL statements, we can use different formats. Any punctuation character may be used as the delimiter between date parts or time parts. In our case, we have used the @ character.

The YEAR is a data type used for representing years. MySQL displays YEAR values in YYYY format. It allows us to assign values to YEAR columns using either strings or numbers. The allowable range is from 1901 to 2155. Illegal year values are converted to 0000.

A timestamp is a sequence of characters, denoting the date and/or time at which a certain event occurred. Timestamps are typically used for logging events. In MySQL we have a TIMESTAMP data type for creating timestamps. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. It automatically sets to the date and time of the most recent operation if you do not give it a value yourself. The TIMESTAMP data type has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.

The following table summarises the supported TIMESTAMP formats.

Data type	Format
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

The TIMESTAMP data type offers automatic initialisation and updating. We can restrict this data type to have only automatic initialisation or automatic update only.

mysql> CREATE TABLE Prices(Id TINYINT, Price Decimal(8, 2),
  -> Stamp TIMESTAMP);
mysql> INSERT INTO Prices (Id, Price) VALUES (1, 234.34);
mysql> INSERT INTO Prices (Id, Price) VALUES (2, 344.12);

We create a table with a TIMESTAMP column. We insert two rows into the table. The Stamp column is not included in the SQL statements. MySQL automatically fills the column.

mysql> SELECT * FROM Prices;
+------+--------+---------------------+
| Id   | Price  | Stamp         |
+------+--------+---------------------+
|  1 | 234.34 | 2011-01-29 13:24:29 |
|  2 | 344.12 | 2011-01-29 13:24:59 |
+------+--------+---------------------+

The timestamps for the two rows were created. This is the auto-initialisation of the TIMESTAMP data type. This can be turned off by Stamp TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP SQL code.

mysql> UPDATE Prices SET Price=250.50 WHERE Id=1;

We execute the SQL statement to update the Price column in the first row.

mysql> SELECT * FROM Prices;
+------+--------+---------------------+
| Id   | Price  | Stamp         |
+------+--------+---------------------+
|  1 | 250.50 | 2011-01-29 13:25:50 |
|  2 | 344.12 | 2011-01-29 13:24:59 |
+------+--------+---------------------+

The timestamp of the first column was updated. If we wanted to turn off the auto-update of the TIMESTAMP , we could use the Stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP SQL code.

Strings

MySQL has the following string data types:

• CHAR
• VARCHAR
• BINARY
• VARBINARY
• BLOB
• TEXT
• ENUM
• SET

A CHAR is a fixed length character data type. It is declared with a length, CHAR(x) , where x can be between 0 to 255. CHAR always uses the same amount of storage space per entry. In case we specify an item which is shorter than the declared length, the value is right-padded with spaces to the specified length. Trailing spaces are removed when the value is retrieved.

mysql> CREATE TABLE Chars(Id TINYINT, Chars CHAR(3));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO Chars VALUES (1, 'a'), (2, 'ab'), 
  -> (3, 'abc'), (4, 'abce');
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                  |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'Chars' at row 4 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

In the above SQL code, we have created a Chars table, which has one column of the CHAR data type. The length is set to three characters. The second SQL statement inserts four rows into the table. Note that there is a warning. With the SHOW WARNINGS statement we find out that the data to be inserted at the fourth row has been truncated. It is because it exceeded the maximum length allowed.

mysql> SELECT * FROM Chars;
+------+-------+
| Id   | Chars |
+------+-------+
|  1 | a   |
|  2 | ab  |
|  3 | abc   |
|  4 | abc   |
+------+-------+

This is what we have in the table.

mysql> SELECT Id, LENGTH(Chars) AS Length FROM Chars;
+------+--------+
| Id   | Length |
+------+--------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    3 |
+------+--------+

We have retrieved Ids and the length of the characters that we have inserted. Above we have stated that chars are stored at fixed size. Why do we have different size values for the rows. We would expect each row to have exactly 3 characters. The reason is that MySQL trims spaces for chars at the data retrieval. By setting the sql_mode to PAD_CHAR_TO_FULL_LENGTH the spaces are also trimmed.

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Id, LENGTH(Chars) AS Length FROM Chars;
+------+--------+
| Id   | Length |
+------+--------+
|  1 |    3 |
|  2 |    3 |
|  3 |    3 |
|  4 |    3 |
+------+--------+
4 rows in set (0.00 sec)

By changing the sql_mode , we get the expected results.

VARCHAR data types stores variable-length strings. The length of the string can be from 0 to 65535. VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved. Most shorter string data types are stored in this data type. For example emails, names of people, of merchandise, addresses etc.

mysql> CREATE TABLE FirstNames(Id TINYINT, FirstName VARCHAR(20));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO FirstNames VALUES (1, 'Tom'), (2, 'Lucy'), (3, 'Alice'),
  -> (4, 'Robert'), (5, 'Timothy'), (6, 'Alexander');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

We create a FirstNames table in which we store six first names.

mysql> SELECT Id, LENGTH(FirstName) AS Length FROM FirstNames;
+------+--------+
| Id   | Length |
+------+--------+
|  1 |    3 |
|  2 |    4 |
|  3 |    5 |
|  4 |    6 |
|  5 |    7 |
|  6 |    9 |
+------+--------+

We can see that names in a VARCHAR column type are stored in variable length. This saves disk space.

BINARY and VARBINARY are binary byte data types. They contain byte strings rather than character strings. They have no character sets. Sorting and comparison are based on the numeric values of the bytes in the values. The range of the BINARY data types is from 0 to 255. It stores values in fixed length. The range of the VARBINARY is from 0 to 65535.

A BLOB is a binary large object data type. It can hold a variable amount of binary data. It can be used to store binary data like images or documents. BLOB has four types:

Blog type	Range in bytes
TINYBLOB	0 - 255
BLOB	0 - 65535
MEDIUMBLOB	0 - 16777215
LONGBLOB	0 - 4294967295

A TEXT datatype is used for storing large textual data. For example articles, blogs, pages or comments.

Blog type	Range in bytes
TINYTEXT	0 - 255
TEXT	0 - 65535
MEDIUMTEXT	0 - 16777215
LONGTEXT	0 - 4294967295

The last data types we are going to mention are ENUM and SET The ENUM is a string object with a value chosen from a permitted list of values. They are enumerated explicitly in the column specification. We can insert only one value from the list.

mysql> CREATE TABLE SizeTable(Size ENUM('S', 'M', 'L', 'XL', 'XXL'));

We create a table, which has one column of the ENUM type. The list of permitted values is explicitly stated.

mysql> INSERT INTO SizeTable VALUES ('S'), ('L');

We insert two rows in the table.

mysql> INSERT INTO SizeTable VALUES ('Large');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'Size' at row 1 |
+---------+------+-------------------------------------------+

Large was not mentioned in the list. In such a case a warning is issued. It says that the data was truncated.

mysql> SELECT * FROM SizeTable;
+------+
| Size |
+------+
| S  |
| L  |
|    |
+------+

We have two regular values in the table. In the third case, empty text was written.

A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values. It is similar to the ENUM data type. The difference is that it can contain zero or more values from the list of permitted values.

This part of the MySQL tutorial was dedicated to MySQL data types.