MySQL数据类型大全

Mysql常用数据类型主要包括以下三大类：

数值型：

• 整数类型：bit、bool、tiny int、small int、medium int、 int、 big int
• 浮点数类型：float、double、
• 定点数类型：decimal

字符串类型：

• 字符串：char、varchar、
• 文本类型：tiny text、text、medium text、longtext、
• 二进制文本类型：tiny blob、blob、medium blob、long blob

日期类型：date、datetime、timestamp、time、year

其他数据类型：binary、varbinary、enum、set、geometry、point、multipoint、linestring、multilinestring、polygon、geometrycollection等

1、数值型

1）整数型

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

2）浮点型(float和double)

设一个字段定义为float(6,3)，总位数是6位，整数部分最大是3位

如果插入数123.45678，实际数据库里存储的是123.457，

如果插入数12.123456，实际数据库里存储的是12.1234

如果插入数12.123456，实际数据库里存储的是12.1234，

如果插入数12.12，实际数据库里存储的是12.1200.

3）定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数，d<30且 d4）浮点数与定点数
为了能够引起大家的重视，在介绍浮点数与定点数以前先让大家看一个例子：

create table test (

c1 float(10,2),

c2 decimal(10,2)

);

insert into test values(131072.32,131072.32);

select * from test;

从上面的例子中我们看到c1列的值由131072.32变成了131072.31，这就是浮点数的不精确性造成的。

在mysql中float、double（或real）是浮点数，decimal（或numberic）是定点数。浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。

在今后关于浮点数和定点数的应用中，大家要记住以下几点：
1. 浮点数存在误差问题；
2. 对货币等对精度敏感的数据，应该用定点数表示或存储；
3. 编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
4. 要注意浮点数中一些特殊值的处理。

2、字符串

char和varchar的区别：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

请注意上表中最后一行的值只适用不使用严格模式时；如果MySQL运行在严格模式，超过列长度的值不保存，并且会出现错误。

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同，因为检索时从CHAR列删除了尾部的空格。通过下面的例子说明该差别：

CREATE TABLE vc (

v VARCHAR(4),

c CHAR(4)

);

INSERT INTO vc VALUES ('ab ', 'ab ');

SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;

varchar和text的区别：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。
查询速度：char > varchar > text

text和blob的区别
1.BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。
2.BLOB存储的数据只能整体读出。
3.TEXT可以指定字符集，BLOB不用指定字符集。

text和blob
在使用text和blob字段类型时要注意以下几点，以便更好的发挥数据库的性能。

①BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理.

②使用合成的（synthetic）索引。合成的索引列在某些时候是有用的。一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符 是没有用处的）。我们可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

③在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT *查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。这也是 BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你可以搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索BLOB或 TEXT值。

④把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中 的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

3、日期时间类型

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间

4、MySQL数据类型最佳实践

• 数据类型的选择原则，更简单或者占用空间更小：
  • 如果长度能够满足，整型尽量使用 tinyint、smallint、medium_int 而非 int。
  • 如果字符串长度确定，采用 char 类型。
  • 如果 varchar 能够满足，不采用 text 类型。
  • 精度要求较高的使用 decimal 类型，也可以使用 BIGINT，比如精确两位小数就乘以 100 后保存。
  • 尽量采用 timestamp 而非 datetime。相比 datetime，timestamp 占用更少的空间，以 UTC 的格式储存自动转换时区。