为了账号安全,请及时绑定邮箱和手机立即绑定

MySQL数据类型

标签:
MySQL

Summaryin 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

原文链接:http://outofmemory.cn/mysql/mysql-data-types

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消