数据类型

数据类型的选择原则：所选择的类型越简单越好，能保存数值的类型越小越好 (选小不选大，所占字节数少)

常见数据类型

• 数值型

  • 整型

  • 小数

    • 定点数
    • 浮点数

• 字符型

  • 保存较短文本：char、varchar
  • 保存较长文本：text，blob (较长的二进制数据)
• 日期型

整型

如何设置无符号和有符号？

创建表时，如果不指定，则默认创建为有符号 (可以通过插入负数来验证)

CREATE TABLE t_int(
    t1 INT
);
insert into t_int values(-1111);

如果想要指定为无符号，则使用关键字 UNSIGNED

CREATE TABLE t_int2(
    t1 INT,
    t2 INT UNSIGNED
);

朝无符号列插入负数，最终数据为 0

如果插入的数值超出了整型范围怎么办？

• 会报警告 ( 5.7 中直接报错 )
• 默认插入临界值 ( 大于最大值则插入最大值，小于最小值则插入最小值 )

如果不设置长度，则会有默认长度：对整型来说，长度表示的是当列中数据长度不足指定长度时，使用 0 来左填充，但是必须搭配 zerofill 关键字使用

DROP TABLE IF EXISTS t_int2;
CREATE TABLE t_int2(
    t1 INT ZEROFILL,
    t2 INT UNSIGNED
);
INSERT INTO t_int2 VALUES(123, 123);
SELECT * FROM t_int2;

并且使用 zerofill 后该列会变为无符号列

小数

DECIMAL 能够表示的精确度比浮点数类型更加精确

浮点型

• double(M, D)
• float(M, D)

定点型

• dec(M, D)
• decimal(M, D) （ 和上面是一个东西 ）

M 和 D 是什么？

D：小数点后保留的位数

M：整数部分 + 小数部分的最大位数

如果不指定 M 和 D，执行下面这段 SQL，会报警告吗？为什么？

DROP TABLE IF EXISTS t_float;
CREATE TABLE t_float(
    f1 FLOAT,
    f2 DOUBLE,
    f3 DECIMAL
);
INSERT INTO t_float VALUES(123.1234, 123.1234, 123.1234);

报警告

打开表结构，发现 float 和 double 如果不指定 M 和 D，则没有位数限制

而 decimal 则默认为 M=10，D=0。即 decimal 默认不允许有小数位

定点型和浮点型区别

• 定点型的精确度较高，适合在插入数值的精度要求较高的场合使用 ( 比如货币运算 )

字符型

char 和 varchar

char 和 varchar 的区别

• char 表示固定长度的字符串；varchar 表示可变长度的字符串
• char 可以省略指定最大字符数，默认为 1；varchar 不可省略

binary 和 varbinary

类似于 char 和 varchar，但是这两个只能保存二进制字符串而不包含非二进制字符串

Enum

枚举类型，要求插入的值必须属于列表中指定的值之一

如果列表成员数量为 1~255，则需要 1 个字节存储

如果列表成员数量为 255 ~ 65535 则需要 2 个字节存储

CREATE TABLE t_enum(
    c1 ENUM('a', 'b', 'c')
);
INSERT INTO t_enum VALUES('a');
#插入不在列表中的
INSERT INTO t_enum VALUES('e');

Set 类型

和 Enum 类型，里面可以保存 0~64 个成员。和 Enum 最大的区别是：Set 类型一次可以选取多个成员，而 Enum 只能选一个

根据成员个数不同，存储所占的字节也不同

DROP TABLE IF EXISTS t_set;
CREATE TABLE t_set(
    s1 SET('a', 'b', 'c', 'd')
);
INSERT INTO t_set VALUES('a,b,c,d');

PS：set 和 enum 都不区分大小写

日期型

分类

• date：只保存日期
• time：只保存时间
• year：只保存年
• timestamp：保存日期+时间
• datetime：保存日期+时间

timestamp 和 datetime 的区别

• timestamp 支持的时间范围较小
• timestamp 和实际时区有关，更能反映实际的日期和时间 ( 把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回 )；而 datetime 只能返回插入时的当地时区
• timestamp 的属性受 mysql 版本和 SQLMode 的影响很大

关于区别2的测试

CREATE TABLE t_date(
    t1 DATETIME,
    t2 TIMESTAMP
);
INSERT INTO t_date VALUES(NOW(), NOW());

可以发现，插入后查询出来的都是一样的，这时我们修改数据库时区

SET time_zone="+9:00";

然后再次查询

SELECT* FROM t_date;

发现 timestamp 类型的那一列将原本插入的值转换为了当地时区