About the fields in MySQL

About the fields in MySQL

Type value	Type description
Mysql_type_tiny	tinyint field
Mysql_type_short	smallint field
Mysql_type_long	Integer or int field
Mysql_type_int24	Mediumint Field
Mysql_type_longlong	bigint field
Mysql_type_decimal	Decimal or Numeric field
Mysql_type_newdecimal	Precision Math Decimal or numeric
Mysql_type_float	float field
Mysql_type_double	Double or real field
Mysql_type_bit	Bit field
Mysql_type_timestamp	Timestamp field
Mysql_type_date	Date field
Mysql_type_time	Time field
Mysql_type_datetime	DateTime field
Mysql_type_year	Year Field
Mysql_type_string	Char Field
Mysql_type_var_string	varchar field
Mysql_type_blob	Blob or Text field (use Max_length to determine maximum length)
Mysql_type_set	Set field
Mysql_type_enum	Enum field
Mysql_type_geometry	Spatial field
Mysql_type_null	Null-type Field
Mysql_type_char	No longer attach importance to, replace with Mysql_type_tiny

Column type storage requirements

The storage requirements for each column type supported by MySQL are listed by category.

The maximum size of a row in a MyISAM table is 65,534 bytes. Each blob and text column account accounts for only 5 to 9 bytes.

If the MyISAM table includes variable-length column types, the record format is also variable length. When you create a table, under certain conditions, MySQL can change a column from a variable-length type to a fixed-size type or vice-versa.

Numeric Type storage requirements

Column type	Storage requirements
TINYINT	1 bytes
SMALLINT	2 bytes
Mediumint	3 bytes
INT, INTEGER	4 bytes
BIGINT	8 bytes
FLOAT (P)	If 0 <= p <= 24 is 4 bytes, if <= p <= 53 is 8 bytes
FLOAT	4 bytes
DOUBLE [PRECISION], item REAL	8 bytes
DECIMAL (m,D), NUMERIC (m,D)	variable length; see the discussion below
BIT (M)	Approx. (M+7)/8 bytes

The storage requirements for DECIMAL (and numeric) are related to the specific version:

Uses a binary format to compress 9 decimal (10) numbers to 4 bytes to represent the decimal column value. The integer of each value and the storage of fractional parts are determined separately. A multiple of each 9-digit number requires 4 bytes, and the "remaining" bits require a portion of 4 bytes. The following table shows the storage requirements for exceeding the number of digits:

The remaining	Bytes
Number of digits	Number
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	4

Storage requirements for date and time types

Column type	Storage requirements
DATE	3 bytes
Datetime	8 bytes
TIMESTAMP	4 bytes
Time	3 bytes
Year	1 bytes

Storage requirements for string types

Column type	Storage requirements
CHAR (M)	m bytes, 0 <= m <= 255
VARCHAR (M)	l+ 1 bytes, where l <= m and 0 <= m <= 65535 (see note below)
BINARY (M)	m bytes, 0 <= m <= 255
VARBINARY (M)	l+ 1 bytes, where l <= m and 0 <= m <= 255
Tinyblob, Tinytext	l+ 1 bytes, where l < 28
BLOB, TEXT	l+ 2 bytes, where l < 216
Mediumblob, Mediumtext	l+ 3 bytes, where l < 224
Longblob, Longtext	l+ 4 bytes, where l < 232
ENUM ('value1', 'value2',...)	1 or 2 bytes, depending on the number of enumeration values (up to 65,535 values)
SET ('value1', 'value2',...)	1, 2, 3, 4, or 8 bytes, depending on the number of set members (up to 64 members)

L description is variable length as character type is varchar (200) but actually only 10 characters are used l=10 m=200

The VARCHAR, blob, and text classes are variable-length types. The storage requirements for each type depend on the actual length of the column values (denoted by L in the preceding table), not the maximum possible size of the type. For example, a VARCHAR (10) column can hold a string with a maximum length of 10. The actual storage requirement is the length of the string (L), plus a byte that records the length of the string. For the string ' ABCD ',L is 4 and the storage requires 5 bytes.

