Basic knowledge: Mysql Data Type and column type (1)

We need to convert all kinds of information in the real world into something that computers can understand. The converted information forms data. For example, a person's birth date is May 23, 1987, and his height is 170 cm. Data not only consists of numbers, letters, texts, and other special characters in the text format, but also contains graphics, images, animations, images, sounds, and other multimedia data. However, the most commonly used and basic data is text data.

1. Mysql Data Type

MySQL has the following data types:

(1) numeric type

A value is a value such as 32 or 153.4. MySQL supports scientific notation, which is expressed by an integer or floating point followed by "E" or "e", a symbol ("+" or "-"), and an integer index. Both 1.24e + 12 and 23.47e-1 are valid scientific notation. 1.24e12 is not legal because the symbols before the index are not given.

A floating point number consists of an integer, a decimal point, and a decimal point. The integer and decimal parts can be empty, but cannot be empty at the same time.

You can place a negative value "-" before a value to indicate a negative value.

(2) character (string) Type

String type (also called string type) is like "hello, world !" Or a value like "blood case caused by a steamed bread" or a value like phone number 87398413. You can enclose string values either in single or double quotes.

Beginners often cannot tell the difference between a value of 87398143 and a string of 87398143. They are all numbers. How can I use a numeric type or a numeric type? The key lies in that the numeric 87398143 is involved in the calculation, for example, it is a total payment amount in the financial sector, while the numeric 87398143 is not involved in the calculation, only indicates the phone number, street numbers and house number are not involved in calculation.

(3) Date and Time

The date and time are values such as "" or "12:30:43. MySQL also supports combination of dates and times, for example, "12:30:43 ".

(4) Null Value

Null indicates an unknown value. For example, if the mailing address in the form is unclear, leave it blank and leave it blank. This is the null value.

We use the create table statement to create a table (see the previous chapter), which contains the column definitions. For example, we have created a joke table, which has two columns: Content and writer:

The syntax for defining a column is as follows:

