MySQL Data Type Conversion

MySQL Data Type introduction 0 rating/679 posted on:

 

1. MySQLData Type

Mysql has the following data types:

• 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.

• 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, there are street numbers, street number numbers, and so on. They are not involved in calculation.

• Date and Time Type

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 ".

• 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:

Create table ( Content varchar (20) not null, Writer varchar (10) null )

The syntax for defining a column is as follows:

Col_name col_typy [col_attributes] [general_attributes]

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 to display 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. This attribute is 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. A common attribute 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.MySQLColumn (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.

2.1Value column type

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

The storage required for various types of values is shown in table 3.

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 negative maximum and minimum 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, so 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 hundreds of thousands 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.2String 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

The following table lists the types of string value columns defined by mysql, as well as the maximum size and storage requirements for each type. For variable-length column types, the values of each row occupy different storage volumes, depending on the length of the values actually stored in the column. The length is represented by l in the table.

Type description	Maximum Size	Storage Requirements
Char (m)	MB bytes	MB bytes
Varchar (m)	MB bytes	L + 1 byte
Tinyblob, tinytext	28-1 bytes	L + 1 byte
Blob, text	216-1 bytes	L + 2 bytes
Mediumblob, mediumtext	224-1 bytes	L + 3 bytes
Longblob, longtext	232-1 bytes	L + 4 bytes
Enum ("value1", "value2 ",...)	65535 members	1 or 2 bytes
Set ("value1", "value2 ",...)	64 members	1, 2, 3, 4, or 8 bytes

 

Table 5: maximum size and storage requirements of the string column type

L The additional bytes are the number of bytes required to store the value length. Mysql processes variable-length values by storing the content and length of values. These additional bytes are unsigned integers. Note that the maximum length of a variable length type, the number of additional bytes required for this type, and the relationship between unsigned integers occupying the same number of bytes. For example, the mediumblob value may be a maximum of 224-1 characters in length and three bytes are required to record the result. The maximum unsigned value of the Three-byte integer mediumint is 224-1. This is not accidental.

2.3Date and Time column type

Mysql provides several time value column types: date, datetime, time, timestamp, and year. The following table lists the types that mysql provides to define the storage date and time values, and provides valid value ranges for each type.

Type name	Description
Date	Date value in "yyyy-mm-dd" Format
Time	Time Value in "hh: mm: ss" Format
Datetime	"Yyyy-mm-dd hh: mm: ss" Format
Timestamp	Timestamp value in the format of "yyyymmddhhmmss"
Year	Year Value in "yyyy" Format

 

Table 6: Date and Time column types

Type name	Value Range	Storage Requirements
Date	"1000-01-01" to "9999-12-31"	3 bytes
Time	"-838: 59: 59" to "838: 59: 59"	3 bytes
Datetime	"1000-01-01 00:00:00" to "9999-12-31 23:59:59"	8 bytes
Timestamp	A time from 19700101000000 to 2037	4 bytes
Year	From 1901 to 2155	1 byte

 

Table 7: date column type value range and storage requirements

The following is an example:

Create table student ( Name varchar (20) not null, Chinese tinyint (3 ), Maths tinyint (3 ), English tinyint (3 ), Birthday date )

In this example, create a student table with the name field and character type column. null is not allowed ). There are three integer columns: chinese, maths, and english. There is also a birthday date type column.

MySQL decimalType

Generally, the value of the floating point column is rounded to the specified decimal number. If 1. 2 3 4 5 6 is stored in a FLOAT (8, 1) column, the result is 1. 2. If the same value is stored in the FLOAT (8, 4) column, the result is 1. 2 3 4 6. This indicates that a floating-point column with enough digits should be defined to obtain the value as accurate as possible. To be accurate to 1‰, do not define that the type has only two decimal places.

This processing of floating point values has exceptions in MySQL3.23, and the performance of FLOAT (4) and FLOAT (8) has changed. These two types are now single-precision (4 bytes) and double-precision (8 bytes) types. In terms of the Value storage in the given form (only limited by hardware, these two types are real floating point types.

DECIMAL type is different from FLOAT and DECIMAL type, where DECIMAL is actually stored in strings. The maximum possible value range of DECIMAL is the same as that of DOUBLE, but the valid value range is determined by the values of M and D. If M is changed and D is fixed, the value range increases with M. The first three rows in Table 2-7 illustrate this. If M is fixed and D is changed, the value range decreases with D (but the accuracy increases ). The last three rows in Table 2-7 illustrate this.

The value range of the given DECIMAL type depends on the MySQL version. For versions earlier than MySQL3.23, each value in the DECIMAL (M, D) column occupies M bytes, and the symbols (if needed) and DECIMAL points are included in M bytes. Therefore, for columns of the DECIMAL type (5, 2), the value range is-9.99 to 9 9. 9 9, because they overwrite all possible 5 character values.

Like MySQL3.23, DECIMAL values are processed in accordance with ANSI standards. ANSI standards stipulate that DECIMAL (M, D) must be able to represent any value of M-bit numbers and D-DECIMAL places. For example, DECIMAL (5, 2) must be able to represent all values from-999.99 to 999.99. The symbols and DECIMAL points must be stored. Therefore, DECIMAL values have occupied M + 2 bytes since MySQL3.23. For DECIMAL (5, 2), the "longest" value (-9 9 9. 9 9) requires 7 bytes. At one end of the positive value range, a positive number is not required. Therefore, MySQL uses it to expand the value range so that it exceeds the value range specified by ANSI. For example, the maximum value of DECIMAL (5, 2) is 9 9 9. 9 9, because there are 7 bytes available.

In short, in MySQL 3.23 and later versions, the value range of DECIMAL (M + 2, D) is equal to that of DECIMAL (M + 2, D) in earlier versions. In all MySQL versions, if the D of a DECIMAL column is 0, the DECIMAL point is not stored. The result is that the value range of the column is expanded, because the bytes used to store the decimal point can now be used to store other numbers.