For char, varchar, and text types, the values in the preceding table, L and M , should be interpreted as the number of characters, and the length of these types in the column definition represents the number of characters. For example, to save a Tinytext value requires the L character + 1 bytes.

To calculate the number of bytes used to hold a specific char, varchar, or text column value, you need to consider the character set used by that column. In a specific case, when using Unicode, you must remember that all Unicode characters use the same number of bytes. To subdivide the storage used for different classes of Unicode characters.

notes: The valid maximum length of a varchar column is 65,532 characters.

The Ndbcluster engine supports only fixed-width columns. This indicates that a varchar column in a table in a MySQL cluster behaves like a type char (unlike the one that still has an extra byte space per record). For example, in the cluster table, each record in a column declared as varchar (100) is stored with 101 bytes, regardless of the length of the string in the actual stored record.

The Blob and text classes require 1, 2, 3, or 4 bytes to record the length of a column value, depending on the maximum possible length of the class.

In the NDB cluster storage engine, the implementation of the text and BLOB columns is different, where each record in the text column consists of two separate parts. One is a fixed size (256 bytes) and is actually saved in the original table. The other includes any data that exceeds 256 bytes and is stored in the hidden table. Records in the 2nd table are always 2,000 bytes long. This means that if the size of the <= 256,text column is 256  (where size represents the record); otherwise, the size isa zero + size +(2000–present (Size – 256)%2000).

The size of the Enum object is determined by the number of different enumeration values. Enumeration with one byte, you can have 255 possible values. When the value of the enumeration is between 256 and 65,535, two bytes are used.

The size of the set object is determined by the number of different set members. If the set size is n, the object occupies (n+7)/8 bytes, rounded to 1, 2, 3, 4, or 8 bytes. A set can have a maximum of 64 members.

You can use the Is_num () macro to test whether a field has a numeric type. Passing a type value to Is_num () evaluates to True if the field is a numeric type:

if (Is_num (Field->type))

printf ("Field is numeric\n");

There are three different types that are not described

Mysql_type_long_blob

Mysql_type_medium_blob

Mysql_type_varchar

M
Indicates the maximum display size. The largest legal display size is 255.
D
Applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be less than m-2.
Square brackets ("[" and "]") indicate the part of the optional type modifier.
Note that if you specify one for Zerofill,mysql, the unsigned property will be automatically added for that column.
Tinyint[(m)] [unsigned] [Zerofill]
A very small integer. The signed range is 128 to 127, the unsigned range is 0 to 255.

