Wednesday, May 6

MySQL Data Types

in this tutorial, you will learn about MySQL data types and how to use them effectively in the MySQL database design.
Database table contains multiple columns with specific data types such as numeric or string. MySQL provides more data types other than just numeric or string. Each data type in MySQL can be determined by the following characteristics:
  • Kind of values it can represent.
  • The space that takes up and whether the values are fixed-length or variable-length.
  • Does the values of the data type can be indexed.
  • How MySQL compares the value of a specific data type.

Numeric Data Types

You can find all SQL standard numeric types in MySQL including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addition, MySQL also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except the BIT type. The following table shows you the summary of numeric types in MySQL:
Numeric TypesDescription
TINYINTA very small integer
SMALLINTA small integer
MEDIUMINTA medium-sized integer
INTA standard integer
BIGINTA large integer
DECIMALA fixed-point number
FLOATA single-precision floating-point number
DOUBLEA double-precision floating-point number
BITA bit field

String Data Types

In MySQL, string can hold anything from plain text to binary data such as images and files. String can be compared and searched based on pattern matching by using the LIKE operator or regular expression. The following table shows you the string data types in MySQL:
String TypesDescription
CHARA fixed-length non-binary (character) string
VARCHARA variable-length non-binary string
BINARYA fixed-length binary string
VARBINARYA variable-length binary string
TINYBLOBA very small BLOB (binary large object)
BLOBA small BLOB
MEDIUMBLOBA medium-sized BLOB
LONGBLOBA large BLOB
TINYTEXTA very small non-binary string
TEXTA small non-binary string
MEDIUMTEXTA medium-sized non-binary string
LONGTEXTA large non-binary string
ENUMAn enumeration; each column value may be assigned one enumeration member
SETA set; each column value may be assigned zero or more set members

Date and Time Data Types

MySQL provides types for date and time as well as a combination of date and time. In addition, MySQL also provides timestamp data type for tracking the changes of a row in a table. If you just want to store the year without date and month, you can use YEAR data type. The following table illustrates the MySQL date and time data types:
Date and Time TypesDescription
DATEA date value in ‘CCYY-MM-DD’ format
TIMEA time value in ‘hh:mm:ss’ format
DATETIMEA date and time value in ‘CCYY-MM-DD hh:mm:ss’ format
TIMESTAMPA timestamp value in ‘CCYY-MM-DD hh:mm:ss’ format
YEARA year value in CCYY or YY format

Spatial Data Types

MySQL supports many spatial data types that contain various kind of geometrical and geographical values as shown in the following table:
Spatial Data TypesDescription
GEOMETRYA spatial value of any type
POINTA point (a pair of X Y coordinates)
LINESTRINGA curve (one or more POINT values)
POLYGONA polygon
GEOMETRYCOLLECTIONA collection of GEOMETRY values
MULTILINESTRINGA collection of LINESTRING values
MULTIPOINTA collection of POINT values
MULTIPOLYGONA collection of POLYGON values

No comments:

Post a Comment