SQL: The Practical Guide — Text data type, Numeric data type and Temporal data type( part 5.2 )

If you want to store the data that might exceed 64 KB limit of VARCHAR, then text data type is good for you

Clearly, you can only store text value in the text column.

There is different types of text data in SQL

TINYTEXT

If you want to store a maximum of 255 characters in the column you can use tinytext data type. Yes, you can also use CHAR data type, I will suggest you to use CHAR type.

TEXT

A column with data type text can store up to 64 KB of data ( sequence of characters of length 65, 535). But this is also the maximum limit of the VARCHAR, So you can use VARCHAR also to store such value. I will suggest you to use the VARCHAR instead

MEDIUMTEXT

You can also use mediumtext if your data is greater than 65, 535 Byte. This data type is capable of storing data of size 16 MB

LONGTEXT

You can store data having a size greater than 16,777,215 Byte. if you want to store the whole article you can use this data type on your column. This data type is capable of storing up to 4 GB of data

When choosing to use one of the text types, you should be aware of the following:

• If the data being loaded into a text column exceeds the maximum size for that type, the data will be truncated if MySQL is not in strict mode
• Trailing spaces will not be removed when data is loaded into the column.
• When using text columns for sorting or grouping, only the first 1,024 bytes are used, although this limit may be increased if necessary.

NUMERIC TYPE

To store the number in the table there is a numeric data type that you can use.

There is a different types of the numeric data type

TINYINT

If you want to store integer you can use this data type. You can use either a signed or unsigned version of this data type.

The signed version will store the data which is either a positive integer or negative integer

In the signed version you can store positive integer not more than 127. if you will try to store the positive integer greater than 127 say 129 MySQL server is going to give you an error

You can store the negative number not less than -128 in the signed version of the tinyint. If you will try to insert the number less than -128 say -134 then MySQL server is going to give you an error

The unsigned version will store the data which is a positive integer.

In the unsigned version, you can store the positive integer not more than 255. if you will do so MySQL server will give you an error

SMALLINT

A signed version of this data type can hold integer not less than -32,768 and not greater than 32, 767

An unsigned version of this data type can hold positive integer not greater than 65,535

MEDIUMINT

A signed version of this data type can not hold integer less than -8,388,608 and greater than 8,388,607

An unsigned version of this data type can hold positive integer not greater than 16,777,215

INT

Signed version of this data type can’t hold integer less than -2,147,483,648

And greater than 2,147,483,647

An unsigned version of this data type can hold positive integer not greater than 4,294,967,295

BIGINT

Signed version of this data type can hold integer not less than -9,223,372,036,854,775,808 and not greater than 9,223,372,036,854,775,807

Unsigned version of this data type can hold positive integer not more than 18,446,744,073,709,551,615

When you create a column using one of the integer types, MySQL will allocate an appropriate amount of space to store the data, which ranges from one byte for a tinyint to eight bytes for a bigint. Therefore, you should try to choose a type that will be large enough to hold the biggest number you can envision being stored in the column without needlessly wasting storage space.

FLOATING POINT

For floating-point numbers (such as 3.1415927), you may choose from the numeric types shown below

When using a floating-point type, you can specify a precision (the total number of allowable digits both to the left and to the right of the decimal point) and a scale (the number of allowable digits to the right of the decimal point), but they are not required. I suggest you not to use the precision and scale value.

If you specify a precision and scale for your floating-point column, remember that the data stored in the column will be rounded if the number of digits exceeds the scale and/or precision of the column.

For example, a column defined as float(4,2) will store a total of four digits, two to the left of the decimal and two to the right of the decimal.

Therefore, such a column would handle the numbers 27.44 and 8.19 just fine, but the number 17.8675 would be rounded to 17.87, and attempting to store the number 178.375 in your float(4,2) column would generate an error.

Like the integer types, floating-point columns can be defined as unsigned, but this designation only prevents negative numbers from being stored in the column rather than altering the range of data that may be stored in the column.

Temporal Data

Along with strings and numbers, you will almost certainly be working with information about dates and/or times. This type of data is referred to as temporal data.

There is different type temporal data as shown below

DATE

If you want to hold the date only in the column you should use the date data type. As you can see in the allowable values column in the above table it’s range is 1000–01–01 to 9999–12–31 .

This means you can insert the date which is between 1000–01–01 to 9999–12–31. To insert the date your date format should be exactly like YYYY-MM-DD So for example in the column with the date data type you can insert the date like ‘2019–04–05’ but you can’t insert date like ’23 Jan 2019’ ( invalid date format for MySQL )

DATETIME

If you also want to store the time value with the date you can use the datetime temporal data type

Its format is YYYY-MM-DD HH:MI: SS For example datetime format like “2019–03–07 10:35:45” is correct format to use in the datetime column

You can only insert the date and time that is between 1000–01–01 00:00:00 to 9999–12–31 23:59:59 ( note that date time is 24-hour format )

TIMESTAMP

A column with this data type also store the date and time with a range

1970–01–01 00:00:00 to 2037–12–31 23:59:59

There is a difference between datetime and timestamp

• The range is different for the timestamp ( smaller range )
• A column with data type timestamp is automatically populated with current date time of the server by database server at the time of insertion of data into the table but in datetime column, you have to manually insert the date and time with correct format.

YEAR

A column with this data type can hold year value of the date like “2019”, “2022” etc.

Minimum year value that you can insert into this column is “1901” and maximum is “2155”

TIME

You can store the time value in this type of column with a minimum value of -838:59:59 and a maximum value of 838:59: 59. For example, if you want to store 34 Hours 32 minute and 44 seconds you can store it in this format

“34:32:44”.

If your thinking what is the use of negative time then consider this, If any event that is going to happen in future then that will be represented by positive time value and if some event happened in past then that event time is represented by a negative value

Please note that all date and time should be in the correct format before inserting into the table. For datetime and timestamp the format is YYYY-MM-DD HH:MI:SS and for the time the format is HHH:MI:SS

The table below lists the range of various component of datetime format