Field Type in MySQL

1. Common Types

Other seller types	MySQL type
Bool,	Tinyint (1)
Boolean	Tinyint (1)
Char varying (M)	Varchar (M)
Dec	Decimal
Fixed	Decimal
Float4	Float
Float8	Double
Int1	Tinyint
Int2	Smallint
Int3	Mediumint
Int4	Int
Int8	Bigint
Long varbinary	Mediumblob
Long varchar	Mediumtext
Long	Mediumtext
Middleint	Mediumint
Numeric	Decimal

 

2. Fields of the logical type

0: false 1: True

3,

BLOB fields are used to store binary data.

In MySQL, blob is a type series, including tinyblob, blob, mediumblob, and longblob. The only difference between these types is that the maximum size of the stored files varies.

Four blob types in MySQL
Type size (unit: bytes)
Tinyblob Max. 255
Blob Max 65 K
Mediumblob up to 16 MB
Up to 4 GB longblob

 

Details:

MySQL supports a large number of column types, which can be divided into three types: Number type, date and time type, and string (character) type. This section first provides an overview of available types, summarizes the storage requirements for each column type, and then provides a more detailed description of the type nature in each class.

The overview is intended to be simplified. More detailed descriptions should take into account additional information about specific column types, such as the allowed format for which you can specify values. The column types supported by MySQL are listed below. Use the following code letters to describe: M indicates the maximum display size. The max valid display size is 255. D is applicable to the floating point type and specifies the number of digits following the decimal point. The maximum possible value is 30, but should not be greater than the M-2. Square brackets ("[" and "]") indicate the part of the optional type modifier. Note: If you specify zerofill, MySQL automatically adds the unsigned attribute to this column.

Tinyint [(m)] [unsigned] [zerofill]

A small integer. The signed range is-128 to 127, and the unsigned range is 0 to 255.

Smallint [(m)] [unsigned] [zerofill]

A small integer. The signed range is-32768 to 32767, And the unsigned range is 0 to 65535.

Mediumint [(m)] [unsigned] [zerofill]

An integer of medium size. The signed range is-8388608 to 8388607, And the unsigned range is 0 to 16777215.

Int [(m)] [unsigned] [zerofill]

A normal integer. The signed range is-2147483648 to 2147483647, And the unsigned range is 0 to 4294967295.

Integer [(m)] [unsigned] [zerofill]

This is a synonym for int.

Bigint [(m)] [unsigned] [zerofill]

A large integer. The signed range is-9223372036854775808 to 9223372036854775807, And the unsigned range is 0

18446744073709551615. Note that all arithmetic operations are completed with signed bigint or double values. Therefore, you should not use signed big integers greater than 9223372036854775807 (63 bits), except for bitwise functions! Note that when the two parameters are integer values,-, +, and * use bigint operations! This means that if you take two big integers (or from the function that returns integers) and the result is greater than 9223372036854775807, you can get unexpected results. A floating-point number cannot be unsigned. For a single-precision floating-point number, its precision can be <= 24. For a double-precision floating-point number, it is between 25 and 53, these types, such as float and double, are immediately described below. Float (x) has the same float and double ranges, but the display size and decimal places are undefined. In mysql3.23, this is a real floating point value. In earlier MySQL versions, float (precision) always has two decimal places. This syntax is provided for ODBC compatibility.

Float [(m, D)] [zerofill]

A small (single precision) floating point number. It cannot be unsigned. The allowed values are-3.402823466e + 38 to-1.175494351e-38,0 and 1.175494351e-38 to 3.402823466e + 38. M indicates the display width, and D indicates the decimal digits. Float without parameters or a parameter with <24 represents a single precision floating point number.

Double [(m, D)] [zerofill]

A normal-sized (double-precision) floating point number. It cannot be unsigned. The allowed values are-1.7976931348623157e + 308 to-2.225074255072014e-308, 0, and 2.225074255072014e-308 to 1.7976931348623157e + 308. M indicates the display width, and D indicates the number of decimal places. A double or float (x) (25 <= x <= 53) without a parameter represents a double-precision floating point number.

