[PHP] Some field types in MySQL database

MySQL has the following field types:

Varchar
Tinyint
Text
Date
Smallint
Mediumint
Int
Bigint
Float
Double
Decimal
Datetime
Timestamp
Time
Year
Char
Tinyblob
Tinytext
Blob
Mediumblob
Mediumtext
Longblob
Longtext
Enum
Set
Bool
Binary
Varbinary

1. Text Type

Char

Usage: Char (length)

The char type can contain a maximum of 255 characters. When the length of the string inserted into the table is smaller than the length, spaces are used to fill the insufficient part on the right of the field.

Varchar

Usage: varchar (length)

Varchar can contain a maximum of 255 characters. Varchar and Char are almost the same. The difference is that varchar is of a variable type and does not fill in spaces.

Tinytext

Usage: tinytext

Tinytext can contain a maximum of 255 characters.

Text

Usage: Text

The maximum text length is 65535 (2 to the power of 17) characters.

Mediumtext

Usage: mediumtext

Mediumtext can contain a maximum of 16777215 characters.

Longtext

Usage: longtext

Longtext can contain a maximum of 4294976259 characters.

(Tinytext, text, mediumtext, and longtext are all edge length field types. They are the same as varchar, but the maximum length is different)

Enum

Usage: Enum ('value1', 'value2', 'value3',...) [Default 'value']

When using Enum, you can limit that the inserted value can only be one of the values in the list in parentheses, and you can also use the optional default 'value' to specify the default value.

Set

Usage: Set ('value1', 'value2', 'value3',...) [Default 'value']

When using set, you can restrict the inserted values to only one or more values in the list of values contained in parentheses, you can also use the optional default 'value' to specify the default value.

 

2. digit type

INT/Integer

Usage: int (display size) [unsigned] [zerofill]

Tinyint

Usage: tinyint (display size) [unsigned] [zerofill]

Mediumint

Usage: mediumint (display size) [unsigned] [zerofill]

Bigint

Usage: bigint (display size) [unsigned] [zerofill]

Float

// Usage: Float () [zerofill]

Usage: float [(m, D)] [zerofill]

It indicates a small floating point number (single precision) and cannot be an unsigned number. M indicates the display width, and D indicates the precision.

Double

Usage: Double [(m, D)] [zerofill]

Indicates a double-precision floating point number. It cannot be an unsigned number. M indicates the display width, and D indicates the precision.

Decimal/numeric

Usage: decimal [(M [, d])] [zerofill]

The numbers in the decimal column are stored as characters, and each number is stored as a string. Each character represents each digit of the value. If D is 0, there is no decimal point. The maximum range of decimal is the same as that of double, but the actual range is specified by the given Parameter M. If M is omitted, M is 0.

3. Date and Time

Date

Usage: Date

The date type stores data in YYYY-MM-DD format. It allows values between 1000-01-01 and 9999-12-31.

Datetime

Usage: datetime [null | not null] [Default]

The datetime type stores data in YYYY-MM-DD hh: mm: SS format.

Timestamp

Usage: Timestamp (Format)

Format can be any number between 2 and 14.

The following is a comparison list of format numbers:

2 YY

4 yymm

6 yymmdd

8 yyyymmdd

10 yymmddhhmm

12 yymmddhhmmss

14 yyyymmddhhmmss

Time

Usage: Time

Time data is stored in HH: mm: SS format.

Year

Usage: year [(2 | 4)]

In the format of two digits, the allowed date is the year between 1970 and 2069. (Add to in front of 79 to 99, and 20 in front of 01 to 69)

The value range from 1901 to 2155 is allowed in the format of 4.