Explanation of MySQL table field types | Web Scraping Tool | ScrapeStorm

Correctly defined table fields are very important in the overall optimization of the database. We should only use the type and field size we really need to use; don’t define a field with 10 characters wide if you know you’re only using 2 characters. These types of fields (or columns), also known as data types, store data in these fields.

MySQL uses many different data types, generally grouped into three categories: number, date, time, and string types.

1. Number

MySQL uses all standard ANSI SQL numeric data types, so if you have worked with other different database systems before learning MySQL, these definitions will look familiar. Common numeric data types and their descriptions are listed below:

int – a normal-sized integer, which can be signed. If signed, its allowed range is from -2147483648 to 2147483647. If unsigned, the allowed range is from 0 to 4294967295. A width of up to 11 bits can be specified.

tinyint – a very small integer, which can be signed. If signed, it allows the range from -128 to 127. If unsigned, the allowed range is from 0 to 255, and a width of up to 4 digits can be specified.

SMALLINT – a small integer, which can be signed. If signed, the allowed range is -32768 to 32767. If unsigned, the allowed range is from 0 to 65535, and a width of up to 5 bits can be specified.

MEDIUMINT – a medium-sized integer, which can be signed. If signed, the allowed range is -8388608 to 8388607. If unsigned, the allowed range is from 0 to 16777215, and a width of up to 9 bits can be specified.

BIGINT – a large integer, which can be signed. If signed, the allowed range is -9223372036854775808 to 9223372036854775807. If unsigned, the allowed range is from 0 to 18446744073709551615. A width of up to 20 bits can be specified.

FLOAT(M,D) – Unsigned floating point numbers cannot be used. The display length (M) and the number of decimal places (D) can be defined. This is not required and defaults to 10,2. where 2 is the number of decimal places and 10 is the total number of digits (including decimals). Decimal precision can be up to 24 floating point.

DOUBLE(M,D) – Unsigned double-precision floating-point numbers cannot be used. The display length (M) and the number of decimal places (D) can be defined. This is not required and defaults to 16,4 where 4 is the number of decimal places. The decimal precision can reach DOUBLE of 53 digits. REAL is a synonym for DOUBLE.

DECIMAL(M,D) – Unpacked floating point numbers cannot be unsigned. When unpacking decimals, each decimal corresponds to a byte. It is required to define the display length (M) and the number of decimals (D). NUMERIC is a synonym for DECIMAL.

 

2. Date and Time

DATE – A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30, 1973 would be stored as 1973-12-30.

DATETIME – Date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, December 30, 1973 3:30 PM would be stored as 1973-12-30 15:30:00.

TIMESTAMP – A timestamp between midnight on January 1, 1970, and sometime in 2037. This looks like the previous DATETIME format, without just the hyphens between the numbers; 3:30pm on December 30, 1973 would be stored as 19731230153000(YYYYMMDDHHMMSS).

TIME – Stores time in HH:MM:SS format.

YEAR(M) – Stores the year in 2- or 4-digit format. If the length is specified as 2 (eg YEAR(2)), the year can be from 1970 to 2069 (70 to 69). If length is specified as 4, the year range is 1901-2155, and the default length is 4.

 

3. String

CHAR(M) – A fixed-length string is between 1 and 255 characters in length (eg CHAR(5)), and is stored with right spaces padded to the specified length. The limited length is not required, it will default to 1.

VARCHAR(M) – A variable-length string is the number of characters between 1 and 255 (higher versions of MySQL exceed 255); for example: VARCHAR(25). When creating a VARCHAR type field, the length must be defined.

BLOB or TEXT – The maximum length of the field is 65535 characters. BLOBs are “Binary Large Objects” and are used to store large binary data such as images or other types of files. Text fields defined as TEXT also hold large amounts of data; the difference between the two is that sorting and comparing stored data on BLOBs are case-sensitive, while TEXT fields are case-insensitive. Do not specify the length of BLOB or TEXT.

TINYBLOB or TINYTEXT – BLOB or TEXT columns with a maximum length of 255 characters. Do not specify the length of TINYBLOB or TINYTEXT.

MEDIUMBLOB or MEDIUMTEXT – BLOB or TEXT columns have a maximum length of 16777215 characters. Do not specify the length of MEDIUMBLOB or MEDIUMTEXT.

LONGBLOB or LONGTEXT – BLOB or TEXT columns have a maximum length of 4294967295 characters. Do not specify the length of LONGBLOB or LONGTEXT.

ENUM – Enumeration, which is a fancy list of terms. When defining an ENUM, to create a list of its values, these are the items that must be used for selection (can also be NULL). For example, if you want the field to contain “A” or “B” or “C”, then you can define ENUM as ENUM(“A”, “B”, “C”) and only these values ​​(or NULL) can be used Populate this field.