Mysql笔记之 数据类型

Mysql笔记之 数据类型

1.整型类型：

类型名称	存储需求
TINYINT	1个字节
SMALLINT	2个字节
MEDIUMINT	3个字节
INT(INTEGER)	4个字节
BIGINT	8个字节

注意：

CREATE TABLE tb(
    id  INT(11),
    name VARCHAR(25)
)

说明：以上建表语句中INT(11)
其中11表示的是该数据类型指定的显示宽度，指定能够显示的数值中数字的个数。
显示宽度和数据类型的取值范围是无关的。显示宽度只是指明mysql最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。
显示宽度只是用于显示，并不能限制取值范围和占用空间。

---

2.浮点数类型和定点数类型

类型名称	存储需求
FLOAT	4个字节
DOUBLE	8个字节
DECIMAL(M,D),DEC	M+2个字节

解释：
1.FLOAT和DOUBLE分别为单精度浮点类型和双进度浮点类型
2.DECIMAL为定点类型
3.浮点类型和定点类型都可以用(M,N)来表示，其中M称为精度，表示有多少位
N称为标度，是表示小数的位数。
4.不论是定点类型学还是浮点类型，如果用户指定的精度超出精度范围，则四舍五入

注意：
在mysql中，定点数以字符串形式存储，在对精度比较高的时候，例如货币，使用DECIMAL比较好。还有要避免做浮点数的比较，和运算。

---

3.日期和时间类型

类型名称	日期格式	存储需求
YEAR	YYYY	1个字节
TIME	HH:MM:SS	3个字节
DATE	YYYY-MM-DD	3个字节
DATETIME	YYYY-MM-DD HH:MM:SS	8个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	4个字节

1.YEAR 类型是一个单字节类型用于表示年，在存储时只需要1个字节。
2.TIME 类型用在只需要时间信息的值，在存储时需要3个字节。
3.DATE 类型用在仅需要日期时，没有时间部分，需要3个字节存储。
4.DATETIME 类型用在需要同时包含日期和时间信息的值，在存储时需要8个字节。
5.TIMESTAMP 类型的显示格式和DATETIME相同，显示宽度固定在19个字符，存储需要4个字节，但取值范围比DATETIME小

DATETIME的取值范围为‘1000-01-01 00:00:00’~’9999-12-3 23:59:59’
TIMESTAMP的取值范围为‘1970-01-01 00:00:01’UTC~’2038-01-19 03:14:07’UTC

注意：
TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC(世界标准时间)格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

在TIME,或DATE类中的字段中插入当前时间，可以用CURRENT_DATE() 或NOW()
在DATETIME或TIMESTAMP的字段中插入当前时间，可以使用NOW()

---

4.字符串类型

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此L<=M和1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此L<2^8
TEXT	小的非二进制字符串	L+2字节，在此L<2^16
MEDIUMENT	中等大小的非二进制字符串	L+3字节，在此L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目
SET	一个设置，字符串对象可以由零个或多个SET成员	1,2,3,4或8个字节，取决与集合成员的数量(最多84个成员)

例:
一个VARCHAR(10)列能保存最大 长度为10个字符的一个字符串，实际的存储需要是字符串的长度L,加上1个字节以记录字符串的长度，对于字符”abcd”,L是4而存储要求是5个字节。

注意
1.VARCHAR ,BLOB和TEXT类型是变长类型
2.CHAR类型，尾部的空格将被删除掉
3.VARCHAR类型，值在保存和检索时尾部的空格仍保留。
4.TEXT列保存非二进制字符串，如文章内容、评论。当保存或查询TEXT列的值是，不删除尾部的空格
5.TINYTEXT 最大长度为255字符的TEXT列
6.TEXT最大长度为65535字符的TEXT列
7.MEDIUMTEXT最大长度为16777215字符的TEXT列
8.LONGTEXT最大长度为4294967295或4GB字符的TEXT列

---

5.二进制类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约(M+7)/8个字节
BINARY(M)	固定长度二进制字符串	M个字节
VARBINARY(M)	可变长度二进制字符串	M+1个字节
TINYBLOB(M)	非常小的BLOB	L+1字节，在此L<2^8
BLOB(M)	小BLOB	L+2字节，在此L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节，在此L<2^24
LONGBLOB(M)	非常大的BLOB	L+4字节，在此L<2^32

提示 ：BIN()函数将数字转换为二进制。

BINARY和VARBINARY 类型类似于CHAR和VARCHAR,不同的是他们包含二进制字节字符串。
BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 他们可容纳值的最大长度不同 。
BLOB列存储的是二进制字符串；TEXT列存储的是非二进制字符串。BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

---

6.数据类型的选择

6.1.整数和浮点数
如果不需要小数部分，则使用整数来保存；如果需要表示小数部分，则使用浮点类型。

6.2.浮点数和定点数
浮点数相对于定点数的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。但是由于浮点数容易产生误差，一次对于精确度要求比较高是时，建议使用DECIMAL来存储，DECIMAL在MYSQL中是以字符串存储的用于定义货币等精确度要求教高的数据。在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。另外两个浮点数进行减法和比较运算时也容易出问题，如果进行数值比较，最好用DECIMAL类型。

6.3.日期和时间类型
只需记录年份，使用YEAR；只记录时间，用TIME；
同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME.存储范围较大的日期使用DATETIME

6.4.CHAR 与 VARCHAR
区别：
a.CHAR是固定长度字符，VARCHAR是可变长度字符；
b.CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格。
c.CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之，使用VARCHAR
存储引擎对于选择CHAR和VARCHAR的影响：
a.MyISAM：最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
b.InnoDB：使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储，比较节省空间，所以对磁盘I/O和数据存储总量比较好。

6.5.ENUM与SET
a.ENUM只能取单值，它的数据列表是一个枚举集合。像性别就适合ENUM类型，只能取‘男’或‘女’ 一个值
b.SET可取多值。空字符串也是一个合法的SET值，在去多个值的时候，适合SET类型，比如兴趣爱好。

6.6.BLOB与TEXT
a.BLOB是二进制字符串，主要用于图片、音频信息等。
b.TEXT是非二进制字符串，用于存储纯文本。