Each data column has its own specific type, which determines how MySQL views the column data. We can store integer values in columns of character type, and MySQL will treat them as strings.
There are three types of columns in MySQL: number, string and date/time.
In general, there are only three types of columns, just like numeric types. But each column type can be subdivided.
Various column types are described in detail below.
Data column type of numeric class
Numeric column types include integer and floating-point types.
Tinyint: 1 bytes is a small positive integer with sign:-128~ 127 and unsigned: 0~255.
Small: 2-byte small integer, signed: -32768~32767, unsigned: 0~65535.
Medium: a 3-byte integer of medium size, signed: -8388608~8388607, unsigned: 0~ 167772 15.
Int: 4-byte standard integer, signed:-2147483648 ~ 2147483647, unsigned: 0~4294967295.
Bigint: 8-byte integer, signed:-922337203685475808 ~ 9237203685475807, unsigned: 0 ~184464073709551615.
Float: 4-byte single-precision floating-point number, minimum non-zero value:+-1.175494351e-38, maximum non-zero value: +-3.4023466e+38.
Double: 8-byte double-precision floating-point number, minimum non-zero value: +-2.225073850720 14e-308, maximum non-zero value:+-1.7961348623157e+308.
Decimal: M+2-byte floating-point number in string form, with variable value range, which is determined by the values of M and D. ..
MYSQL supports a large number of column types, which can be divided into three types: number type, date-time type and string (character) type. This chapter first summarizes the available types and summarizes the storage requirements of each type, and then provides a more detailed description of the type categories in each type. The overview is deliberately simplified. A more detailed description should refer to the additional information of the close-up column type, for example, you can specify the allowable format of the value for it.
The column types supported by MySQL are listed below. The following code letters are used in the description:
M represents the maximum display size. The maximum display size length is 255. D applies to floating-point types. Indicates the number of digits after the decimal point. The maximum possible value is 30, but it should not be greater than M-2.
Square brackets ("["and "]") specify optional type modifiers.
Note that if ZEROFILL is specified for a column, MySQL will automatically add unsigned attributes to the column.
Warning: You should know that when you use subtraction between two integer values, if one of them is unsigned, the result is also unsigned. See section 6.3.5 Cast function.
Tinyint[(m)][ unsigned] [zero padding ]- 128 to 127. The unsigned range is 0 to 255.
They are synonyms for TINYINT( 1).
Small [(m)] [unsigned] [zerofill] is a small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
Mediumint [(m)] [unsigned] [zero fill] is a medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 167772 15.
INT[(M)] [UNSIGNED] [ZEROFILL] An integer of normal size. The signed range is -2 147483648 to 2 147483647. The unsigned range is 0 to 4294967295.
A synonym for integer [(m)] [unsigned] [zero fill] int.
Bigint[(m)][ unsigned] [zerofill] is a big integer. The signed range is-908 to 9008.19989.999999999995 The unsigned range is 0 to1844674073709551615.
There are some things you should know about BIGINT columns:
BIGINT or DOUBLE value, so you should not use unsigned large integers greater than 9223372036854775807(63 bits), except for the bit function! If you do this, some big numbers in the result may be wrong, because there is rounding error when converting BIGINT to DOUBLE. MySQL 4.0 can handle BIGINT in the following situations:
Use integers to store large unsigned values in BIGINT columns.
In MIN(big_int_column) and MAX(big_int_column).
Operators (+,-,*, etc. ) used when both operands are integers.
Usually, you can store exact integers as strings in BIGINT columns. In this case, MySQL will perform a string-to-number conversion, including a double-precision representation with no intermediate value.
When both parameters are integer values, "-","+"and "*" will use BIGINT operation! This means that if the result of the product of two large integers (or the result of the function returning an integer) is greater than 9223372036854775807, unexpected results may be obtained.
Float (precision) [unsigned] [zero fill] floating point number.
Accuracy can be
Float [(m, d)] [unsigned] [zerofill] Small (single precision) floating point number. The allowed values are -3.402823466E+38 to-1.175451e-38,0 and 1. 175438+0e-38 to 3.5466e. If UNSIGNED is specified, negative values are not allowed. M is the display width and d is the number of decimal places. FLOAT has no parameters or X.
Double [(m, d)] [unsigned] [zerofill] floating point number of normal size (double precision). The allowed values are-1.796961348 623157e+308 to-2.2014e-308,0 and 2.3488+04e- if UNSIGNED is specified, negative values are not allowed. M is the display width and d is the number of decimal places. DOUBLE has no Hu parameter or 25 parameters.
Double [(M, D)][ unsigned] [zero padding]
Real numbers [(m, d)][ unsigned] [zero padding] They are synonyms for DOUBLE.
Decimal [(m [,d])] [unsigned] [zerofill] uncompressed floating-point number. Works like a CHAR column: "unpacked" means that the number is stored as a string, and each bit of the value will use one character. Decimal and negative "-"signs are not included in m (but their spaces are reserved). If d is 0, the value will have no decimal point or fractional part. The maximum range of decimal values is consistent with DOUBLE, but for a given decimal column, the actual range can be limited by the selected m and d. If UNSIGNED is specified, negative values are not allowed. If d is omitted, it defaults to 0. If m is omitted, the default value is 10. Before MySQL 3.23, the m parameter must contain spaces required for symbols and decimal points.
DEC[(M[, D])][ unsigned] [zero padding]
Synonym of the number [(m [,d])][ unsigned] [zero padding] decimal.
Dating. The supported range is "1000-01-01"to "nine thousand nine hundred and ninety-nine-12-31". MySQL displays date values in' YYYY-MM-DD' format, but allows you to assign values to date columns as strings or numbers. See Chapter 6. 2. 2. 2 Date Time, Date and Time Stamp Type.
Date time is a combination of date and time. The range of support is "1000-0100: 00" to "nine thousand nine hundred and ninety-nine-12-3123: 59: 59". MySQL displays date and time values in the format "YYYY-MM-DD HH:MM:SS", but allows you to assign values to date and time columns as strings or numbers. See Chapter 6. 2. 2. 2 Date Time, Date and Time Stamp Type.
Time stamp [(M)] Time stamp. The range is "1970-01-0100: 00: 00" to any time in 2037. In MySQL 4.0 and earlier, timestamp values are displayed in the format of YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD, depending on whether m is 14 (or omitted), 12, 8 or 6, but you can specify timestamp columns as strings or numbers. Starting from MySQL 4. 1, the timestamp is returned as characters in the format of "YYYY-MM-DD HH:MM:DD". If you want to return as a number, you must add +0 after the timestamp field. Different timestamp lengths are not supported. Starting from MySQL 4.0. 12, you can use the-new option to make the server work like 4. 1. The TIMESTAMP column is useful for recording the date and time of an insert or update operation, because if you don't assign a value to it yourself, it will be automatically set to the date and time of the last operation. You can also set it to the current date and time by specifying a null value for it. See Chapter 6.2.2 Date and Time Types. Parameter m only affects the display format of timestamp columns; Its value always occupies 4 bytes of storage space. Note that when the m of the TIMESTAMP(M) column is 8 or 14, it returns a number, and other TIMESTAMP(M) columns return a string. This is just to reliably dump and restore tables in other formats. See Chapter 6. 2. 2. 2 Date Time, Date and Time Stamp Type. Time, a time. The range is' -838:59:59' to' 838:59:59'. MySQL displays time values in' HH:MM:SS' format, but allows you to assign values to time columns using strings or numbers. See Chapter 6. 2. 2. 3 Time Types. YEAR[(2 | 4)]2-digit or 4-digit format year (default is 4 digits). The allowed values are 190 1 to 2155,0000 (four-digit year format) and 1970-2069 (70-69), using two-digit format. MySQL displays YEAR values in YYYY format, but allows you to assign values to the year column using strings or numbers. (The year type was not supported before MySQL 3.22. ) See Chapter 6. 2. 2. 4 Type of year.
[NATIONAL] CHAR(M) [BINARY] A fixed-length string. When stored, the right side is always filled with spaces to the specified length. The range of m is 0 to 255 (1 to 255 before MySQL version 3.23). Trailing spaces will be deleted when the value is retrieved. CHAR values are sorted and compared according to the default character set, regardless of case, unless the keyword BINARY is specified. NATIONAL CHAR (or NCHAR for short) is a CHAR column defined by ANSI SQL, which will use the default character set. This is the default setting in MySQL.
CHAR is short for CHARACTER. MySQL allows you to create columns of type CHAR(0). Some old programs need a column when they run, but they don't use the value of this column, so you have to build this column in order to adapt to it. In this case, CHAR(0) will be very beneficial. When a column only needs to store two values: one is CHAR(0) (the column is not defined as NOT NULL), it will only take one bit to store two values: NULL or "". See chapter 6. 2. 3. 1 CHAR and VARCHAR types. CHAR This is a synonym for CHAR( 1).
[NATIONAL] VARCHAR(M) [BINARY] A string with variable length. Note: trailing spaces will be deleted when saving (this is different from ANSI SQL convention). The range of m is 0 to 255 (1 to 255 before MySQL 4.0.2).
VARCHAR values are sorted and compared in a case-insensitive manner, unless the keyword BINARY is specified. See implicit column definition changes in Chapter 6.5.3. 1. VARCHAR is the abbreviation of character change. See chapter 6. 2. 3. 1 CHAR and VARCHAR types.
TINYBLOBTINYTEXT is a BLOB or text column with a maximum length of 255 (2 8-1) characters. See implicit column definition changes in Chapter 6.5.3. 1. See Chapter 6. 2. 3. 2 BLOB and Text Types.
Blotextblob or text column, the maximum length is 65535 (2 16- 1) characters. See implicit column definition changes in Chapter 6.5.3. 1. See Chapter 6. 2. 3. 2 BLOB and Text Types.
MEDIUMBLOBMEDIUMTEXT is a BLOB or text column with a maximum length of1677 7215 (2 24-1) characters. See implicit column definition changes in Chapter 6.5.3. 1. See Chapter 6. 2. 3. 2 BLOB and Text Types.
A BLOB or text column with a maximum length of 4294967295 (2 32-1) characters. See implicit column definition changes in Chapter 6.5.3. 1. Please note that since server/client protocols and MyISAM tables usually have a limit of 16M for each communication packet/table row, you still cannot use this type of full range. See Chapter 6. 2. 3. 2 BLOB and Text Types. Enumeration ('value 1',' value 2', ...) is an enumeration type. A string object with only one value, which is selected from the value columns "value 1", "value2", ..., null or special ""error value. An enumeration column can have up to 65535 different values. See section 6. 2. 3. 3 enumerating types. SET('value 1',' value2', ...). A string object with zero or more values, where each value must be selected from the value columns "value 1" and "value2" ... The collection column can have up to 64 members. See Chapter 6. 2. 3. 4 Set Types.
MySQL supports all ANSI/ISO SQL92 numeric types. These types include data types of exact numbers (numbers, decimals, integers and small integers) and data types of approximate numbers (floating point, real number and double precision). The keyword INT is synonymous with integers and the keyword DEC is synonymous with decimals.
MySQL implements numeric and decimal types as the same type, which is allowed in the SQL92 standard. They are used to store values that are important for accuracy, such as currency-related data. When one of them is used to declare a column, you can (usually) specify the precision and numerical range; For example:
Decimal salary (5,2)
In this example, 5 (precision) represents the significant number of decimal places, and 2 (data number) represents the number of digits after the decimal point. Therefore, in this case, the salary column can store values ranging from -99.99 to 99.99. (In fact, MySQL can store values as high as 999.99 in this column because it does not store positive symbols).
Translator's note:
The Influence of M and D on the Range of Decimal (m, d)
Value range of type description (MySQL= 3.23)
Decimal (4, 1) -9.9 to 99.9 -999.9 to 9999.9.
Decimal (5, 1) -99.9 to 999.9 -9999.9 to 99999.9.
Decimal (6, 1) -999.9 to 9999.9 -99999.9 to 999999.9.
Decimal (6,2)-99.99 to 999.99 -9999.99 to 99999.99
Decimal (6,3)-9.999 to 99.999 -999.999 to 9999.999.
# In MySQL 3.23 and later versions, the value range of DECIMAL(M, d) is equal to that of DECIMAL(M+2, d) in earlier versions. End of comment:
In ANSI/ISO SQL92, the syntax DECIMAL(p) is equivalent to DECIMAL(p, 0). Similarly, where the allowable decision value p is executed, the syntax DECIMAL is equivalent to DECIMAL(p, 0). MySQL currently does not support any variants of decimal/numeric data types. Generally speaking, this is not a serious problem, and the main functional advantages of these types can be obtained by defining the control accuracy and numerical range.
Decimal and numeric values are stored as strings instead of binary floating-point numbers to protect the decimal accuracy of these values. One character is used for each digit of the numeric value, decimal point (if scale >; 0) and "-"(negative). If scale is 0, decimals and values do not contain decimal points or decimal parts.
The maximum range of DECIMAL and NUMERIC values is the same as that of DOUBLE, but for a given DECIMAL or NUMERIC column, its actual range can set the precision or decimal limit of the column. When the number of decimal places assigned to the column exceeds the value specified by scale, the column will be rounded according to scale. When the size of a given decimal or numeric column exceeds the specified (or default) limit range of precisionandscale, MySQL stores the value as the endpoint value of the column range.