MySQL Field Data Type

Source: Internet
Author: User
MySQL has three types of columns: Numbers, strings, and dates. The numeric column type is used to store various numeric data, such as price, age, or quantity. There are two types of numeric columns: integer and floating point. All numeric column types can have two options: UNSIGNED and ZEROFILL. UNSIGNED Columns cannot have negative numbers. ZE is selected.

MySQL has three types of columns: Numbers, strings, and dates. The numeric column type is used to store various numeric data, such as price, age, or quantity. There are two types of numeric columns: integer and floating point. All numeric column types can have two options: UNSIGNED and ZEROFILL. UNSIGNED Columns cannot have negative numbers. ZE is selected.

MySQL has three types of columns: Numbers, strings, and dates. Numeric type

The number column type is used to store various numeric data, such as price, age, or quantity. There are two types of numeric columns: integer and floating point. All numeric column types can have two options: UNSIGNED and ZEROFILL. UNSIGNED Columns cannot have negative numbers. If ZEROFILL is selected, zero is added to the values. The following figure shows the available numeric column types in MySQL.

  • TINYINT -- a tiny integer that supports-128 to 127 (SIGNED), 0 to 255 (UNSIGNED), and needs to be stored in 1 byte
  • BIT -- same as TINYINT (1)
  • BOOL -- same as TINYINT (1)
  • SMALLINT -- a small integer, supporting-32768 to 32767 (SIGNED), 0 to 65535 (UNSIGNED), requiring 2 bytes to store MEDIUMINT -- a medium integer, supports-8388608 to 8388607 (SIGNED), 0 to 16777215 (UNSIGNED), and three bytes are required for storage.
  • INT -- an integer that supports-2147493648 to 2147493647 (SIGNED), 0 to 4294967295 (UNSIGNED), and needs to be stored in 4 bytes
  • INTEGER -- same as INT
  • BIGINT -- a large integer that supports-9223372036854775808 to 9223372036854775807 (SIGNED), 0 to 18446744073709551615 (UNSIGNED), and needs to be stored in 8 bytes
  • FLOAT (precision) -- A floating point number. Precision <= 24 is used for single-precision floating point; precision is between 25 and 53, and is used for precision Floating Point. The FLOAT (X) has the same range as the FLOAT and DOUBLE types of the phase sequence, but the display size and decimal places are not defined. Before MySQL3.23, this is not a real floating point value and always has two decimal places. All the calculations in MySQL use dual precision, which may cause unexpected problems.
  • FLOAT -- a small menu precision floating point number. Supports-3.402823466E + 38 to-1.175494351E-38,0 and 1.175494351E-38 to 3.402823466E + 38, which requires 4 bytes of storage. If it is UNSIGNED, the range of positive numbers remains unchanged, but negative numbers are not allowed.
  • DOUBLE-a double-precision floating point number. Supports-1.7976931348623157E + 308 to-2.225074255072014e-308,0 and 2.225071095072014e-308 to 1.7976931348623157E + 308. If it is FLOAT, UNSIGNED will not change the positive number range, but the negative number is not allowed.
  • Double precision -- same as DOUBLE
  • REAL -- same as DOUBLE
  • DECIMAL -- stores a number as a string, and each character occupies one byte
  • DEC -- same as DECIMAL
  • NUMERIC -- same as DECIMAL
String column type

The string column type is used to store character data of any type, such as name, address, or newspaper. Below are available string column types in MySQL

  • CHAR -- character. A string of fixed length. Fill in spaces on the right to reach the specified length. It can be 0 to 155 characters in length. When you search for a value, spaces with the suffix will be deleted.
  • VARCHAR -- Variable Length character. A variable-length string with the suffix space removed when storing the value. Supports from 0 to 255 characters
  • TINYBLOB -- tiny binary object. 255 characters are supported. Storage with a length of more than 1 byte is required. Like TINYTEXT, the search is case sensitive. (0.25KB)
  • TINYTEXT -- supports 255 characters. The length must be + 1 byte. Like TINYBLOB, The Case sensitivity is ignored during search. (0.25KB)
  • BLOB-binary object. 65535 characters are supported. Storage with a length of + 2 bytes is required. (64 KB)
  • TEXT -- supports 65535 characters. Requires Length + 2 bytes of storage. (64 KB)
  • MEDIUMBLOB -- a binary object of medium size. 16777215 characters are supported. Storage with a length of + 3 bytes is required. (16 M)
  • MEDIUMTEXT-supports 16777215 characters. Storage with a length of + 3 bytes is required. (16 M)
  • LONGBLOB -- a large binary object. 4294967295 characters are supported. Storage with a length of + 4 bytes is required. (4G)
  • LONGTEXT -- supports 4294967295 characters. Storage with a length of + 4 bytes is required. (4G)
  • ENUM -- enumeration. There can be only one specified value, that is, NULL or "", with a maximum of 65535 values
  • SET -- a SET. There can be 0 to 64 values, all from the specified list
Date and Time column types

The date and time column types are used to process time data. data such as the date of the current day or the date of birth can be stored. Format: Y indicates year, M (before M) indicates month, D Indicates day, H indicates hour, M (after M) indicates minute, S indicates second. The following are available date and time column types in MySQL:

  • DATETIME -- format: 'yyyy-MM-DD HH: MM: ss', range: '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 ′
  • DATE -- format: 'yyyy-MM-DD ', range: '2017-01-01' to '2017-12-31 ′
  • TIMESTAMP -- format: 'yyyymmddhhmms', 'yymmddhhmms', 'yyyymmdd', and 'yymmdd'; range: '2017-01-01 00:00:00 'to '2017-01-01 00:00:00 ′
  • TIME -- format: 'hh: MM: ss'
  • YEAR -- format: 'yyyy, range: '20180101' to '20180101 ′
Numeric type
Column Type Required storage capacity
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
INTEGER 4 bytes
BIGINT 8 bytes
FLOAT(X) 4 if X <= 24 or 8 If 25 <= X <= 53
FLOAT 4 bytes
DOUBLE 8 bytes
DOUBLE PRECISION 8 bytes
REAL 8 bytes
DECIMAL(M,D) MByte (D+ 2, ifM < D)
NUMERIC(M,D) MByte (D+ 2, ifM < D)
? Date and Time Type
Column Type Required storage capacity
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 byte
String type
Column Type Required storage capacity
CHAR(M) MByte,1 <= M <= 255
VARCHAR(M) L+ 1 byte, in thisL <= MAnd1 <= M <= 255
TINYBLOB,?TINYTEXT L+ 1 byte, in thisL<2 ^ 8
BLOB,?TEXT L+ 2 bytes.L<2 ^ 16
MEDIUMBLOB,?MEDIUMTEXT L+ 3 bytes.L<2 ^ 24
LONGBLOB,?LONGTEXT L+ 4 bytes.L<2 ^ 32
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumerated values (maximum 65535)
SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (up to 64 members)

Original article address: MySQL field data type. Thank you for sharing it.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.