Smallint[(m)] [unsigned] [Zerofill]
A small integer. The signed range is 32768 to 32767, the unsigned range is 0 to 65535.
Mediumint[(m)] [unsigned] [Zerofill]
A medium-sized integer. The signed range is 8388608 to 8388607, the unsigned range is 0 to 16777215.
Int[(m)] [unsigned] [Zerofill]
A normal size integer. The signed range is 2147483648 to 2147483647, 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, the unsigned range is 0 to
18446744073709551615. Note that all arithmetic operations are done with a signed bigint or double value, so you should not use a signed large integer greater than 9223372036854775807 (63-bit), except for the bit function! Note that when two parameters are integer values,-, +, and * will use the bigint operation! This means that if you multiply by 2 large integers (or from a function that returns integers), you can get unexpected results if the result is greater than 9223372036854775807. A floating-point number, which cannot be unsigned, is a single-precision floating point, whose precision can be <=24, to a double-precision floating point, between 25 and 53, these types such as float and double type are described below immediately. Float (x) has the same range as the corresponding float and double, but the display dimensions and scale are undefined. In mysql3.23, this is a real floating-point value. In earlier versions of MySQL, float (precision) always had 2 decimal places. This syntax is provided for ODBC compatibility.
float[(m,d)] [Zerofill]
A small (single-precision) floating-point number. cannot be unsigned. The allowable values are -3.402823466e+38 to -1.175494351e-38,0 and 1.175494351e-38 to 3.402823466e+38. M is the number of digits that display the width and d is a decimal. Float with no parameters or a parameter with <24 represents a single precision floating point number.
double[(m,d)] [Zerofill]
A normal-size (double-precision) floating-point number. cannot be unsigned. The allowed values are -1.7976931348623157e+308 to -2.2250738585072014e-308, 0, and 2.2250738585072014e-308 to 1.7976931348623157e+308. M is the display width and d is the number of decimal digits. A double or float (x) without a parameter (< = x < = 53) represents a two-precision floating-point number.
Double precision[(m,d)] [Zerofill]
 　 
real[(m,d)] [Zerofill]
These are double synonyms.
decimal[(M[,d]) [Zerofill]
A floating-point number that is uncompressed (unpack). cannot be unsigned. Behaves like a char column: "Uncompressed" means that the number is stored as a string, and each bit of the value uses one character. decimal point, and for negative numbers, the "-" symbol is not calculated in M. If D is 0, the value will have no decimal point or fractional part. The maximum range of decimal values is the same as double, but for a given decimal column, the actual range can be limited by the choice of M and D. If D is omitted, it is set to 0. If M is omitted, it is set to 10. Note that in mysql3.22, the M parameter includes a symbol and a decimal point.
numeric (M,D) [Zerofill]
This is a synonym for decimal.
Date
A date. The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '. MySQL Displays the date value in ' YYYY-MM-DD ' format, but allows you to assign a value to the date column using a string or a number.
Datetime
A date and time combination. The scope of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. MySQL displays datetime values in ' yyyy-mm-dd hh:mm:ss ' format, but allows you to assign a value to a datetime column using a string or a number.
Timestamp[(m)]
A time stamp. The range is ' 1970-01-01 00:00:00 ' to sometime in 2037. MySQL Displays the timestamp value in Yyyymmddhhmmss, Yymmddhhmmss, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or omitted), 12, 8, or 6, However, you are allowed to assign a value to the timestamp column using a string or a number. A timestamp column is useful for recording the date and time of an insert or update operation, because if you do not assign it yourself, it is automatically set to the date and time of the most recent operation. You can set it to the current date and time by assigning it a null value.
Time
A time. The range is ' -838:59:59 ' to ' 838:59:59 '. MySQL displays the time value in ' hh:mm:ss ' format, but allows you to assign a value to the time column using a string or a number.
year[(2|4)]
A 2-or 4-digit year (the default is 4-bit). The allowed values are 1901 to 2155, and 0000 (4-bit 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 numeric value to the year column. (The year type is the new type in mysql3.22.) ）
char (m) [binary]
A fixed-length string that, when stored, always fills the right to the specified length with a space. The range of M is 1 ~ 255 characters. When the value is retrieved, the trailing space is deleted. Char values are sorted and compared in a case-insensitive manner based on the default character set, unless binary keywords are given. National char (short form NCHAR) is an ANSI SQL way to define a char column that should use the default character set. This is the default for MySQL. Char is an abbreviation for character.
[National] varchar (m) [binary]
A variable-length string. Note: When the value is stored, the trailing spaces are deleted (this differs from the ANSI SQL specification). The range of M is 1 ~ 255 characters. The varchar values are sorted and compared in a case-insensitive manner based on the default character set, unless the binary keyword value is given. VARCHAR is an abbreviation for 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 that has only one value, selected from the Value list ' value1 ', ' value2 ', ..., or null. An enum can have a maximum of 65535 different values.
Set (' value1 ', ' value2 ',...)
A collection. A string object that can have 0 or more values, each of which must be from the Value list ' value1 ', ' value2 ', ... Elected. A set can have a maximum of 64 members.
This article is from the Learning Network (www.gzu521.com), the original address: http://www.gzu521.com/campus/article/program/200812/180642.htm

Timestamp: Contains year, month, day, time, minute, second, 1 per thousand seconds.

About the fields in MySQL