A list of common SQL data types

A list of common SQL data types

Data Type	Description
CHARACTER (n)	Character/string. Fixed length n.
VARCHAR (n) or Character varying (n)	Character/string. Variable Length. The maximum length is n.
BINARY (n)	Binary string. Fixed length n.
BOOLEAN	Store TRUE or FALSE values
VARBINARY (n) or Binary varying (n)	Binary string. Variable Length. The maximum length is n.
INTEGER (p)	INTEGER (no decimal point ). Precision p.
SMALLINT	INTEGER (no decimal point ). Precision 5. The range of data storage is from-2 to-1 (32768), which occupies 2 bytes of storage space.
INTEGER	INTEGER (no decimal point ). Precision: 10. Data is stored in the range of-2 to 31 to 1, occupying 4 bytes of storage space.
BIGINT	INTEGER (no decimal point ). Precision 19.
DECIMAL (p, s)	Exact value, precision p, number of digits after decimal point s. For example, decimal (5, 2) is a number with three digits before the decimal point and two digits after the decimal point.
NUMERIC (p, s)	Exact value, precision p, number of digits after decimal point s. (Same as DECIMAL)
FLOAT (p)	Approximate value. The ending number is accurate to p. A floating point number that uses the 10-based exponential notation. This type of size parameter is composed of a single number with a specified minimum precision.
REAL	Approximate value, with an accuracy of 7.
FLOAT	Approximate value, with an accuracy of 16.
DOUBLE PRECISION	Approximate value, with an accuracy of 16.
DATE	The value of year, month, and day.
TIME	Storage hour, minute, and second values.
TIMESTAMP	Stores the values of year, month, day, hour, minute, and second.
INTERVAL	It is composed of integer fields, representing a period of time, depending on the type of the interval.
ARRAY	An ordered set of fixed lengths of Elements
MULTISET	Unordered set of variable-length elements
XML	Store XML data

The data type that is often used in table creation.

The names of data types are different in different database platforms. in SQL Server and my SQL (or other platforms), there is also a tinyint type (ranging from 0 to 255 ).

Select different data types based on the actual situation.

1. Defining a data length that is too long will result in a waste of storage space.

For example:

For the "name" field: Chinese names are generally not too long and can be defined as char (10 ). Char (10) can store 5 Chinese characters. However, it is necessary to consider whether ethnic minorities or other names are long (for example, foreigners ?) In this case, it must be changed to char (20) or longer.

2. Inaccurate selection types may affect subsequent operations.

For example:

For the "Age" field: theoretically, "Age" can be defined as char and tinyint. It is okay to define the age as char, but in many cases, we need to calculate, sort, and compare the age, tinyint is easier to use. In addition, human life on Earth cannot exceed 150, and the range of smallint and int is too large, resulting in a waste of space.

For the "phone number" field: We recommend that you use the varchar type. If the phone number is divided into a fixed phone number and a mobile phone number, the length is different. Varchar is more flexible than char, with variable length and smaller storage space.