Introduction to five data types in the MySQL database

The following articles mainly introduce the MySQL data types. The five data types in the MySQL database are character type, text type, numeric type, logical type, and date type, the following is a detailed description of the article, hoping to help you in your future studies.

MySQL Data Type

VARCHAR VS CHAR

The differences between VARCHAR and CHAR data are subtle, but important. They are used to store strings of less than 255 characters.

Assume that you enter the data Bill Gates in a 40-character VARCHAR field. When you extract this data from this field, the length of the data you extract is 10 characters-the length of the string Bill Gates. Now, if you input a string into a CHAR field with a length of 40 characters, the length of the retrieved data will be 40 characters. Extra spaces will be appended to the string.

When you create your own site, you will find it easier to use VARCHAR fields than CHAR fields. When using VARCHAR fields, you do not need to worry about cutting out unnecessary spaces in your data.

Another outstanding advantage of the VARCHAR field is that it can occupy less memory and hard disk space than the CHAR field. When your database is large, this memory and disk space savings will become very important.

MySQL Data Type 2 text type

TEXT

You can use text data to store strings with more than 2 billion characters. When you need to store large strings of characters, you should use text-type data.

Note that text data has no length, but the plain data mentioned in the previous section has a length. Data in a text field is usually either empty or large.

When you collect data from the multi-line text editing box TEXTAREA of HTML form, you should store the collected information in text fields. However, whenever you avoid using a text field, you should not apply it. Text fields are both large and slow. Misuse of text fields slows down the server. Text fields also consume a large amount of disk space.

Once you input any data or even null values to a text field, 2 K space is automatically allocated to the data. You cannot reclaim this bucket unless you delete this record.

MySQL Data Type 3 numeric type

SQL supports many different numeric data types. You can store integer INT, decimal NUMERIC, and MONEY.

INT VS SMALLINT VS TINYINT

Their difference is only the character length:

The number of tables in INT type data ranges from-2,147,483,647 to 2,147,483,647.

SMALLINT data can store integers from-32768 to 32768

TINYINT fields can only store integers from 0 to 255, and cannot store negative numbers.

Generally, the smallest integer data should be used as much as possible to save space. A tinyint data occupies only one byte, and an INT data occupies four bytes. This seems to be a little different, but in a relatively large table, the number of bytes increases rapidly. On the other hand, once you have created a field, it is very difficult to modify it. Therefore, to ensure security, you should predict the maximum value that a field needs to store, and then select the appropriate data type.

MUNERIC

In order to have more control over the data stored in a field, you can use NUMERIC data to simultaneously represent the integer and decimal parts of a number. NUMERIC data enables you to represent a very large number-much larger than INT data. A NUMERIC field can store numbers from-1038 to 1038. NUMERIC data also enables you to represent the number of fractional parts. For example, you can store a decimal number of 3.14 in a NUMERIC field.

When defining a NUMERIC field, you must specify the size of the integer part and the decimal part at the same time. For example: MUNERIC)

The integer part of a NUMERIC data can only have a maximum of 28 digits. the digits in the decimal part must be smaller than or equal to the digits in the integer part, and the decimal part can be zero.

MONEY VS SMALLMONEY

You can use INT or NUMERIC data to store money. However, there are two other data types dedicated for this purpose. If you want your outlets to earn a lot of MONEY, you can use MONEY-type data. If you are not ambitious, you can use SMALLMONEY data. MONEY data can store MONEY from-922,337,203,685,477.5808 to 922,337,203,685,477.5807. If you need to store a larger amount than this, you can use NUMERIC data.

SMALLMONEY data can only store the amount of money from-214,748.3648 to 214,748.3647. Similarly, if possible, you should use the SMALLMONEY type to replace the MONEY type data to save space.

Four logical MySQL Data Types

BIT

If you use the check box CHECKBOX) to collect information from the webpage, you can store this information in the BIT field. A bit field can have only two values: 0 or 1.

Note: after creating a table, you cannot add a BIT field to the table. If you want to include a BIT field in a table, you must complete it when creating the table.

MySQL Data Type-5 date type

DATETIME VS SMALLDATETIME

A datetime field can store the date range from the first millisecond of January 1, January 1, 1753 to the last millisecond of January 1, December 31, 9999.

If you do not need to cover such a large range of dates and times, you can use SMALLDATETIME data. It is used in the same way as DATETIME data, except that it can represent a smaller date and time range than DATETIME data, and is not as precise as DATETIME data. A smalldatetime field can be stored from January 1, January 1-20, 1900 to January 1, June 6. It can only be accurate to seconds.

DATETIME fields do not contain actual data before you enter the date and time. It is important to understand this.