MySQL data type and data type range Introductory Tutorial

MySQL uses a number of different data types to break down into three broad categories: numbers, dates and times, and string types.

Numeric data type:

MySQL uses all the standard ANSI SQL numeric data types, so if you have a different database system than MySQL, these definitions will look familiar. The following list shows the common numeric data types and their descriptions.

INT-A normal size can be signed or unsigned integers. If signed, the allowable range is-2147483648 to 2147483647. If there are no symbols, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.

TINYINT-A very small integer that can be signed or unsigned. If signed, the allowable range is from-128 to 127. If there are no symbols, the allowable range is from 0 to 255. You can specify the width of a 4-digit number.

SMALLINT-A small integer that can be signed or unsigned. If signed, the allowable range is from-32768 to 32767. If there are no symbols, the allowable range is from 0 to 65535. You can specify a width of 5 digits.

Mediumint-a medium-sized, signed or unsigned integer. If signed, the allowable range is-8388608 to 8388607. If there are no symbols, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.

BIGINT-A large, either signed or unsigned integer. If signed, the allowable range is-9223372036854775808 to 9223372036854775807. If there are no symbols, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 11 digits.

Float (m,d)-a floating-point number that cannot be unsigned. The number of display lengths (M) and decimal (D) that can be defined. This is not required and will default to 10, 2, where 2 is a decimal digit and 10 is the total number of digits (including decimals). Decimal precision can be up to 24-bit holdings.

Double (m,d)-double-precision floating-point number, cannot be unsigned. The number of display lengths (M) and decimal (D) that can be defined. This is not required and will default to 16.4, where 4 are decimal places. The decimal to 53-digit double precision can go. Real is synonymous with double.

DECIMAL (m,d)-a unpack floating-point number that cannot be unsigned. Each decimal in the decompression decimal, corresponding to a byte. The defined display length (M) and the number of digits after the decimal point (D) are required. Numeric is synonymous with decimal.

Date and Time type:

The date and time data types for MySQL are:

Date-day format between Yyyy-mm-dd,1000-1-1 and 9999-12-31. For example, December 30, 1973 will be stored as the 1973-12-30 day.

DateTime-The combination of date and time YYYY-MM-DD HH:MM:SS format, in 1000-1-1 00:00:00 and 9999-12-31 23:59:59. For example, December 30, 1973 15:30:00 will be stored as 1973-12-30 15:30:00.

TIMESTAMP-1970 year, January 1, midnight, a time stamp between 2037. This looks like the previous DateTime format, with no hyphen between the digits, December 30, 1973 3:30 will be stored as 19731230153000 (YYYYMMDDHHMMSS).

Time-When the HH:MM:SS format is stored.

Year (M)-Stores years in 2-bit or 4-bit number format. If the length is specified as 2 (for example, year (2)), the year can be 1970 to 2069 (70 to 69). If the length is specified as 4, the year can be between 1901 and 2155. The default length is 4.

String type:

Although numbers and date types are somewhat similar, most of the data that will be stored will be in the string format. The string data types that are commonly used in MySQL are listed below.

CHAR (M)-a fixed-length string with a length of 1 to 255 characters (for example, char (5)), padding to the right to the specified length and space storage. The length defined is not required, but the default value is 1.

VARCHAR (M)-variable-length strings are between 1 and 255 characters long, such as VARCHAR (25). When creating a varchar field, you must define a length.

BLOB or TEXT-the maximum length of a field is 65,535 characters. A blob is a binary large object that is used to store large amounts of binary data, such as images or other types of files. fields are defined as text that also holds large amounts of data, and the difference between the two is that stored data are sorted and compared to case-sensitive blobs and are case-insensitive in text fields. Does not specify the length of a blob or text.

Tinyblob or Tinytext-a BLOB or text column with a maximum length of 255 characters. Cannot specify Tinyblob or Tinytext length.

Mediumblob or Mediumtext-a BLOB or text column with a maximum length of 16,777,215 characters. You cannot specify the length of a mediumblob or mediumtext.

Longblob or Longtext-a BLOB or text column with a maximum length of 4,294,967,295 characters. You cannot specify the length of a longblob or longtext.

enum-enum, which is a bizarre list of terms. When you define an enumeration, the value that you want to create a list item must be selected (or it can be null). For example, if you want a field to contain "a" or "B" or "C", the enum is defined as an enum (' A ', ' B ', ' C '), and only those values (NULL) can populate the field.

It may not be clearly written, add the following article

1. Integral type (xxxint)

MySQL data type	Meaning
tinyint (m)	1 byte representation ( -128~127)
smallint (m)	2 byte representation ( -32768~32767)
Mediumint (M)	3 byte representation ( -8388608~8388607)
Int (m)	4 byte representation ( -2147483648~2147483647)
BigInt (M)	8-byte representation (18 +-9.22*10 of the second party)

2. Floating-point type (float and double)

MySQL data type	Meaning
Float (m,d)	Single-precision floating-point type, 8-bit precision (4 bytes), M is the total number of decimal digits, D is the number of digits after the decimal point
Double (m,d)	Double-precision floating-point type, 16-bit precision (8 bytes)

Parameter M only affects the display effect, does not affect the precision, D is different, will affect the precision. For example, set a field definition

3. Fixed-point number (decimal)
The decimal (m,d) fixed-point type floating-point type holds an approximate value in the database, and the fixed-point type holds the exact value in the database. The parameter m is the maximum number (precision) of the fixed-point type number, the range is the number of digits to the right of the 0~65,d decimal point, and the range is 0~30, but not more than M. The calculation of fixed-point number can be accurate to 65 digits.

4. String (Char,varchar,xxxtext)

MySQL data type	Meaning
CHAR (n)	A fixed-length string with a maximum of 255 characters
VARCHAR (n)	A fixed-length string with a maximum of 65,535 characters
Tinytext	Variable-length strings, up to 255 characters
Text	Variable-length strings, up to 65,535 characters
Mediumtext	Variable-length strings, up to 2 of 24-1 characters
Longtext	Variable-length strings, up to 2 of 32-1 characters

5. Binary data (XXXBLOB)
Xxxblob and Xxxtext are corresponding, but stored in different ways, Xxxtext is stored as text, if the stored English words are case-sensitive, and Xxxblob is stored in binary mode, case-insensitive. The data stored in the XXXBLOB can only be read as a whole. Xxxtext can specify the character set, Xxxblob not specify the character set.

6. Date Time Type (Date,time,datetime,timestamp)

MySQL data type	Meaning
Date	Date ' 2008-12-2 '
Time	Time ' 12:25:36 '
Datetime	Date Time ' 2008-12-2 22:06:44 '
Timestamp	Not fixed

Timestamp is special, if you define a field with a type of timestamp, the time of the field is automatically refreshed when other fields are modified. So the field of this data type can store the time that the record was last modified, not the actual storage time.

7. Properties of data Types

mysql keyword	Meaning
Null	Data columns can contain null values
Not NULL	Data columns are not allowed to contain null values
DEFAULT xxx	Default value, if no value is specified when inserting a record, the default value is taken
PRIMARY KEY	Primary key
Auto_increment	incremented, if no value is specified when inserting a record, add 1 to the value of the previous record and apply only to the integer type
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set