Double Precision [(m, D)] [zerofill]

Real [(m, D)] [zerofill]

These are double synonyms.

Decimal [(M [, d])] [zerofill]

Unpack floating point number. It cannot be unsigned. The behavior is like a char column: "uncompress" means that the number is stored as a string, and each bit of the value uses one character. Decimal point. For negative numbers, the "-" symbol is not calculated in M. If D is 0, there will be no decimal point or decimal part. The maximum range of a decimal value is the same as that of a double value. However, for a given decimal column, the actual range can be limited through the selection of M and D. If D is omitted, it is set to 0. If M is saved, it is set to 10. Note: In mysql3.22, the M parameter includes the symbol and the decimal point.

Numeric (M, d) [zerofill]

This is a synonym for decimal.

Date

A date. The supported range is '2017-01-01 'to '2017-12-31 '. MySQL displays the date value in 'yyyy-MM-DD 'format, but allows you to assign the value to the date column using strings or numbers.

Datetime

A combination of dates and times. The supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. MySQL displays datetime values in 'yyyy-MM-DD hh: mm: ss' format, but allows you to assign values to datetime columns using strings or numbers.

Timestamp [(m)]

A time stamp. The value range is '2017-01-01 00:00:00 'to a certain time on January 1, 1970. MySQL uses yyyymmddhhmmss, yymmddhhmmss, yyyymmdd, or yymmdd to display the timestamp value, depending on whether M is 14 (or omitted), 12, 8, or 6, however, you can use strings or numbers to assign values to the timestamp column. A timestamp column is useful for recording the date and time of an insert or update operation, because if you do not assign a value to it yourself, it is automatically set to the date and time of the most recent operation. You can assign it a null value to set it to the current date and time.

Time

A time. The value range is '-838: 59: 59' to '2014: 59: 59 '. MySQL displays the time value in 'hh: mm: ss' format, but allows you to assign the value to the Time column using a string or number.

Year [(2 | 4)]

A year in the format of two or four digits (4 digits by default ). The allowed values are 1901 to 2155, and 0000 (4-digit year format), if you use 2 bits, 1970-2069 (70-69 ). MySQL displays the Year Value in yyyy format, but allows you to assign a string or number value to the year column. (Year is a new type in mysql3.22 .)

Char (m) [binary]

A fixed-length string, when stored, is always filled with spaces to the right to the specified length. The range of M is 1 ~ It can contain 255 characters. When the value is retrieved, the tail of the space is deleted. Char values are sorted and compared in a case that is not distinguished by the default Character Set, unless binary keywords are given. National char (short form nchar) is an ansi SQL method to define char columns should use the default character set. This is the default value of MySQL. Char is an abbreviation of character.

[National] varchar (m) [binary]

A variable-length string. Note: When the value is stored, spaces at the end are deleted (different from the ansi SQL Specification ). The range of M is 1 ~ It can contain 255 characters. Varchar values are sorted and compared based on the default character set in uppercase/lowercase, unless binary keyword values are given. Varchar is an abbreviation of character varying.

Tinyblob

Tinytext

A blob or text column with a maximum length of 255 (2 ^ 8-1) characters.

Blob

Text

A blob or text column, with a maximum length of 65535 (2 ^ 16-1) characters.

Mediumblob

Mediumtext

A blob or text column with a maximum length of 16777215 (2 ^ 24-1) characters.

Longblob

Longtext

A blob or text column, with a maximum length of 4294967295 (2 ^ 32-1) characters.

Enum ('value1', 'value2 ',...)

Enumeration. A String object with only one value. This value type is set to 'value1 ', 'value2',..., or null. An Enum can have a maximum of 65535 different values.

Set ('value1', 'value2 ',...)

A collection. A String object with zero or multiple values, each of which must be selected from the Value List 'value1 ', 'value2. A set can have up to 64 members.

The above content is collected and organized by Xiamen Digital Engine. If it is reproduced, please indicate the source of the original text and keep this part of content.