The column name is given by col_name. A column name can contain a maximum of 64 characters, including letters, numbers, underscores, and dollar signs. A column name can start with any valid symbol (including numbers. However, the column name cannot be composed of digits, because it may be inseparable from the data. MySQL retains words such as select, delete, and create. These words cannot be used as column names, but function names (such as POS and min) can be used.

Col_type indicates the specific values that can be stored in a column. The column type specifier can also represent the maximum length of values stored in the column. For some types, a value can be used to explicitly describe the length. The length of other values is defined by the type name. For example, char (10) explicitly specifies the length of 10 characters, while the maximum implicit length of tinyblob value is 255 characters. Some type specifiers allow you to specify the maximum display width (that is, the number of characters used for displaying the value ). The floating point type allows you to specify the number of decimal places, so you can control the precision of the floating point.

You can specify optional type description attributes and more common attributes after the column type. Attributes are used to modify the type and modify the method of processing column values. The attributes are of the following types:

(1) Special attributes are used to specify columns. For example, the unsigned attribute is only for integer type, while the Binary Attribute is only for char and varchar type.

(2) general attributes can be used for any column except a few columns. You can specify NULL or not null to indicate whether a column can store null. You can also use default and def_value to indicate that when a new row is created but the value of the column is not explicitly given, the value def_value can be assigned to the column. Def_value must be a constant. It cannot be an expression or reference other columns. You cannot specify the default value for blob or text columns.

If you want to give the special attributes of multiple columns, you can specify them in any order as long as they follow the column type and before the general attributes. Similarly, if you want to give multiple common attributes, you can also give them in any order, as long as they are placed after the column type and the column-specific attributes that may be given.

2. MySQL column (field) Type

Each table in the database is composed of one or more columns (fields. When creating a table using the create table statement, you must specify a type for each column (field. The column (field) type is more refined than the data type. It accurately describes the types of values that a given column (field) may contain, such as whether to take decimal places or whether there are many texts.

MySQL has integer and floating point value column types, as shown in table 1. The integer column type can be signed or unsigned. There is a special attribute that allows the automatic generation of integer column values, which is very useful for application systems that require a unique sequence or ID number.

Type	Description
Tinyint	Very small integer
Smallint	Small integer
Mediumint	Medium integer
Int	Standard integer
Bigint	Large integer
Float	Single-precision floating point number
Double	Double-precision floating point number
Decimal	Floating Point Number of a string

 

Table 1: Numeric column type

The names and value ranges of each numeric type are shown in table 2.

Type description	Value Range
Tinyint [(m)]	Signed value:-128 to 127 (-27 to 27-1) Unsigned value: 0 to 255 (0 to 28-1)
Smallint [(m)]	Signed value:-32768 to 32767 (-215 to 215-1) Unsigned value: 0 to 65535 (0 to 21 6-1)
Mediumint [(m)]	Signed value:-8388608 to 8388607 (-22 3 to 22 3-1) Unsigned value: 0 to 16777215 (0 to 22 4-1)
Int [(m)]	Signed value:-2147683648 to 2147683647 (-231 to 231-1) Unsigned value: 0 to 4294967295 (0 to 232-1)
Bigint [(m)]	Signed value:-9223372036854775808 to 9223373036854775807 (-263 to 263-1) Unsigned value: 0 to 18446744073709551615 (0 to 264-1)
Float [(m, d)]	Minimum non-zero value: ± 1. 175494351e-38
Double [(m, d)]	Minimum non-zero value: ± 2. 225074255072014e-308
Decimal (M, d)	Variable; its value range depends on m and D

 

Table 2: value range of the value column type

Type description	Storage Requirements
Tinyint [(m)]	1 byte
Smallint [(m)]	2 bytes
Mediumint [(m)]	3 bytes
Int [(m)]	4 bytes
Bigint [(m)]	8 bytes
Float [(m, d)]	4 bytes
Double [(m, d)]	8 bytes
Decimal (M, d)	M bytes (MySQL <3.23), m + 2 bytes (mysql> 3.23)

 

Table 3: Data column storage requirements

MySQL provides five types of integers: tinyint, smallint, mediumint, Int, and bigint. Int is the abbreviation of integer. These types are different in the value range that can be expressed. An integer column can be defined as unsigned to disable negative values. This enables the value range of the column to be greater than 0. Different types of storage requirements are also different. The storage required for a type with a large value range is large.

 

MySQL provides three floating point types: float, double, and decimal. Different from integer types, the floating point type cannot be unsigned, and its value range is also different from that of integer types. The difference is that these types have the maximum value and the minimum non-zero value. The minimum value provides a metric of the corresponding type of precision, which is very important for recording scientific data (of course, there are also the maximum and minimum values of negative values ).

When you select a value type, you must consider the range of the value to be represented. You only need to select the minimum type that can cover the range of the value. Selecting a large type will result in a waste of space, making the table unnecessary to increase, and it is not as effective as selecting a small type. For integer values, if the data value range is small, such as the age of a person or the number of siblings, tinyint is the most suitable. Mediumint can represent millions of values and can be used for more types of values, but the storage cost is high. Bigint has the largest value range among all integer types, and the required storage space is twice that of the int type of the integer that represents the maximum range. Therefore, it is only used when it is actually needed. For floating point values, double occupies twice the space of float. Unless high precision or a large range of values are particularly required, the data should be represented by a float model that only uses half of the storage cost.

When defining integer columns, you can specify the optional display size M. In this case, m should be an integer ranging from 1 to 255. It indicates the number of characters used to display the column's median. For example, mediumint (4) specifies a mediumint column with a display width of 4 characters. If an integer column with no specific width is defined, it is automatically assigned a default width. The default value is the length of the "longest" value for each type. If the printable expression of a specific value requires more than m characters, the full value is displayed. The value is not truncated to fit M characters.

For each floating point type, you can specify the maximum display size m and the number of decimal places D. The m value should be 1 to 255. The value of D can be 0 to 3 0, but should not be greater than m-2 (if you are familiar with ODBC terminology, m and D correspond to the "precision" and "number of decimal places" of ODBC concepts "). Both m and D are optional for float and double, but are required for decimal. If M and D are omitted, the default value is used.

2.2 string column type

MySQL provides several string types for storing character data, which are as follows:

Type name	Description
Char	Fixed Length string
Varchar	Variable Length string
Tinyblob	Very small blob (large binary object)
Blob	Small blob
Mediumblob	Moderate blob
Longblob	Large blob
Tinytext	Very small text string
Text	Small text string
Mediumtext	Medium text string
Longtext	Large text string
Enum	Enumeration. A column can be assigned to an enumeration member.
Set	Set; columns can be assigned to multiple set members.

 

Table 4: String column types