MySQL数据类型

MySQL 的数据类型有大概可以分为 5 种:

• 整数类型
• 浮点数类型和定点数类型
• 日期和时间类型
• 字符串类型
• 二进制类型等

要知道，MySQL本质上是一个存储，以Java为例，可以使用byte类型的地方使用了long类型问题不大，因为绝大多数的对象在程序中都是短命对象，方法执行完毕这块内存区域就被释放了，7个字节实际上不存在浪不浪费一说。但是MySQL作为一个存储，8字节的BIGINT放那儿就放那儿了，占据的空间是实实在在的。

1.整数类型

1.五种整数类型

• TINYINT ：1byte
• SMALLINT ：2byte
• MEDIUMINT ：3byte
• INT：4byte
• BIGINT ： 8byte
  

2.整形数据类型可用属性
（1）M

• 显示宽度 (在需要0填充的时候才有意义，否则不需要指定)
• 只是定义查询结果的显示宽度，并不能限制数据类型的大小范围，数据类型的大小由数据类型自身决定；比如int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间

（2）zerofill ：

• 需要配合M使用；
• 如果某列是zerofill，那么默认就是无符号；
• 指定了显示宽度M后，如果插入的数据的位数<M，那么左边使用0填充 ；

（3）unsigned ： 无符号类型(非负，默认为signed)

3.演示

CREATE TABLE test_int_width (
    a INT(5),
    b INT(5) UNSIGNED,
    c INT(5) UNSIGNED ZEROFILL,
    d INT(8) UNSIGNED ZEROFILL
) ENGINE=INNODB CHARSET=utf8;

INSERT INTO test_int_width VALUES(1, 1, 1, 1111111111);

SELECT * FROM test_int_width

4.总结
在开发中，我们经常会碰到有些定义整型的写法是int(n)，这种写法从我个人开发的角度看我认为是没有多大用

2.浮点数类型

• float(M,D) 和 double(M,D)
  M称为精度，表示总共的位数；D称为标度，示浮点型数据小数点之后的位数

create table test_float (
    num float(5, 2)
) engine=innodb charset=utf8;

insert into test_float values(1.233);
insert into test_float values(1.237);
insert into test_float values(10.233);
insert into test_float values(100.233);
insert into test_float values(1000.233);
insert into test_float values(10000.233);
insert into test_float values(100000.233);

• 小数位超过D位则四舍五入，即1.233四舍五入为1.23，1.237四舍五入为1.24
• （5,2）则表示总共支持五位，即小数点前只支持三位数，所以我们并没有看到1000.23、10000.233、100000.233这三条数据的插入，因为插入都报错了
• 当我们不指定M、D的时候，会按照实际的精度来处理。
• 如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。如果希望保证值比较准确，推荐使用定点数数据类型

3.定点数

CREATE TABLE test_decimal (
    float_num FLOAT(10, 2),
    double_num DOUBLE(20, 2),
    decimal_num DECIMAL(20, 2)
) ENGINE=INNODB CHARSET=utf8;

INSERT INTO test_decimal VALUES(1234567.66, 1234567899000000.66, 1234567899000000.66);
INSERT INTO test_decimal VALUES(1234567.66, 12345678990000000.66, 12345678990000000.66);

SELECT * FROM test_decimal

• float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据

• decimal无论写入数据中的数据是多少，都不会存在精度丢失问题；这就是我们要引入decimal类型的原因，decimal类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中。

• float/double在db中存储的是近似值，而decimal则是以字符串形式进行保存的

• decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理而decimal在不指定M、D时默认为decimal(10, 0)

4.日期类型

CREATE TABLE test_time (
    date_value DATE,
    time_value TIME,
    year_value YEAR,
    datetime_value DATETIME,
    timestamp_value TIMESTAMP
) ENGINE=INNODB CHARSET=utf8;

INSERT INTO test_time VALUES(NOW(), NOW(), NOW(), NOW(), NOW());

SELECT * FROM test_time 

4.1 YEAR

1. 4位写入

1. 在写入的时候可以以 4 位字符串或者4 位数字格式写入
   例如，输入'2010'或 2010，插入数据库的值均为 2010。
2. 4位的范围为 ‘1901’～’2155’

2. 2位写入

1. 以 2 位字符串格式表示的 YEAR，范围为 ‘00’ 到 ‘99’。
   ‘00’～’69’ 和 ‘70’～’99’ 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。
   ‘0’ 与 ‘00’ 的作用相同。
   插入超过取值范围的值将被转换为 2000。

2. 以 2 位数字表示的 YEAR，范围为 1～99。
   1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。
   注意，在这里 0 值将被转换为 0000，而不是 2000。

4.2 TIME

格式

TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

取值范围

TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。

插入的格式

可以使用各种格式指定 TIME 值，如下所示。
‘D HH：MM：SS’ 格式的字符串。
还可以使用这些“非严格”的语法：

• ‘HH：MM：SS’
• ‘HH：MM’、
• ‘D HH’
  这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。
• ‘SS’
• ‘HHMMSS’
  没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，‘101112’ 被理解为’10：11：12’，但是 ‘106112’ 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

注意：

• 为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。
  例如，读者可能认为 ‘1112’ 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 ‘12’ 和 12 被解释为00：00：12。
• 相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，‘11：12’ 表示 11：12：00，而不是 00：11：12。

4.3 DATE 类型

• DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。
• 日期格式为 ‘YYYY-MM-DD’，其中 YYYY 表示年，MM 表示月，DD 表示日。

1.插入方式
在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。

1.字符串插入

以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符中格式表示的日期，取值范围为 '1000-01-01'～'9999-12-3'。
例如，输入 '2015-12-31' 或者 '20151231'，插入数据库的日期为2015-12-31。

以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，在这里YY表示两位的年值。
MySQL 解释两位年值的规则：'00～69' 转换为 '2000~2069'，'70~99' 转换为 '1970～1999'。
例如:
	输入 '15-12-31'，插入数据库的日期为 2015-12-31；
	输入 '991231'，插入数据库的日期为 1999-12-31。

2.数字插入

以 YYMMDD 数字格式表示的日期，00~69 转换为 2000～2069，80～99 转换为 1980～1999。
例如:
	输入 151231，插入数据库的日期为 2015-12-31
	输入 991231，插入数据库的日期为 1999-12-31。

3.函数插入

使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

4.不严格语法
提示：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。

例如，'98-11-31'、'98.11.31'、'98/11/31'和'98@11@31' 是等价的，这些值也可以正确地插入数据库。

4.4 DATETIME 类型

• DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。

• 日期格式为 ‘YYYY-MM-DD HH：MM：SS’；其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

• 在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。
  1.字符串插入

以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期;
取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。

例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，
插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。

以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期;
在这里 YY 表示两位的年值。
与前面相同，'00～79' 范围的年值转换为 '2000～2079'，'80～99' 范围的年值转换为 '1980～1999'。

例如，输入 '14-12-31 05：05：05'，插入数据库的 DATETIME 为 2014-12-31 05：05：05；
     输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。
例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；
     输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

Mysql不严格语法：
任何标点符号都可用作日期部分或时间部分之间的间隔符。但是日期和时间之间必须用空格分开

例如，'98-12-31 11：30：45'、'98.12.31 11+30+35'、
'98/12/31 11*30*45' 和 '98@12@31 11^30^45' 是等价的，这些值都可以正确地插入数据库。

4.5 TIMESTAMP 类型

• TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符

• 日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。

• TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入数据时，要保证在合法的取值范围内。

提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

• TIMESTAMP 与 DATETIME有一个最大的区别是：

DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。
提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

• datetime占8个字节，timestamp占4个字节
• 由于大小的区别，datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407
• datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间
• datetime存储的时间与时区无关，timestamp存储的时间及显示的时间都依赖于当前时区
• datetime虽然直观，但不便于计算

5.字符串

括号中的M表示可以为其指定长度（字符个数）
char(M)

• CHAR(M) 为固定长度字符串，在定义时指定字符串字符个数，范围是 0～255 个字符 ，且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的在右边用空格补足；
• char实际占用的字节数即存储的字符所占用的字节数
• MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会

varchar(M)

• varchar为可变长度字符串，长度M的范围和编码方式有关；在utf8编码的数据库中其长度范围为0~21844
• varchar实际占用的字节数为存储的字符+1或+2或+3
• MySQL处理varchar类型数据时不会将结尾的所有空格处理掉

CREATE TABLE test_string (
    char_value CHAR(5),
    varchar_value VARCHAR(5)
) ENGINE=INNODB CHARSET=utf8;

INSERT INTO test_string VALUES('a', 'a');
INSERT INTO test_string VALUES(' a', ' a');
INSERT INTO test_string VALUES('a ', 'a ');
INSERT INTO test_string VALUES(' a ', ' a ');

SELECT LENGTH(char_value),LENGTH(varchar_value) FROM test_string

• varchar能存储的字符串长度和编码类型有关

1.MySQL要求一个行的定义长度不能超过65535即64K
2.对于未指定varchar字段not null的表，会有1个字节专门表示该字段是否为null
3.varchar(M):
    当M范围为0<=M<=255时会专门有一个字节记录varchar型字符串长
    当M>255时会专门有两个字节记录varchar型字符串的长度
    把这一点和上一点结合，那么65535个字节实际可用的为65535-3=65532个字节
所有英文无论其编码方式，都占用1个字节;
对于gbk编码，一个汉字占两个字节，因此最大M=65532/2=32766；
对于utf8编码，一个汉字占3个字节，因此最大M=65532/3=21844，上面的结论都成立

举一反三，对于utfmb4编码方式，1个字符最大可能占4个字节，那么varchar(M)，M最大为65532/4=16383，可以自己验证一下

同样的，上面是表中只有varchar型数据的情况，如果表中同时存在int、double、char这些数据，
需要把这些数据所占据的空间减去，才能计算varchar(M)型数据M最大等于多少。

varchar、text和blob

最后讲一讲text和blob两种数据类型，它们的设计初衷是为了存储大数据使用的，因为之前说了，MySql单行最大数据量为64K。

• text和varchar是一组既有区别又有联系的数据类型，其联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：

M>255时转为tinytext
M>500时转为text
M>20000时转为mediumtext

所以过大的内容varchar和text没有区别

• 单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来
• text可以指定text(M)，但是M无论等于多少都没有影响
• text不允许有默认值，varchar允许有默认值
• varchar和text两种数据类型，使用建议是能用varchar就用varchar而不用text（存储效率高），- varchar(M)的M有长度限制，之前说过，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。

至于text和blob，简单过一下就是text存储的是字符串而blob存储的是二进制字符串，简单说blob是用于存储例如图片、音视频这种文件的二进制数据的。